The statement below, when parse returns … PARSE FAILED: Invalid Definition
->>- Why??
@Select(Contracts\Contract ID) = @Prompt(‘Enter desired Contract ID’, ‘A’, ‘Contracts\Contract ID’,‘MULTI’, ‘CONSTRAINED’)
If the Contract ID is a numeric type, then change ‘A’ to ‘N’ and it should work.
Mohan
Listserv Archives (BOB member since 2002-06-25)
This is not parsing because you mixed = and MULTI
Either change = to IN or change MULTI to MONO and all will be fine.
Kerry
“Caine, Carol-Ann” wrote:
The statement below, when parse returns … PARSE FAILED: Invalid Definition – Why??
@Select(Contracts\Contract ID) = @Prompt(‘Enter desired Contract ID’, ‘A’, ‘Contracts\Contract ID’,‘MULTI’, ‘CONSTRAINED’)
Listserv Archives (BOB member since 2002-06-25)
round 2. now that i’ve established that i want to use @prompt in my query i have a new question. Here’s my syntax:
@prompt(‘What section?’, ‘A’, ‘??’, ‘multi’, ‘constrained’)
the only other arguement i’m particularly concerned about is the third one, regarding LOVs. I’d like to refer to an object’s LOVs but since this is FREE HAND SQL report - it’s not associated with a universe in particular… so how do i get this prompt to return an LOV for users to choose from?? Entering the values by hand within {} is not appropriate (or dynamic)
help!
ROD
Listserv Archives (BOB member since 2002-06-25)
In a message dated 10/21/00 1:39:21 PM Central Daylight Time, wetsel_rod@BAH.COM writes:
round 2. now that i’ve established that i want to use @prompt in my query
i have a new question. Here’s my syntax:
@prompt(‘What section?’, ‘A’, ‘??’, ‘multi’, ‘constrained’)
the only other arguement i’m particularly concerned about is the third one, regarding LOVs. I’d like to refer to an object’s LOVs but since this is FREE HAND SQL report - it’s not associated with a universe in particular… so how do i get this prompt to return an LOV for users to choose from?? Entering the values by hand within {} is not appropriate (or dynamic)
If you can’t enter the values within {} then you are out of luck. The only way to associate a list of values with a prompt is to use an object. The only way to use an object is to use a universe. Since you are using freehand SQL you will not be able to use this feature.
Alternative: make a small universe with exactly the objects (SQL) that you are using in your freehand query. Then you will be able to create dynamic LOV queries for your prompts.
Regards,
Dave Rathbun
Integra Solutions
www.islink.com
Listserv Archives (BOB member since 2002-06-25)
For people trying out this prompt and still getting an error, maybe its a good to know that the last two parameters of the prompt function are not proper.
incorrect syntax:
>@prompt('What section?', 'A', '??', 'multi', 'constrained')correct syntax:
>@prompt('What section?', 'A', '??', multi, constrained)regards,
Arash
arashz 
(BOB member since 2006-06-08)
Hi There,
Can you combine a "@prompt(‘What section?’, ‘A’, ‘??’, multi, constrained) " with a LIKE ‘%<Value of @prompt>%’ oracle in the designer? I tried doing this but i get a syntax error…
thanks in advance,
Antz
antzcp04 (BOB member since 2006-08-04)
Hi Antz,
the following free-hand SQL works well for me:
SELECT *
FROM dwhdim.dt_currencies
WHERE currency_code LIKE '%'||@prompt('1. what currency:','A',,mono,free)||'%'
Marek Chladny 
(BOB member since 2003-11-27)
Great! Exactly what i need. Thanks soo much!
antzcp04 (BOB member since 2006-08-04)
You are welcome 8)
Marek Chladny (BOB member since 2003-11-27)
I am having a similar problem. Du to performace issues I was enforced to use freehand sql statements for my BO reports. Nevertheless I do need a “dynamic” LOV to prompt the user for values, currentliy in the db.
Besides the mentioned alternative “small universe” isn’t there a way to do this otherwise?! I have heared something of Visual Basic workarounds.
If you list further alternatives for “dynamic LOVs when using freehand SQL” please try to give some pros and cons too, if you can.
edited:
I already found out, that it could also work with two data providers, one for the list and the other is the free-hand SQL. But the problem could be that the dataprovider for the LOV also brings up the performance issues, I already mentioned. Remember, that is the reason why I switched to fre-hand SQL in the first place… 
prinzartus 
(BOB member since 2006-09-07)
It is said that free hand sql is needed because of performance reasons. Why it free hand sql better for that than a universe, for giving hints maybe?
The prompt specified can be built in a universe just as well.
My point is, I see no reason for free hand at all. So because of the maintenance nightmare, don’t use it at all 
!
HenkK
HenkK 
(BOB member since 2004-03-02)
Tell that to my boss!
We had some reports that performed pretty bad. Our customer cant wait more than half an hour for his ad-hoc reports (and even BO resets the connection or stops the quiery building or something after one hour; i have been told). We found out that free hand SQL perform much better, so we have some free hand sql DPs as workaround for those performance issues. My colleagues told me that BO even created the sam SQL stmtns as the “free hander people” did, nevertheless, the Free Hand DP based reports perform in still acceptable time. Still we sometimes wait for 1-2 minutes with special reports. Okay, we do handle lots of data, but I wonder wheter we did miss some performance guidlines or performance settings …
:?
Maintainance is horrible, you are right on this one!
prinzartus 
(BOB member since 2006-09-07)
Right now I can’t think of any reason why a free hand sql would be faster than a universe based sql. It almost sounds like a challenge … 
What database are you on? SQL can be tuned in a lot of ways, you can even use hints in your universe.
HenkK
HenkK (BOB member since 2004-03-02)
As far as I know our SQL Guru tried to produce the Queries by himself to test the performance, and it turned out, that his SQL Query performed quite well. Then we used his Query as FH DP and performace was acceptable. Since then, whenever we encounter “low performnce”, the “guru” provides us (Designer and ReporteBuilder) with his SQL-stmt.
I am also not a friend of free hand SQL DPs because it seems to be dirty walkarounds. But as long as I dont find a clean alternative for those performance issues (with the help this forum perhaps) we will stick to FH-SQL even more.
We do process tables with 3 million rows for example. Without the use of free hand they reports often take to long to be produced. I dont know if the large tables are the reason for our performance issue, maybe you can tell me about your performance experiences.
prinzartus (BOB member since 2006-09-07)
If you use Oracle DB:
Is the big table partitioned? Are there all necessary indexes? Are all table and index statistics gathered?
In my previous job we had tables with a couple of 100 millions of rows and the performance was still OK.
Marek Chladny 
(BOB member since 2003-11-27)
As a do not want to hi-jack this thread, but I am VERY interesting about performance issues with BO, I am going to open a new thread now…
prinzartus (BOB member since 2006-09-07)
Hi There,
Speaking of query testing. How could i test the query time of webi reports whether the queries are efficient. I was thinking of a functionality like view data in deski, but webi does not have that. I was thinking of copying the query statement generated by the report and run in from a third party software tool like toad…
thanks,
Antzcp04
antzcp04 (BOB member since 2006-08-04)