variable where condition

building semantic layer
1

I’m on XI R2 and I’m trying to create a where condition based on user input. DB table has two dates; I need where condition based on date they choose. I tried to create Where clause in universe:

CASE @Prompt(‘Choose a Date Type’,‘A’,{‘By Processed Date’,‘By Created Date’},mono,constrained)
WHEN ‘By Processed Date’ THEN dbo.RMA_SALES_ORDER_DETAILS.ACTUAL_RETURN_DATE
ELSE dbo.RMA_SALES_ORDER_DETAILS.RMA_DATE
END

I get parse error invalid table name. I try ignoring and use in report; get error Invalid Identifier “DBO”.“RMA_SALES_ORDER_DETAILS”.“RMA_DATE”.

SQL syntax for where cluase looks correct (to me).

WHERE
( RMA_SALES_ORDER_DETAILS.RMA_NBR=SALES_ORDER.SALES_ORDER_NBR AND RMA_SALES_ORDER_DETAILS.RMA_LINE_NBR=SALES_ORDER.SALES_ORDER_LINE_NBR )
AND
CASE @Prompt(‘Choose a Date Type’,‘A’,{‘By Processed Date’,‘By Created Date’},mono,constrained) WHEN ‘By Processed Date’ THEN dbo.RMA_SALES_ORDER_DETAILS.ACTUAL_RETURN_DATE ELSE dbo.RMA_SALES_ORDER_DETAILS.RMA_DATE END >= TRUNC(SYSDATE,‘YYYY’)

This syntax works in TOAD when I remove the “dbo.”. I’m new to BO and don’t know much about dbo. (and can’t find much information on it).

Is this possible?

jfini (BOB member since 2008-11-11)

2

Well, create the condition without the dbo. (database object) :).

If you look at your where clause the tables appear listed without the dbo. you have already found the reason for the error.

The tables must be renamed / aliased in the universe.

Mak 1 :uk: (BOB member since 2005-01-06)

3

Thanks for your suggestion. I should have mentioned, I tried that before posting. I then get error in Designer: “The expression type is not compatible with the object type”. The type is Character. Any other suggestions?

jfini (BOB member since 2008-11-11)

4

Thats a different problem…:).

For this to work you will need to convert your date fields and you sysdate reference to character, using whatever function you need to use, for your database.

Mak 1 :uk: (BOB member since 2005-01-06)

5

I want to return the colum name, i.e, “sales_order_rma_details.rma_date”, not an actual date. I’m trying to change the left side of the where clause. Am I missing something?

jfini (BOB member since 2008-11-11)

6

I can make this work by defining the Where clause in Designer as described above, then editing the SQL in WebI to remove the “dbo.”, but is this an acceptable practice?

jfini (BOB member since 2008-11-11)

7

I’m confused now, why not just omit the dbo part in Designer?

Does it work or not?

I wouldn’t advise you to change / hardcode the SQL in Webi.

Mak 1 :uk: (BOB member since 2005-01-06)

8

Thanks for your continued interest. As I mentioned above, if I omit the dbo. in Designer, I get the error: “The expression type is not compatible with the object type”.

That is when you suggested changing the format of all dates to character, but I’m not trying to return a date, I’m trying to return the column name.

jfini (BOB member since 2008-11-11)

9

In order to parse object if I remove dbo. from universe, I must enclose it in quotes, thus my condition becomes:

( RMA_SALES_ORDER_DETAILS.RMA_NBR=SALES_ORDER.SALES_ORDER_NBR AND RMA_SALES_ORDER_DETAILS.RMA_LINE_NBR=SALES_ORDER.SALES_ORDER_LINE_NBR )
AND
CASE @Prompt(‘Choose a Date Type’,‘A’,{‘By Processed Date’,‘By Created Date’},mono,constrained) WHEN ‘By Processed Date’ THEN ‘RMA_SALES_ORDER_DETAILS.ACTUAL_RETURN_DATE’ ELSE ‘RMA_SALES_ORDER_DETAILS.RMA_DATE’ END >= TRUNC(SYSDATE,‘YYYY’)

This produces error in InfoView: A non-numeric character was found where a numer was expected.

jfini (BOB member since 2008-11-11)

10

Forget trying to use CASE WHEN… for everything, a simple AND/OR logic should work, try:

(
   @Prompt('Choose Date Type', 'A', {'Processed Date','Created Date'}, mono, constrained) = 'Processed Date' 
   AND
   dbo.RMA_SALES_ORDER_DETAILS.ACTUAL_RETURN_DATE >= TRUNC (SYSDATE, 'YYYY') 
)
OR
(
   @Prompt('Choose Date Type', 'A', {'Processed Date','Created Date'}, mono, constrained) = 'Created Date' 
   AND
   dbo.RMA_SALES_ORDER_DETAILS.RMA_DATE >= TRUNC (SYSDATE, 'YYYY') 
)

Andreas :de: (BOB member since 2002-06-20)

11

How would I use that in a WHERE condition in Infoview? I created a variable to use as the left side of the where condition; this code encompasses both sides of the condition.

When I add the variable to the query filter in Infoview, it automatically creates the rest of the condition (equal to, greater than, etc.). If I used this syntax to create the query filter, what goes in the rest of it?

jfini (BOB member since 2008-11-11)

12

Create the SQL code as a predefined condition/filter in the universe.

As an alternative, create a new dimension in your universe defined as (in the SELECT box of Designer):

CASE WHEN @Prompt('Choose Date Type', 'A', {'Processed Date','Created Date'}, mono, constrained) = 'Processed Date' 
   THEN MyTable.ACTUAL_RETURN_DATE 
   ELSE  MyTable.SomeOtherDateColumn
END

You can then combine this object with other objects to create query conditions when builing a data provider.

Andreas :de: (BOB member since 2002-06-20)

13

Have you tried exporting the universe with the Designer error anyways and tried using the object? Even if Designer errors, sometimes it works ok once it’s parsed in webi.

Joel Seguin :us: (BOB member since 2004-12-08)

14

Thanks to all of you that have responded. Joel’s suggestion worked. I have saved objects in Designer that had a parse error and they worked. I just didn’t think it would work for an incompatible object error.

I guess I’m not used to working with a product that throws so many false errors.

jfini (BOB member since 2008-11-11)

15

This:-

Is not the same as this:-

The above error is returned because you are mixing character and date syntax, although you were just wanting to return a date.

Mak 1 :uk: (BOB member since 2005-01-06)