CODEWITHSUNDEEP
sqloracle-databaseparameterssql-view
Eric31
Eric31

Reputation: 139

Use of USER in WHERE clause of a view in Oracle Sql Developer

I have been unable to find any information relevant to this question as it is rather specific. The situation is that I have created a view that is supposed to return results based on the user that calls it. The view is the following:

    CREATE VIEW V_ClientReward AS
    SELECT cl.id, EXTRACT(YEAR FROM r1.recommendationDate) AS 
    yearRecommended,

    COALESCE((SELECT COUNT(*) 
    FROM Recommendation r2 
    WHERE r2.client1 = cl.id AND 
    EXTRACT(YEAR FROM r2.recommendationDate)= EXTRACT(YEAR FROM 
    r1.recommendationDate)
    AND r2.client2 IN(SELECT id FROM Client WHERE registrationDate IS NOT 
    NULL) GROUP BY r2.client1), 0) AS numberRecommendedContracted,

    COALESCE((SELECT COUNT(*) 
    FROM Recommendation r2 
    WHERE r2.client1 = cl.id AND 
    EXTRACT(YEAR FROM r2.recommendationDate)= EXTRACT(YEAR FROM 
    r1.recommendationDate)
    AND r2.client2 NOT IN(SELECT id FROM Client WHERE registrationDate IS 
    NOT NULL) GROUP BY r2.client1), 0) AS numberRecommendedNotContracted,

    ((SELECT rewardImport FROM ClientReward WHERE rewardYear = EXTRACT(YEAR 
    FROM r1.recommendationDate))*COALESCE((SELECT COUNT(*) 
    FROM Recommendation r2 
    WHERE r2.client1 = cl.id AND 
    EXTRACT(YEAR FROM r2.recommendationDate)= EXTRACT(YEAR FROM 
    r1.recommendationDate)
    AND r2.client2 IN(SELECT id FROM Client WHERE registrationDate IS NOT 
    NULL)
    GROUP BY r2.client1), 0)) AS totalEarned 

    FROM Client cl INNER JOIN Recommendation r1
    ON cl.id=r1.client1
    INNER JOIN WebClient
    ON idUser = cl.id
    WHERE nick = USER
    GROUP BY cl.id, EXTRACT(YEAR FROM r1.recommendationDate);

The idea being that after I have granted a user SELECT on this view, the simple act of calling it via SELECT * FROM P18.V_ClientReward should get the results based on that user's name. Unfortunately when I execute it I get no results at all, just empty fields. I have already attempted to execute the query specifying the user in question directly as WHERE nick = 'user_name' and it works perfectly.

Any help at all would be greatly appreciated.

Thanks.

Upvotes: 2

Views: 899

Answers (1)

Littlefoot
Littlefoot

Reputation: 142705

I don't have your tables so I'm using one of my own.

First, the table contents: column NICK is used to distinguish database users (scott & hr). The table belongs to user SCOTT.

SQL> connect scott/tiger@xe
Connected.

SQL> select id_emp, fname, nick
  2  from person;

    ID_EMP FNAME                NICK
---------- -------------------- --------------------
         1 Little               HR
         2 Charles              HR
         3 Rio                  HR
         4 Seb                  SCOTT
         5 Romain               SCOTT

Creating a view, using where clause you used:

SQL> create or replace view v_person as
  2    select id_emp, fname
  3    from person
  4    where nick = user;

View created.

Grant privileges to user HR (otherwise, it won't see anything):

SQL> grant select on v_person to hr;

Grant succeeded.

OK, let's check what those users see.

SQL> show user
USER is "SCOTT"
SQL> select * from v_person;

    ID_EMP FNAME
---------- --------------------
         4 Seb
         5 Romain

SQL> connect hr/hr@xe
Connected.

SQL> -- including the owner's name:
SQL> select * from scott.v_person;

    ID_EMP FNAME
---------- --------------------
         1 Little
         2 Charles
         3 Rio

SQL>

Seems to be OK, doesn't it?

As of your problem: did you, by any chance, use lowercase nick value?

SQL> update person set nick = 'scott' where id_emp > 3;

2 rows updated.

SQL> select * From v_person;

no rows selected

SQL>

If not, could you create the same, simple example, which clearly shows what you did?

Upvotes: 1

Related Questions