options validvarname=any;
options DLDMGACTION=REPAIR;
%let yesterday=%nrbquote('%sysfunc(putn(%eval(%sysfunc(today())-7),yymmdd10))'); /*this is working, */
%put %nrbquote(&yesterday.);
%let kebabdate=%sysfunc(intnx(dtday, %sysfunc(datetime()), -7), datetime22.3); /*this is working, */
%put &kebabdate.;
proc sql;
connect to oledb as NBP_data (provider=SQLNCLI11 properties=(
"Data Source" = "xxxx"
"Initial Catalog" = "xxx"
"Integrated Security" = "SSPI"
"Persist Security" = "False") ;
Create Table NBP_data as
select * from connection to NBP_data
(
Select top 100
a.id,
a.Country,
a.state as kanal,
b.RowTimestamp as date,
b.state,
a.SeApplication_ApplicationType,
b.loanamount,
c.Email,
c.FirstName,
c.LastName,
c.Phone,
c.BenefitGroup_Code as Fackforbund,
c.Employername,
c.civilstatus,
c.discriminator,
c.employmentcontracttype,
c.SeApplicant_EmployedSince,
c.SeApplicant_GrossMonthlyIncome as StatedIncome,
d.Onboardingtype,
d.ConfidentialityLevel,
d.IsActiveCustomer,
d.Postcode,
d.InternalCustomerkey as CustNR,
d.CustomerCountry as LivesIn,
e.Assettype,
e.OwnershipType,
e.INSERTTYPE as CarInstertype,
f.Discriminator as HasChildren,
f.INSERTTYPE as childinserttype,
x.RowTimestamp as NotworkinRowTime,
x.PropertyVerificationId,
a.RowTimestamp as WorkinRowTime
FROM [DDD].[DD].[AP_APPLICATION] a
LEFT join [DDD].[DD].[AP_SCENARIO] b on b.ApplicationId = a.Id
LEFT join [DDD].[DD].[AP_APPLICANT] c on c.ScenarioId = b.id
Left join [DDD].[DD].[CU_CUSTOMERS] d on d.AnonymousCustomerKey= c.AnonymousCustomerKey
Left join [DDD].[DD].[AP_SEASSET] e on e.SeApplicantId=a.id
Left join [DDD].[DD].[ap_child] f on f.SeScenarioId=b.id
Left join [DDD].[DD].[PV_APPLICANT] x on d.AnonymousCustomerKey=x.AnonymousCustomerKey
where a.RowTimestamp = '2024-04-10' /*this is working, */
*/ where a.RowTimestamp > %nrbquote(&yesterday.) /* this is working */ */ where x.RowTimestamp > '2024-04-10' /* this is NOT working */ and where cast(x.PropertyVerificationId as varchar(38)) is not null /*(same table as x.Rowtimestamp) */
);
quit; a.Rowtimestamp is is numeric, date datetime22.3, length 8 bytes = works fine x.Rowtimestamp is numeric, date , datetime22.3, lenths 8 bytes = does not work ERROR: Open cursor error: ICommand::Execute failed. : Conversion failed when converting from a character string to uniqueidentifier. Alphabetic List of Variables and Attributes # Variable Type Len Format Informat Label 313032 CastedVarchar Char 38 $38. $38. CastedVarchar NotworkinRowTime Num 8 DATETIME22.3 DATETIME22.3 NotworkinRowTime 05APR2024:17:34:40.534 WorkingRowTime Num 8 DATETIME22.3 DATETIME22.3 WorkingRowTime 05APR2024:05:22:10.094
... View more
