nico1964
01-04-21, 11:52
Hallo,
ich bräuchte wieder einmal jemanden, der mir vielleicht auf die Sprünge hilft.
Ich schlage mich seit 2 Wochen mit einem Problem herum, welches auf mieser Datenqualität entsteht. Die Daten muss ich nehmen wie sie sind.
Daher habe ich mit einem Kollegen der SQL ziemlich gut beherrscht, dem aber AS400 Kenntnisse fehlen eine CTE verbrochen.:rolleyes:
<f.defclassfromdate
<f.entrynofrom
[PHP]
with
contacts
as
(
select distinct kuns.gesid CD_CL_NR
from kun3sel kuns
)
,
kundedefid
as
(select distinct
'kuns.cmandnr || kuns.kmandnr || kuns.kdnr' as KUNDE_DEF_ID_Orig,
kuns.gesidc as CD_CL_NR
from kun3sel kuns
)
,
DODStartClassBase
as
(
SELECT * from
(SELECT
cs.gesid, KR1_STICHTAG as DefClassStartDate ,
KR1_STICHTAG as DefClassStartSince,
(case when dh.KR1_DEF_KLASSE='BO'
then 0 else 1 end) as DefClassStart,
(KR1_ANL_DATUM*100000+KR1_ANL_ZEIT) as EntryNoStart,
ROW_NUMBER() OVER(PARTITION BY gesid ORDER BY dh.KR1_DEF_KLASSE,
(KR1_ANL_DATUM*100000+KR1_ANL_ZEIT)) Ranking
FROM kun3sel cs , leakr1 dh
where cs.gesid = dh.partnernr
and dh.KR1_STICHTAG <= 20210123
) y
)
,
DODStartClass as
(
select * from
(Select CD_CL_NR, DefClassStartDate, DefClassStartSince,
DefClassStart, EntryNoStart, b.DefClassStart, b.DefClassStart2,
ROW_NUMBER() OVER(PARTITION BY CD_CL_NR ORDER BY Ranking) Rang,
ROW_NUMBER() OVER(PARTITION BY CD_CL_NR ORDER BY Ranking) Rang
from DODStartClassBase a
left outer join DODStartClassBase b
on a.CD_CL_NR=b.CD_CL_NR
and a.Ranking+1=b.Ranking
and DefClassStart<>DefClassStart
) a where a.Rang=1
)
,
toDef as
(
select * from
(
SELECT cs.CD_CL_NR, KR1_STICHTAG DefClassToDate,
KR1_STICHTAG DefClassToSince,
(case when dh.KR1_DEF_KLASSE='BO' then 0 else 1 end) DefClass,
(KR1_ANL_DATUM*100000+KR1_ANL_ZEIT) EntryNoTo,
ROW_NUMBER() OVER(PARTITION BY cs.CD_CL_NR ORDER BY KR1_STICHTAG,
(KR1_ANL_DATUM*100000+KR1_ANL_ZEIT)) Rang
FROM contacts cs
inner join LEAKR1.dh
on cs.CD_CL_NR = dh.partnern1r
where dh.KR1_STICHTAG<=refDate and
(case when dh.KR1_DEF_KLASSE='BO' then 0 else 1 end) = 1
) a
),
FromDef
as
(
select * from
(
SELECT cs.CD_CL_NR, KR1_STICHTAG DefClassFromDate,
KR1_STICHTAG DefClassFromSince,
(case when dh.KR1_DEF_KLASSE='BO' then 0 else 1 end) DefClass,
(KR1_ANL_DATUM*100000+KR1_ANL_ZEIT) EntryNoFrom,
ROW_NUMBER() OVER(PARTITION BY cs.CD_CL_NR ORDER BY KR1_STICHTAG,
(KR1_ANL_DATUM*100000+KR1_ANL_ZEIT)) Rang
FROM contacts cs
inner join LEAKR1 dh
on cs.CD_CL_NR =dh.partnernr
where dh.STICHTAG <= refDate and
(case when dh.DEFKLASSE ='BO' then 0 else 1 end) = 0
) a
)
select distinct
k.KUNDE_DEF_ID_Orig,
(case when z.DefClassToDate is not null
then k.KUNDE_DEF_ID_Orig || convert(varchar(8), z.DefClassToDate)
else '' end) KUNDE_DEF_ID, z.DefClassToDate BeginDate,
z.DefClassFromDate EndDate,
(case when z.DefClassToDate is null
then null else OverAllEvent end) OverAllEvent
from(
select CD_CL_NR, DefClassToDate, DefClassFromDate,
ROW_NUMBER() OVER(PARTITION BY CD_CL_NR
ORDER BY DefClassToDate, EntryNoTo) OverAllEvent,
ROW_NUMBER() OVER(PARTITION BY CD_CL_NR
ORDER BY DefClassToDate desc, EntryNoTo desc) RangActual
from
(
select CD_CL_NR, DefClassToDate, DefClassFromDate, EntryNoTo, EntryNoFrom,
ROW_NUMBER() OVER(PARTITIon BY CD_CL_NR, DefClassFromDate,
EntryNoFrom ORDER BY DefClassToDate, EntryNoTo) RANG_FROM
from
(
select c.CD_CL_NR, dod.DefClassStart, dod.DefClassStartSince,
t.DefClassToDate,
t.EntryNoTo,
(case when
f.DefClassFromDate is null then 99991231 else DefClassFromDate end),
(case when
f.EntryNoFrom is null then 99999999999999 else EntryNoFrom end),
ROW_NUMBER() OVER(PARTITIon BY c.CD_CL_NR, t.DefClassToDate, EntryNoFrom
ORDER BY
(case when
f.DefClassFromDate is null then 99991231 end),
(case when
f.EntryNoFrom is null then 99999999999999 end)) RANG_TO
from contacts c
left join DODStartClass dod on c.CD_CL_NR=dod.CD_CL_NR
left join toDEF t on c.CD_CL_NR=t.CD_CL_NR
and t.DefClassToDate>=dod.DefClassStartDate
and t.EntryNoTo>=dod.EntryNoStart
left join fromDef f on t.CD_CL_NR=f.CD_CL_NR
and t.DefClassToDate
select distinct
k.KUNDE_DEF_ID_Orig,
(case when z.DefClassToDate is not null
then k.KUNDE_DEF_ID_Orig || convert(varchar(8), z.DefClassToDate)
else '' end) KUNDE_DEF_ID, z.DefClassToDate BeginDate,
z.DefClassFromDate EndDate,
(case when z.DefClassToDate is null
then null else OverAllEvent end) OverAllEvent
from(
select CD_CL_NR, DefClassToDate, DefClassFromDate,
ROW_NUMBER() OVER(PARTITION BY CD_CL_NR
ORDER BY DefClassToDate, EntryNoTo) OverAllEvent,
ROW_NUMBER() OVER(PARTITION BY CD_CL_NR
ORDER BY DefClassToDate desc, EntryNoTo desc) RangActual
from
(
select CD_CL_NR, DefClassToDate, DefClassFromDate, EntryNoTo, EntryNoFrom,
ROW_NUMBER() OVER(PARTITIon BY CD_CL_NR, DefClassFromDate,
EntryNoFrom ORDER BY DefClassToDate, EntryNoTo) RANG_FROM
from
(
select c.CD_CL_NR, dod.DefClassStart, dod.DefClassStartSince,
t.DefClassToDate,
t.EntryNoTo,
(case when
f.DefClassFromDate is null then 99991231 else DefClassFromDate end),
(case when
f.EntryNoFrom is null then 99999999999999 else EntryNoFrom end),
ROW_NUMBER() OVER(PARTITIon BY c.CD_CL_NR, t.DefClassToDate, EntryNoFrom
ORDER BY
(case when
f.DefClassFromDate is null then 99991231 end),
(case when
f.EntryNoFrom is null then 99999999999999 end)) RANG_TO
from contacts c
left join DODStartClass dod on c.CD_CL_NR=dod.CD_CL_NR
left join toDEF t on c.CD_CL_NR=t.CD_CL_NR
and t.DefClassToDate>=dod.DefClassStartDate
and t.EntryNoTo>=dod.EntryNoStart
left join fromDef f on t.CD_CL_NR=f.CD_CL_NR
and t.DefClassToDate<f.defclassfromdate
and t.EntryNoTo<f.entrynofrom
) x where x.RANG_TO=1
) y where y.RANG_FROM=1
) z inner join kundedefid k on z.CD_CL_NR=k.CD_CL_NR
where
z.RangActual=1
and
(
z.DefClassFromDate>=refDate
or
((DefClassFromDate/10000)=(refDate/10000) and
Mode(DefClassFromDate/100 , 100)=Mode(refDate/100 , 100))
);
Wenn ich diese dann auf der AS400 mit RUNSQLSTM aufrufe, bekomme ich folgende Fehlermeldung
MSG ID WTK SATZ TEXT
SQL0084 30 6 Position 4 SQL-Anweisung nicht zulässig.
Satz 6 ist das with
Sind auf 7.3 mit akutellem PTF Stand auf einer Power 7
Leider finde ich nicht warum, aber da mangelt es bei mir 100% an den SQL Kenntnissen (was daraus resultiert, das meine Chefs eine gescheite Schulung ablehnen, den da müssten sie Geld in die Hand nehmen, mich würde es ja interessieren).
Mein Kollege hat das am SQL Server durchgeführt und dort funktioniert die Version tadellos.
Ich bin echt schon am verzweifeln</f.entrynofrom
</f.defclassfromdate
</f.entrynofrom
</f.defclassfromdate
ich bräuchte wieder einmal jemanden, der mir vielleicht auf die Sprünge hilft.
Ich schlage mich seit 2 Wochen mit einem Problem herum, welches auf mieser Datenqualität entsteht. Die Daten muss ich nehmen wie sie sind.
Daher habe ich mit einem Kollegen der SQL ziemlich gut beherrscht, dem aber AS400 Kenntnisse fehlen eine CTE verbrochen.:rolleyes:
<f.defclassfromdate
<f.entrynofrom
[PHP]
with
contacts
as
(
select distinct kuns.gesid CD_CL_NR
from kun3sel kuns
)
,
kundedefid
as
(select distinct
'kuns.cmandnr || kuns.kmandnr || kuns.kdnr' as KUNDE_DEF_ID_Orig,
kuns.gesidc as CD_CL_NR
from kun3sel kuns
)
,
DODStartClassBase
as
(
SELECT * from
(SELECT
cs.gesid, KR1_STICHTAG as DefClassStartDate ,
KR1_STICHTAG as DefClassStartSince,
(case when dh.KR1_DEF_KLASSE='BO'
then 0 else 1 end) as DefClassStart,
(KR1_ANL_DATUM*100000+KR1_ANL_ZEIT) as EntryNoStart,
ROW_NUMBER() OVER(PARTITION BY gesid ORDER BY dh.KR1_DEF_KLASSE,
(KR1_ANL_DATUM*100000+KR1_ANL_ZEIT)) Ranking
FROM kun3sel cs , leakr1 dh
where cs.gesid = dh.partnernr
and dh.KR1_STICHTAG <= 20210123
) y
)
,
DODStartClass as
(
select * from
(Select CD_CL_NR, DefClassStartDate, DefClassStartSince,
DefClassStart, EntryNoStart, b.DefClassStart, b.DefClassStart2,
ROW_NUMBER() OVER(PARTITION BY CD_CL_NR ORDER BY Ranking) Rang,
ROW_NUMBER() OVER(PARTITION BY CD_CL_NR ORDER BY Ranking) Rang
from DODStartClassBase a
left outer join DODStartClassBase b
on a.CD_CL_NR=b.CD_CL_NR
and a.Ranking+1=b.Ranking
and DefClassStart<>DefClassStart
) a where a.Rang=1
)
,
toDef as
(
select * from
(
SELECT cs.CD_CL_NR, KR1_STICHTAG DefClassToDate,
KR1_STICHTAG DefClassToSince,
(case when dh.KR1_DEF_KLASSE='BO' then 0 else 1 end) DefClass,
(KR1_ANL_DATUM*100000+KR1_ANL_ZEIT) EntryNoTo,
ROW_NUMBER() OVER(PARTITION BY cs.CD_CL_NR ORDER BY KR1_STICHTAG,
(KR1_ANL_DATUM*100000+KR1_ANL_ZEIT)) Rang
FROM contacts cs
inner join LEAKR1.dh
on cs.CD_CL_NR = dh.partnern1r
where dh.KR1_STICHTAG<=refDate and
(case when dh.KR1_DEF_KLASSE='BO' then 0 else 1 end) = 1
) a
),
FromDef
as
(
select * from
(
SELECT cs.CD_CL_NR, KR1_STICHTAG DefClassFromDate,
KR1_STICHTAG DefClassFromSince,
(case when dh.KR1_DEF_KLASSE='BO' then 0 else 1 end) DefClass,
(KR1_ANL_DATUM*100000+KR1_ANL_ZEIT) EntryNoFrom,
ROW_NUMBER() OVER(PARTITION BY cs.CD_CL_NR ORDER BY KR1_STICHTAG,
(KR1_ANL_DATUM*100000+KR1_ANL_ZEIT)) Rang
FROM contacts cs
inner join LEAKR1 dh
on cs.CD_CL_NR =dh.partnernr
where dh.STICHTAG <= refDate and
(case when dh.DEFKLASSE ='BO' then 0 else 1 end) = 0
) a
)
select distinct
k.KUNDE_DEF_ID_Orig,
(case when z.DefClassToDate is not null
then k.KUNDE_DEF_ID_Orig || convert(varchar(8), z.DefClassToDate)
else '' end) KUNDE_DEF_ID, z.DefClassToDate BeginDate,
z.DefClassFromDate EndDate,
(case when z.DefClassToDate is null
then null else OverAllEvent end) OverAllEvent
from(
select CD_CL_NR, DefClassToDate, DefClassFromDate,
ROW_NUMBER() OVER(PARTITION BY CD_CL_NR
ORDER BY DefClassToDate, EntryNoTo) OverAllEvent,
ROW_NUMBER() OVER(PARTITION BY CD_CL_NR
ORDER BY DefClassToDate desc, EntryNoTo desc) RangActual
from
(
select CD_CL_NR, DefClassToDate, DefClassFromDate, EntryNoTo, EntryNoFrom,
ROW_NUMBER() OVER(PARTITIon BY CD_CL_NR, DefClassFromDate,
EntryNoFrom ORDER BY DefClassToDate, EntryNoTo) RANG_FROM
from
(
select c.CD_CL_NR, dod.DefClassStart, dod.DefClassStartSince,
t.DefClassToDate,
t.EntryNoTo,
(case when
f.DefClassFromDate is null then 99991231 else DefClassFromDate end),
(case when
f.EntryNoFrom is null then 99999999999999 else EntryNoFrom end),
ROW_NUMBER() OVER(PARTITIon BY c.CD_CL_NR, t.DefClassToDate, EntryNoFrom
ORDER BY
(case when
f.DefClassFromDate is null then 99991231 end),
(case when
f.EntryNoFrom is null then 99999999999999 end)) RANG_TO
from contacts c
left join DODStartClass dod on c.CD_CL_NR=dod.CD_CL_NR
left join toDEF t on c.CD_CL_NR=t.CD_CL_NR
and t.DefClassToDate>=dod.DefClassStartDate
and t.EntryNoTo>=dod.EntryNoStart
left join fromDef f on t.CD_CL_NR=f.CD_CL_NR
and t.DefClassToDate
select distinct
k.KUNDE_DEF_ID_Orig,
(case when z.DefClassToDate is not null
then k.KUNDE_DEF_ID_Orig || convert(varchar(8), z.DefClassToDate)
else '' end) KUNDE_DEF_ID, z.DefClassToDate BeginDate,
z.DefClassFromDate EndDate,
(case when z.DefClassToDate is null
then null else OverAllEvent end) OverAllEvent
from(
select CD_CL_NR, DefClassToDate, DefClassFromDate,
ROW_NUMBER() OVER(PARTITION BY CD_CL_NR
ORDER BY DefClassToDate, EntryNoTo) OverAllEvent,
ROW_NUMBER() OVER(PARTITION BY CD_CL_NR
ORDER BY DefClassToDate desc, EntryNoTo desc) RangActual
from
(
select CD_CL_NR, DefClassToDate, DefClassFromDate, EntryNoTo, EntryNoFrom,
ROW_NUMBER() OVER(PARTITIon BY CD_CL_NR, DefClassFromDate,
EntryNoFrom ORDER BY DefClassToDate, EntryNoTo) RANG_FROM
from
(
select c.CD_CL_NR, dod.DefClassStart, dod.DefClassStartSince,
t.DefClassToDate,
t.EntryNoTo,
(case when
f.DefClassFromDate is null then 99991231 else DefClassFromDate end),
(case when
f.EntryNoFrom is null then 99999999999999 else EntryNoFrom end),
ROW_NUMBER() OVER(PARTITIon BY c.CD_CL_NR, t.DefClassToDate, EntryNoFrom
ORDER BY
(case when
f.DefClassFromDate is null then 99991231 end),
(case when
f.EntryNoFrom is null then 99999999999999 end)) RANG_TO
from contacts c
left join DODStartClass dod on c.CD_CL_NR=dod.CD_CL_NR
left join toDEF t on c.CD_CL_NR=t.CD_CL_NR
and t.DefClassToDate>=dod.DefClassStartDate
and t.EntryNoTo>=dod.EntryNoStart
left join fromDef f on t.CD_CL_NR=f.CD_CL_NR
and t.DefClassToDate<f.defclassfromdate
and t.EntryNoTo<f.entrynofrom
) x where x.RANG_TO=1
) y where y.RANG_FROM=1
) z inner join kundedefid k on z.CD_CL_NR=k.CD_CL_NR
where
z.RangActual=1
and
(
z.DefClassFromDate>=refDate
or
((DefClassFromDate/10000)=(refDate/10000) and
Mode(DefClassFromDate/100 , 100)=Mode(refDate/100 , 100))
);
Wenn ich diese dann auf der AS400 mit RUNSQLSTM aufrufe, bekomme ich folgende Fehlermeldung
MSG ID WTK SATZ TEXT
SQL0084 30 6 Position 4 SQL-Anweisung nicht zulässig.
Satz 6 ist das with
Sind auf 7.3 mit akutellem PTF Stand auf einer Power 7
Leider finde ich nicht warum, aber da mangelt es bei mir 100% an den SQL Kenntnissen (was daraus resultiert, das meine Chefs eine gescheite Schulung ablehnen, den da müssten sie Geld in die Hand nehmen, mich würde es ja interessieren).
Mein Kollege hat das am SQL Server durchgeführt und dort funktioniert die Version tadellos.
Ich bin echt schon am verzweifeln</f.entrynofrom
</f.defclassfromdate
</f.entrynofrom
</f.defclassfromdate