BookmarkSubscribeRSS Feed
Babloo
Rhodochrosite | Level 12

could someone of you guide me to combine the following SQL into the master SQL?

 

 SQL to combine in the below master  SQL (big one)

select x.policyinstanceid , x.customerID, y.value as client_segment from
(select f.identifier as policyinstanceid , f.customerID
from session.incomm a left join qis.rppolicyinstance f
on a.policyinstanceid=f.identifier) as x left join qis.rpcustomerfield y
on  x.customerid=y.customerid where y.externalidentifier='customerSubsegmentGE'
proc sql noprint;
	connect to &glb_db2.(datasrc=&glb_db2_src. Authdomain=DB2AuthODS connection=global);
	create table incomm2 as
		select * from connection to db2(

with temp1 as (select  y.ExternalIdentifier as commnr,
		y.communicationtypeid,
		y.communicationdescriptionid,
		y.inboxforcommunicationID,
		y.isincoming as incoming,
		y.policyid,
   		y.policyinstanceid,
   		y.tscommunicationcreated,
   		y.datetobecompleted,
   		y.createdbylogonuserid,
		z.externalidentifier as polisnr
from qis.rpcommunication y, qis.rppolicy z
where y.policyid=z.identifier) 

		select b.name as documenttype, b.identifier as communicationdescriptionid,c.identifier as communicationtypeid ,c.externalidentifier,c.Name as commtype,
			e.identifier as createdbylogonuserid,
			e.externalidentifier as creator_duser,	
			e.lastname,e.firstname 
		from temp1 as a
			left join  qis.rpcommunicationdescription as b
				on         a.communicationdescriptionid=b.identifier
			left join  qis.rpcommunicationtype as c
				on         a.communicationtypeid=c.identifier
		left join  
(select distinct identifier as inboxforcommunicationid,name as afdeling from qis.rpinboxforcommunication where identifier in ('00000000001','10000000000','10000000001','10000000003')) as d
				on         a.inboxforcommunicationID=d.inboxforcommunicationid
			left join  qis.rplogonuser as e
				on         a.createdbylogonuserid=e.identifier


);
	disconnect from db2;  
quit;

Master SQL:

 

 

 

1 REPLY 1
ChrisHemedinger
Community Manager

Hi @Babloo - as this is similar / related to some of your other recent posts, I decided to chime in.

 

It's clear to me from the code you shared that the data model is complex, and only a person with a good understanding of the relationships between these tables can provide a good answer.  I don't think it's reasonable to expect a solution for the specific code question here.

 

However, if you have questions about good practices for joining tables, using subqueries in the process, or efficiencies that can be gained via a single query instead of multiple table pulls -- then those questions are fair game.  Just make sure that the experts here don't need too much domain knowledge about your specific data to respond.

 

Also, please avoid posting multiple versions of the same/similar question.  Since it's the same pool of experts reading/evaluating each topic, it's better if you keep a single topic together in a thread until it's solved.

 

Chris

Register for SAS Innovate 2025!! The premier event for SAS users, May 6-9 in Orlando FL. Sign up now for the best deals!

sas-innovate-wordmark-2025-midnight.png

Register Today!

Join us for SAS Innovate 2025, our biggest and most exciting global event of the year, in Orlando, FL, from May 6-9. Sign up by March 14 for just $795.


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.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 1 reply
  • 806 views
  • 2 likes
  • 2 in conversation