Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
hi all,
anyone please help me in understanding below script including loops
Contact_SQL:
SQL SELECT
Id,
AccountId,
LastName,
FirstName,
Salutation,
OtherStreet,
OtherCity,
OtherState,
OtherPostalCode,
OtherCountry,
MailingStreet,
MailingCity,
MailingState,
MailingPostalCode,
MailingCountry,
Phone,
Fax,
MobilePhone,
HomePhone,
OtherPhone,
AssistantPhone,
ReportsToId,
Email,
Department,
AssistantName,
LeadSource,
Birthdate,
Description,
OwnerId,
CreatedDate,
CreatedById,
LastModifiedDate,
LastModifiedById,
SystemModstamp,
LastCURequestDate,
LastCUUpdateDate
FROM Contact
WHERE LastModifiedDate >= $(vLastExecTime) and LastModifiedDate < $(vExecTime);
// For the 1st reload, this section will be skipped.
if Not $(FirstReload) and not isnull(QvdCreateTime('$(vQVDPath)Contact_SQL.QVD'))then
Load *
FROM $(vQVDPath)Contact_SQL.qvd (qvd)
WHERE NOT EXISTS(Id);
end if
// If Opportunity table exists then proceed to the next step
if NoOfRows('Contact_SQL') > 0 then
STORE Contact_SQL INTO $(vQVDPath)Contact_SQL.qvd;
Contact:
Load
Id as [Contact Id],
AccountId as AccountId,
LastName as [Contact First Name],
FirstName as [Contact Last Name],
Salutation as [Contact Salutation],
Salutation & ' ' & FirstName & ' ' & LastName as [Contact Full Name],
OtherStreet as [Contact Other Street],
OtherCity as [Contact Other City],
OtherState as [Contact Other State],
OtherPostalCode as [Contact Other PostalCode],
OtherCountry as [Contact Other Country],
MailingStreet as [Contact Mailing Street],
MailingCity as [Contact Mailing City],
MailingState as [Contact Mailing State],
MailingPostalCode as [Contact Mailing PostalCode],
MailingCountry as [Contact Mailing Country],
Phone as [Contact Phone],
Fax as [Contact Fax],
MobilePhone as [Contact Phone (Mobile)],
HomePhone as [Contact Phone (Home)],
OtherPhone as [Contact Phone (Other)],
AssistantPhone as [Contact Phone (Assistant)],
Email as [Contact Email],
Department as [Contact Dept],
AssistantName as [Contact Assistant],
Birthdate as [Contact Birthday],
Description as [Contact Description],
OwnerId as [Contact Owner Id],
CreatedDate as [Contact CreatedDate],
CreatedById as [Contact CreatedById],
LastModifiedDate as [Contact LastModifiedDate],
LastModifiedById as [Contact LastModifiedById],
SystemModstamp as [Contact SystemModstamp],
LastCURequestDate as [Contact LastCURequestDate],
LastCUUpdateDate as [Contact LastCUUpdateDate]
RESIDENT Contact_SQL;
DROP TABLE Contact_SQL;
End if
regards
manu
There are no loops present in this script.
It boils down to something like:
From DBMS, load rows that fall in a certain window (see WHERE clause) into an internal table called Contact_SQL
IF FirstLoad is not set, and a previous copy of this table exists in a QVD file,
THEN
From QVD file, add all rows to the internal table Contact_SQL that don't already exist
END IF
IF Contact_SQL has more than 0 rows
THEN
STORE Contact_SQL into the external QVD file (overwrite if it already exists)
COPY Contact_SQL into internal table Contact
DELETE internal table Contact_SQL
END IF
Best,
Peter
Prior to the section of the script that you posted, I believe you will find the variables vLastExecTime, vExecTime and FirstReload set.
QvdCreateTime(\\Path\QVD_NAME) is a function that returns the timestamp of the QVD_NAME.
In this section, select data between the 2 timestamps, vLastExecTime and vExecTime.
FROM Contact
WHERE LastModifiedDate >= $(vLastExecTime) and LastModifiedDate < $(vExecTime);
In this section, if this is the first time the script is run, there should not be a qvd. If this is not the first time the script is run and there was not data selected during the first run, there should not be a qvd. If this is not the first run and a qvd exist, load the previous selected records from the qvd with the ignoring the records with Id that exists in the Contact_SQL that was just generated. You are basically concatenating the unique records in the qvd to the query above. Looks like you have only the last loaded Id record.
// For the 1st reload, this section will be skipped.
if Not $(FirstReload) and not isnull(QvdCreateTime('$(vQVDPath)Contact_SQL.QVD'))then
Load *
FROM $(vQVDPath)Contact_SQL.qvd (qvd)
WHERE NOT EXISTS(Id);
end if
In this section, if you have records you will create a new qvd or overwrite the existing qvd with the concatenated records.
// If Opportunity table exists then proceed to the next step
if NoOfRows('Contact_SQL') > 0 then
STORE Contact_SQL INTO $(vQVDPath)Contact_SQL.qvd;
In this section, create a new table Contact from Contact_SQL renaming fields and then you drop the original Contact_SQL.
Contact:
Load
Id as [Contact Id],
AccountId as AccountId,
LastName as [Contact First Name],
Thank you peter
Thanks jerry