Forum Discussion
SQL WHERE Statement
I am trying to write a query where the date used could be one of 3 depending on if the dates are valid or not.
In the screenshot below I have managed to create a field PODate to contain the correct date that I want to see in the report depending when whether the ConfDelivDate, AvailShipDate or ReqDate is valid.
In the Crystal Report that this originated in the PODate can be used to group data by and also as part of the data selection.
In SQL I can use the PODate for Ordering the data.
However, when I use it in the WHERE statement I get an error.
Any ideas on what I should be putting in the WHERE clause?
3 Replies
You can't use the [PODate] label in the WHERE clause. You have to copy-and-paste (as they need to remain identical in both locations) the CASE...END statement from the SELECT block to the WHERE block - being sure to leave off the trailing "AS PODate".
So, your WHERE panel would change:
AND (PODate <= GETDATE())To:
AND ( CASE WHEN ... THEN ... WHEN ... THEN ... ELSE ... END <= GETDATE() )The only place you can reference the [PODate] (based on your screenshot) is in the ORDER BY panel.
Cheers,
Lain
- Thanks, that gives me just what I wanted
Helen- WHERE is a pre-processor command, while SELECT and ORDER by are post-processor commands.
You can not use alias defined in post in a pre command.
