SQL Statement that promts the user for what to search on

[jrosen]

I am working with a Database Application called Connect-Care that runs on Oracle 8i on a Windows platform. I am attempting to create a query that that will promts the user for what to search on, instead of editing the SQL statment every time

 

[MikeLacey]

Hi jrosen,

Try this:
[tt]
Select *
[tab]From OUR_USERS
[tab]Where USER_NAME = '&User_Name';
[/tt]

I'm not sure if this is what you want -- to be honest -- but the example will work ok in sql/plus

 

[buttersoft]

I think the request is exactly the same as my own......

Does anyone know how to present a user with a form, which asks them to complete their choices to search for, and then this input is entered into the WHERE clause of the script and the script runs.

I have a script that looks for 3 to 4 letter codes in a file with several thousand such codes. The user could decide he wants to report on ARM PON XAR for example, and thus the WHERE clause would have to be modified to
WHERE (EPIC = 'ARM OR EPIC = 'PON' OR EPIC = 'XAR), so what I want to do is present a form asking for a list perhaps seperated by a TAB or comma of EPICS, and then this has to overwrite the WHERE clause that is presently in the SQL script.

Anyone know how to do this?

 

[karluk]

I would attempt to solve this using static sql, rather than modifying the where clause every time.

To take a simple example, suppose I want to let the user do a search on a table called 'data_table' by supplying a list of values for column 'epic'. I would construct a table, 'search_values', with two columns - 'User_id' and 'value'.

When the user entered the search criteria I would insert into table search_values and then execute the following query:

select * from data_table
where epic in
(select value from search_values
where user_id = &user)

Of course it would also be necessary to add a cleanup step to delete the rows from search_values.

It seems to me that this approach is a little more complex than dynamic sql for simple searches. However it is also quite powerful. With the right design for the table 'search_values' it would be possible to support arbitrarily complex searches, including and, or, and not conditions on any number of columns.

 

[buttersoft]

Thanks for this I will try it out...