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
How to Get Multiple Columns from Table Using Jpa
How to Test a Void Method Using Junit And/Or Mockito
How to Update Thousands of Records into MySQL Db in Milliseconds
How to Valid @Requestheader in Spring Boot
Update Objects in One List Based on Values from Second One Using Streams
Requestmethod Post Not Supported || Spring Login
How to Replace Non-Printable Unicode Characters in Java
Launch New Activity from Fragment in Android Studio
Selenium Webdriver: Clicking on Elements Within an Svg Using Xpath
Springboot Data - No Property Registration Found
Java 8: How to Write Lambda Stream to Work With Jsonarray
Selenium Basic Authentication Via Url Headlesschrome ( on Linux Server)
How to Solve the "Failed to Lazily Initialize a Collection of Role" Hibernate Exception
Query in Repository With Limit
How to Find Index of First Vowel in a String