Max Function in @Prompt

[Former Member]

Hi,

We have a week dimension in our Cal table. The user wants to use a condition where if he will enter week number (for eg: 13-2009) then data of that particular week should be displayed and if he will enter ' * ' then data of last week in the table will be displayed.

To fetch the last week we can use max(cal.week). But how to use it in prompt? Because when I am using max function then getting an error that group functions are not supported in prompt. Below is the prompt condition I am using:

CAL.WEEK IN (decode(@Prompt('Enter week (or * for last week)', 'A',, MONO,FREE),'*',(MAX(CAL.WEEK)),@Prompt('Enter week (or * for last week)', 'A',, MONO,FREE)))

I tried it by creating an object for MAX(CAL.WEEK) and then tried to use it in prompt, but since prompt doesnot support it, this object was not visible in the object list while creating the prompt.

Please help.

Thanks,

Kirti

[MariannevL · Accepted solution]

The problem you are facing is that a condition containing a grouping function belongs in the having close, not in the where...

There is no way to create something in the universe that will be put in the having clause.

So what you need is to put it in as subselect, which can go in the where.

Try the predefined filter like this...

(CAL.WEEK = @Prompt('Enter week (or * for last week)', 'A',, MONO,FREE) 
OR (@Prompt('Enter week (or * for last week)', 'A',, MONO,FREE)='*' 
   AND CAL.WEEK=(SELECT max(C2.WEEK) FROM CAL C2)))

Do not forget the brackets around the two conditions with OR

Good luck,

Marianne

[Former Member]

Hi,

Try with following syntax:

CAL.WEEK IN (decode(@Prompt('Enter week (or * for last week)', 'A',, MONO,FREE),'*',@Prompt('Enter week (or * for last week)', 'A','Classname/Objname', MONO,FREE)))

where create a object for MAX(CAL.WEEK) in required class and use it above.

Cheers,

Suresh Babu Aluri.

[Former Member]

Hi Suresh,

Thanks for your reply. I have already tried this way, but the problem is that @prompt does not support any group function, hence even if I use this MAX function and create a separate object and then try to use it in this statement, it is not at all visible in the object lists(any object with any group function is not visible during the definition of @prompt function

Kindly suggest some other workaround if possible.