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
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,
from session.incomm a left join qis.rplogonuser b
/**** 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 ***/
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
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.
Registration is open! SAS is returning to Vegas for an AI and analytics experience like no other! Whether you're an executive, manager, end user or SAS partner, SAS Innovate is designed for everyone on your team. Register for just $495 by 12/31/2023.
If you are interested in speaking, there is still time to submit a session idea. More details are posted on the website.