1. Registered User
Join Date
Jul 2013
Posts
7

I have to write a case statement where the scenario is:
For option 'A' only column values with 'A' option sud come but for selection for Option 'B' all the vlues sud come from the table ie. 'A' and 'B' since the column has only 'A' and 'B' values.
So i have writtien the case as below:

CASE WHEN Table.col a In ('A') THEN 'Ant'
when Table.col a In('A','B') then 'Ball' .

Now this is properly executing for 'Ant' and 'A' vlaues are coming.
But for 'Ball' option only 'B' values are coming ,no 'A' values are coming.
I have written the second statement as for that condition all column values sud come which in this scenario consist of 'A' and 'B'.

2. Registered User
Join Date
May 2003
Location
USA
Posts
5,737
I think you have a basic misunderstanding of how if-then-else logic works.
You might want to use a UNION ALL statement to solve your problem.

3. Registered User
Join Date
Jul 2013
Posts
7
Better i will tell what i need.
The option is like that when the table has column values as 'A' then it sud show 'Ant' and when the table has both 'A' and 'B' values then 'Ball' option sud be shown.

The scenario wants when the selection of 'Ant'is made,'A' data sud come and for selection of 'Ball',all the data from table sud come.
Since the table has only 'A' and 'B' data tht's why for second condition i am giving 'A' and 'B' option for retreival of all the data.Tht's why this statement.
so i have to work the condition for
if 'A' then 'Ant'
else if 'A' and 'B' then 'Ball'

If u have any other option,then pls suggest.

4. Registered User
Join Date
May 2003
Location
USA
Posts
5,737
Originally Posted by biswa
Better i will tell what i need.
The option is like that when the table has column values as 'A' then it sud show 'Ant' and when the table has both 'A' and 'B' values then 'Ball' option sud be shown.

The scenario wants when the selection of 'Ant'is made,'A' data sud come and for selection of 'Ball',all the data from table sud come.
Since the table has only 'A' and 'B' data tht's why for second condition i am giving 'A' and 'B' option for retreival of all the data.Tht's why this statement.
so i have to work the condition for
if 'A' then 'Ant'
else if 'A' and 'B' then 'Ball'

If u have any other option,then pls suggest.
UNION ALL. Check the SQL reference manual if you don't understand.

5. Registered User
Join Date
Oct 2013
Posts
1

## data base d-1

hello,

what is the better form to create a database D-1 on DB2?
I need this to create reports and not impact the environment of production.

thank you!
Last edited by andersonhpolo; 10-08-13 at 10:25.

6. Registered User
Join Date
Feb 2008
Location
Japan
Posts
3,483
Originally Posted by biswa
I have to write a case statement where the scenario is:
For option 'A' only column values with 'A' option sud come but for selection for Option 'B' all the vlues sud come from the table ie. 'A' and 'B' since the column has only 'A' and 'B' values.
So i have writtien the case as below:

CASE WHEN Table.col a In ('A') THEN 'Ant'
when Table.col a In('A','B') then 'Ball' .

Now this is properly executing for 'Ant' and 'A' vlaues are coming.
But for 'Ball' option only 'B' values are coming ,no 'A' values are coming.
I have written the second statement as for that condition all column values sud come which in this scenario consist of 'A' and 'B'.
Your requirements were not clear for me.
(My be because I'm not native of English)

Please post DDL(s) and INSERT statement(s) and expected result from the data.

For example,
wha result do you want from the following test table and data?

If the table and/or data were not appropriate to describe your issue,
please post DDL(s) and INSERT statement(s) to describe your issue and expected result from the data.

Code:
```CREATE TABLE test.case_biswa
( pk1   SMALLINT NOT NULL
, pk2   SMALLINT NOT NULL
, col_a CHAR(1)  NOT NULL
, description VARCHAR(20)
, PRIMARY KEY (pk1 , pk2)
);

INSERT INTO test.case_biswa
VALUES
( 1 , 1 , 'A' , 'A only' )
, ( 2 , 3 , 'B' , '      B only' )
, ( 3 , 1 , 'A' , 'A and B' )
, ( 3 , 4 , 'B' , 'A and B' )
, ( 4 , 3 , 'A' , 'A and B and C' )
, ( 4 , 4 , 'B' , 'A and B and C' )
, ( 4 , 5 , 'C' , 'A and B and C' )
, ( 5 , 2 , 'A' , 'A       and C' )
, ( 5 , 4 , 'C' , 'A       and C' )
, ( 6 , 1 , 'B' , '      B and D' )
, ( 6 , 2 , 'D' , '      B and D' )
, ( 7 , 1 , 'E' , 'no A and no B' )
;```

Code:
```------------------------------ Commands Entered ------------------------------
SELECT * FROM test.case_biswa;
------------------------------------------------------------------------------

PK1    PK2    COL_A DESCRIPTION
------ ------ ----- --------------------
1      1 A     A only
2      3 B           B only
3      1 A     A and B
3      4 B     A and B
4      3 A     A and B and C
4      4 B     A and B and C
4      5 C     A and B and C
5      2 A     A       and C
5      4 C     A       and C
6      1 B           B and D
6      2 D           B and D
7      1 E     no A and no B

12 record(s) selected.```

7. Registered User
Join Date
Jul 2013
Posts
7
The Condition is for the front end Report.
Now in the report,I have the to show 'Ant' and 'Ball' option to the user.
When user selects 'Ant' then all data from the table for COL_A(below ex) containg 'A' column values sud come.
When user selects 'Ball' then all data containing 'A' and 'B' values ie. all the values from the table sud come.

The table has only value 'A' and 'B' for tht particulat column.

For examle the table looks like

Col 1 Col 2 COL_A
------ ------ ----- --------------------
1 x A
2 y B
3 z A
4 l A
5 m B
6 o A
7 o B
8 p A
9 q A
10 r B
11 s B
12 t A

So i have written the statement.The same case statement condition worked earlier when application was intefaced with
oracle and i used In operator but maybe in DB2 case statement we cannot use In operator.

8. Registered User
Join Date
Dec 2007
Location
Richmond, VA
Posts
1,328
Biswa,
Marcus has answered you twice already, look up. You have a misconception of how CASE and IN work. A case statement ends once the first matching condition is found. An IN states find any in my list. You are looking for must have both. Completely different scenario.

Dave

9. Registered User
Join Date
Feb 2008
Location
Japan
Posts
3,483

Here, <selected_option> is a declared variable or a parameter which includes the option selected by user.
Code:
```SELECT t.*
FROM  sample_data AS t
WHERE <selected_option> = 'Ant'
AND col_a = 'A'
OR  <selected_option> = 'Ball'
AND col_a IN ('A' , 'B')
;```

By the way,
what is "sud"? (I'm not well at English.)
For example, you wrote...
For option 'A' only column values with 'A' option sud come
but for selection for Option 'B' all the vlues sud come from the table ...

10. Registered User
Join Date
May 2003
Location
USA
Posts
5,737
Originally Posted by tonkuma
By the way, what is "sud"? (I'm not well at English.)
That is not English.

11. Registered User
Join Date
Feb 2008
Location
Japan
Posts
3,483
Originally Posted by biswa
...

When user selects 'Ball' then all data containing 'A' and 'B' values ie. all the values from the table sud come.

The table has only value 'A' and 'B' for tht particulat column.

...
If it was guaranteed, the query might be simplified more, like...

Code:
```SELECT t.*
FROM  sample_case_biswa AS t
,  parameter
WHERE <selected_option> = 'Ant'
AND col_a = 'A'
OR  <selected_option> = 'Ball'
;```

12. Registered User
Join Date
Feb 2008
Location
Japan
Posts
3,483
The table has only value 'A' and 'B' for tht particulat column.
If the condition was guaranteed,
the query might be simplified more, like...
Code:
```SELECT t.*
FROM  sample_data AS t
WHERE col_a = 'A'
OR  selected_option = 'Ball'```

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•