Print Page - How to include Filter in query subject SQL

Title: How to include Filter in query subject SQL
Post by: meeecog on 30 Apr 2014 05:33:23 PM

Hello All

We have a requirement to make it easy to our end users while selecting data based on the specific date ..It's done in people soft..same we want to implement in cognos FM ..

I used below SQL .. it's validated..but I'm not able to come out of prompt screen ..it's keep on asking for value..pls advice..

Select
   A.EMPLID,
A.EFFDT,
   A.EFFSEQ
From
   HR.PS_JOB A

WHERE ( A.EFFDT =
(SELECT MAX(A_ED.EFFDT) FROM HR.PS_JOB A_ED
WHERE A.EMPLID = A_ED.EMPLID
AND A.EMPL_RCD = A_ED.EMPL_RCD
AND A_ED.EFFDT <= #prompt('dd')#) - This is where I want to the prompt..
AND A.EFFSEQ =
(SELECT MAX(A_ES.EFFSEQ) FROM HR88.PS_JOB A_ES
WHERE A.EMPLID = A_ES.EMPLID
AND A.EMPL_RCD = A_ES.EMPL_RCD
AND A.EFFDT = A_ES.EFFDT) )

Title: Re: How to include Filter in query subject SQL
Post by: bbtresoo on 05 May 2014 11:08:43 AM

Quote from: meeecog on 30 Apr 2014 05:33:23 PM
Hello All

We have a requirement to make it easy to our end users while selecting data based on the specific date ..It's done in people soft..same we want to implement in cognos FM ..

I used below SQL .. it's validated..but I'm not able to come out of prompt screen ..it's keep on asking for value..pls advice..

Select
   A.EMPLID,
A.EFFDT,
   A.EFFSEQ
From
   HR.PS_JOB A

WHERE ( A.EFFDT =
(SELECT MAX(A_ED.EFFDT) FROM HR.PS_JOB A_ED
WHERE A.EMPLID = A_ED.EMPLID
AND A.EMPL_RCD = A_ED.EMPL_RCD
AND A_ED.EFFDT <= #prompt('dd')#) - This is where I want to the prompt..
AND A.EFFSEQ =
(SELECT MAX(A_ES.EFFSEQ) FROM HR88.PS_JOB A_ES
WHERE A.EMPLID = A_ES.EMPLID
AND A.EMPL_RCD = A_ES.EMPL_RCD
AND A.EFFDT = A_ES.EFFDT) )

Hi,

how do you create this line AND A_ED.EFFDT <= #prompt('dd')#?
if I well understand you don't need a prompt screean to show up?

Title: Re: How to include Filter in query subject SQL
Post by: meeecog on 29 May 2014 03:01:21 PM

Thanks

I want to give prompt to the user ..that's why I added that line in my SQL ..

Title: Re: How to include Filter in query subject SQL
Post by: bdbits on 04 Jun 2014 04:56:18 PM

Your #prompt macro has no default value so it is required. It will not run without you supplying a value. If you enter a valid value for A_ED.EFFDT does it work?

Put the SQL into your favorite query tool (e.g. SSMS or PL/SQL Developer) and make sure it runs there with a value in place of the #prompt. Then put the #prompt in and supply the value you were using when the query ran successfully.

COGNOiSe.com - The IBM Cognos Community

IBM Cognos 10 Platform => Cognos 10 BI => Framework Manager => Topic started by: meeecog on 30 Apr 2014 05:33:23 PM