topic Re: Database view and system date in Application Development and Automation Discussions

Database view and system date

Former Member — Fri, 06 Feb 2009 14:19:08 GMT

Hi.

We have a very simple database view. We need to be able to have a condition where TO_DATE >= SY-DATUM.

Seems simple enough but when I add this selection condition I get the following error message in the activation log :

Join for field AGR_USERS-TO_DAT uses an SY field

Message no. MC 759

Diagnosis

A join condition on a field of the system table SY (or SYST) was defined. This is not permitted for database views because the system table is not known to the database.

Ok, so the database does not have visibility on the sy structure, fair enough.

So my question to you gurus is : Is there a workaround for this?

Thanks for your help.

Re: Database view and system date

ThomasZloch — Fri, 06 Feb 2009 14:34:14 GMT

Not possible for database views, maybe you can use a maintenance view for your purposes? There you can use SY-fields in the conditions.

Thomas

Re: Database view and system date

Former Member — Fri, 06 Feb 2009 14:35:00 GMT

Rather than a JOIN condition, try making it a SELECTion condition.

Rob

Re: Database view and system date

Former Member — Fri, 06 Feb 2009 14:42:09 GMT

Thanks for the quick replies guys.

Thomas, I was hoping that maybe there was a database table somewhere where we could find the current date so I could link to that table.

Rob, that is what I do but SAP takes my selection condition and moves it to the Tables/Join Conditions. Really...

Re: Database view and system date

Former Member — Fri, 06 Feb 2009 14:50:12 GMT

It allowed me to put it in the selection conditions and activate it.

What type of view is this?

Rob

Re: Database view and system date

Former Member — Fri, 06 Feb 2009 14:53:23 GMT

It's a database view.

Re: Database view and system date

Former Member — Fri, 06 Feb 2009 14:59:58 GMT

I tried it on a maintenance view.

Rob

Re: Database view and system date

Former Member — Fri, 06 Feb 2009 15:04:55 GMT

Rob, thanks for trying to help me out...

Excerpt from SAP help :

System variables, i.e. fields of structure SYST, can also be defined for maintenance and help views. You can also use the prefix SY- instead of the prefix SYST-, that is SYST-LANGU and SY-LANGU are the same.

So yes, I assume it does work for maintenance views. But I do not think using a maintenance view is an option. Although to be honest, I do not even know what this view is used for. They just asked me to add a condition to it.

It looks like we are going to have to look at the bigger picture and figure out an alternative solution. A maintenance view might be a possible solution, will have to look into it.

Thanks again Rob and you too Thomas.

Re: Database view and system date

Former Member — Fri, 06 Feb 2009 15:17:22 GMT

Well, be careful with a maintenace view. You won't be able to SELECT data from it in a program.

Rob