cancel
Showing results for 
Search instead for 
Did you mean: 

cds view excluding lines with a selection NOT IN another table

Guillaume_P_FR
Explorer
98

Hi experts.

I'm looking for some help / advices on CDS view SQL syntax (I'm not a developer).

I'm trying to fetch datas from PRPS and PRPHI tables (I'm building a CDS view to populate a composite dimension in SAP Analytics cloud.

Guillaume_P_FR_0-1719262734944.png

I'd like my WBS dimension to have a level property field populated from my CDS view

 

select distinct from prps

inner join prhi on prps.pspnr = prhi.posnr

inner join proj on prps.psphi = proj.pspnr

key prps.posid as ID, // prhi.posnr has internal format

prps.postu as DESCRIP,

proj.pspid as PARENT, // link to project

'lvl1' as TYPE }

where prhi.up = '00000000'

above "where" is working fine for isolating lvl1 wbs into a subset of data

How could I process to isolate lvl2 WBSs (second line of the screenshot for example) ?

(then I add it to my result set with a union)

Same for lvl3 WBSs ... the condition would be something like PRPS.PSPNR NOT IN (select distinct from prhi {prhi.up}) but it seem the "IN" keyword is not allowed in "CDS SQL"

How would you proceed ? (hope my need is clear)

Thanks in advance.

G.

View Entire Topic
Guillaume_P_FR
Explorer
0 Kudos

In fact the information is available directly in a field :

PRPS.STUFE

Answered.

G.