Parameters in SQL query
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 8 of 8

Thread: Parameters in SQL query

  1. #1
    Join Date
    Apr 2001
    Posts
    42
    I have a query where a user can enter up to three parameters(search conditions), but the user may not enter all three every time they query is called, but they will have to enter at least one of the parameters. For example, I have a query that selects from a table based on a contract number, invoice number, and transaction number. The user can has to enter the invoice number, but the other two are optional(for purposes of narrowing the data returned).
    My question is how do I set my query up to run and ignore(??) the parameters that are not required to be entered?// I hope I have made sense with my question.

    Thanks in advance for your help
    Utep
    Share on Google+

  2. #2
    Join Date
    Apr 2001
    Location
    Czechia
    Posts
    712
    Good method is sequential creation of WHERE clause (and whole query, of course) as character string:

    Start with empty string.
    Test each parameter and if given, add string ' AND column=value_of_parameter '
    If final string is not empty, replace the first 'AND' with 'WHERE'
    Put it to the rest of query.
    Then send it to Oracle.

    Hope It's not confusing.
    Ales



    Share on Google+

  3. #3
    Join Date
    Apr 2001
    Posts
    42
    Hey Ales,
    thanks for the reply, would I use the decode statement to test for the parameter, if not could you give me an example.

    Thanks
    Ulysses
    Utep
    Share on Google+

  4. #4
    Join Date
    Apr 2001
    Location
    Czechia
    Posts
    712
    It's up to you ... if you choose DECODE, you can use it, but bear in mind it's usable only in a SQL statement.

    You must know whether user filled a parameter, so you must have a value of parameter indicating "not given", for example:

    (you didn't mention a language, I'll use PL/SQL)

    Suppose we have table T with these columns of NUMBER type:
    CN = contract number
    IN = invoice number
    TN = transaction number
    ...

    Assume no one of them can be 0 (zero), then we can choose 0 as value for not-given-parameter.
    Parameters are p_cn, p_in and p_tn and they are comming in this code from a user_interface. If a user filled a parameter, it doesn't contain 0.
    ...
    WC:='' --means WhereClause :-)
    IF p_cn!=0 THEN
    WC:=WC||' AND CN='||to_char(p_cn);
    END IF;

    IF p_in!=0 THEN
    WC:=WC||' AND CN='||to_char(p_in);
    END IF;

    IF p_tn!=0 THEN
    WC:=WC||' AND CN='||to_char(p_tn);
    END IF;

    IF LENGTH(WC)>0 THEN
    WC:=SUBSTR(WC,5); --cut off first AND
    WC:='WHERE'||WC;
    END IF;
    FQUERY:='SELECT * FROM T '||WC;
    --then use dbms_sql for opening cursor ...

    If you need more info, just write.
    Ales

    Share on Google+

  5. #5
    Join Date
    Apr 2001
    Location
    Czechia
    Posts
    712
    Of course, I made silly mistake (hope only this one):

    IF p_cn!=0 THEN
    WC:=WC||' AND CN='||to_char(p_cn);
    END IF;

    IF p_in!=0 THEN
    WC:=WC||' AND IN='||to_char(p_in);
    END IF;

    IF p_tn!=0 THEN
    WC:=WC||' AND TN='||to_char(p_tn);
    END IF;

    Ales

    Share on Google+

  6. #6
    Join Date
    Apr 2001
    Posts
    42
    Is it possible to accomplish this through a SQL query statement?//
    Utep
    Share on Google+

  7. #7
    Join Date
    Nov 2000
    Location
    Baltimore, MD USA
    Posts
    1,339
    You 'can', but the dynamic SQL will perform better.

    If :c1 is required, but :c2 and :c3 are optional, you can do this:


    SELECT
    ----*
    FROM
    ----T
    WHERE
    ----T.C1----=----:C1----------------AND
    ----T.C2----=----NVL(:C2,T.C2)----AND
    ----T.C3----=----NVL(:C3,T.C3)

    HTH,

    - Chris
    Share on Google+

  8. #8
    Join Date
    Apr 2001
    Posts
    42
    Thanks for all the help Ales and Chris.

    Ulysses
    Utep
    Share on Google+

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