Advertisement
If you have a new account but are having problems posting or verifying your account, please email us on hello@boards.ie for help. Thanks :)
Hello all! Please ensure that you are posting a new thread or question in the appropriate forum. The Feedback forum is overwhelmed with questions that are having to be moved elsewhere. If you need help to verify your account contact hello@boards.ie

SQL - Re-use Input Parameter value

  • 18-04-2013 9:46am
    #1
    Registered Users Posts: 2,492 ✭✭✭


    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 :)


Comments

  • Moderators, Society & Culture Moderators Posts: 9,689 Mod ✭✭✭✭stevenmu


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

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


  • Registered Users Posts: 2,492 ✭✭✭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.


  • Registered Users Posts: 23,212 ✭✭✭✭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.
    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.


  • Moderators, Technology & Internet Moderators Posts: 1,332 Mod ✭✭✭✭croo


    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?


  • Registered Users Posts: 2,492 ✭✭✭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?


  • Advertisement
  • Moderators, Technology & Internet Moderators Posts: 1,332 Mod ✭✭✭✭croo


    Lookup double ampersand variables in oracle.


  • Registered Users Posts: 2,492 ✭✭✭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."


  • Moderators, Technology & Internet Moderators Posts: 1,332 Mod ✭✭✭✭croo


    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.


  • Registered Users Posts: 9,555 ✭✭✭DublinWriter


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


  • Registered Users Posts: 2,492 ✭✭✭trotter_inc


    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?


  • Advertisement
  • Registered Users Posts: 9,555 ✭✭✭DublinWriter


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


Advertisement