I've 3 DE
- MASTER DE with fields SubscriberKey(PK), donottrack, Createddate, Modifieddate
- Staging DE with field CustomerID(PK), Createddate
- Reference DE with field donottrack default value false.
SubscriberKey would be like - 10digit(numeric)_emailId and CustomerId - 10digit(numeric).
I'm able to solve the above problem statement with CROSS JOIN. Below is my Queries
Select a.SubscriberKey, b.Created_Date, b.Last_Modified_Date, CASE WHEN LEFT (a.SubscriberKey,10) = (b.Customer_ID) THEN b.DoNotTrack ELSE r.DoNotTrack END as DoNotTrack from ent.[STAGING_DE2] a LEFT JOIN ent.[MASTER_CUSTOMER_CONSENT_DATA] b ON LEFT (a.SubscriberKey,10) = (b.Customer_ID) CROSS JOIN ent.[ReferenceDE] r
But If I don't want to use CROSS JOIN, Just want to save Result of 1st half of SQL in DE and then again write 1 more SQL query to update the value of DonotTrack field with value from other DE. How could I do that?. It will look like something below,
1st SQL result stored in DE :
Select a.SubscriberKey, b. DoNotTrack, a.Created_Date, a.Last_Modified_Date from ent.[STAGING_DE2] a LEFT JOIN ent.[MASTER_CUSTOMER_CONSENT_DATA] b ON LEFT (a.SubscriberKey,10) = b.Customer_ID
2nd SQL will update the same DE, but took value of DonotTrack from Reference DE, query I'm trying to build like - I'm able to solve this too, see my below query
Select a.SubscriberKey, a.Created_Date, a.Last_Modified_Date, r.DoNotTrack as DoNotTrack FROM ent.[RESULTING_DE] a FULL JOIN ent.[ReferenceDE] r ON 1=1 WHERE a.DoNotTrack = 'False'
Since I was not having any common field in above both DE, but I just want to reference the Donottrack value from Reference DE, I've applied above solution and it worked.