Home » SQL & PL/SQL » SQL & PL/SQL » COALESCE and CASE
COALESCE and CASE [message #686781] |
Tue, 03 January 2023 09:10 |
|
questvba
Messages: 15 Registered: July 2022
|
Junior Member |
|
|
Hi,
I have the following SQL:
WITH cou AS (
SELECT 'Jean' AS col1, 'Claude' AS col2, NULL AS col3, NULL as col4 FROM DUAL
UNION
SELECT 'Laurent' AS col1, NULL AS col2, NULL AS col3, NULL as col4 FROM DUAL
UNION
SELECT NULL AS col1, 'Sylvain' AS col2, NULL AS col3, NULL as col4 FROM DUAL
UNION
SELECT NULL AS col1, NULL AS col2, '00000326' AS col3, NULL as col4 FROM DUAL
UNION
SELECT NULL AS col1, NULL AS col2, NULL AS col3, 'ZZZ0' as col4 FROM DUAL
UNION
SELECT NULL AS col1, NULL AS col2, '00090521' AS col3, NULL as col4 FROM DUAL
)
SELECT
COALESCE(
CASE
WHEN cou.col1 IS NOT NULL AND cou.col2 IS NOT NULL THEN cou.col1 || '-' || cou.col2
ELSE COALESCE(cou.col1,cou.col2)
END,cou.col3, cou.col4) AS response1
,COALESCE(cou.col1 || '-' || cou.col2, cou.col3, cou.col4) AS response2
FROM cou
;
What result is expected? If I have a data in col1 or col2 then I concatenate the data otherwise I take the data in col3 or col4.
For the moment, I use a CASE in the COALESCE.
Do you think it is possible to remove the CASE. I tried in response2 but it obviously doesn't work from line 4 as it adds the '-'.
If you have any ideas... Thanks
And above all, I wish you a great year 2023 with many challenges. And good health!
BR,
Lionel
|
|
|
Re: COALESCE and CASE [message #686782 is a reply to message #686781] |
Tue, 03 January 2023 13:13 |
|
Michel Cadot
Messages: 68694 Registered: March 2007 Location: Nanterre, France, http://...
|
Senior Member Account Moderator |
|
|
Yes it can be done without CASE...
SQL> WITH cou AS (
2 SELECT 'Jean' AS col1, 'Claude' AS col2, NULL AS col3, NULL as col4 FROM DUAL
3 UNION
4 SELECT 'Laurent' AS col1, NULL AS col2, NULL AS col3, NULL as col4 FROM DUAL
5 UNION
6 SELECT NULL AS col1, 'Sylvain' AS col2, NULL AS col3, NULL as col4 FROM DUAL
7 UNION
8 SELECT NULL AS col1, NULL AS col2, '00000326' AS col3, NULL as col4 FROM DUAL
9 UNION
10 SELECT NULL AS col1, NULL AS col2, NULL AS col3, 'ZZZ0' as col4 FROM DUAL
11 UNION
12 SELECT NULL AS col1, NULL AS col2, '00090521' AS col3, NULL as col4 FROM DUAL
13 )
14 SELECT NVL2(col1||col2, TRIM('-' FROM col1||'-'||col2), COALESCE(col3, col4))
15 FROM cou
16 /
NVL2(COL1||COL2
---------------
Jean-Claude
Laurent
Sylvain
00000326
00090521
ZZZ0
But it is harder to maintain than a single CASE:
SQL> WITH cou AS (
2 SELECT 'Jean' AS col1, 'Claude' AS col2, NULL AS col3, NULL as col4 FROM DUAL
3 UNION
4 SELECT 'Laurent' AS col1, NULL AS col2, NULL AS col3, NULL as col4 FROM DUAL
5 UNION
6 SELECT NULL AS col1, 'Sylvain' AS col2, NULL AS col3, NULL as col4 FROM DUAL
7 UNION
8 SELECT NULL AS col1, NULL AS col2, '00000326' AS col3, NULL as col4 FROM DUAL
9 UNION
10 SELECT NULL AS col1, NULL AS col2, NULL AS col3, 'ZZZ0' as col4 FROM DUAL
11 UNION
12 SELECT NULL AS col1, NULL AS col2, '00090521' AS col3, NULL as col4 FROM DUAL
13 )
14 SELECT CASE
15 WHEN col1 IS NOT NULL AND col2 IS NOT NULL THEN col1||'-'||col2
16 WHEN col1 IS NOT NULL THEN col1
17 WHEN col2 IS NOT NULL THEN col2
18 WHEN col3 IS NOT NULL THEN col3
19 WHEN col4 IS NOT NULL THEN col4
20 ELSE ''
21 END
22 FROM cou
23 /
CASEWHENCOL1ISN
---------------
Jean-Claude
Laurent
Sylvain
00000326
00090521
ZZZ0
Yes it is longer to write but you write it once and many will have to maintain it later.
[Updated on: Tue, 03 January 2023 13:15] Report message to a moderator
|
|
|
|
|
|
|
Goto Forum:
Current Time: Sun Sep 29 00:36:34 CDT 2024
|