BookmarkSubscribeRSS Feed
Babloo
Rhodochrosite | Level 12

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;
1 REPLY 1
LinusH
Tourmaline | Level 20
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.
Data never sleeps

sas-innovate-2024.png

📢

ANNOUNCEMENT

The early bird rate has been extended! Register by March 18 for just $695 - $100 off the standard rate.

 

Check out the agenda and get ready for a jam-packed event featuring workshops, super demos, breakout sessions, roundtables, inspiring keynotes and incredible networking events. 

 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 1 reply
  • 501 views
  • 0 likes
  • 2 in conversation