Results 1 to 8 of 8

Thread: Coders block?

  1. #1
    Join Date
    Dec 2010
    Posts
    3

    Unanswered: Coders block?

    Hey All,

    First time poster, looking for some ideas or guidance with a small project I currently have.

    I have slowly been ramping up on my db2 skills using online sites and now i hope this site can help..

    Basically what I need is a stored procedure that is sent multiple parameters. I need to query tables based on these parameters. It would be pretty simple except I cannot expect all parameters to be populated on every call.

    For example lets say the screen has first name, last name, employee number and department. I need to pull records off a table based on this. If they pass all 4 parms at all times it becomes a simple select and I'm done..

    BUT the issue is I can be passed just a first name, or a last name and nothing else. Any combination really, the less parameters the more rows I would return. If someone passes first name last name and employee number I would grab 1 record, if they pass just last name I can grab all "smiths"...

    So, I am not sure if there is such a concept of dynamic clauses, where if I want the AND statement executed I can put it in an if statement of some sort... I can ask for "-1" if a parameter is not chosem..

    So..

    (Psuedo code/english?)

    select ****
    where 1=1
    (if vfname != -1) AND fname = vfname
    (if vlname!= -1) AND lname = vlname
    (if vemp!= -1) AND emp = vemp
    (if vdep!= -1) AND dep = vdep


    If all parms are -1 I would just return the entire table...

    I've been working with DB2 for just a little bit so I doubt something like this is out there so this is why I come here for ideas....

    I hope all this made sense, I'll provide more detail is needed

  2. #2
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    SQL is by design quite close to how you would describe that in a human language; you just need to express what you want in a human language:

    select stuff from table where parameter is null or parameter equals something etc.

  3. #3
    Join Date
    Dec 2010
    Posts
    3
    Quote Originally Posted by n_i View Post
    SQL is by design quite close to how you would describe that in a human language; you just need to express what you want in a human language:

    select stuff from table where parameter is null or parameter equals something etc.
    Right...

    More like:
    select stuff from table where parameter equals something IF parameter was provided..


    But you might be on to something, what is I do the following...I ask for a -1 when no department was chosen... I can do the following:

    select * from table
    where (-1 = vDepNum OR depNum = vDepNum);


    If I recall, it will check the first statement and if -1 = -1 it will not continue with the second part of the or, am I correct? If it's not -1 it means I was passed a parameter and I can use it in my clause....

    hmm if what I said above is correct then this might be what I need.

  4. #4
    Join Date
    May 2009
    Posts
    508
    Provided Answers: 1
    jcoder, if you don't want to build a dynamic statement, you can look into the CASE expression - IBM DB2 9.7 for Linux, UNIX, and Windows

  5. #5
    Join Date
    Dec 2010
    Posts
    3
    Stealth_DBA - Thanks. I was under the impression a CASE statement could not be used within a where or and clause, but looking at the documentation it seems that is not the case(no pun intended).

    I have not done anything with dynamic SQL, but I will look into both.

    Thank you for your help

  6. #6
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    Keep in mind writing your SQL that way it will be tablespace scans. If you want good performance, look at dynamic sql that Stealth mentioned or write all the variations of statements and execute the appropriate one based on your inputs.
    Dave Nance

  7. #7
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Quote Originally Posted by dav1mo View Post
    writing your SQL that way it will be tablespace scans.
    Could you explain why?

  8. #8
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    Let's see, what access path would you choose with this sql statement?

    Code:
    select * from table
    where (-1 = vDepNum OR depNum = vDepNum)
      and (-1 = vEmpFname or empFname = vEmpFname)
      and (-1 = vEmpLname or empLname = vEmpLname)
      and (-1 = vEmp or emp = vEmp)
    although now that I think of it with dynamic rules at run time DB2 may be able to get the right path.
    VS:
    Code:
    select * from table
    where  depNum = vDepNum
    or:
    Code:
    select * from table
    where empFname = vEmpFname
      and empLname = vEmpLname
    Dave

Posting Permissions

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