Create Table Tester1 (Name varchar2(20), ID1 int, ID2 int , ID3 int, ID4 int, City1 varchar2(30), City2 varchar2(30), BIRTHMONTH varchar2(30))
INSERT INTO TESTESR1 VALUES('ABC',100,200,300,400, 'DUBLIN','SIERRS','MARCH')
INSERT INTO TESTESR1 VALUES('BCD',100,200,200,400, 'NEWYORK',NULL,'APRIl')
INSERT INTO TESTESR1 VALUES('BCD',100,500,200,400, NULL,'JERSEY','JANUARY')
INSERT INTO TESTESR1 VALUES('BCD',100,500,200,444, 'KINGSTON','JERSEY123','FEBRUARY')
INSERT INTO TESTESR1 VALUES('BCD',100,555,200,444, 'VIJAR','BALTIMORE','FEBRUARY')
Create Table CMC_RAW_STG (CODE varchar2(20), CityVALUE varchar2(30) )
INSERT INTO CMC_RAW_STG VALUES ('D12345','DUBLIN');
INSERT INTO CMC_RAW_STG VALUES ('S12345','SIERRS');
INSERT INTO CMC_RAW_STG VALUES ('N12345','NEWYORK');
INSERT INTO CMC_RAW_STG VALUES ('K12345','KINGSTON');
INSERT INTO CMC_RAW_STG VALUES ('B12345','BALTIMORE');
The below is what my current code looks like, to get B.CODE from CMC_RAW_STg table i need to use LEFTJOIN City1, City2 values.
How to make it single query or more efficiant, the reason i am asking is in my real query i have many columns in select list also need to do multiple unions in order to eliminate that clutter just looking for any easy and efficiant solution to rewrite this query
Please advise
your throughts please...
SELECT A.Name,
A.ID1,
A.ID2,
A.ID3,
A.ID4,
B.Code
A.BIRTHMONTH
FROM Tester1 A
LEFT JOIN CMC_RAW_STG B
ON B.SRC = 'COMMON WEALTH'
AND B.MGR = 'MANAGE ORIENT'
AND B.CITYVALUE = A.City1
UNION
SELECT A.Name,
A.ID1,
A.ID2,
A.ID3,
A.ID4,
B.Code
A.BIRTHMONTH
FROM Tester1 A
LEFT JOIN CMC_RAW_STG B
ON B.SRC = 'COMMON WEALTH'
AND B.MGR = 'MANAGE ORIENT'
AND B.CITYVALUE = A.City2
Thanks
ASita