Using variable in SQL WHERE statement
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 4 of 4

Thread: Using variable in SQL WHERE statement

  1. #1
    Join Date
    Feb 2006
    Location
    Iowa
    Posts
    7

    Using variable in SQL WHERE statement

    I thought I posted this already, in How To but can't find it so I will try again here:

    I need to modify the WHERE statement in the following code:

    SELECT PSLET_PROJECTID, PSLET_CONTRACTLETDATE, PSLET_OLD_CONTRACTLETDATE,
    PSLET_PROJECT_GROUP
    FROM S4111000.PSLET_INFORMATION
    WHERE PSLET_PROJECTNUMBER = p_INProjectNumber
    AND PSLET_DELBYTE IN ('A', 'N', 'H')

    I want to create a variable and set the value for PSLET_DELBYTE
    depending on who calls this program:

    IF p_INProgramNumber = 'S4115019'
    THEN
    v_sqlLetDelbyte := ('A', 'N', 'H', 'D');
    ELSE
    v_sqlLetDelbyte := ('A', 'N', 'H');
    END IF;

    I changed the SQL statement to:

    SELECT PSLET_PROJECTID, PSLET_CONTRACTLETDATE, PSLET_OLD_CONTRACTLETDATE,
    PSLET_PROJECT_GROUP
    FROM S4111000.PSLET_INFORMATION
    WHERE PSLET_PROJECTNUMBER = p_INProjectNumber
    AND PSLET_DELBYTE IN v_sqlLetDelbyte

    The program will not compile because it doesn't like the variable
    v_sqlLetDelbyte, but I get in to quotation HELL if I put quotation marks around the whole thing '('A', 'N', 'H', 'D')'.

    Dynamic SQL will have the same problem. Is there a way to write
    this sql without having to write two separate statements?

    Any help will be appreciated!!

    Jan

  2. #2
    Join Date
    Jul 2002
    Location
    Lake Worth, FL
    Posts
    1,483

    Cool


    Try this:
    Code:
    ...etc...
    AND INSTR(v_sqlLetDelbyte,PSLET_DELBYTE) > 0;

    "The person who says it cannot be done should not interrupt the person doing it." --Chinese Proverb

  3. #3
    Join Date
    Feb 2006
    Location
    Iowa
    Posts
    7

    Thank You!

    I have several places I had to change the code, and it still compiles with your suggested code. Thank you very much!! I will set up the test data and run to make sure I get what I want. But looks good so far.

    Again, thank you!

    Jan

  4. #4
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    How about ...
    Code:
    SELECT PSLET_PROJECTID, PSLET_CONTRACTLETDATE, PSLET_OLD_CONTRACTLETDATE,
    PSLET_PROJECT_GROUP
    FROM S4111000.PSLET_INFORMATION
    WHERE PSLET_PROJECTNUMBER = p_INProjectNumber
    AND (PSLET_DELBYTE IN ('A', 'N', 'H') OR (p_INProjectNumber = 'S4115019'
    AND PSLET_DELBYTE = 'D'))
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width