Problem to make a CDS view. ( Max keyword is not working )

[former_member258693]

Hello Experts.

I want to make a CDS VIEW about system status ( From  TJ02T, JEST )

But. CDS view doesn't work Max aggregation comparing STAT field.  I want to get only one  max line but the result It has two line.

STAT field is CHAR5 . But MAX aggregation it works characteristc domain. isn't it?

Here is my source.

CDS Define Source

@AbapCatalog.sqlViewName: 'ZVP3PSB90041CDS' @EndUserText.label:       'Project Status (System)' define view ZVP3PSB90041CDSI as select from proj as proj                     inner join tcj41 as tcj41                             on proj.stspd = tcj41.stspd                     inner join  jsto as jsto                        on proj.objnr = jsto.objnr                        and proj.stspd = jsto.stsma                     inner join jest as jest                        on proj.objnr = jest.objnr                     inner join tj02 as tj02                        on jest.stat = tj02.istat //                    inner join tj02t as tj02t                        //on tj02.istat = tj02t.istat { key  proj.pspid as pspid,        jsto.objnr,        max ( jest.stat )  as istat } where ( tcj41.stspd <> '' or tcj41.stspr <> '' ) and tcj41.grpgs = 'PS' and jest.inact = ' ' group by proj.pspid, jsto.objnr, jest.stat

But the result has two line like this

[former_member258693 · Accepted solution]

Sorrry. I was idiot.

I have to use only key fields on GROUP BY statement,   Like this

GROUP BY proj.pspid, jsto.objnr

[sreehari_vpillai · Accepted solution]

Small correction - You need to specify all the columns(not just keys) which you are selecting in the statement avoiding all the aggregation columns.

Sree