Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi all,
Need another set of eyes....can anyone see why my Do while would not work? It should do it while length of vURL is > 0.
Thanks
LET vURL = 'https://api8preview.sapsf.com:443/odata/v2/Candidate';
//CUSTOM CONNECT TO "Provider=QvRestConnector.exe;url=$(vURL);timeout=900;method=GET;sendExpect100Continue=True;autoDetectResponseType=true;checkResponseTypeOnTestConnection=true;keyGenerationStrategy=0;authSchema=basic;skipServerCertificateValidation=false;useCertificate=No;certificateStoreLocation=CurrentUser;certificateStoreName=My;queryParameters=$format%2json;PaginationType=None;allowResponseHeaders=false;allowHttpsOnly=false;XUserId=XKQJXJZIBCRaUEZJSBQIWQZOFLeIHZVMFDVCDHB;XPassword=PCRGRSUEQLYGXZdOPTcIWSIGXLZYWRdNNLZCDZMGUJNKSYMf;";
DO while LEN(vURL) > 0
CUSTOM CONNECT TO "Provider=QvRestConnector.exe;url=$(vURL);timeout=900;method=GET;sendExpect100Continue=True;autoDetectResponseType=true;checkResponseTypeOnTestConnection=true;keyGenerationStrategy=0;authSchema=basic;skipServerCertificateValidation=false;useCertificate=No;certificateStoreLocation=CurrentUser;certificateStoreName=My;queryParameters=$format%2json;PaginationType=None;allowResponseHeaders=false;allowHttpsOnly=false;XUserId=XKQJXJZIBCRaUEZJSBQIWQZOFLeIHZVMFDVCDHB;XPassword=PCRGRSUEQLYGXZdOPTcIWSIGXLZYWRdNNLZCDZMGUJNKSYMf;";
RestConnectorMasterTable:
SQL SELECT
"__next",
"__KEY_d",
(SELECT
"candidateId",
"employeeJobTitle",
"serviceDate",
"cellPhone",
"address2",
"lastModifiedDateTime",
"homePhone",
"usersSysId",
"visibilityOption",
"publicIntranet",
"city",
"nickName",
"employeeServiceLine",
"externalCandidate",
"dateofAvailability",
"middleName",
"lastName",
"dataPrivacyId",
"primaryEmail",
"country",
"partnerSource",
"candidateLocale",
"employeeCostCenter",
"anonymized",
"partnerMemberId",
"consentToMarketing",
"privacyAcceptDateTime",
"lastLoginDateTime",
"firstName",
"candidateType",
"creationDateTime",
"zip",
"contactEmail",
"address",
"shareProfile",
"anonymizedDateTime",
"employeeNumber",
"__KEY_results",
"__FK_results",
(SELECT
"uri",
"type",
"__FK___metadata"
FROM "__metadata" FK "__FK___metadata"),
(SELECT
"__KEY_languages",
"__FK_languages",
(SELECT
"uri" AS "uri_u0",
"__FK___deferred"
FROM "__deferred" FK "__FK___deferred")
FROM "languages" PK "__KEY_languages" FK "__FK_languages"),
(SELECT
"__KEY_references",
"__FK_references",
(SELECT
"uri" AS "uri_u1",
"__FK___deferred_u0"
FROM "__deferred" FK "__FK___deferred_u0")
FROM "references" PK "__KEY_references" FK "__FK_references"),
(SELECT
"__KEY_jobsApplied",
"__FK_jobsApplied",
(SELECT
"uri" AS "uri_u2",
"__FK___deferred_u1"
FROM "__deferred" FK "__FK___deferred_u1")
FROM "jobsApplied" PK "__KEY_jobsApplied" FK "__FK_jobsApplied"),
(SELECT
"__KEY_prefix",
"__FK_prefix",
(SELECT
"uri" AS "uri_u3",
"__FK___deferred_u2"
FROM "__deferred" FK "__FK___deferred_u2")
FROM "prefix" PK "__KEY_prefix" FK "__FK_prefix"),
(SELECT
"__KEY_memberships",
"__FK_memberships",
(SELECT
"uri" AS "uri_u4",
"__FK___deferred_u3"
FROM "__deferred" FK "__FK___deferred_u3")
FROM "memberships" PK "__KEY_memberships" FK "__FK_memberships"),
(SELECT
"__KEY_candidateProfileExtension",
"__FK_candidateProfileExtension",
(SELECT
"uri" AS "uri_u5",
"__FK___deferred_u4"
FROM "__deferred" FK "__FK___deferred_u4")
FROM "candidateProfileExtension" PK "__KEY_candidateProfileExtension" FK "__FK_candidateProfileExtension"),
(SELECT
"__KEY_state",
"__FK_state",
(SELECT
"uri" AS "uri_u6",
"__FK___deferred_u5"
FROM "__deferred" FK "__FK___deferred_u5")
FROM "state" PK "__KEY_state" FK "__FK_state"),
(SELECT
"__KEY_education",
"__FK_education",
(SELECT
"uri" AS "uri_u7",
"__FK___deferred_u6"
FROM "__deferred" FK "__FK___deferred_u6")
FROM "education" PK "__KEY_education" FK "__FK_education"),
(SELECT
"__KEY_jobReqFwdCandidates",
"__FK_jobReqFwdCandidates",
(SELECT
"uri" AS "uri_u8",
"__FK___deferred_u7"
FROM "__deferred" FK "__FK___deferred_u7")
FROM "jobReqFwdCandidates" PK "__KEY_jobReqFwdCandidates" FK "__FK_jobReqFwdCandidates"),
(SELECT
"__KEY_outsideWorkExperience",
"__FK_outsideWorkExperience",
(SELECT
"uri" AS "uri_u9",
"__FK___deferred_u8"
FROM "__deferred" FK "__FK___deferred_u8")
FROM "outsideWorkExperience" PK "__KEY_outsideWorkExperience" FK "__FK_outsideWorkExperience"),
(SELECT
"__KEY_mobility",
"__FK_mobility",
(SELECT
"uri" AS "uri_u10",
"__FK___deferred_u9"
FROM "__deferred" FK "__FK___deferred_u9")
FROM "mobility" PK "__KEY_mobility" FK "__FK_mobility"),
(SELECT
"__KEY_tags",
"__FK_tags",
(SELECT
"uri" AS "uri_u11",
"__FK___deferred_u10"
FROM "__deferred" FK "__FK___deferred_u10")
FROM "tags" PK "__KEY_tags" FK "__FK_tags"),
(SELECT
"__KEY_customWorkSample",
"__FK_customWorkSample",
(SELECT
"uri" AS "uri_u12",
"__FK___deferred_u11"
FROM "__deferred" FK "__FK___deferred_u11")
FROM "customWorkSample" PK "__KEY_customWorkSample" FK "__FK_customWorkSample"),
(SELECT
"__KEY_community",
"__FK_community",
(SELECT
"uri" AS "uri_u13",
"__FK___deferred_u12"
FROM "__deferred" FK "__FK___deferred_u12")
FROM "community" PK "__KEY_community" FK "__FK_community"),
(SELECT
"__KEY_suffix",
"__FK_suffix",
(SELECT
"uri" AS "uri_u14",
"__FK___deferred_u13"
FROM "__deferred" FK "__FK___deferred_u13")
FROM "suffix" PK "__KEY_suffix" FK "__FK_suffix"),
(SELECT
"__KEY_candidateProfileConversionInfo",
"__FK_candidateProfileConversionInfo",
(SELECT
"uri" AS "uri_u15",
"__FK___deferred_u14"
FROM "__deferred" FK "__FK___deferred_u14")
FROM "candidateProfileConversionInfo" PK "__KEY_candidateProfileConversionInfo" FK "__FK_candidateProfileConversionInfo"),
(SELECT
"__KEY_certifications",
"__FK_certifications",
(SELECT
"uri" AS "uri_u16",
"__FK___deferred_u15"
FROM "__deferred" FK "__FK___deferred_u15")
FROM "certifications" PK "__KEY_certifications" FK "__FK_certifications"),
(SELECT
"__KEY_comments",
"__FK_comments",
(SELECT
"uri" AS "uri_u17",
"__FK___deferred_u16"
FROM "__deferred" FK "__FK___deferred_u16")
FROM "comments" PK "__KEY_comments" FK "__FK_comments")
FROM "results" PK "__KEY_results" FK "__FK_results")
FROM JSON (wrap off) "d" PK "__KEY_d"
//WITH CONNECTION (Url "$(vURL)")
;
[Candidate_MetaData]:
LOAD [uri],
[type],
[__FK___metadata] AS [__KEY_results]
RESIDENT RestConnectorMasterTable
WHERE NOT IsNull([__FK___metadata]);
[Languages]:
LOAD [__KEY_languages],
[__FK_languages] AS [__KEY_results]
RESIDENT RestConnectorMasterTable
WHERE NOT IsNull([__FK_languages]);
[References]:
LOAD [__KEY_references],
[__FK_references] AS [__KEY_results]
RESIDENT RestConnectorMasterTable
WHERE NOT IsNull([__FK_references]);
[JobsApplied]:
LOAD [__KEY_jobsApplied],
[__FK_jobsApplied] AS [__KEY_results]
RESIDENT RestConnectorMasterTable
WHERE NOT IsNull([__FK_jobsApplied]);
[Prefix]:
LOAD [__KEY_prefix],
[__FK_prefix] AS [__KEY_results]
RESIDENT RestConnectorMasterTable
WHERE NOT IsNull([__FK_prefix]);
[Memberships]:
LOAD [__KEY_memberships],
[__FK_memberships] AS [__KEY_results]
RESIDENT RestConnectorMasterTable
WHERE NOT IsNull([__FK_memberships]);
[CandidateProfileExtension]:
LOAD [__KEY_candidateProfileExtension],
[__FK_candidateProfileExtension] AS [__KEY_results]
RESIDENT RestConnectorMasterTable
WHERE NOT IsNull([__FK_candidateProfileExtension]);
[State]:
LOAD [__KEY_state],
[__FK_state] AS [__KEY_results]
RESIDENT RestConnectorMasterTable
WHERE NOT IsNull([__FK_state]);
[Education]:
LOAD [__KEY_education],
[__FK_education] AS [__KEY_results]
RESIDENT RestConnectorMasterTable
WHERE NOT IsNull([__FK_education]);
[JobReqFwdCandidates]:
LOAD [__KEY_jobReqFwdCandidates],
[__FK_jobReqFwdCandidates] AS [__KEY_results]
RESIDENT RestConnectorMasterTable
WHERE NOT IsNull([__FK_jobReqFwdCandidates]);
[OutsideWorkExperience]:
LOAD [__KEY_outsideWorkExperience],
[__FK_outsideWorkExperience] AS [__KEY_results]
RESIDENT RestConnectorMasterTable
WHERE NOT IsNull([__FK_outsideWorkExperience]);
[Mobility]:
LOAD [__KEY_mobility],
[__FK_mobility] AS [__KEY_results]
RESIDENT RestConnectorMasterTable
WHERE NOT IsNull([__FK_mobility]);
[Tags]:
LOAD [__KEY_tags],
[__FK_tags] AS [__KEY_results]
RESIDENT RestConnectorMasterTable
WHERE NOT IsNull([__FK_tags]);
[CustomWorkSample]:
LOAD [__KEY_customWorkSample],
[__FK_customWorkSample] AS [__KEY_results]
RESIDENT RestConnectorMasterTable
WHERE NOT IsNull([__FK_customWorkSample]);
[Community]:
LOAD [__KEY_community],
[__FK_community] AS [__KEY_results]
RESIDENT RestConnectorMasterTable
WHERE NOT IsNull([__FK_community]);
[Suffix]:
LOAD [__KEY_suffix],
[__FK_suffix] AS [__KEY_results]
RESIDENT RestConnectorMasterTable
WHERE NOT IsNull([__FK_suffix]);
[CandidateProfileConversionInfo]:
LOAD [__KEY_candidateProfileConversionInfo],
[__FK_candidateProfileConversionInfo] AS [__KEY_results]
RESIDENT RestConnectorMasterTable
WHERE NOT IsNull([__FK_candidateProfileConversionInfo]);
[Certifications]:
LOAD [__KEY_certifications],
[__FK_certifications] AS [__KEY_results]
RESIDENT RestConnectorMasterTable
WHERE NOT IsNull([__FK_certifications]);
[Comments]:
LOAD [__KEY_comments],
[__FK_comments] AS [__KEY_results]
RESIDENT RestConnectorMasterTable
WHERE NOT IsNull([__FK_comments]);
Candidates:
LOAD
[candidateId] as Can_candidateId,
[employeeJobTitle] as Can_employeeJobTitle,
[serviceDate],
Mid([serviceDate],7,13) as ServiceDate,
len(trim([cellPhone])) as [CellNo_Length],
[cellPhone],
[address2] as Can_address2,
[lastModifiedDateTime],
Mid([lastModifiedDateTime],7,13) as ModifiedDate,
[homePhone] as Can_homePhone,
[usersSysId] as Can_usersSysId,
[visibilityOption] as Can_visibilityOption,
[publicIntranet] as Can_publicIntranet,
[city] as Can_city,
[nickName] as Can_nickName,
[employeeServiceLine] as Can_employeeServiceLine,
[externalCandidate] as Can_externalCandidate,
[dateofAvailability],
Mid([dateofAvailability],7,13) as AvailabilityDate,
[middleName] as Can_middleName,
[lastName] as Can_lastName,
[dataPrivacyId] as Can_dataPrivacyId,
[primaryEmail] as Can_primaryEmail,
[country] as Can_country,
[partnerSource] as Can_partnerSource,
[candidateLocale] as Can_candidateLocale,
[employeeCostCenter] as Can_employeeCostCenter,
[anonymized] as Can_anonymized,
[partnerMemberId] as Can_partnerMemberId,
[consentToMarketing] as Can_consentToMarketing,
[privacyAcceptDateTime],
Mid([privacyAcceptDateTime],7,13) as PrivacyAcceptDate,
[lastLoginDateTime],
Mid([lastLoginDateTime],7,13) as LastLoginDate,
[firstName] as Can_firstName,
lastName & ', ' & firstName as Candidate_Name,
[candidateType] as Can_candidateType,
creationDateTime,
Mid([creationDateTime],7,13) as CreateDate,
[zip] as Can_zip,
[contactEmail] as Can_contactEmail,
[address] as Can_address,
[shareProfile] as Can_shareProfile,
[anonymizedDateTime],
Mid([anonymizedDateTime],7,13) as AnonymizedDate,
[employeeNumber] as Can_employeeNumber,
[__KEY_results],
[__FK_results] AS [__KEY_d]
RESIDENT RestConnectorMasterTable
WHERE NOT IsNull([__FK_results]);
STORE * FROM [RestConnectorMasterTable] into qvd\Candidates.QVD;
LET vCount = FieldValueCount('__next');
LET vURL_new = FieldValue('__next', vCount);
//Exit Do when vURL = vURL_new;
IF(vURL <> vURL_new) then
vURL = vURL_new;
ELSEIF(vURL = vURL_new) then
vURL = '';
end if
Trace $(vURL);
Loop
DROP TABLE RestConnectorMasterTable;
DROP TABLE Candidates;
EXIT SCRIPT;
y this :
LET vCount = FieldValueCount('__next');
LET vURL_new = FieldValue('__next', $(vCount));
IF($(vURL) <> $(vURL_new)) then
let vURL = $(vURL_new);
ELSEIF( $(vURL) = $(vURL_new)) then
let vURL = '';
end if
Because I'm reading data from SAP Successfactors and you can only retrieve 1000 rows at a time and need to get new URL each time.