How to include Filter in query subject SQL

[meeecog]

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) )

[bbtresoo]

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?

[meeecog]

Thanks

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

[bdbits]

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.