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:
I started with this:
(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... ":
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.
[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
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)
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)
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.