join two Oracle tables passthrough

[jhh197]

Hi ,

I have two datasets in oracle test1 and test2 and test1 has 3 columns pid, mid and sid which we need to concatenate to form a join key premid1 so that we can join to premid in test2 table in oracle 

Test1 

State	PID	MID	SID	Amount	MDT	RCRD_IND	SCOUNT	MFLAG
VA	100988	700	520650000	100	202111	Y	1	Y
VA	100345	1	56341040	200	202111	N	1	N

I need to form premid1 using lpad and concatenation of PID,MID and SID to form join key (premid1) and also have filters like in code below to select records from test1 

Test2 

State	Prem_ID	SPL
VA	000100988-0700-000520650000	23540
VA	000100345-0001-000056341040	42522
VA	000200346-0009-000036984700	34521

Now it has Prem_ID 

I have used below code 

PROC SQL ;
CONNECT to ORACLE as ORA(authdomain=xxxx path= xxxx connection=global);
Create Table pt_level as
Select * From CONNECTION TO ORA
(
WITH TMP_HDR AS
(
select
Case when SS.State = 'VA'
then LPAD (ss.PID, 9, 0) || '-' || LPAD (ss.MID, 4, 0) || '-' || LPAD (ss.SID,12,0)
else 'ERROR'
End as prem_id1,
SS.state,
ss.amount

From
M_v.TEST1 SS
Where State = 'VA'
AND MDT= 202112
and RCRD_IND = 'Y'
and SCOUNT = 1
and MFLAG = 'Y'
),

TMP_DTL AS
(
SELECT DISTINCT
SP.STATE,
SP.PID,
SP.SID
PC.SPL,
sp.amount

From TMP_HDR SP
left JOIN TEST2 PC on SP.STATE = PC.STATE
and SP.PREMID1 = PC.PREM_ID

GROUP BY STATE,PID,SID
),
SELECT H.*
FROM TMP_DTL H

);
DISCONNECT FROM ORACLE ;
QUIT;

Is there any other way i can try to join both tables ?

Can anyone please help . Thank you 

[LinusH]

First, why are you not happy with your current solution?

Second, this is pure Oracle PL SQL. If intend to stick with that, you should probably turn to an Oracle forum instead.

[s_lassen · Accepted solution]

I do not think you need to make it that complicated. Something like this should work:

PROC SQL ;
CONNECT to ORACLE as ORA(authdomain=xxxx path= xxxx connection=global);
Create Table pt_level as
Select * From CONNECTION TO ORA
(select
    test1.STATE,
    test1.PID,
    test1.SID
    test2.SPL,
    test1.amount
 from  
    test1 left join test2 on
	  test1.state=test2.state and 
	  test2.PREM_ID=LPAD (test1.PID, 9, 0) || '-' || LPAD (test1.MID, 4, 0) || '-' || LPAD (test1.SID,12,0)
  where 
	test1.state='VA' and 
	test1.MDT= 202112 and
    test1.RCRD_IND = 'Y' and
    test1.SCOUNT = 1 and
    test1.MFLAG = 'Y'
  );	

DISCONNECT FROM ORACLE ;
QUIT;

[jhh197]

Hi ,
I am checking this one .

Thank you so much for helping

[jhh197]

Hi ,
I am checking this one

Thank you so much for helping

[jhh197]

Thank you so much for all your help