Use system fields in CDS view?

[mh97]

Hi,

I am trying to create a CDS view (or several) for an ABAP program on NW 7.4 SP05. Unfortunately, on SP05 we cannot use parameters. I really need to use the system date, but of course "sy-datum" doesn't work.

Can anyone tell me if it is at all possible to reference a system field in CDS views in NW 7.4 SP05?  If so, how, for example to reference current system date.

For example, is it possible and if so how would I write this snippet in a CDS view:

....   FROM ztab AS z
            INNER JOIN pa0001 AS p1
                ON p1~pernr = z~pernr
            INNER JOIN pa0000 AS p0
                ON p0~pernr  =   z~pernr

.....

         WHERE p0~stat2  =  '3'
             AND p1~begda <=  sy-datum
             AND p1~endda >= sy-datum
             AND p0~begda <=  sy-datum
             AND p0~endda >=  sy-datum

...

Thanks,

Margaret

[mh97]

I guess that's a no?

[former_member186444]

Hi Margaret,

did you find an answer to this? I suppose I could pass a parameter in but I'd like to know if it's possible to use systems fields in CDS.

thanks,

Malcolm.

[mh97]

Hi Malcolm,

No, I did not find an answer. If you have the ability to pass parameters you can do that. Unfortunately our support pack level did not support that so that limited the usefulness of CDS views for this application.

Good luck!

Margaret

[kilian_kilger]

Hi,

there will probably (*) be the possibility to access the system fields in the next release of Netweaver.

But you really have to think about if you really need it. If you only want to use the system fields in the WHERE clause, there is no need to do that in the CDS view. You can do this as well in the last step in the ABAP. The speed on the database will be same.

Many people using SQL think that they have to "restrict" the values they select beforehand, like you do in an imperative programming language like ABAP. But SQL views are a lazy programming language (like Haskell for example) and are behaving very differently! So having 5000 views on top of each other and only restricting the dates in the final SELECT Statement in ABAP will probably have the same speed.

The same misconception applies to to views with parameters. Many programmers think they would spare some time by using them and giving some restrictions into the view. The exact contrary happens. On many databases, you loose speed by thinking that way (views with parameters are translated into table functions and many database optimizers have problems with them).

But of course, CDS is all about data modelling and if your data model really requires a restriction on the system date, there is no way around that. But technically, there is.

But another thing, which have to be taking into account, is unit testing. If you use a system date in a view, I can say that it is very probably not unit tested. So at least I would always prefer a parameter to the system date, which can be far better tested.

Best regards,

Kilian.

(*) the usual legal disclaimer applies

[mh97]

Kilian,

Thank you for the great explanation. I agree with all your points. Unfortunately the system is not up to the support stack where parameters are possible.  Yes it is not necessary and probably doesn't give a speed improvement, since a "view" is really just a predefined join. However it would simplify the ABAP code since that clause is used repeatedly in selecting from HR tables (to select the currently valid record). I've since moved on from this application, but I'm pretty sure I was just hoping to streamline the ABAP code.

Regards,

Margaret