cancel
Showing results for 
Search instead for 
Did you mean: 

SELECT-statement with Where function in CUSTOM CDS-VIEW APP?

lukasgw7
Explorer
0 Kudos

Hi,

I want to see with the Custom CDS-View Fiori App which production order was used when another production order is moved. This is supposed to be part of my analysis for an embedded story dashboard.

Is it possible in the Custom CDS-View App to use WHERE functions for a custom column?

My intention is that I get back a production order ID, in which I search by the WHERE function which production order ID was used at this timestamp by the column "starttimestamp" and that the work center is the same.

In the Custom CDS-View column I created, the production order ID used should be written.

Is something like this possible in the Fiori app? perhaps in the later processing in the embedded analytic story?

I think that's about how I would write it in ABAP directly:

SELECT i~ProductionOrderID
FROM YY1_CustomTable AS c
INNER JOIN I_MFGORDERWITHSTATUS AS i
ON c~ProductionOrderID = i~ProductionOrderID
WHERE i~StartTimeStamp = c~MovedTimeStamp 
AND i~WorkcenterID = c~WorkcenterID.

In the Fiori app I only need to add the WHERE function, because I have already created the join function.

Thank you for the help!

Lukas

Accepted Solutions (0)

Answers (2)

Answers (2)

0 Kudos

Yes, it is possible to use WHERE functions for a custom column in a Custom CDS-View Fiori App. You can create a custom column in the CDS view and define its value using expressions that include WHERE functions to filter the data.

To achieve your desired outcome, you can create a custom column in your CDS view and define its value using the expression similar to the ABAP code you provided. Here's an example of how you can create the custom column:


@AbapCatalog.sqlViewName: 'YY1_CDS_VIEW'

@AbapCatalog.compiler.compareFilter: true

@AccessControl.authorizationCheck: #NOT_REQUIRED

@EndUserText.label: 'Production Order ID Used'

define view YY1_CustomCDSView

as select from YY1_CustomTable as c

inner join I_MFGORDERWITHSTATUS as i

on c.ProductionOrderID = i.ProductionOrderID

{

c.MovedTimeStamp,

c.WorkcenterID,

i.ProductionOrderID as ProductionOrderIDUsed

}

where i.StartTimeStamp = c.MovedTimeStamp

and i.WorkcenterID = c.WorkcenterID;


In this example, we create a custom column named ProductionOrderIDUsed which is derived from the i.ProductionOrderID column of the I_MFGORDERWITHSTATUS table using the WHERE functions in the WHERE clause to filter the data based on the condition you mentioned.Once you have defined the custom column in your CDS view, you can use it as a data source in your embedded analytic story dashboard and visualize the data as required.

lukasgw7
Explorer
0 Kudos

Thanks for the answer!

I think your answer is intended to the use of ADT for the creation of the column?

Because I only use the Custom CDS-View app to create Analytic Cubes from standard CDS-Views with calculated columns. I don't think such a code is possible in the Calculations function? I would use ADT in case there is really no other possibility.


many greetings

Lukas

JoeyLi
Product and Topic Expert
Product and Topic Expert
0 Kudos

Try it in ADT (embedded ABAP) then use the CDS you edited in ADT in frontend, instead of doing it in custom CDS APP. Or you can do some condition settings in Custom Analytical queries if you plan to use the data model in a query.