• Global community
    • Language:
      • Deutsch
      • English
      • Español
      • Français
      • Português
  • 日本語コミュニティ
    Dedicated community for Japanese speakers
  • 한국 커뮤니티
    Dedicated community for Korean speakers
Exit
0

Query

New Here ,
Jun 19, 2020 Jun 19, 2020

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.

Views

161

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Community Expert ,
Jun 20, 2020 Jun 20, 2020

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

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
New Here ,
Jun 21, 2020 Jun 21, 2020

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.

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
New Here ,
Jun 21, 2020 Jun 21, 2020

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 ' '

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Community Expert ,
Jun 21, 2020 Jun 21, 2020

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

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
New Here ,
Jun 21, 2020 Jun 21, 2020

Copy link to clipboard

Copied

So that query gets me: (when using quotes)

Error Executing Database Query.
[Macromedia][SQLServer JDBC Driver][SQLServer]Invalid column name 'CP'.

 

and when using '

Error Executing Database Query.
[Macromedia][SQLServer JDBC Driver][SQLServer]The data types text and varchar are incompatible in the equal to operator.
 

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
New Here ,
Jun 21, 2020 Jun 21, 2020

Copy link to clipboard

Copied

LATEST

FIXED.  Colum type was text.  I had to change to varchar.  thank you for pointing me there.

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Resources
Documentation