If logic in WHERE condition inside SQL statement

grofaty · #1 (**permalink**) 02-21-07, 05:38

Hi,

I would like to change where condition in SQL statement according to input value entered in application.

Sample data:
The logic is:
- if there is no 'region_variable' (variable value is '') the SQL should be without REGION condition - so retrieving all data of all regions:
SELECT * FROM DB2ADMIN.TAB WHERE STATUS = 'Y'

- if REGION is 000 or 100 then SQL should be:
SELECT * FROM DB2ADMIN.TAB WHERE STATUS = 'Y' AND REGION IN (:region_variable)

Code:

ID          REGION STATUS
----------- ------ ------
          1 000    Y
          2 100    Y
          3 100    Y

How to write such an SQL to have an if logic in WHERE condition? Can I use CASE in WHERE condition?

Code to create sample:

Code:

CREATE TABLE DB2ADMIN.TAB (ID INT NOT NULL PRIMARY KEY,
 REGION CHAR(3), STATUS CHAR(1));
INSERT INTO DB2ADMIN.TAB VALUES (1,'000','Y'),(2,'100','Y'),(3,'100','Y');

Thanks,
Grofaty

umayer · #2 (**permalink**) 02-21-07, 06:05

This might work:

SELECT *
FROM DB2ADMIN.TAB
WHERE STATUS = 'Y'
AND REGION =
CASE WHEN :region_variable = '' THEN REGION
ELSE :region_variable
END
;

if region_variable is blanc then the column REGION is compared with itself which is always true unless it is <NULL>.
if there is a value in region_variable then the column is compared to that value.

grofaty · #3 (**permalink**) 02-21-07, 08:56

umayer, thanks a lot this works fine if :region_variable has only one value.

One more question: I also have to write such an SQL that more than one value can exist inside :region_variable.

Sample:
SELECT * FROM DB2ADMIN.TAB WHERE STATUS = 'Y' AND REGION IN (:region_variable)

:region_variable is '100', '000' (into application region field user writes: '100', '000' ) so SQL should be:
SELECT * FROM DB2ADMIN.TAB WHERE STATUS = 'Y' AND REGION IN ('100', '000')

How to write such an SQL that :region_variable could have more than one value plus the functionality to have no region at all like in previous sample?

Thanks,
Grofaty

umayer · #4 (**permalink**) 02-21-07, 09:33

you cant use the IN :region_value in the intended way ( besides using dynamic sql ).

TRANSLATE might be helpful.
...
WHERE STATUS = 'Y'
AND ( :region_variable = '' OR TRANSLATE(:region_variable,'',REGION) ^= :region_variable )

if more than one region is specified, the values must be separated by any character like dash or komma or blanc.

vini_srcna · #5 (**permalink**) 02-21-07, 11:12

It can be done using programming logic as well.
If region_variable = ' ' then call without_region and some more logic like that.
In the corresponding Subroutines you can code the respective SQL statements.

grofaty · #6 (**permalink**) 02-21-07, 12:45

Quote:

Originally Posted by umayer

you cant use the IN :region_value in the intended way ( besides using dynamic sql ).

TRANSLATE might be helpful.
...
WHERE STATUS = 'Y'
AND ( :region_variable = '' OR TRANSLATE(:region_variable,'',REGION) ^= :region_variable )

if more than one region is specified, the values must be separated by any character like dash or komma or blanc.

Umayer, thank you very much for helping me out.

I have tried your suggestion but I think it is not working well:
If :region_variable is '100,000' then SQL looks like:

Code:

SELECT * FROM DB2ADMIN.TAB
WHERE STATUS = 'Y'
AND ( '100,000' = '' OR TRANSLATE('100,000','',REGION) ^= '100,000');

the result of this SQL is correct.

But any other value alway returns all records which is not correct result.
For example if :region_variable is '100' the SQL looks like:

Code:

SELECT * FROM DB2ADMIN.TAB
WHERE STATUS = 'Y'
AND ( '100' = '' OR TRANSLATE('100','',REGION) ^= '100');

but it does not display only 100 rows, instead all rows from table are returned, which is not what I want.

The same problem is if only '000' is used as :region_variable. But it looks fine if empty variable is used (so '').

So I need some kind of solution that :regional_variable can be:
- empty string, tha is: ''
- value: 100
- value: 000
- value 000, 100 or 100, 000
- or any other more then two values combination.

Any idea?

Thanks,
Grofaty

grofaty · #7 (**permalink**) 02-21-07, 12:49

Quote:

Originally Posted by vini_srcna

It can be done using programming logic as well.

The problem is that the program is already written. Our company bought "SQL reporting application" witch only accepts SQL as input and host variables definitions as input fields in web application. So I can't change program, only SQL is allowed to be changed. So I am desperately looking for SQL solution. Thanks for help anyway.

sathyaram_s · #8 (**permalink**) 02-21-07, 13:22

An alternative is to use COALESCE .. Noit much different though ..

Code:

with temp(seqno,ctry,regn) as 
(
values(1,'GB','EU'),
(2,'US','NA'),
(3,'CA','NA')
)
select * 
from temp
where 
 
regn=coalesce(case when :invar='' then cast(null as char(10)) else :invar),regn)

Here when the input variable invar is ''
then the sql will be equivalent of

Code:

with temp(seqno,ctry,regn) as 
(
values(1,'GB','EU'),
(2,'US','NA'),
(3,'CA','NA')
 )
 
 where 
 
 regn=coalesce(cast(null as char(10)),regn)

Here all records will be selected as regn=regn

If you give a value, say NA

then
the SQL will be equivalent of

Code:

 with temp(seqno,ctry,regn) as 
 (
 values(1,'GB','EU'),
 (2,'US','NA'),
 (3,'CA','NA')
  )
 
  where 
 
 regn=coalesce('NA',regn)

Here only NA records will be selected.

I guess this will solve your problem.

If you want ctry also be part of the where clause (ie, you get values for two columns) then the where clause should be :

Code:

ctry=coalesce(case when :invar1='' then cast(null as char(10)) else :invar1),ctry) and
regn=coalesce(case when :invar2='' then cast(null as char(10)) else :invar2),regn)

It doesn't matter if the user enters only one or both values.

If the input value comes from the same source, and this value can be either regn or country then use OR in the where clause.

Code:

ctry=coalesce(case when :invar='' then cast(null as char(10)) else :invar),ctry) OR 
regn=coalesce(case when :invar ='' then cast(null as char(10)) else :invar ),regn)

HTH
Sathyaram

sathyaram_s · #9 (**permalink**) 02-21-07, 13:52

Sorry grofaty, the following may be an acceptable solution if multiple regions could come in ..

For this solution, you will need a table UDF .. This UDF assumes a comma seperated input list :

Code:

CREATE FUNCTION CSV_TABLE(INLIST VARCHAR(1000))
RETURNS TABLE(LISTVAL CHAR(10))
BEGIN ATOMIC
RETURN
WITH TEMP1(STR1) AS
(
VALUES(REPLACE(INLIST||',''''','''',''))
)
,
TEMP2(STR3,STR4) AS
(
SELECT LEFT(STR1,POSSTR(STR1,',')-1),SUBSTR(STR1,POSSTR(STR1,',')+1) FROM TEMP1
UNION ALL
SELECT LEFT(STR4,POSSTR(STR4,',')-1),SUBSTR(STR4,POSSTR(STR4,',')+1) FROM TEMP2 WHERE LENGTH(STR4)>0
)
SELECT STR3 FROM TEMP2 ;
END

Code:

with temp(seqno,ctry,regn) as 
(
values(1,'GB','EU'),
(2,'US','NA'),
(3,'CA','NA'),
(4,'IN','AP')
)
select * 
from temp
where 
regn in (values(case when :invar='' then regn end )) OR 
regn in (select rgnname from table(csv_table(:invar)) as rgnlist(rgnname))

Or, you can use a CTE instead of the Table UDF

Code:

WITH TEMP1(STR1) AS
(
VALUES(REPLACE(:invar||',''''','''',''))
)
,
TEMP2(rgnname,STR4) AS
(
SELECT LEFT(STR1,POSSTR(STR1,',')-1),SUBSTR(STR1,POSSTR(STR1,',')+1) FROM TEMP1
UNION ALL
SELECT LEFT(STR4,POSSTR(STR4,',')-1),SUBSTR(STR4,POSSTR(STR4,',')+1) FROM TEMP2 WHERE LENGTH(STR4)>0
),
temp(seqno,ctry,regn) as 
(
values(1,'GB','EU'),
(2,'US','NA'),
(3,'CA','NA'),
(4,'IN','AP')
)
select * 
from temp
where 
regn in (values(case when :invar='' then regn end  )) OR 
regn in (select rgnname from temp2 )

HTH

Sathyaram

n_i · #10 (**permalink**) 02-21-07, 13:58

Quote:

Originally Posted by grofaty

The problem is that the program is already written. Our company bought "SQL reporting application" witch only accepts SQL as input and host variables definitions as input fields in web application. So I can't change program, only SQL is allowed to be changed.

For what it's worth, we used a stored procedure that returned a result set in a similar situation. You can implement any complex logic in a stored procedure or use dynamic SQL.

sathyaram_s · #11 (**permalink**) 02-21-07, 14:06

n_i, Will that not mean a change of application code ?

I mean, for example in Java, you use SQL Statement objects and execute them for SQL Statements whereas stored procs use callable stmts ...
Also, I guess the host language stmts for handling SQL resultsets may be different from handling Stored Proc resultsets ..

Cheers
Sathyaram

grofaty · #12 (**permalink**) 02-21-07, 15:49

sathyaram_s, I haven't tested your suggestions yet it looks so complex...

I have one solution but it is not complete:

Code:

SELECT * FROM DB2ADMIN.TAB WHERE STATUS='Y' AND REGION IN (:replace_variable)
UNION ALL
SELECT * FROM DB2ADMIN.TAB WHERE NOT EXISTS
(
SELECT * FROM DB2ADMIN.TAB WHERE STATUS='Y' AND REGION IN (:replace_variable)
)

If :replace_variable is '000' the result is fine.
If :replace_variable is '100' the result is fine.
If :replace_variable is '000','100' the result is fine.
If :replace_variable is '100','000' the result is fine.
If :replace_variable is empty (so '') the result is fine.
The problem is if there is one or all values that does not exist in table. E.g. value '300' or '300','400', etc. If so all the rows from the table are returned instead of no row.

Any idea how to solve this problem? As I see there should be some kind of LENGTH function to solve the problem.

Thanks,
Grofaty

sathyaram_s · #13 (**permalink**) 02-21-07, 16:27

The complexity is my solution is primarily to do with the multi-value requirement ...

I cannot think of a non-recursive solution because you do not know the number of values that come in ...

Also remember, if hostvar is '000','001' then when db2 processes "in (:hostvar) " , it does not consider the value as two separate strings but as one string only ... So effectively, you will be looking for a region with a value '000','001' and NOT region '000' or '001'

Back to the basics, please clarify - what is the replace_variable .. Is it a DB2 host variable or is it a Unix variable - where you just go and find/replace the string with your input values ?

I assume it is a db2 host variable ...

Cheers
Sathyaram

grofaty · #14 (**permalink**) 02-22-07, 01:40

sathyaram_s, I have been talking to programmer of this reporting application and application works like this:
- application gets data from host variable and store this variable into memory
- then application concats SQL and result of host variable into one SQL
- SQL is executed

I have tried this out one simple sample and inserting '000','001' into application input field and it works like I have described above. So '000','100' in input field as host_variable it is working well.

umayer · #15 (**permalink**) 02-22-07, 01:44

@ grofaty

you are right, I muddled up REPLACE and TRANSLATE.

please try:

WHERE STATUS = 'Y'
AND ( :region_variable = '' OR REPLACE(:region_variable,REGION,'') ^= :region_variable )

sorry for my mistake.

Internet Services

Web Development

Digital Marketing

Consumer Tech