Combine multiple SQL into a single step via left join

Babloo — Wed, 26 Sep 2018 02:23:33 GMT

Appreciate if someone of you guide me to combine the following SQL into one single step via left join. session.incomm table is common for all the below SQL.

/* Inbox for communication */
create table incomm4 as
select distinct b.identifier as inboxforcommunicationID, b.name as afdeling
from session.incomm a left join qis.rpinboxforcommunication b
on a.inboxforcommunicationID=b.identifier
where b.identifier in ('00000000001','10000000000','10000000001','10000000003')
);
/* createur */
create table incomm5 as
select distinct createdbylogonuserid, left(trim(lastname))||' '||left(trim(firstname)) as creator, creator_duser
from connection to db2(
select 	b.identifier as createdbylogonuserid,
		b.externalidentifier as creator_duser,	
		b.lastname,b.firstname
from session.incomm a  left join qis.rplogonuser b
on a.createdbylogonuserid=b.identifier
);
/**** INFO CLIENT ***/
create table incomm6 as
select x.policyinstanceid , x.customerID, y.value as client_segment from
(select b.identifier as policyinstanceid , b.customerID
from session.incomm a left join qis.rppolicyinstance b
on a.policyinstanceid=b.identifier) as x left join qis.rpcustomerfield y
on  x.customerid=y.customerid where y.externalidentifier='customerSubsegmentGE'
);
/**** INFO Policy ***/
proc sql;
create table incomm8 as
select distinct x.*, y.name as product from
(select b.identifier as policyid   , b.productid
	from session.incomm a left join qis.rppolicy b
	on a.policyid=b.identifier) as x left join qis.rpproduct y
	on x.productid=y.identifier
);

quit;

Re: Combine multiple SQL into a single step via left join

LinusH — Thu, 27 Sep 2018 01:53:27 GMT

Since none of the queries is reusing any result from any of the other queries I can't see how they can be combined. Unless that there's succeeding steps that you don't share.

And right now the code is totally in DB2 SQL which would not make this a SAS question. On the other hand, I can't see any DB2 specifics in the code, so it could easily be transformed to implicit SQL, which IMO is easier to read and maintain.

topic Combine multiple SQL into a single step via left join in SAS Procedures

Combine multiple SQL into a single step via left join

Re: Combine multiple SQL into a single step via left join