Prompting in Freehand SQL

[Arthur1]

Hi,

Is it possible to use @prompts while using FreeHand SQL?

I seem to get an error when I try and do so. I am using a Sybase DB.

 

[Sridharan]

What is the error message you are getting????

Provide the prompt syntax that you are using so that we can have a look at it to provide suggestions/input!

Sri

 

[blom0344]

Free-hand SQL is just what it is, it is a direct SQL statement that is parsed by the database itself, it does not use the middle-layer you will get when using a universe database provider. Straight forward SQL will not hold a prompt, since there is no cursor to hold the contents of a variable. I do not know if Sybase has an alternative around this, but I expect not..............

T. Blom
Information analyst
tbl@shimano-eu.com

 

[Sridharan]

Hi,

Infact Free Hand SQL can use Prompts both @Variable() or the @Prompt(). The only restriction I see is that LOVs have to hardcoded and cannot give the Class\Object names since the Query is not based on Universe. I think Arthur1 must be using a Object name, thats why he is getting the error. Not sure but if he can provide the statement he is using it will clear the things up.

Sri

 

[blom0344]

That is very interesting. Can you give us an example of an SQL statement using @Prompt() / @Variable() and the RDBMS you are aiming the SQL at. Did you tune the RDBMS to accept these variables? Both DB2 and ORACLE do not seem to be inclined to work with these options............

T. Blom
Information analyst
tbl@shimano-eu.com

 

[Sridharan]

blom,

Infact I haven't used that so far as I'm against Free Hand SQL except for some extreme reporting but I did test it against MS-Access/Oracle it works great. Its a normal thing but no Class\Object name for LOVs. Below is a sample sql infact a simple sql I used to test it.

select region_name from region where region_name = @variable('Enter Region')

select region_name from region where region_name = @prompt('Enter Region','C',,,)

Sri

 

[Arthur1]

Hi,

Actually I wanted to know if it was possible to use prompts in Free-Hand SQL, and it does seem so. I got my piece of code working now.

I try not to use free hand SQL for reports but it does come in handy for testing. Thank you both for your input.

 

[ronacaxton]

I have the same problem. I'm using the @variable in Free-Hand SQL and I get the prompt on the screen but the VALUES button is grayed out. How do I get the VALUES button to work?

My shorted statement is:

select fund_id from matched_trades
where fund_id in @variable('Click the VALUES button to select a Fund Id(s).')

 

[skrandel]

You can't get the values button using @variable. You have to use @prompt. But then you MUST hardcode the values.

These advanced sorts of features should be used within a universe.

Free-hand SQL is not secure.

Steve Krandel
Knightsbridge Solutions
formerly BASE Consulting Group

http://www.knightsbridge.com

 

[Sridharan]

Yes you need to use @prompt or else use the Universe and then change the sql.

Sri