ibmi-brunch-learn

Announcement

Collapse
No announcement yet.

Dynamic SQL - conditional build vs embedded conditions

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

  • Dynamic SQL - conditional build vs embedded conditions

    I'm working on an SQL program that will read data from a file into a subfile. There are lots of optional conditions that the user can input.

    I'm looking at two approaches.

    The obvious way is dynamically including only the relevant conditions when I build the SQL string, as per http://www.itjungle.com/fhg/fhg082405-story01.html
    Something like this:
    Code:
    sqlstmt = 'select abc, def from file';
    if typecond <> ' ' or namecond <> ' ';
      sqlstmt += ' where';
    endif;
    if typecond <> ' ';
      sqlstmt += ' type = ''' + typecond + '''';
    endif;
    if namecond <> ' ';
      sqlstmt += 'name = ''' + namecond + '''';
    endif;

    This would work, I know. But I have a lot of nested conditions so the code to build the SQL is complex, which makes it hard to understand the SQL statement.


    The other idea I had was to incorporate the conditions into the SQL itself
    Code:
    sqlstmt = 'select abc, def from file where '
            + '     (''' + typecond + ''' = ' ' or type = ''' + typecond + ''')'
            + ' and (''' + namecond + ''' = ' ' or name = ''' + namecond + ''')';
    This makes the code much more readable.

    I assume (I haven't tried it) that I could further simplify it using parameter markers
    Code:
    sqlstmt = 'select abc, def from file where '
            + '     (? = ' ' or type = ?)'
            + ' and (? = ' ' or name = ?)';
    
    //the open mainCursor would specify the parameters for the '?'s:
    exec sql
     open mainCursor
     using :typecode, :typecode,
            :namecode, :namecode;
    I think this would work, yes?

    If so, would it work well, or are there disadvantages? The actual statement I am thinking of would have a lot of nested conditions that could resolve out to this:
    Code:
    select abc, def from file where 
         (' ' = ' ' or (field1 > ' '
     and ('x' = ' ' or (field2 > 'x'
     and ('y' = ' ' or (field3 > 'y'
     and (' ' = ' ' or (field4 > 'y'
     and ('y' = ' ' or (field5 > 'y'
    ))))))))))
    So because the condition for field1 was left blank, the very first condition (' ' = ' ') will always be true and the rest of the conditions are meaningless.
    I wonder if there might be a performance penalty here? Would the SQL processor still look at and maybe use access paths for field2/3/4/5? Or would it recognise that they do not need to be considered and ignore them?


    P.S. any idea how many posts I need to make before my posts stop needing to be moderator approved?

  • #2
    Re: Dynamic SQL - conditional build vs embedded conditions

    IIRC... it's 5 posts
    Regards

    Kit
    http://www.ecofitonline.com
    DeskfIT - ChangefIT - XrefIT
    ___________________________________
    There are only 3 kinds of people -
    Those that can count and those that can't.

    Comment


    • #3
      Re: Dynamic SQL - conditional build vs embedded conditions

      I like Paul Tuohy's approach to that using SQL Descriptors, in the article at the below link.

      A First Look At SQL Descriptors

      I think that approach is a good balance between keeping the code readable, and maintaining good performance.

      The technique that doubles up on apostrophes gets irritating.

      The other technique where you code a lot of OR conditions, sacrifices performance. You want to avoid coding OR conditions if possible.

      Mike

      Comment


      • #4
        Re: Dynamic SQL - conditional build vs embedded conditions

        i do this for each possible selection;

        Code:
        4= case                               
           When :PreStore = 0 then 4          
           When :PreStore = B.Strnum then 4   
           else 1                             
           End

        If prestore is used then it checks it.

        I avoid building the long sql string.
        Hunting down the future ms. Ex DeadManWalks. *certain restrictions apply

        Comment


        • #5
          Re: Dynamic SQL - conditional build vs embedded conditions

          Originally posted by Michael Jones View Post
          I like Paul Tuohy's approach to that using SQL Descriptors, in the article at the below link.

          A First Look At SQL Descriptors

          I think that approach is a good balance between keeping the code readable, and maintaining good performance.

          The technique that doubles up on apostrophes gets irritating.

          The other technique where you code a lot of OR conditions, sacrifices performance. You want to avoid coding OR conditions if possible.

          Mike
          I was afraid you'd say that about the OR conditions

          What I need to do is position in a file to a particular record by file key and read from that point onwards in key order.
          The SQL equivalent of a SETLL on the specified keys, and then a keyless READ from that point on.
          The primary key includes multiple fields and the user can choose how many of those keys to input.
          That requires a construct like:
          Code:
          where (filekey1 > userkey1 or (filekey1 = userkey1 
            and (filekey2 > userkey2 or (filekey2 = userkey2
            and (filekey3 > userkey3 or (filekey3 = userkey3
            and (filekey4 > userkey4 or (filekey4 = userkey4
          ...
            and (filekeyn > userkeyn or (filekeyn = userkeyn
          ))))))))
          The only ways I can think of to accomplish that are:

          1. to build the SQL dynamically and include only the required keys
          e.g. if the user only wants 2 keys:
          Code:
          where (filekey1 > userkey1 or (filekey1 = userkey1 
            and (filekey2 > userkey2 or (filekey2 = userkey2
          ))))
          2. to include in the SQL statement a condition flag to indicate how many of the keys to use:
          Code:
          keycount = 2
          
          where (keycount < 1 or (filekey1 > userkey1 or (filekey1 = userkey1 
            and (keycount < 2 or (filekey2 > userkey2 or (filekey2 = userkey2
            and (keycount < 3 or (filekey3 > userkey3 or (filekey3 = userkey3
            and (keycount < 4 or (filekey4 > userkey4 or (filekey4 = userkey4
          ...
            and (keycount < n or (filekeyn > userkeyn or (filekeyn = userkeyn
          ))))))))))))
          So if only the first two keys are used, then keycount is 2, so keycount < 3 will be true and the SQL processor will ignore keys 3 and up


          Dynamic makes for code that is messy and hard to read, and as you say, the extra conditions are inefficient.

          Comment

          Working...
          X