How do you use a list of values with an object using the @prompt function in Freehand SQL? when i run the query i should get a prompt and able to see the list of values for that prompt.
Thanks,
RC
rc7bb (BOB member since 2003-12-01)
Use prompt function.You have to specify all the valid values in the function itself unlike universe based LOV’s where you can use list from a lov of an object.
syntax
var1 in @prompt(‘select var1 values’,‘A’,{‘l1’,‘l2’,‘l3’},MULTI,FREE)
Reema
reemagupta (BOB member since 2002-09-18)
Lists of Values are not supported when using FreehandSQL. If you utilize freehand SQL there is no associated universe so BO doesn’t associate any sort of SQL or Result set to the object in question.
digpen 
(BOB member since 2002-08-15)
Dear all,
In continuation to this thread, I have created a report with 2 free hand sql’s. It has start and end date as prompts.
For sql 1
e.Start_Date BETWEEN @variable('3 This Year Start Date dd-mon-yyyy') AND @variable('4 This Year End Date dd-mon-yyyy')
and sql 2
e.Start_Date BETWEEN @variable('5 Last Year Start Date dd-mon-yyyy') AND @variable('6 Last Year End Date dd-mon-yyyy')For some test, I changed it to
e.Start_Date BETWEEN @prompt('3 This Year Start Date dd-mon-yyyy','D',{'01-JAN-2001'},MONO,FREE) AND @prompt('4 This Year End Date dd-mon-yyyy','D',{'01-JAN-2001'},MONO,FREE)
and
e.Start_Date BETWEEN @prompt('5 Last Year Start Date dd-mon-yyyy,'D',{'01-JAN-2001'},MONO,FREE) AND @prompt('6 Last Year End Date dd-mon-yyyy,'D',{'01-JAN-2001'},MONO,FREE)It started showing me 01-JAN-2001 in LOV, which is correct.
I then changed back to original sql with @variable. For 1st sql, values button is greyed out, but for 2nd it still shows LOV as 01-JAN-2001.
It this known issue or any suggestions to get rid of this lov for 2nd sql?
Many thanks.
Shiv (BOB member since 2004-03-17)
In the second one, there’s not a closing quote after both of the “yyyy”. I don’t know if this makes a difference.
KSG (BOB member since 2002-07-17)
Hello KSG,
Thanks for your reply. Sorry, there should be closing bracket.
But I replaced this @prompt sql with the original @variable sql where there is no default value, and it still shows me ‘01-JAN-2001’ in LOV for 2nd sql.
How to get rid of this LOV for 2nd sql?
Many thanks.
Shiv (BOB member since 2004-03-17)
Shiv,
Maybe you can get rid of that by changed the text of your prompt slightly, just a character here or there. This usually solves prompt problems when using universe …
HenkK
HenkK 
(BOB member since 2004-03-02)
Thanks for your reply HenkK, I will try it.
Best Regards.
Shiv (BOB member since 2004-03-17)