Using the 'Not In' operator in the SQL expression editor

[former_member270991]

How do you use the Not In operator, in the where clause for a dimension, using a Select statement?

For example:

"Consent_DE_E_28002"."IDENTIFIER" NOT IN (Select("DE_E_T_28056")from "DE_E_28025")

Even when I use the fully qualified table name (@catalog('CATISSUEPROD')."CATISSUECORE"."DE_E_28025") in the from statement I receive the following error:

Cause of Error

Invalid table or view identifier 'DE_E_28025'

[Former Member · Accepted solution]

You can create the logic using @select expression ...

For this create a object using "DE_E_28025"."DE_E_T_28056" and use that in the expression  using @select expression with that object .... Please make sure to use this only in where clause statement and not select statement.. As this is a restriction expression...

[former_member270991 · Accepted solution]

Thank you!

This worked so the final where statement looked like this:

"Consent_DE_E_28002"."IDENTIFIER" NOT IN (select(@Select(Consent Documentation\testid))from @Select(Consent Documentation))

[Former Member · Accepted solution]

Perfect.. glad to be able to help..