Sql from Oracle XE Jira version 6.4.12

[Pertti Oinonen]

Hi,

what I am doing wrong with this sql, I get allways error below:

JOIN  cwd_directory d ON u.directory_id = d.ID

      *

ERROR at line 16:

ORA-00942: table or view does not exist

-----------------------------------------------------

SELECT d.directory_name,

    u.user_name,

        TO_DATE('19700101','yyyymmdd') + ((attribute_value/1000)/24/60/60) as last_login_date

FROM cwd_user u

JOIN (

    SELECT DISTINCT child_name

    FROM cwd_membership m

    JOIN globalpermissionentry gp ON m.parent_name = gp.GROUP_ID

    WHERE gp.permission IN ('ADMINISTER', 'USE', 'SYSTEM_ADMIN')

        ) m ON m.child_name = u.user_name

JOIN (

    SELECT *

    FROM cwd_user_attributes ca

    WHERE attribute_name = 'login.lastLoginMillis'

    ) a ON a.user_id = u.ID

JOIN  cwd_directory d ON u.directory_id = d.ID

order by last_login_date desc;

Br.

Pertti Oinonen

[Andy Heinzer · Accepted solution]

Hi Pertti,

That error tends to indicate there is either a problem with the syntax or your permissions to view a particular table.  I take it you got this query from this kb: Retrieve last login dates for users from the database

It looks like there might have been an extra white space on that line.  Perhaps SQL is thinking that the table name is incorrect here.  Try this instead:

SELECT d.directory_name, u.user_name, TO_DATE('19700101','yyyymmdd') + ((attribute_value/1000)/24/60/60) as last_login_date
FROM cwd_user u
JOIN (
    SELECT DISTINCT child_name
    FROM cwd_membership m
    JOIN licenserolesgroup gp ON m.parent_name = gp.GROUP_ID
    ) m ON m.child_name = u.user_name
JOIN (
    SELECT *
    FROM cwd_user_attributes ca
    WHERE attribute_name = 'login.lastLoginMillis'
    ) a ON a.user_id = u.ID
JOIN cwd_directory d ON u.directory_id = d.ID
order by last_login_date desc;

If that doesn't help, then try to see if you can return a basic list of those two tables being referred to here such as

select * from cwd_directory;

and

select * from cwd_user;

I would expect both of these would return at least one result each.  But if you get an error accessing either table, perhaps there is another problem here.  You could also try to adjust that line from:

JOIN cwd_directory d ON u.directory_id = d.ID

to

JOIN cwd_directory d ON u.directory_id = d.id

I am curious to see if perhaps Oracle might have some level of case sensitivity when referencing the column name.  In my Postgresql, that column name is id and not ID.

Please let me know the results.

Andy

[Andy Heinzer]

Hi Pertti,

I see you marked this answer as accepted.  Thank you. 

Could you please let me know which steps seemed to help here?  My concern is that there might be something inaccurate or incorrect in our KB and I'd like to make sure it is up to date if need be.

Thanks

Andy