cancel
Showing results for 
Search instead for 
Did you mean: 

query filter database error

tim_cummings2
Member
0 Kudos

I have a report with 2+ queries.

The first query is against a date table that has 1 row per day and contains other columns like Calendar Date, Calendar Date minus One, Fiscal Month Start Date-Time, Fiscal Month End Date-Time... The query is filtered on Calendar Date by a prompt. The prompt allows the user to select "Current Day", "Previous Day" or a date of their choice

I have a second query that is based on the first query. No matter what date they choose using the prompt in the first query, I want this second query to return the date table row for the day prior to the one selected in the first query.

The rest of the queries in this report use the Fiscal Month Start Date-Time and Fiscal Month End Date-Time to filter their results. Some of the queries use the Query 1 start/end dates and some use the start/end date from the second query.

The problem is I get an error when I try to use a date-time value from the first or second query in order to filter the rest of the queries.

The error I get is:

"The following database error has occurred. For more information about this error, please refer to SAP Knowledge base article 2054721 on the SAP Support Portal.

When I generate the SQL in Webi and try to run it against the Oracle DB directly, The error points to the format of the constant that BO generates from the column from the 1st or 2nd query.

Example:

WHERE FISCAL_MONTH_START_DATE_TIME <= '29-10-2016 23:59:59'

The problem is in the format of the string Webi generated. Oracle by default requires a Date-Time string to be formatted like '29-Oct-2016 11:59:59 PM'. If it is not in that format, then it needs to be converted with a function. Since Webi is generating the string, I don't have control over it using a function on the string that Webi generates.

How can I get Webi to generate the proper formatted string for use in the filter?

I have tried using the formatting options on the columns involved. When I output the columns on a list, they are formatted as I expected, but it does not change the format of the string Webi generates for the filter.

Is there any way to make this work in Webi?

Accepted Solutions (0)

Answers (0)