Please help convert this Access SQL query

[jlindahl]

Here is the query that is being used for an access database. My trouble is, i am using an oracle database. I don't really understand joins, so can anyone please shed some light on this subject. Thanks in advance.

select i.approved, i.assigned_to, i.date_modified, i.date_submitted, i.issue_desc, i.issue_id, i.issue_name, i.priority_id, i.status_id, i.tested, i.user_id, i.version, s.status_id, s.status, p.priority_id, p.priority_order, u.user_id, u.user_name
from (((issues i left join statuses s on s.status_id=i.status_id) left join priorities p on p.priority_id=i.priority_id) left join users u on u.user_id=i.assigned_to) WHERE (i.status_id<>3) order by i.date_modified Desc

FYI - When i run this, i get and error type message of &quot;missing SELECT keyword&quot;

 

[rrrkrishnan]

Try this.

select i.approved, i.assigned_to, i.date_modified, i.date_submitted, i.issue_desc, i.issue_id, i.issue_name, i.priority_id, i.status_id, i.tested, i.user_id, i.version, s.status_id, s.status, p.priority_id, p.priority_order, u.user_id, u.user_name
from issues i
left outer join statuses s on s.status_id=i.status_id
left outer join priorities p on p.priority_id=i.priority_id
left outer join users u on u.user_id=i.assigned_to
WHERE (i.status_id<>3)
order by i.date_modified Desc

 

[rrrkrishnan]

Sorry the previous response had the ANSI SQL syntax. I think this should work in oracle.

select i.approved, i.assigned_to, i.date_modified, i.date_submitted, i.issue_desc, i.issue_id, i.issue_name, i.priority_id, i.status_id, i.tested, i.user_id, i.version, s.status_id, s.status, p.priority_id, p.priority_order, u.user_id, u.user_name
from issues i, statuses s, priorities p, users u
WHERE (i.status_id<>3)
and s.status_id=i.status_id(+)
and p.priority_id=i.priority_id(+)
and u.user_id=i.assigned_to(+)
order by i.date_modified Desc

 

[jlindahl]

Thanks, and i bet that would work, but this is the error i get now.

Oracle Automation (0x800A01B8)
SQL execution error, ORA-01417: a table may be outer joined to at most one other table

 

[rrrkrishnan]

Try this.

select dt1.approved, dt1.assigned_to, dt1.date_modified, dt1.date_submitted, dt1.issue_desc,
dt1.issue_id, dt1.issue_name, dt1.priority_id, dt1.status_id, dt1.tested, dt1.user_id, dt1.version,
dt1.status_id, dt1.status, dt2.priority_id, dt2.priority_order, dt3.user_id, dt3.user_name
from (select i.*,s.s.status_id
from issues i, statuses s
WHERE i.status_id<>3
and s.status_id=i.status_id(+)) dt1,
(select i.*, p.priority_id, p.priority_order
from issues i, priorities p
WHERE i.status_id<>3
and p.priority_id=i.priority_id(+)) dt2,
(select i.*,u.user_id, u.user_name
from issues i, users u
WHERE i.status_id<>3
and u.user_id=i.assigned_to(+)) dt3
where dt1.issue_id = dt2.issue_id
and dt2.issue_id= dt3.issue_id
order by dt1.date_modified Desc


join dt1, dt2 and dt3 using the columns which form the key for table issues. I assume it is issue_id.

Performance could be a problem, but this is the way to do it if you need to do this with one SQL stmt.Otherwise you can use a temp table. insert all records from issues
into temp and then update columns status, priority and user.

 

[jlindahl]

well, progress..... i now get this new error.

Error Type:
Oracle Automation (0x800A01B8)
SQL execution error, ORA-00918: column ambiguously defined

in all honesty, i think there might be a better way to do this, just by changing the actual program. this was a issue tracking program that used access, but i may just go ahead and modify the program itself, because i really don't understand these table joins.

thanks for all your help.

 

[rrrkrishnan]

My last attempt.

select dt1.approved, dt1.assigned_to, dt1.date_modified, dt1.date_submitted, dt1.issue_desc,
dt1.issue_id, dt1.issue_name, dt1.priority_id, dt1.status_id, dt1.tested, dt1.user_id, dt1.version,
dt1.status_id, dt1.status, dt1.priority_id, dt2.priority_order, dt1.user_id, dt3.user_name
from (select i.approved, i.assigned_to, i.date_modified, i.date_submitted, i.issue_desc,
i.issue_id, i.issue_name, i.priority_id, i.status_id, i.tested, i.user_id, i.version,
i.status_id,s.status
from issues i, statuses s
WHERE i.status_id<>3
and s.status_id=i.status_id(+)) dt1,
(select p.priority_order
from issues i, priorities p
WHERE i.status_id<>3
and p.priority_id=i.priority_id(+)) dt2,
(select u.user_name
from issues i, users u
WHERE i.status_id<>3
and u.user_id=i.assigned_to(+)) dt3
where dt1.issue_id = dt2.issue_id
and dt2.issue_id= dt3.issue_id
order by dt1.date_modified Desc