Copy link to clipboard
Copied
OK. Why isn't this working? I have ben out of CF since about 2008.
SELECT *
FROM PURCHASEORDERTABLE
WHERE shipdate IS '#dateformat(Now(), "MM/DD/YYYY")#' AND Company IS CP
Having an issue with the AND statement.
Copy link to clipboard
Copied
"IS" is not a valid comparison operator in SQL for what you're trying to do. Try:
WHERE shipdate = '...' AND company = '...'
I'm also not sure what you're doing with the company comparison - you can't just refer to variables without any sort of identifier so that SQL knows it's a variable. Where does CP come from? Are you trying to do something in CF to compare them?
Dave Watts, Eidolon LLC
Copy link to clipboard
Copied
company is a database field and CP is a company. There are 4 in the same DB. So I am trying to get shippingdates for the company CP. I have also tried your suggestion before posting. doesn't work.
Copy link to clipboard
Copied
[Macromedia][SQLServer JDBC Driver][SQLServer]Invalid column name 'CP'.
The error occurred in C:/ColdFusion2018/cfusion/wwwroot/TCN/cpindex.cfm: line 9
7 : SELECT *
8 : FROM PURCHASEORDERTABLE
9 : WHERE enterdate = '#dateformat(Now(), "MM/DD/YYYY")#' AND Company = "CP"
10 : </cfquery>
Not sure why it is looking at CP as a column. It does this with both " " and ' '
Copy link to clipboard
Copied
First, I'd try to run your test queries in a database console instead of in CF. That'll help you separate CF errors from SQL errors. If you're using SQL Server, you can use SQL Server Management Studio. You won't be able to use any CFML expressions when you're doing this, of course, but it'll force you to separate those out and see where you're introducing the error.
Second, you could try isolating each part of your WHERE clause. So, you'd try something like:
SELECT * FROM PURCHASEORDERTABLE
WHERE Company = 'CP'
and see if that works. If it does, there's probably some problem with the first part of the WHERE clause. I don't see anything obviously wrong with it from here, but there's a lot I can't see.
Dave Watts, Eidolon LLC
Copy link to clipboard
Copied
So that query gets me: (when using quotes)
|
and when using '
|
Copy link to clipboard
Copied
FIXED. Colum type was text. I had to change to varchar. thank you for pointing me there.