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

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:

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

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:

<span>JOIN</span> cwd_directory <span>d</span> <span>ON</span> u<span>.</span>directory_id <span>=</span> <span>d</span><span>.</span>ID

to

<span>JOIN</span> cwd_directory <span>d</span> <span>ON</span> u<span>.</span>directory_id <span>=</span> <span>d</span><span>.</span>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