No Operator Matches the Given Name and Argument Type(S). You Might Need to Add Explicit Type Casts. - Netbeans, Postgresql 8.4 and Glassfish

No operator matches the given name and argument types. You might need to add explicit type casts -- PostgreSQL 14.1

Your immediate problem is the expression vente_T2-vente_T1 - you can't subtract a complete record from another record. You can only do that for columns. However in order to access the column within the CTE, you should give them a proper alias:

WITH vente_T1 AS (
SELECT count(*) as num_vente
FROM vente
WHERE date_vente BETWEEN '2020-01-01' AND '2020-03-31'
), vente_T2 AS (
SELECT count(*) as num_vente
FROM vente
WHERE date_vente BETWEEN '2020-04-01' AND '2020-06-30'
)
SELECT vente_T2.num_vente - vente_T1.num_vente
-- ^ column name ^ column name
FROM vente_T1
cross join vente_T2;

PostgreSQL: No operator matches the given name and argument types. You might need to add explicit type casts in codeigniter using exist function

Date literals in Postgres are surrounded by single quotes, which your query is not doing. But beyond this, you should ideally be using a prepared statement here, which handles proper escaping of literals for you. Codeigniter does not support prepared statements, but it does support query bindings:

$sql =  "SELECT id_anggota FROM karyawan WHERE EXISTS (";
$sql .= " SELECT 1 FROM pengunduran_diri WHERE tgl_pengunduran <= ?)";
$this->db->query($sql, array($row->end_date));

No operator matches the given name and argument types. You might need to add explicit type casts

To be clear what is happening:

create table pwd_test(id int, password varchar);

import psycopg2
import bcrypt
pwd = bcrypt.hashpw('test_pwd'.encode('utf-8'),bcrypt.gensalt())

pwd
b'$2b$12$DTSJSsuuhwgyMdSOqLmb0.4RAk.smxQERas/i7WcR3NKTPtLQfoPK'
# Note the b'
# From here [Binary adaptation](https://www.psycopg.org/docs/usage.html#adapt-binary)
# This gets converted to bytea

cur.execute('insert into pwd_test values(%s,%s)', [2, pwd])
-- On the INSERT it gets cast to a varchar
select * from pwd_test where id = 2;
id | password
----+----------------------------------------------------------------------------------------------------------------------------
2 | \x243262243132244454534a53737575687767794d64534f714c6d62302e3452416b2e736d7851455261732f6937576352334e4b5450744c51666f504b

# When you do the comparison you are comparing the varchar value in the table to the bytea type that is pwd.

cur.execute('select * from pwd_test where password = %s', [pwd])
---------------------------------------------------------------------------
UndefinedFunction Traceback (most recent call last)
<ipython-input-15-e2bde44ff261> in <module>
----> 1 cur.execute('select * from pwd_test where password = %s', [pwd])

UndefinedFunction: operator does not exist: character varying = bytea
LINE 1: select * from pwd_test where password = '\x24326224313224445...
^
HINT: No operator matches the given name and argument types. You might need to add explicit type casts.

# You need to cast the varchar to bytea
cur.execute('select * from pwd_test where password::bytea = %s', [pwd])

cur.fetchone()
id | password
----+----------------------------------------------------------------------------------------------------------------------------
2 | \x243262243132244454534a53737575687767794d64534f714c6d62302e3452416b2e736d7851455261732f6937576352334e4b5450744c51666f504b

So your options are do the cast of password to bytea in the query or change the column type of password to bytea.

No operator matches the given name and argument type. You might need to add an explicit type cast

I change my query just the @Id to @id:

private readonly string askForId = "SELECT * FROM Users WHERE Id = @id";

and change to QueryFirstAsync to QueryFirstOrDefaultAsync becuase I need know if dont find nothing.

public async Task<T> GetById(Guid entity)
{
try
{
connection.Open();
var result = await this.connection.QueryFirstOrDefaultAsync<T>(askForId, new { id = entity});
return result;
}
catch (Exception e)
{
throw e;
}
finally
{
connection.Close();
}
}

And change the entity to new { id = entity} and work fine the query

No operator matches the given name and argument type

You can't use a column alias on the same level where you define it. So the reference to "Quantity" in the expression "Quantity" * "Unit Price" refers to the real (varchar) column, not to the alias you have defined.

If you want to access the value of the alias, you need to use a derived table:

SELECT "Trade Date",
"Due Date",
"Reference",
"Type",
"Symbol",
"Quantity",
"Unit Price",
"Amount",
"Commission",
"Charge",
"VAT",
"Net Amount",
"W/H",
"Net Price",
"Quantity" * "Unit Price" as "Total"
FROM (
SELECT "Trade Date",
"Due Date",
"Reference",
"Type",
"Symbol",
to_number("Quantity", '99999999D99') AS "Quantity",
to_number("Unit Price", '99999999D99') AS "Unit Price",
to_number("Amount", '99999999D99') AS "Amount",
to_number("Commission", '99999999D99') AS "Commission",
to_number("Charge", '99999999D99') AS "Charge",
to_number("VAT", '99999999D99') AS "VAT",
to_number("Net Amount", '99999999D99') AS "Net Amount",
"W/H",
to_number("Net Price", '99999999D99')AS "Net Price"
FROM "public".aa
) t

However the much better solution would be to not store numbers in varchar columns. Numbers should be stored in columns defined as numeric or integer, never in columns defined as varchar or text

No operator matches the given name and argument type(s): What needs to be cast?

I believe you simply want:

SELECT foo.name,
bar.city,
MAX(foo.total - bar.tax_amount),
(CASE WHEN lower(foo.name) SIMILAR TO '%(foo|bar|baz)%' THEN true
ELSE false
END)
....
GROUP BY foo.name, bar.city;

The CASE expression has two forms. One searches for values for a given expression. This takes a column or expression right after the CASE. The second searches through various expressions, stopping at the first one. This takes the WHEN clause right after the CASE.

Or even more simply:

SELECT foo.name, bar.city,
MAX(foo.total - bar.tax_amount),
(lower(foo.name) SIMILAR TO '%(foo|bar|baz)%')
....
GROUP BY foo.name, bar.city;

The CASE is not needed.



Related Topics



Leave a reply



Submit