Select Statement in Filter

Ray Kelly

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

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

Ray Kelly

That option is not possible due to my circumstances.

Gianni Ceresa

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

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

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.