| Help with pre 9i Join to ANSI standard [message #625035] |
Tue, 30 September 2014 09:22  |
 |
sucheta
Messages: 5
Registered: September 2014
|
Junior Member |
|
|
I have been tasked with converting old style join to ANSI. I completed the task but the two queries return slightly different result sets. It seems I have done something incorrect and hope someone can show me the proper conversion? Thanks
Here is the query I need to convert:
Select Table_T2.username, Table_T1.date, Table_T1.days, Table_T1.loginID
From Table_T1, Table_T2, Table_T3, Table_T4
Where Table_T1.statement = Table_T2.userid
and Table_T2.username = 'string'
and Table_T1.id = Table_T4.id +
and 1970 = Table_T4.upperID +
and Table_T4.lowerID = Table_T3.id +
and (Table_T1.id <> 0 and (Table_T3.alternateID is NULL))
|
|
|
|
|
|
|
|
| Re: Help with pre 9i Join to ANSI standard [message #625038 is a reply to message #625037] |
Tue, 30 September 2014 09:35  |
|
sucheta
Messages: 5
Registered: September 2014
|
Junior Member |
|
|
Here it is formatted. Looks worse to me but if others can read it better, so be it.
SELECT table_t2.username,
table_t1.date,
table_t1.days,
table_t1.loginid
FROM table_t1,
table_t2,
table_t3,
table_t4
WHERE table_t1.statement = table_t2.userid
AND table_t2.username = 'string'
AND table_t1.id = table_t4.id +
and 1970 = table_t4.upperid +
AND table_t4.lowerid = table_t3.id +
AND (
table_t1.id <> 0
AND (
table_t3.alternateid IS NULL))
*BlackSwan added {code} tags. Please do so yourself in the future. http://www.orafaq.com/forum/t/174502/
[Updated on: Tue, 30 September 2014 09:38] by Moderator Report message to a moderator |
|
|
|
|
|
|
|
|
|
|
|
| Re: Help with pre 9i Join to ANSI standard [message #625043 is a reply to message #625041] |
Tue, 30 September 2014 09:58 |
cookiemonster
Messages: 13921
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
Nope, the + operator is invalid is you've written it. It's used for adding numbers and needs to have a number column or constant on either side of it.
The outer-join operator, which is apparently what you meant is (+) - the brackets are necessary.
|
|
|
|
|
|
| Re: Help with pre 9i Join to ANSI standard [message #625045 is a reply to message #625044] |
Tue, 30 September 2014 10:20 |
|
sucheta
Messages: 5
Registered: September 2014
|
Junior Member |
|
|
Here is my ANSI attempt and attempt to get it to the correct formatting, syntax, etc for all here to view. I see what everyone means about the + now. My mistake obviously. Apologies. Yes table_t3.alternateid is nullable.
SELECT table_t2.username,
table_t1.date,
table_t1.days,
table_t1.loginid
FROM table_t2,
table_t1
LEFT OUTER JOIN table_t4
ON table_t1.id = table_t4.id
RIGHT OUTER JOIN table_t4
ON table_t4.lowerid = table_t3.id
LEFT OUTER JOIN table_t4
ON table_t4.upperid = 1970
WHERE table_t1.statement = table_t2.userid
AND table_t2.username = 'string'
AND (
table_t1.id <> 0
AND (
table_t3.alternateid IS NULL))
[Edit MC: fix code tags]
[Updated on: Tue, 30 September 2014 10:32] by Moderator Report message to a moderator |
|
|
|
| Re: Help with pre 9i Join to ANSI standard [message #625047 is a reply to message #625045] |
Tue, 30 September 2014 10:55 |
cookiemonster
Messages: 13921
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
Ok, So Here's the issues:
1) Those brackets don't actually do anything useful, so you should just lose them.
2) You're mixing join styles - if you're using ANSI joins you should use them for all tables, so there should be an inner join for t1 and t2 rather than do it in the where clause as you have done.
3) You're mixing outer join styles - while you can use both left and right in the same query it's almost never necessary and just makes it harder to read.
4) You've got two outer joins to t4 where you should have one. Therefore you will get two different sets of t4 records for every t1 record.
It should probably be this:
SELECT table_t2.username,
table_t1.date,
table_t1.days,
table_t1.loginid
FROM table_t1
JOIN table_t2 ON table_t1.statement = table_t2.userid
LEFT JOIN table_t4 ON table_t1.id = table_t4.id
AND table_t4.upperid = 1970
LEFT JOIN table_t3 ON table_t4.lowerid = table_t3.id
WHERE table_t1.id <> 0
AND table_t2.username = 'string'
AND table_t3.alternateid IS NULL;
|
|
|
|
|
|
| Re: Help with pre 9i Join to ANSI standard [message #625057 is a reply to message #625056] |
Tue, 30 September 2014 15:12 |
Bill B
Messages: 1971
Registered: December 2004
|
Senior Member |
|
|
how about
SELECT table_t2.username,
table_t1.date,
table_t1.days,
table_t1.loginid
FROM TABLE_T1 CROSS JOIN TABLE_T2 CROSS JOIN TABLE_T3 CROSS JOIN TABLE_T4
WHERE ( ( ( ( ( (table_t1.statement = table_t2.userid)
AND (table_t2.username = 'string'))
AND (table_t1.id = (table_t4.id
)))
AND (1970 = (table_t4.upperid
)))
AND (table_t4.lowerid = (table_t3.id
)))
AND (table_t1.id <> 0))
AND (table_t3.alternateid IS NULL) |
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
