Date Prompt in Custom SQL Report 1

[MFraz]

Has anyone tried to put a date prompt in an Impromptu Report that is created using a Custom SQL statement? I have a complicated SQL statement that uses a string prompt, a numeric prompt and a date prompt. The string and the numeric work fine, the date does not. If I hardcode the date in the SQL (Date <={d 2002-4-11})there is no problem.

When I use the prompt the error occurs if you Verify the statement, the syntax in the WHERE is this:

Date <=?CloseBegDate?

The error is:

An unexpected token &quot;'2002-04-16'&quot; was found following &quot;alendar_Date <= DATE&quot;. Expected tokens may include: &quot;<space>&quot;. SQLSTATE=42601

Cognos seems to be inserting todays date rather than the default value set in the prompt and it doesn't seem to be constructing the WHERE clause properly.

Any help would be greatly appreciated, we are stuck...

 

[griffindm]

If you are not concerned with the time portion of the date, try using the prompt as a string, and hardcode the string conversion in the SQL, as in [Oracle]

... AND DATE <= TO_DATE(?CloseBegDate?, 'YYYY-MM-DD')

If this is MS SQL Server, try the Convert function for the same effect.

Regards,

Dave Griffin

 

[MFraz]

Thanks for such a quick response, Dave.

We have been playing with the idea of using a string prompt. However, we cannot seem to get the syntax correct. We are using a DB2 database and the error we get when we do what you suggest is:

... The syntax of the string representation of a datetime value is incorrect. SQLSTATE=22007

Here is the WHERE:
Date <= DATE(?StringDate?)
We have tried defaulting this prompt to '2002-04-01' and
2002-04-01...

This works fine when hardcoded:
Date <= DATE('2002-04-16')

Thanks again for your suggestion, I appreciate it.

Melissa

 

[srathinaraj]

Me too..I am using DB2 and while using the promt( whether Date or String) facing the same issue.Any help?
_Raj

 

[griffindm]

_Raj,

Try using the To_Date function within Impromptu to get the syntax it is using to convert a string to a date. The part you are missing is the syntax for the argument on the expected date 'mask' of the string. This is assuming that the SQL generated includes the conversion, which is where you should look to see what that syntax is.

Hope this helps,

Dave Griffin


The Decision Support Group
Reporting Consulting with Cognos BI Tools
&quot;Magic with Data&quot;
Want good answers? Read FAQ401-2487 first!

http://www.decisionsupportgroup.com

 

[vtj]

I had the same issue with DB2. I was told to add the line:
NOSCAN=0 in each of the Schema entries in the DB2CLI.INI file. The Cognos case number was 3094378

I hope this helps

VTJ