Select Statement in Filter

Ray Kelly · Jul 26, 2017 11:24AM

OBIEE 11.1.1.7

I am trying to create a filter as a SQL expression but my syntax appears to be incorrect.

I created a filter on a date field and converted the filter to an SQL expression.

I need the filter to look like this:

"Table"."Date Column" >= (select "Other Table"."Other Column Date" from "Other Table" where ID = {ID_variable})

This is failing.

What am I missing?

Thanks,

Raymond

Sherry George · Jul 26, 2017 11:56AM

As an alternative solution, can you create the filter using "is based on the results of another analysis"?

Ray Kelly · Jul 26, 2017 11:59AM

That option is not possible due to my circumstances.

Gianni Ceresa · Jul 26, 2017 12:47PM

select "Other Table"."Other Column Date" from "Other Table" where ID = {ID_variable}

This isn't a valid LSQL, ID isn't a reference to a column and {ID_variable} isn't a reference to a variable. Fix your syntax and you will have more chances.

Actually you better build an analysis which represent the subquery, copy the LSQL and use it in your filter.

Ray Kelly · Jul 26, 2017 1:20PM

Sorry. That was just a typo.

I cannot copy and paste. I had to type out the formula by hand.

Changing ID to "ID" produced the same results:

"General Error near <select>"

Gianni Ceresa · Jul 26, 2017 3:50PM

Still, it isn't a LSQL valid query. Did you try, as suggested, to build the query as a report and then copy the LSQL?

Do not get the filter in the analysis wrong: SQL expression doesn't mean physical SQL, it means LSQL expression.

A valid LSQL is for example something like: SELECT "PC2"."Time"."Date" FROM "PC2" WHERE "Products"."Product Item Key" IN ('@{ID_variable}')

You see that it looks a bit like yours, but it is definitely different in the way it reference columns in a subject area etc.

Oracle Analytics Cloud and Server

Categories

Select Statement in Filter

Answers