CDS VIew Multiple Row for Status

TrattnigS — Wed, 25 Sep 2024 10:13:52 GMT

Hello i am developing a CDS view which should extract some information from EWM Outbound table "/scdl/db_proci_o":

There are status fields like picking, packing, good issue.
problem is if i query this i get for each outbound document multiple status values but i just want to get the current one.
Also was is strange is that the results seam not correct as i have every time the same pattern "N,N,Y" which makes no sense as the good issue cannot be completed when picking is not completed.

If i enable the last change field i clearly see that the last change has all on "Y" = 9 (completed) and i just want to fetch the latest changed data.

I know there are table functions (row number , rank, sorting etc), but i am still not sure if this is the right track.

CDS example status:

case

when ewmOutbound.status_pick = '9' then 'Y'

when ewmOutbound.status_pick != '9' then 'N'

else ewmOutbound.status_pick

end as Picking,

case

when ewmOutbound.status_pick = '9' and ewmOutbound.status_gi = '9' then 'Y'

else 'N'

end as Packing,

case

when ewmOutbound.status_gi = '9' then 'Y'

when ewmOutbound.status_gi != '9' then 'N'

else ewmOutbound.status_gi

end as Good_Issue,

In a table function i make ROW NUMBER() and assign them a row number.
ROW_NUMBER() OVER (ORDER BY TO_TIMESTAMP(ZSACCONTROLCON.latestchanged, 'YYYYMMDDHH24MISS') DESC) AS DateRank,

i though i just make DateRank = 1 in a where condition after the table function but now i saw the results and i am wondering why there is no number 1.

I highlighted the latest change as you see row 3, but i dont know how to only extract always the latestchange.

Many thanks for any support!

Re: CDS VIew Multiple Row for Status

TrattnigS — Wed, 25 Sep 2024 15:21:52 GMT

I solved , i think .
Problem was i forgot to do the Partitioning to start new count for each outbound, schedule line.

ROW_NUMBER() OVER (PARTITION BY outbound, sched_line ORDER BY TO_TIMESTAMP(ZSACCONTROLCON.latestchanged, 'YYYYMMDDHH24MISS') DESC) AS DateRank,

Question Re: CDS VIew Multiple Row for Status in Technology Q&A

CDS VIew Multiple Row for Status

Re: CDS VIew Multiple Row for Status