Select Statement in Filter

Question

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

Gianni Ceresa · Answer

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.

Ray Kelly · Answer

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 · Answer

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.