- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Only load relevant records (Exists?)
All,
I have the below script. The first part loads Email records. The second part loads interactions with those emails. So 1 email can have 100 interactions for example.
I only want to load interactions from existing email records. Otherwise the dataset would be to large. I tried the below script with an exists function but that doesn't work.
Any help to get this working would be appreciated!
Regards, Paul
TempEmailSend:
SQL SELECT
`cdi_campaignid_Name`,
`cdi_fromname`,
`cdi_openscount`,
`cdi_senton`,
`cdi_subject`,
statuscode,
`Id`
FROM .....`_emailsend`
EmailSend:
Load
`cdi_campaignid_Name` as Campaign_Name,
`cdi_fromname` as ES_From,
`cdi_openscount` as ES_OpenCount,
Date(`cdi_senton`) as Datum,
`cdi_subject` as ES_Subject,
`Id` as `cdi_emailsendid_Id`
Resident TempEmailSend
Where statuscode = 'Sent';
Drop Table TempEmailSend;
TempEE:
SQL SELECT
`cdi_emailsendid_Id`,
`cdi_type` as EE_Type,
`cdi_url` as EE_URL,
"cdi_time" as EE_Date
FROM .....`_emailevent`
Where Exists (cdi_emailsendid_Id);
Accepted Solutions
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Hi Paul,
don't use "exists" in your SQL select. The Exists in SQL has different entry. Try this:
EmailSend:
LOAD *;
SQL SELECT
`cdi_campaignid_Name`,
`cdi_fromname`,
`cdi_openscount`,
`cdi_senton`,
`cdi_subject`,
statuscode,
`Id`
FROM .....`_emailsend`
WHERE statuscode = 'Sent';
TempEE:
LOAD *
WHERE Exists (Id,cdi_emailsendid_Id);
SQL SELECT
`cdi_emailsendid_Id`,
`cdi_type` as EE_Type,
`cdi_url` as EE_URL,
"cdi_time" as EE_Date
FROM .....`_emailevent`;
Thanks.
BR,
Martin
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
try
WHERE EXISTS(Id, cdi_emailsendid_Id) ;
The first parameter should be a previously existing field, and the second a field or expression in the current load.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Nope got the following error:
Connector reply error: SQL##f - SqlState: 42000, ErrorCode: 4294967295, ErrorMsg: Malformed SQL Statement: Expected token 'FROM' but found 0 with value 'Id' instead at position 222. Statement:SELECT `cdi_emailsendid_Id`, `cdi_contactid_Name` as Contact_FullName, `cdi_type` as EE_Type, `cdi_url` as EE_URL, "cdi_time" as EE_Date FROM CData.DynamicsCRM.`cdi_emailevent` Where Exists (Id, cdi_emailsendid_Id)
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Hi Paul,
don't use "exists" in your SQL select. The Exists in SQL has different entry. Try this:
EmailSend:
LOAD *;
SQL SELECT
`cdi_campaignid_Name`,
`cdi_fromname`,
`cdi_openscount`,
`cdi_senton`,
`cdi_subject`,
statuscode,
`Id`
FROM .....`_emailsend`
WHERE statuscode = 'Sent';
TempEE:
LOAD *
WHERE Exists (Id,cdi_emailsendid_Id);
SQL SELECT
`cdi_emailsendid_Id`,
`cdi_type` as EE_Type,
`cdi_url` as EE_URL,
"cdi_time" as EE_Date
FROM .....`_emailevent`;
Thanks.
BR,
Martin
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
try a presceding load like this:
TempEE:
Load cdi_emailsendid_Id,
EE_Type
EE_URL,
EE_Date
Where Exists (cdi_emailsendid_Id);
;
SQL SELECT
`cdi_emailsendid_Id`,
`cdi_type` as EE_Type,
`cdi_url` as EE_URL,
"cdi_time" as EE_Date
FROM .....`_emailevent`
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Great that worked!
Question; I hoped it would speed up my load process but it seems to cost even more time. Is there a way that I can make my load faster?
Regards, Paul
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Hi zhadrakas,
on the EmailSend Table, the field Id is renamed as `cdi_emailsendid_Id`.. so he can't use the Id no more on the where exists
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Hi Paul,
exists itself is quite memory consuming. In other hand you can put your Exists into SQL and leave the job to be done by DB, but I am not sure whether that would decrease reload time or not. Maybe use 64bit connector instead of 32bit (if you do not so and if it is possible), try to reduce number of fields to minimum...but this is more like general advice you are probably aware of...
Thanks.
BR
Martin
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
what about combining the queries into one SQL:
somethink like this
TempEmailSend:
SQL SELECT
SEND.`cdi_campaignid_Name` as Campaign_Name,
SEND.`cdi_fromname` as ES_From,
SEND. `cdi_openscount` as ES_OpenCount,
SEND.`cdi_senton`, as Datum,
SEND.`cdi_subject` as ES_Subject,
SEND.statuscode ,
SEND.`Id` as `cdi_emailsendid_Id`,
EVENT.`cdi_type` as EE_Type,
EVENT.`cdi_url` as EE_URL,
EVENT.`cdi_time` as EE_Date
FROM `_emailsend` SEND,
`_emailevent` EVENT
Where SEND.statuscode = 'Sent'
AND EVENT.`cdi_emailsendid_Id = SEND.`Id`