on 04-01-2011 4:03 PM
Hi Everyone,
For some reason, this command's where statement works fine in DBVisualizer, but when I use it in Crystal, with the date range criteria, the report doesn't return any data.
Any ideas?
Full SQL Statement
SELECT DISTINCT BI_CLOSE_DT, BI_SO_COM, BI_SO_NBR, BI_ACCT, BI_OPEN_DT, BI_SO_DET_KEY, BI_SO_STAT_CD, BI_SO_TO_ACCT, BI_SO_TO_CUST_NBR, BI_SO_TYPE_CD, BI_SRV_LOC_NBR
FROM BI_SO_DET
WHERE BI_SO_STAT_CD = 'X' and BI_SO_TYPE_CD IN ( 'NEW', 'NCBM', 'NEW-WF' ) and BI_SO_TO_ACCT IS NOT NULL and (BI_OPEN_DT >= to_date('1-jan-2005') and BI_OPEN_DT < to_date('31-dec-2005'))
Edited by: Johnbr on Apr 1, 2011 5:45 PM
Hi John,
What is the datasource you are using ? If you are using ODBC, try with a Native driver, because some times it can't translate your date fromat to crystal date format. Crystal can't understand few date formats.
or
Remvoe date range from your where clause and create a add command and create two parameters like from date and to date and use these parameters in your add command to filter the data.
Thanks,
Sastry
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Hi Sastry,
It's Oracle 11g via ODBC. It's a date time field DD/MM/YYYY HH:MM:SSAM
All I wanted to do was restrict the amount of data the query returned for some testing and further development of the report (return a dozen records instead of 17000). I was able to do this by using a simple extract statement instead:
and EXTRACT(YEAR FROM TO_DATE(BI_OPEN_DT)) > 2010
John
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Try changing your date format to '2005-01-01' & '2005-12-31'.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
85 | |
23 | |
11 | |
9 | |
8 | |
5 | |
5 | |
5 | |
5 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.