Dynamic population of @ Prompt

[mk7393]

I have free hand SQl which receives some input through @prompt.

I have hard coded values in the prompt now. IS there any way by which I can get it dynamically from the database.?

Remember I do not use a report from Universe but from Free hand SQL.

Thanks

 

[skrandel]

You'd have to write another query and then populate the results to a file.

Read up on running BO from the command line and you'll see that you can create a variable file and it will automatically fill in your prompts.

If prompts are read from the database, are they really prompts? Sure sounds like a job for a subquery.

Steve Krandel
Westbay Solutions

http://www.westbaysolutions.com

 

[mk7393]

Hi Steve,

Thanks for the reply..I need some moer clarity. So explaining my issue in more detail..

Say I have some employee names in a database table

In the BO report there is dropdown which gives the names of all employees. I need to select a particular employee and run the report for him.

So I need the dropdown to fetch the latest values from the database.


Can you give me pointers on achieving this


Thanks
Murali

 

[skrandel]

That's totally different than what you asked.

Just set the LOV to refresh on each use.

Steve Krandel
Westbay Solutions

http://www.westbaysolutions.com

 

[mk7393]

HI Steve,

Thanks again for the reply.

This is how my prompt looks currently where the values are hard coded. How can I pick the values from Database in a @ prompt.

@Prompt ('Select an employee','A',{'Aams','Paul',
'Smith','Barb','Richard')'},MONO,CONSTRAINED)

Do you have the syntax?

Thanks in advance

 

[skrandel]

Oops! I forgot you were using Free-Hand SQL. Sorry, there is absolutely no way to do this.

Steve Krandel
Westbay Solutions

http://www.westbaysolutions.com

 

[mk7393]

Thanks Steve.

I will design something else then:-((

 

[blom0344]

No need for

Simply build an SQL statement that fetches the input through a subquery (like Steve mentioned)

SELECT A,B,C FROM TABLE1 WHERE D IN (SELECT X FROM TABLE2 WHERE .....)

You probably can get away with an uncorrelated subquery to populate the outer query. The WHERE clause of the subquery will determine the outcome of the list of values fed to the main query...

Ties Blom
Information analyst 
tbl@shimano-eu.com

 

[mk7393]

Hi Ties,

thanks for the reply.

But how can I populate the prompt with the values from database?

-Murali

 

[skrandel]

You can't do this.

If you really want the power of Lists of Values, do this from a universe. Free hand SQL in BO really isn't the answer for ANYTHING in production. It uses unsecured connections and opens you up for security problems.

If you want to do what you are trying to do, you'll have to use a universe, then modify the SQL in a report that is generated from that universe. As long as you have the same number of objects, you'll get the result you want AND have the LOV populated.

Steve Krandel
Westbay Solutions

http://www.westbaysolutions.com

 

[mk7393]

Thanks Steve..