Execute SELECT query using CASE/ IF...ELSE statement

Bhumika Lekurwale

Execute SELECT query using CASE/ IF...ELSE statement

Hi,

My sole purpose is to select fields(CUA1, PONO) from subquery one if CUA1 and PONO are not null

else select fields(CUA1, PONO) from subquery query two.

subquery one::  SELECT CUA1, PONO FROM TableX Join TableY on CONO = CONO AND ORNO = ORNO AND ADID = ADID WHERE ADRT = 1

subquery two::  SELECT CUA1, PONO FROM TableZ  Join TableY on CONO = CONO AND CUNO = CUNO AND ADID = ADID WHERE  ADRT = 1

Instead of IF..ELSE, I am fine with CASE STATEMENT as well.

I am trying to execute the below query but its failing. Can anyone correct me.

IF CUA1 IS NOT NULL AND PONO IS NOT NULL
BEGIN
    SELECT CUA1, PONO
    FROM TableX Join TableY on CONO = CONO AND ORNO = ORNO AND ADID = ADID 
    WHERE
     ADRT = 1
END
ELSE
BEGIN
    SELECT CUA1, PONO 
    FROM TableZ  
    Join TableY on CONO = CONO AND CUNO = CUNO AND ADID = ADID
    WHERE
     ADRT = 1
END;


Regards,
Bhumika

Phil Grainger

Execute SELECT query using CASE/ IF...ELSE statement
(in response to Bhumika Lekurwale)
Personally, I’d do this with a UNION – much simpler

________________________________

Phil Grainger

Enablement Manager

[login to unmask email]

Direct



+44 (0)118 921 8000

Mobile



+44(0)7808 643 479


E2, Eskdale Road
Winnersh
Berkshire
RG41 5TS


[http://media.cms.bmc.com/images/corp_signature_bmclogo_2014.jpg] http://www.bmc.com

[cid:[login to unmask email]






From: Bhumika Lekurwale [mailto:[login to unmask email]
Sent: 20 November 2017 14:37
To: [login to unmask email]
Subject: [DB2-L] - Execute SELECT query using CASE/ IF...ELSE statement


Hi,

My sole purpose is to select fields(CUA1, PONO) from subquery one if CUA1 and PONO are not null

else select fields(CUA1, PONO) from subquery query two.

subquery one:: SELECT CUA1, PONO FROM TableX Join TableY on CONO = CONO AND ORNO = ORNO AND ADID = ADID WHERE ADRT = 1

subquery two:: SELECT CUA1, PONO FROM TableZ Join TableY on CONO = CONO AND CUNO = CUNO AND ADID = ADID WHERE ADRT = 1

Instead of IF..ELSE, I am fine with CASE STATEMENT as well.

I am trying to execute the below query but its failing. Can anyone correct me.

IF CUA1 IS NOT NULL AND PONO IS NOT NULL

BEGIN

SELECT CUA1, PONO

FROM TableX Join TableY on CONO = CONO AND ORNO = ORNO AND ADID = ADID

WHERE

ADRT = 1

END

ELSE

BEGIN

SELECT CUA1, PONO

FROM TableZ

Join TableY on CONO = CONO AND CUNO = CUNO AND ADID = ADID

WHERE

ADRT = 1

END;


Regards,
Bhumika

-----End Original Message-----
BMC Software Limited Registered Office: Building E2, Eskdale Road, Winnersh, Wokingham, Berkshire, United Kingdom, RG41 5TS Registered in England No. 1927903 The content of this email is confidential. If you are not the addressee, you may not distribute, copy or disclose any part of it. If you receive this message in error, please delete this from your system and notify the sender immediately.
Attachments

  • image001.jpg (8k)
  • image002.png (5.9k)

Bhumika Lekurwale

RE: Execute SELECT query using CASE/ IF...ELSE statement
(in response to Phil Grainger)

Hi Phil,

But what if the Values for (CUA1, PONO) will be available in both the queries.

It will fetch records from both queries in case of UNION

Thanks,

Bhumika

Edited By:
Bhumika Lekurwale[Organization Members] @ Nov 22, 2017 - 10:11 AM (Asia/Calcutta)
Bhumika Lekurwale[Organization Members] @ Nov 22, 2017 - 10:13 AM (Asia/Calcutta)

Phil Grainger

Execute SELECT query using CASE/ IF...ELSE statement
(in response to Bhumika Lekurwale)
Depends whether you use UNION or UNION ALL surely

________________________________

Phil Grainger

Enablement Manager

[login to unmask email]

Direct



+44 (0)118 921 8000

Mobile



+44(0)7808 643 479


E2, Eskdale Road
Winnersh
Berkshire
RG41 5TS


[http://media.cms.bmc.com/images/corp_signature_bmclogo_2014.jpg] http://www.bmc.com

[cid:[login to unmask email]






From: Bhumika Lekurwale [mailto:[login to unmask email]
Sent: 21 November 2017 17:13
To: [login to unmask email]
Subject: [DB2-L] - RE: Execute SELECT query using CASE/ IF...ELSE statement


Hi Phil,

But what if the Values for (CUA1, PONO) will be available in both the query.

It will create duplicate records.

Thanks,

Bhumika

-----End Original Message-----
BMC Software Limited Registered Office: Building E2, Eskdale Road, Winnersh, Wokingham, Berkshire, United Kingdom, RG41 5TS Registered in England No. 1927903 The content of this email is confidential. If you are not the addressee, you may not distribute, copy or disclose any part of it. If you receive this message in error, please delete this from your system and notify the sender immediately.
Attachments

  • image001.jpg (8k)
  • image002.png (5.9k)

Bhumika Lekurwale

RE: Execute SELECT query using CASE/ IF...ELSE statement
(in response to Phil Grainger)

Hi Phil

Then I will try with UNION, lets see what I will get.

Regards,

Bhumika

Michael Hannan

RE: Execute SELECT query using CASE/ IF...ELSE statement
(in response to Bhumika Lekurwale)

Bhumika,

Here is how I would handle your problem easily in DB2 for zOS:

SELECT COALESCE(Z.CUA1,X.CUA1) AS CUA1
    , COALESCE(Z.PONO,X.PONO) AS PONO 
FROM TableY Y
Left Join TableX X 
on X.CONO = Y.CONO
and X.ORNO = Y.ORNO 
and X.ADID = Y.ADID 
and ADRT = 1
Left Join TableZ Z 
on Z.CONO = Y.CONO
and Z.ORNO = Y.ORNO 
and Z.ADID = Y.ADID 
and (X.CUA1 is null OR X.PONO is null)
and ADRT = 1

You will note that Left join to Z only happens if Left Join to X finds Null values. If join to Z did not happen then Z columns will have Null values. Coalesce function can then choose the X values. 

Hope I have interpreted your problem correctly. I did not know which tables contained column ADRT. Union with Subqueries was another way to do it in the very old days when we had no Outer Joins.

Michael Hannan
 
In Reply to Bhumika Lekurwale:

Hi,

My sole purpose is to select fields(CUA1, PONO) from subquery one if CUA1 and PONO are not null

else select fields(CUA1, PONO) from subquery query two.

subquery one::  SELECT CUA1, PONO FROM TableX Join TableY on CONO = CONO AND ORNO = ORNO AND ADID = ADID WHERE ADRT = 1

subquery two::  SELECT CUA1, PONO FROM TableZ  Join TableY on CONO = CONO AND CUNO = CUNO AND ADID = ADID WHERE  ADRT = 1

Instead of IF..ELSE, I am fine with CASE STATEMENT as well.

I am trying to execute the below query but its failing. Can anyone correct me.

IF CUA1 IS NOT NULL AND PONO IS NOT NULL
BEGIN
    SELECT CUA1, PONO
    FROM TableX Join TableY on CONO = CONO AND ORNO = ORNO AND ADID = ADID 
    WHERE
     ADRT = 1
END
ELSE
BEGIN
    SELECT CUA1, PONO 
    FROM TableZ  
    Join TableY on CONO = CONO AND CUNO = CUNO AND ADID = ADID
    WHERE
     ADRT = 1
END;


Regards,
Bhumika

 

Michael Hannan,
DB2 Application Performance Specialist
CPT Global Ltd

Edited By:
Michael Hannan[Organization Members] @ Dec 10, 2017 - 06:04 AM (Europe/Berlin)
Michael Hannan[Organization Members] @ Dec 10, 2017 - 06:10 AM (Europe/Berlin)