1

I've 3 DE

  1. MASTER DE with fields SubscriberKey(PK), donottrack, Createddate, Modifieddate
  2. Staging DE with field CustomerID(PK), Createddate
  3. 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.

5
  • stackoverflow.com/questions/10256848/… . Should help you! Oct 19 at 7:13
  • Thanks for the comment Swati, but it's not the specific use case I'm looking for.
    – darkCloud
    Oct 20 at 6:33
  • Your first LEFT JOIN does not have an ON statement but is followed by a FULL JOIN.
    – shd.lux
    Oct 20 at 11:51
  • Please update your question to include a list of the fields in ent.[ReferenceDE]. Oct 21 at 14:15
  • Hi @AdamSpriggs - In ReferenceDE, there would be only one field which is "DonotTrack" and I created on record in this DE just to reference the value of it.
    – darkCloud
    Oct 27 at 12:13
0

In the absence of details about ent.[ReferenceDE], here's my interpretation:

For all rows in Staging_DE2 that match a row in ent.[ReferenceDE] use the DoNotTrack value from ent.[ReferenceDE], otherwise use the DoNotTrack value from Staging_DE2.

Select 
  a.SubscriberKey
, b.Created_Date
, b.Last_Modified_Date
, case 
     when r.SubscriberKey is null then a.DoNotTrack
     else r.DoNotTrack
  end DoNotTrack
from ent.[STAGING_DE2] a
left join ent.[ReferenceDE] r ON r.SubscriberKey = LEFT(a.SubscriberKey,10)

Or it could be simpler with a COALESCE, which returns the first non-null value in from the specified fields/expressions:

Select 
  a.SubscriberKey
, b.Created_Date
, b.Last_Modified_Date
, coalesce(r.DoNotTrack,a.DoNotTrack) DoNotTrack 
from ent.[STAGING_DE2] a
left join ent.[ReferenceDE] r ON r.SubscriberKey = LEFT(a.SubscriberKey,10)
1
  • Hi Adam, thanks for the reply. So in reference DE there would be only one field which is DonotTrack. I'm doing left join Staging DE with Master DE based on subscriberKey first 10 digit. If the record of Staging DE is not present in Master DE, then get the value of DonotTrack as False which is default set in Reference DE. There would be no other field in Reference DE apart from this. The catch here we are going to have like 200 queries so we cannot set False as Donot track if we don't found in Master. Instead of that we will just change reference DE value,that will flow in each query.
    – darkCloud
    Oct 22 at 13:07

Not the answer you're looking for? Browse other questions tagged or ask your own question.