#prompt in SQL

[mrcool]

Hi All,

I am building a SQl in which I have a date prompt along with some value prompts on the report. #prompt syntax is working for all the value prompts except date prompt.
My back end is oracle and the column I am using has datatype of TIMESTAMP. I have casted the TIMESTAMP Column to date and applying the filter condition but it is not working.
Below is the condition Iam using

WHERE cast(Reporteddate as date) BETWEEN
CAST(CAST(#prompt('P_DateFrom')# AS varchar(10)) AS date)
                AND           CAST(CAST(#prompt('P_ToDate')# AS varchar(10)) AS date)

When I run the report with out this filter the above date column is fetching with default timestamp with all the date values even after casting to date.
For Ex:
22/12/2010 12:00:00 AM
23/12/2010 12:00:00 AM
Is this the reason for #prompt failure for date prompt?

Guru's You help is required in this.. :)

Thanks in advance

Mrcool

[cognostechie]

Try this -

Reporteddate between #prompt('P_DateFrom','Date''2010-01-01''','current_date')# and #prompt('P_DateTo','Date''2010-01-01''','current_date')#

No need to cast the timestamp date to date. I have it working in a FM Model like this with Oracle as the DB. 

[mrcool]

Thanks Mate..Will try this and let you know..

Cheers,
Mrcool

[cognostechie]

Sorry, what I suggested earlier will work but will still cast the Date as character.

You can also use this:

Reporteddate between #prompt('P_DateFrom','Date','current_date')# and #prompt('P_DateTo','Date','current_date')#

You can also remove current_date if you do not want to use that as default.

[mrcool]

Hi Cognostechie,

when I am using below condition the SQL is not validating in the report:

A.ReportedDate   between #prompt('P_Authorised_Date','Date')# AND #prompt('P_Authorised_ToDate','Date')#
What could be the reason?

Below condition worked but the report is still throwing error. :(
A.ReportedDate  Between #prompt('P_Authorised_Date','Date','Current_Date')# AND #prompt('P_Authorised_ToDate','Date','Current_Date')#

When I tried to check the formate in which the date is passing by selecting #prompt('P_Authorised_Date','Date','Current_Date')# in the select and choosing current date(17-01-2011) in the date prompt I could see the result as 1993.
This could be the reason for the error?


Thanks,
Mrcool

[cognostechie]

Your original post said you are trying to build this SQL so I presumed you are doing this in Framework Manager. Do this in FM, not in Report Studio. Once it starts working, then create two Prompts in Report Studio and map those prompts to these prompt macros. Look at this SQL. It's working perfectly in Framework Manager in one single query subject with Oracle as the DB. The data type of 'RequiredDate' is Datetime in Oracle .

Select
           O.*
From
   Orders O
where
O.requireddate between #prompt('From_Date','date''2010-01-01','current_date')# and
#prompt('To_Date','date''2010-01-01','current_date')#

When it prompts you for the date, put the date in the format 2010-08-01  (YYYY-MM-DD). It should work. This will require you to use a Text Box prompt in Report Studio but start with this and I will tell you later how to use a Date prompt.

Try to make it work in FM first. It will not work in the databse SQL editor because the prompt here is a Cognos macro which will not be recognised by Oracle

The way it is supposed to work is that it will prompt you to enter the date, then it will hard code the date in the SQL. In your example (17-01-2011), it basically did 2011 - 18 (17+1) and made it 1993. When it prompt you enter the date in the format I mentioned above.

[mrcool]

Thank You CognosTechie...
I do not have FM for this report. I am using SQl object for this report with my query. I can undertand that it will not work in SQL editor but the problem here is, it is validating in Cognos nad failing while running the report.

Thanks,

mrcool

[mrcool]

Finally resolved the issue with below logic:

trunc(COMPLETEDTE) between TO_DATE(#sq(prompt('P_Authorised_Date', 'Date'))#, 'YYYY-MM-DD') and TO_DATE(#sq(prompt('P_Authorised_Date2', 'Date'))#, 'YYYY-MM-DD')

Thank you all for your suggessions..

Cheers,
Mrcool

[cogsol_11]

Cognostechie,

The information you have provided is certainly helpful. I have a similar problem and would like to get your thoughts on it:

I have a SP in Oracle which does a select * on a table with one date column. The date column is having data in the format mm-dd-yyyy.

On importing this SP in FM, I will need to pass the parameter from a prompt to the i/p argument of the SP.

Another constraint being if the user does not enter the value, prompt should assume current date as the value.

Following your earlier example i did get the text box prompt working when values are typed in like 11-May-2008. But I would ideally need this to work with Date prompts (calendar widget).

Can you please shed some light on how to implement this..

Thanks