Adding another subselect into larger SQL

Babloo — Tue, 25 Sep 2018 14:27:43 GMT

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:

Re: Adding another subselect into larger SQL

ChrisHemedinger — Tue, 25 Sep 2018 14:33:29 GMT

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

topic Re: Adding another subselect into larger SQL in SAS Procedures

Adding another subselect into larger SQL

Re: Adding another subselect into larger SQL