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;
