SQL - Re-use Input Parameter value

trotter_inc

Hi guys,

I'm writing a basic SQL statement on Oracle 9i and I'm having a little trouble...basically I want to run an SQL query in which the WHERE clause re-uses an input parameter value which is entered by the user. However, I don't the user to have to re-type the parameter value for every instance the value is used.

Let me explain using an example...

If I have something like this:

SELECT * from myTable WHERE field1 = '&inputParam1' AND field2 = '&inputParam1';

If I run this the user is prompted TWICE to enter the value for &inputParam1; is there a way to tell the SQL to simply ask once for the &inputParam1 value and then re-use the value entered for every instance of &inputParam1??

I know I could write the SQL using a DECLARE, BEGIN and END format whereby I declare a variable such as "var_productInput := '&inputParam1';" but the users want to simply take the output and paste it in to Excel. If I use the DECLARE, BEGIN and END format I need to output the results using dbms_output.put_line(), right? I just need a single SQL statement.

I have tried using '&&inputParam1' instead of '&inputParam1' but this seems to bind the value entered for &&inputParam1 for the entire database session. The user is prompted once and then for every re-execution of the SQL the same &inputParam1 value is used.

I want the user to be able to run the SQL statement but be prompted for the &inputParam1 value ONCE for each execution...can anyone help me out here PLEASE??

Thanks guys

stevenmu

I don't know anything about Oracle, but can you not just do:

SELECT * from myTable WHERE field1 = '&inputParam1' AND field2 = '&inputParam1';

trotter_inc

stevenmu wrote: »

I don't know anything about Oracle, but can you not just do:

SELECT * from myTable WHERE field1 = '&inputParam1' AND field2 = '&inputParam1';

APOLOGIES! I just updated my original post there. That is exactly what I have:

SELECT * from myTable WHERE field1 = '&inputParam1' AND field2 = '&inputParam1';


However, if you run this then you are prompted TWICE to enter the value for '&inputParam1'. I need a way of just entering the value for '&inputParam1' ONCE and then having all other instances of '&inputParam1' using this value.

My example is quite simplified for demonstration purposes, in my actual code I need to use the '&inputParam1' value approx 5 times and I don't want the user to enter the same value 5 times.

Tom Dunne

stevenmu wrote: »

I don't know anything about Oracle, but can you not just do:

SELECT * from myTable WHERE field1 = '&inputParam1' AND field2 = '&inputParam1';

No, anywhere it sees '&' it attempts to use a substitution variable. I think it is more a "feature" of SQL rather than Oracle.

trotter_inc wrote:

I know I could write the SQL using a DECLARE, BEGIN and END format whereby I declare a variable such as "var_productInput := '&inputParam1';" but the users want to simply take the output and paste it in to Excel. If I use the DECLARE, BEGIN and END format I need to output the results using dbms_output.put_line(), right? I just need a single SQL statement.

Yes, that's probably the best way of doing it.

Just make sure you SET SERVEROUTPUT ON beforehand. Otherwise you get nothing more than "Anonymous block complete". That catches me every now and then.

croo

trotter_inc wrote: »

Hi guys,

I'm writing a basic SQL statement on Oracle 9i and I'm having a little trouble...basically I want to run an SQL query in which the WHERE clause re-uses an input parameter value which is entered by the user. However, I don't the user to have to re-type the parameter value for every instance the value is used.

Let me explain using an example...

If I have something like this:

SELECT * from myTable WHERE field1 = '&inputParam1' AND field2 = '&inputParam1';

If I run this the user is prompted TWICE to enter the value for &inputParam1; is there a way to tell the SQL to simply ask once for the &inputParam1 value and then re-use the value entered for every instance of &inputParam1??

I know I could write the SQL using a DECLARE, BEGIN and END format whereby I declare a variable such as "var_productInput := '&inputParam1';" but the users want to simply take the output and paste it in to Excel. If I use the DECLARE, BEGIN and END format I need to output the results using dbms_output.put_line(), right? I just need a single SQL statement.

I have tried using '&&inputParam1' instead of '&inputParam1' but this seems to bind the value entered for &&inputParam1 for the entire database session. The user is prompted once and then for every re-execution of the SQL the same &inputParam1 value is used.

I want the user to be able to run the SQL statement but be prompted for the &inputParam1 value ONCE for each execution...can anyone help me out here PLEASE??

Thanks guys

Why not do

SELECT * from myTable WHERE field1 = '&inputParam1' AND field2 = field1;

Or am I missing something?

trotter_inc

croo wrote: »

Why not do

SELECT * from myTable WHERE field1 = '&inputParam1' AND field2 = field1;

Or am I missing something?

You're right in what you said, the above would work but I was trying to keep my example as simple as possible. Here is the exact code I'm trying to resolve:

....
....
AND logical.pal_Prodcode =

CASE WHEN '&productCode' = 'CCBanorte' OR '&productCode' = 'Banorte' THEN 'CCBANORTE'
ELSE '&productCode'
END

You see, the user may enter 'CCBanorte' or 'Banorte'; in this instance I want to find records in the database where the pal_prodcode = 'CCBanorte'. If they enter anything other than 'CCBanorte' or 'Banorte' then I just take the value as they enter it.

Using the above, they are prompted to enter &productCode twice...can it be updated so that it just prompts once for the value?

croo

Lookup double ampersand variables in oracle.

trotter_inc

croo wrote: »

Lookup double ampersand variables in oracle.

I already did, please see my OP:

I have tried using '&&inputParam1' instead of '&inputParam1' but this seems to bind the value entered for &&inputParam1 for the entire database session. The user is prompted once and then for every re-execution of the SQL the same &inputParam1 value is used.

Here is a quote for Oracle on it:

"This double ampersand substitution causes a session variable to be created for each distinct ampersand substitution."

croo

trotter_inc wrote: »

I already did, please see my OP:

Sorry, I didn't re-read the OP after you provided the second code example.

It's been a couple of years since I used Oracle and I don't have any version installed at the moment to experiment... but nothing is coming to mind. It seems the new express versions would prompt each time even for the && but see you're on 9i.

DublinWriter

SELECT * from myTable WHERE field1 = '&inputParam1' AND field1= field2

trotter_inc

DublinWriter wrote: »

SELECT * from myTable WHERE field1 = '&inputParam1' AND field1= field2

I don't think you read all the posts above, here is one I made in reply to a post the same as yours:

You're right in what you said, the above would work but I was trying to keep my example as simple as possible. Here is the exact code I'm trying to resolve:

....
....
AND logical.pal_Prodcode =

CASE WHEN '&productCode' = 'CCBanorte' OR '&productCode' = 'Banorte' THEN 'CCBANORTE'
ELSE '&productCode'
END

You see, the user may enter 'CCBanorte' or 'Banorte'; in this instance I want to find records in the database where the pal_prodcode = 'CCBanorte'. If they enter anything other than 'CCBanorte' or 'Banorte' then I just take the value as they enter it.

Using the above, they are prompted to enter &productCode twice...can it be updated so that it just prompts once for the value?

DublinWriter

Your only other solution would be to coding up a PL/SQL function.