Inserting a macro in to sql

[Jyothi Rani]

Hello All,

I got a sql query which I will be assigning to a query subject  in Report studio. I got a required filter(date). I need to insert a macro for run time prompting.

The sql query is like this,
SELECT *
             FROM ABCD
            WHERE XYZ <> 'B'
    AND DATE(PQR_TSTMP) >=  <FROM DT>
              AND DATE(PQR_TSTMP) <=  <TO DT>
         AND C_I_C IN (<CIC>)


So where should I need to insert the date promt(d_prompt) in the above sql query, So that when I run the report , It has to prompt for date.

Thanks.
Jyo....

[cognostechie]

..assigning to a query subject  in Report studio..

Query subject in Report Studio or Framework Manager ?

Cognos has a prompt macro which can be used for this and you can use that in Framwork Manager. Any report using that table/query subject will inherit that prompt. This is usually done when you are dealing with the HR module.

SELECT *
             FROM ABCD
            WHERE XYZ <> 'B'
    AND DATE(PQR_TSTMP) >=  #prompt('Name of the Prompt ','date','put default value here')#
              AND DATE(PQR_TSTMP) <=  #prompt('Name of the Prompt ','date','put default value here')#
         AND C_I_C IN (<CIC>)

[blom0344]

I think you need to stay within Cognos syntax to work with prompts this way:

SELECT *
             FROM ABCD
            WHERE XYZ <> 'B'
    AND CAST_TIMESTAMP(PQR_TSTMP) >=  #prompt('Name of the Prompt ','date','put default value here')#
              AND CAST_TIMESTAMP(PQR_TSTMP) <=  #prompt('Name of the Prompt ','date','put default value here')#
         AND C_I_C IN (<CIC>)