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:
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
This makes the code much more readable.
I assume (I haven't tried it) that I could further simplify it using parameter markers
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:
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?
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 + ''')';
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;
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' ))))))))))
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?
Comment