MVSFORUMS.com :: View topic

MVSFORUMS.com
A Community of and for MVS Professionals

FAQ

Quick Manuals

Profile

SELECT query

MVSFORUMS.com Forum Index -> Database

View previous topic :: View next topic

Author

Message

danm
Intermediate

Joined: 29 Jun 2004
Posts: 170
Topics: 73

Posted: Fri Jun 16, 2006 11:43 am Post subject: SELECT query

Code:

COL1 COL2
---- ----
XX A
XX A
XX B
XX C
XX D
YY A
YY A
YY C

This is what I want:
1. Get the rows where col1 = 'XX'
2. If no rows is found, get the rows where col1 = 'YY'

Examples:

SELECT COL1, COL2 FROM TABLE1
WHERE COL1 = 'XX'
UNION ALL
SELECT * FROM TABLE1
WHERE COL1 = 'YY'
AND NOT EXISTS(SELECT COL1, COL2 FROM TABLE1
WHERE COL1 = 'XX')
ORDER BY COL1, COL2;

COL1 COL2
---- ----
XX A
XX A
XX B
XX C
XX D

SELECT COL1, COL2 FROM TABLE1
WHERE COL1 = 'ZZ'
UNION ALL
SELECT * FROM TABLE1
WHERE COL1 = 'YY'
AND NOT EXISTS(SELECT COL1, COL2 FROM TABLE1
WHERE COL1 = 'ZZ')
ORDER BY COL1, COL2;

COL1 COL2
---- ----
YY A
YY A
YY C

The SELECT statement is used 3 times. Is there a simpler way?

kolusu
Site Admin

Joined: 26 Nov 2002
Posts: 12388
Topics: 75
Location: San Jose

Posted: Fri Jun 16, 2006 12:12 pm Post subject:

Try this

Code:

SELECT *
FROM TABLE
WHERE COL1 = (SELECT CASE WHEN SUM(CASE COL1 WHEN 'XX'
THEN INT(1) ELSE INT(0) END) >= 1
THEN CHAR('XX')
ELSE
CHAR('YY') END
FROM TABLE)
;

Hope this helps...

Cheers

Kolusu
_________________
Kolusu
www.linkedin.com/in/kolusu

danm
Intermediate

Joined: 29 Jun 2004
Posts: 170
Topics: 73

Posted: Mon Jun 19, 2006 9:52 am Post subject:

It works fine. Thanks, Kolusu.

What if I want:
1. Get the rows where col1 = 'XX'
2. Get rows where col1 = 'YY' only if there was no match in col2

Example: Rows in Table4

Code:

COL1 COL2 COL3
---- ---- ----
XX A 1
XX A 2
XX B 3
XX C 4
XX D 5
YY A 21
YY A 22
YY C 23
YY I 24
YY I 25
YY J 26

SELECT COL2, COL3 FROM TABLE4
WHERE COL1 = 'XX'
UNION ALL
SELECT COL2, COL3 FROM TABLE4 T4
WHERE COL1 = 'YY'
AND NOT EXISTS(SELECT 1 FROM TABLE4
WHERE COL1 = 'XX'
AND COL2 = T4.COL2)
ORDER BY COL2, COL3;

COL2 COL3
---- ----
A 1
A 2
B 3
C 4
D 5
I 24
I 25
J 26

Can the above query be simplified?

kolusu
Site Admin

Joined: 26 Nov 2002
Posts: 12388
Topics: 75
Location: San Jose

Posted: Mon Jun 19, 2006 10:19 am Post subject:

Danm,

Try this Query

Code:

SELECT *
FROM TABLE
WHERE COL1 = 'XX'
OR (COL1 = 'YY' AND COL2 NOT IN (SELECT DISTINCT COL2
FROM TABLE
WHERE COL1 = 'XX'))

Hope this helps...

Cheers

Kolusu
_________________
Kolusu
www.linkedin.com/in/kolusu

danm
Intermediate

Joined: 29 Jun 2004
Posts: 170
Topics: 73

Posted: Mon Jun 19, 2006 1:05 pm Post subject:

Thanks agin, Kolusu.

danm
Intermediate

Joined: 29 Jun 2004
Posts: 170
Topics: 73

Posted: Fri Nov 03, 2006 3:21 pm Post subject:

Code:

SELECT *
FROM TABLE
WHERE COL1 = (SELECT CASE WHEN SUM(CASE COL1 WHEN 'XX'
THEN INT(1) ELSE INT(0) END) >= 1
THEN CHAR('XX')
ELSE
CHAR('YY') END
FROM TABLE)
;

1. I try to use a host variable (e.g. :DATAX; datax = 'XX') instead of CHAR('XX') but was unsuccessful.

2. I was also unsuccessful to get it to work with a where clause of 2 columns:
a. Get the rows where col1 = 'XX' and Col2 = 'A'
b. If no rows is found, get the rows where col1 = 'YY' and Col2 = 'A'

Any suggestion?

Display posts from previous:

	MVSFORUMS.com Forum Index -> Database	All times are GMT - 5 Hours
Page 1 of 1

You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum