@KRHE wrote: Yes , you are correct, it mssql server and your code is actually working - this is sorcery :P. I will have to deep dive into this a bit later , have a doctors appointment :P. amazing work
That's what I thought. It's with all error messages always a possibility that the message you're getting is caused by something else that happens earlier and though the error message as such is misleading. In such cases - if you can't fix it "quickly" - you need always to simplify your syntax and debug things step-by-step.
Just as a comment to the code you shared:
The way you coded the joins creates too many rows that you then filter via a where clause. I can't test it but believe that with a few tweaks to your code you could get the desired result set without the need to "post filter" the data via where clause.
Below the code version that I believe implements your selection logic but should be a bit more efficient because it reduces data volumes earlier.
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
(
select
id,
Country,
state,
SeApplication_ApplicationType,
RowTimestamp
from [DDD].[DD].[AP_APPLICATION]
where DATEADD(d,0,DATEDIFF(d,0,RowTimestamp)) = '2024-04-10'
) 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
INNER join [DDD].[DD].[PV_APPLICANT] x
on d.AnonymousCustomerKey=x.AnonymousCustomerKey
and x.PropertyVerificationId is not null /* only required if PropertyVerificationId not already defined with NOT NULL constraint */
left join [DDD].[DD].[AP_SEASSET] e
on e.SeApplicantId=a.id
left join [DDD].[DD].[ap_child] f
on f.SeScenarioId=b.id
);
disconnect from NBP_data;
quit;
In above code syntax DATEADD(d,0,DATEDIFF(d,0,RowTimestamp)) is used to shift RowTimestamp to the beginning of the day so all rows with date 2024-04-10 will get selected.
Alternative syntax would be: where RowTimestamp>='2024-04-10' and RowTimestamp<'2024-04-11'
... View more
