ibmi-brunch-learn

Announcement

Collapse
No announcement yet.

Query table1, efficient conditional comparison against table2

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

  • Query table1, efficient conditional comparison against table2

    I am writing a query over the following files in SQLRPGLE:
    [code]
    File1:
    id1, key
    id2, key
    field11,
    field12

    File2:
    id1, key
    id2, key
    id3, key
    field21,
    [code]

    I want to select all records in File1 that match user input criteria:
    • field11 = some value
    • field12 = some value
    • **matching record in File2 with field21 = some value
    All criteria are optional, so I am trying to condition them all in the SQL

    I started with this:
    Code:
    exec sql
    select * from File1 into :ds
     where (:field11val = ' ' or field11 = :field11val)
       and (:field12val = ' ' or field12 = :field12val)
       and (id1,id2) in (select id1, id2 from File2
                          where (:field21val = ' ' or field21 = :field21val)
    (Note that this is all simplified - in actuality I am checking many more fields fields in both files)

    This worked, except this method implies something like an inner join between File1 and File2 - so even if I specify blank for :field21val, if id1,id2 do not exist in File2 then the record does not return.
    I would rather it did, I only care about a File2 record existing if I specify a value for :field21val

    So I figured, I could condition the "(id1,id2) in (select... ":
    Code:
    exec sql
    select * from File1 into :ds
     where (:field11val = ' ' or field11 = :field11val)
       and (:field12val = ' ' or field12 = :field12val)
       and (:field21val = ' ' or (id1,id2) in (select id1, id2 from File2
                                                where (:field21val = ' ' or field21 = :field21val)
    Now that worked, but I think there is an efficiency issue, which is the whole reason for this post. If :field21val is blank, there is technically no need to do any lookup on File2. But According to Visual Explain, it is still doing a lookup.

    Is there a better way of accomplishing that I am trying to accomplish? I know I could build the SQL as a string but I don't like doing that because it makes the SQL statement so much harder to understand when browsing the code.
Working...
X