SQL SCRIPT analytic privilege OR command not working?

[Loed]

Hi guys,

I have this authorization table which the sql script analytic privilege (AP) is accessing.

This is my SQL SCRIPT in AP:

"COUNTRY" IN (SELECT "ZCOUNTRY" FROM "_SYS_BIC"."AUTH_TABLE" WHERE BNAME = SESSION_USER and ZPROJECT = 'BIG')

AND

"FISCYEAR" IN (SELECT "ZYEAR" FROM "_SYS_BIC"."AUTH_TABLE" WHERE BNAME = SESSION_USER and ZPROJECT = 'BIG')

So if I will use BWTESTUSR2, it is working as expected. Report will only show COUNTRY ES and IN for the YEAR 2017.

However, if I will use the QLIKVIEW user, it will NOT SHOW any data. NO RECORDS upon generation of report.

But if I change the code from AND command to OR, ALL DATA will be shown for both users: BWTESTUSR2 and QLIKVIEW.

"COUNTRY" IN (SELECT "ZCOUNTRY" FROM "_SYS_BIC"."AUTH_TABLE" WHERE BNAME = SESSION_USER and ZPROJECT = 'BIG')

OR

"FISCYEAR" IN (SELECT "ZYEAR" FROM "_SYS_BIC"."AUTH_TABLE" WHERE BNAME = SESSION_USER and ZPROJECT = 'BIG')

What do you think is the reason?

Or how to fix this?

I was expecting to see YEAR 2018 and 2019 for ALL COUNTRIES for QLIKVIEW user.

Any help?

Or how can I do this using a procedure?

Thank you.

Loed

[jagadeesh247]

Hi Leod,

Follow this thread, this might be helpul and similar scenario you are looking for.

https://answers.sap.com/questions/12164669/hana-analytic-privileges-and-procedures-in-logical.html

Thanks.

Regards,

jagadeesh.

[pfefferf]

With your query on the ZCOUNTRY field for user QLIKVIEW you get back only null or empty string values (depening on the default value settings of that column).

The IN predicate uses the Null or empty string values to find matching entries in the COUNTRY column. Because you have no entries with such values I guess, you will not get anything back.

But as you want to reach a kind of "if nothing is specified take everything" to have to adjust your logic that the restriction on COUNTRY is not done in case you have no comparable value.

[Loed]

Thanks Florian. But how to do that in sql script analytic privilege?

I think I cannot do an IF nor CASE statement on it right? So should this be done via stored procedure?

Do you have a sample how to do this in stored procedure?

I already did the basic sample in sap site but not sure how to apply this logic on the stored procedure. 😅

Can you give me a sample regarding this scenario?

Thanks. 🙂

Loed