| |
|
If this is your first visit, be sure to check out the FAQ by clicking the link above.
You may have to register before you can post: click the register link above to proceed.
To start viewing messages, select the forum that you want to visit from the selection below.
|
 |
03-11-10, 07:43
|
|
Registered User
|
|
Join Date: Mar 2010
Posts: 2
|
|
Problem with join query
|
|
Hi Guys.
I hope you can help me solve this problem, i have a query below and i want to convert (+) into join syntax like 'left outer join ? on ? = ?'
i hope someone can solve this. 
SELECT debit_note.note_oid,
debit_note.note_no,
debit_note.pocy_oid,
txn.amt txn_amt
FROM debit_note,
settlement,
settlement_note,
bf_txn
WHERE debit_note.note_oid = settlement_note.note_oid_settled(+)
AND settlement.sett_oid(+) = settlement_note.sett_oid
AND txn.bftx_oid = debit_note.bftx_oid
AND ((TO_DATE(TO_CHAR(settlement.settle_date,'MM/DD/YYYY'),'MM/DD/YYYY')
BETWEEN TO_DATE('12/01/2009','MM/DD/YYYY')
AND TO_DATE('12/01/2009','MM/DD/YYYY')
OR TO_DATE(TO_CHAR(settlement.crt_date,'MM/DD/YYYY'),'MM/DD/YYYY')
BETWEEN TO_DATE('12/01/2009','MM/DD/YYYY')
AND TO_DATE('12/01/2009','MM/DD/YYYY')
OR TO_DATE(TO_CHAR(settlement.reverse_date,'MM/DD/YYYY'),'MM/DD/YYYY')
BETWEEN TO_DATE('12/01/2009','MM/DD/YYYY')
AND TO_DATE ('12/01/2009','MM/DD/YYYY'))
OR TO_DATE (TO_CHAR(debit_note.bill_date,'MM/DD/YYYY'),'MM/DD/YYYY')
BETWEEN TO_DATE('12/01/2009','MM/DD/YYYY')
AND TO_DATE('12/01/2009','MM/DD/YYYY'))
|
|
03-11-10, 12:14
|
|
Registered User
|
|
Join Date: Dec 2003
Posts: 1,072
|
|
Start with
Code:
FROM debit_note,
settlement_note
WHERE debit_note.note_oid = settlement_note.note_oid_settled(+)
. . .
turning into
Code:
FROM debit_note
left outer join
settlement_note on (debit_note.note_oid = settlement_note.note_oid_settled)
You should be able to figure out the rest by extrapolating from that, or by searching Google for some examples.
--=Chuck
|
|
03-11-10, 13:06
|
|
SQL Consultant
|
|
Join Date: Apr 2002
Location: Toronto, Canada
Posts: 19,084
|
|
|
|
Quote:
Originally Posted by chuck_forbes
Code:
on (debit_note.note_oid = settlement_note.note_oid_settled)
|
just curious, why the unnecessary parentheses?
|
|
03-11-10, 13:31
|
|
Registered User
|
|
Join Date: Dec 2003
Posts: 1,072
|
|
Can't remember ... some tool in my distant past had a GUI query editor (I'd bet on MSAccess if you held a gun to my head). When it translated the SQL, it put parens around the join conditions. For poorly formatted SQL it hleps make it more legible (to me). For properly formatted SQL, it's just fluff
Code:
FROM table1 join table2 on (table1.field = table2.field) left join table3 on (table1.field = table3.field and table3.other_field = 'A')
vs
Code:
FROM table1
join
table2 on (table1.field = table2.field)
left join
table3 on (table1.field = table3.field and
table3.other_field = 'A')
vs
Code:
FROM table1
join
table2 on table1.field = table2.field
left join
table3 on table1.field = table3.field and
table3.other_field = 'A'
|
|
03-11-10, 14:13
|
|
SQL Consultant
|
|
Join Date: Apr 2002
Location: Toronto, Canada
Posts: 19,084
|
|
Quote:
Originally Posted by chuck_forbes
For poorly formatted SQL it hleps make it more legible (to me).
|
okay, i totally understand this, but ...
1) if you wrote the poorly formatted SQL in the first place, you should be shot
2) if you're fixing somebody else's poorly formatted SQL, why would you need to stick parentheses in there, why wouldn't you just format it properly?
by the way, my own style has slightly different rules...
Code:
FROM table1
INNER
JOIN table2
ON table2.field = table1.field
LEFT OUTER
JOIN table3
ON table3.field = table1.field
AND table3.other_field = 'A'
notice how the tables being joined have the ON conditions written with that table's column first, so that the table names line up visually (blue), and additional conditions in the ON clause also therefore line up (table3.other_field), giving the ON clause a semblance of order
note also that the column on the right, jutting out like that (red), always refers to a previously-mentioned table
INNER and LEFT OUTER are always on a line by themselves to make it easy to spot new tables being added (you do this too) -- i've seen other people actuall put the joins at the end of the line (blech) like this --
Code:
FROM table1 join
table2 on table1.field = table2.field left join
table3 on table1.field = table3.field and table3.other_field = 'A'
as for the parentheses, yes, that was msaccess, the crappiest SQL generation i've ever seen
|
|
03-11-10, 15:20
|
|
Registered User
|
|
Join Date: Jul 2003
Posts: 2,292
|
|
PRETTY COLORS! me likey
I am all for any consistent type of formatting.
Much easier to read when someone hands you their code.
The worst is when someone hands you code and there are NO SPACES (or minimal spaces)
very annoying.
__________________
- The_Duck
you can lead someone to something but they will never learn anything ...
|
|
03-11-10, 23:50
|
|
Registered User
|
|
Join Date: Mar 2010
Posts: 2
|
|
Hi chuck forbes
thank you for the quick reply i did what you told me and it extracted a lot of records the query should only extract a total of 834 records but it extracted hundred thousands.
this is the query i did, i replaced the (+) with a join syntax:
SELECT hbs.bf_debit_note.note_oid, hbs.bf_debit_note.note_no,
hbs.bf_debit_note.pocy_oid
FROM hbs.bf_debit_note LEFT OUTER JOIN hbs.bf_settlement_note ON
hbs.bf_debit_note.note_oid = hbs.bf_settlement_note.note_oid_settled,
hbs.bf_settlement RIGHT OUTER JOIN hbs.bf_settlement_note ON
hbs.bf_settlement.sett_oid = hbs.bf_settlement_note.sett_oid,
hbs.bf_txn
WHERE hbs.bf_txn.bftx_oid = hbs.bf_debit_note.bftx_oid
AND ( ( TO_DATE (TO_CHAR (hbs.bf_settlement.settle_date, 'MM/DD/YYYY'),
'MM/DD/YYYY'
) BETWEEN TO_DATE ('12/01/2009', 'MM/DD/YYYY')
AND TO_DATE ('12/01/2009', 'MM/DD/YYYY')
OR TO_DATE (TO_CHAR (hbs.bf_settlement.crt_date, 'MM/DD/YYYY'),
'MM/DD/YYYY'
) BETWEEN TO_DATE ('12/01/2009', 'MM/DD/YYYY')
AND TO_DATE ('12/01/2009', 'MM/DD/YYYY')
OR TO_DATE (TO_CHAR (hbs.bf_settlement.reverse_date, 'MM/DD/YYYY'),
'MM/DD/YYYY'
) BETWEEN TO_DATE ('12/01/2009', 'MM/DD/YYYY')
AND TO_DATE ('12/01/2009', 'MM/DD/YYYY')
)
OR TO_DATE (TO_CHAR (hbs.bf_debit_note.bill_date, 'MM/DD/YYYY'),
'MM/DD/YYYY'
) BETWEEN TO_DATE ('12/01/2009', 'MM/DD/YYYY')
AND TO_DATE ('12/01/2009', 'MM/DD/YYYY')
)
|
|
03-12-10, 00:25
|
|
Registered User
|
|
Join Date: Aug 2003
Location: Where the Surf Meets the Turf @Del Mar, CA
Posts: 6,151
|
|
re-posted in human readable format
Code:
SELECT hbs.bf_debit_note.note_oid,
hbs.bf_debit_note.note_no,
hbs.bf_debit_note.pocy_oid
FROM hbs.bf_debit_note
LEFT OUTER JOIN hbs.bf_settlement_note
ON hbs.bf_debit_note.note_oid = hbs.bf_settlement_note.note_oid_settled,
hbs.bf_settlement
RIGHT OUTER JOIN hbs.bf_settlement_note
ON hbs.bf_settlement.sett_oid = hbs.bf_settlement_note.sett_oid,
hbs.bf_txn
WHERE hbs.bf_txn.bftx_oid = hbs.bf_debit_note.bftx_oid
AND ((To_date(To_char(hbs.bf_settlement.settle_date,'MM/DD/YYYY'),
'MM/DD/YYYY') BETWEEN To_date('12/01/2009','MM/DD/YYYY') AND To_date('12/01/2009','MM/DD/YYYY')
OR To_date(To_char(hbs.bf_settlement.crt_date,'MM/DD/YYYY'),
'MM/DD/YYYY') BETWEEN To_date('12/01/2009','MM/DD/YYYY') AND To_date('12/01/2009','MM/DD/YYYY')
OR To_date(To_char(hbs.bf_settlement.reverse_date,'MM/DD/YYYY'),
'MM/DD/YYYY') BETWEEN To_date('12/01/2009','MM/DD/YYYY') AND To_date('12/01/2009','MM/DD/YYYY'))
OR To_date(To_char(hbs.bf_debit_note.bill_date,'MM/DD/YYYY'),
'MM/DD/YYYY') BETWEEN To_date('12/01/2009','MM/DD/YYYY') AND To_date('12/01/2009','MM/DD/YYYY'))
__________________
You can lead some folks to knowledge, but you can not make them think.
The average person thinks he's above average!
For most folks, they don't know, what they don't know.
|
|
03-12-10, 00:33
|
|
Registered User
|
|
Join Date: Aug 2003
Location: Where the Surf Meets the Turf @Del Mar, CA
Posts: 6,151
|
|
Code:
LEFT OUTER JOIN hbs.bf_settlement_note
ON hbs.bf_debit_note.note_oid = hbs.bf_settlement_note.note_oid_settled,
hbs.bf_settlement
RIGHT OUTER JOIN hbs.bf_settlement_note
ON hbs.bf_settlement.sett_oid = hbs.bf_settlement_note.sett_oid,
hbs.bf_txn
NONE of the tables in the code block above contribute any data to the SELECT clause.
Therefore they can & should be eliminated out of the FROM clause.
No data in the result set comes FROM any of the table above.
If any or all are really required for data filtering, they should be subordinated into WHERE clause.
__________________
You can lead some folks to knowledge, but you can not make them think.
The average person thinks he's above average!
For most folks, they don't know, what they don't know.
|
|
03-12-10, 11:15
|
|
Registered User
|
|
Join Date: Jul 2003
Posts: 2,292
|
|
WHY ARE YOU CHANGING A DATE TO A CHAR TO A DATE?
Leave it as date
Code:
SELECT
hbs.bf_debit_note.note_oid,
hbs.bf_debit_note.note_no,
hbs.bf_debit_note.pocy_oid
FROM
hbs.bf_debit_note LEFT OUTER JOIN hbs.bf_settlement_note ON
hbs.bf_debit_note.note_oid = hbs.bf_settlement_note.note_oid_settled,
hbs.bf_settlement RIGHT OUTER JOIN hbs.bf_settlement_note ON
hbs.bf_settlement.sett_oid = hbs.bf_settlement_note.sett_oid,
hbs.bf_txn
WHERE hbs.bf_txn.bftx_oid = hbs.bf_debit_note.bftx_oid
AND ( hbs.bf_settlement.settle_date BETWEEN
TO_DATE ('12/01/2009', 'MM/DD/YYYY') AND TO_DATE ('12/01/2009', 'MM/DD/YYYY')
OR hbs.bf_settlement.crt_date BETWEEN
TO_DATE ('12/01/2009', 'MM/DD/YYYY') AND TO_DATE ('12/01/2009', 'MM/DD/YYYY')
OR hbs.bf_settlement.reverse_date BETWEEN
TO_DATE ('12/01/2009', 'MM/DD/YYYY') AND TO_DATE ('12/01/2009', 'MM/DD/YYYY')
)
OR hbs.bf_debit_note.bill_date BETWEEN
TO_DATE ('12/01/2009', 'MM/DD/YYYY') AND TO_DATE ('12/01/2009', 'MM/DD/YYYY')
)
__________________
- The_Duck
you can lead someone to something but they will never learn anything ...
|
Last edited by The_Duck; 03-12-10 at 11:19.
|
03-12-10, 12:19
|
|
Registered User
|
|
Join Date: Dec 2003
Posts: 1,072
|
|
----------------------------------------------
|
Last edited by chuck_forbes; 03-12-10 at 12:22.
Reason: wrong advice - reconsidering
|
03-12-10, 12:42
|
|
Registered User
|
|
Join Date: Dec 2003
Posts: 1,072
|
|
This would be my original rewrite of your query:
Code:
SELECT hbs.bf_debit_note.note_oid,
hbs.bf_debit_note.note_no,
hbs.bf_debit_note.pocy_oid
FROM hbs.bf_debit_note
INNER
JOIN hbs.bf_txn
ON hbs.bf_txn.bftx_oid = hbs.bf_debit_note.bftx_oid
LEFT OUTER
JOIN hbs.bf_settlement_note
ON hbs.bf_debit_note.note_oid = hbs.bf_settlement_note.note_oid_settled
LEFT OUTER
JOIN hbs.bf_settlement
ON hbs.bf_settlement.sett_oid = hbs.bf_settlement_note.sett_oid
WHERE ( hbs.bf_settlement.settle_date BETWEEN
TO_DATE ('12/01/2009', 'MM/DD/YYYY') AND TO_DATE ('12/01/2009', 'MM/DD/YYYY')
OR hbs.bf_settlement.crt_date BETWEEN
TO_DATE ('12/01/2009', 'MM/DD/YYYY') AND TO_DATE ('12/01/2009', 'MM/DD/YYYY')
OR hbs.bf_settlement.reverse_date BETWEEN
TO_DATE ('12/01/2009', 'MM/DD/YYYY') AND TO_DATE ('12/01/2009', 'MM/DD/YYYY')
OR hbs.bf_debit_note.bill_date BETWEEN
TO_DATE ('12/01/2009', 'MM/DD/YYYY') AND TO_DATE ('12/01/2009', 'MM/DD/YYYY'))
which pushes all the join criteria into the FROM clause. That's not going to get rid of all those extra records, though. Adding "SELECT DISTINCT ..." probably will, but that's not the way I would write the query either (in-line with anacedent's advice on some of those tables not contributing any columns to the SELECT clause).
This is a better version, as it's indicating your intent a little better, but the UNION is performing the same role as the DISTINCT, so it's still a little sloppy, IMO
Code:
SELECT hbs.bf_debit_note.note_oid,
hbs.bf_debit_note.note_no,
hbs.bf_debit_note.pocy_oid
FROM hbs.bf_debit_note
INNER
JOIN hbs.bf_txn
ON hbs.bf_txn.bftx_oid = hbs.bf_debit_note.bftx_oid
WHERE hbs.bf_debit_note.bill_date BETWEEN
TO_DATE ('12/01/2009', 'MM/DD/YYYY') AND TO_DATE ('12/01/2009', 'MM/DD/YYYY'))
UNION
SELECT hbs.bf_debit_note.note_oid,
hbs.bf_debit_note.note_no,
hbs.bf_debit_note.pocy_oid
FROM hbs.bf_debit_note
INNER
JOIN hbs.bf_txn
ON hbs.bf_txn.bftx_oid = hbs.bf_debit_note.bftx_oid
LEFT OUTER
JOIN hbs.bf_settlement_note
ON hbs.bf_debit_note.note_oid = hbs.bf_settlement_note.note_oid_settled
LEFT OUTER
JOIN hbs.bf_settlement
ON hbs.bf_settlement.sett_oid = hbs.bf_settlement_note.sett_oid
WHERE ( hbs.bf_settlement.settle_date BETWEEN
TO_DATE ('12/01/2009', 'MM/DD/YYYY') AND TO_DATE ('12/01/2009', 'MM/DD/YYYY')
OR hbs.bf_settlement.crt_date BETWEEN
TO_DATE ('12/01/2009', 'MM/DD/YYYY') AND TO_DATE ('12/01/2009', 'MM/DD/YYYY')
OR hbs.bf_settlement.reverse_date BETWEEN
TO_DATE ('12/01/2009', 'MM/DD/YYYY') AND TO_DATE ('12/01/2009', 'MM/DD/YYYY'))
SO this would be as explicit as you could get
Code:
SELECT hbs.bf_debit_note.note_oid,
hbs.bf_debit_note.note_no,
hbs.bf_debit_note.pocy_oid
FROM hbs.bf_debit_note
WHERE hbs.bf_debit_note.bill_date BETWEEN
TO_DATE ('12/01/2009', 'MM/DD/YYYY') AND TO_DATE ('12/01/2009', 'MM/DD/YYYY'))
and EXISTS (select 1 from hbs.bf_txn where hbs.bf_txn.bftx_oid = hbs.bf_debit_note.bftx_oid)
UNION ALL
SELECT hbs.bf_debit_note.note_oid,
hbs.bf_debit_note.note_no,
hbs.bf_debit_note.pocy_oid
FROM hbs.bf_debit_note
WHERE EXISTS (select 1 from hbs.bf_txn where hbs.bf_txn.bftx_oid = hbs.bf_debit_note.bftx_oid) and
EXISTS (SELECT 1
FROM hbs.bf_settlement_note
LEFT OUTER
JOIN hbs.bf_settlement
ON hbs.bf_settlement.sett_oid = hbs.bf_settlement_note.sett_oid
WHERE hbs.bf_debit_note.note_oid = hbs.bf_settlement_note.note_oid_settled
AND ( hbs.bf_settlement.settle_date BETWEEN
TO_DATE ('12/01/2009', 'MM/DD/YYYY') AND TO_DATE ('12/01/2009', 'MM/DD/YYYY')
OR hbs.bf_settlement.crt_date BETWEEN
TO_DATE ('12/01/2009', 'MM/DD/YYYY') AND TO_DATE ('12/01/2009', 'MM/DD/YYYY')
OR hbs.bf_settlement.reverse_date BETWEEN
TO_DATE ('12/01/2009', 'MM/DD/YYYY') AND TO_DATE ('12/01/2009', 'MM/DD/YYYY')))
which could be simplified to
Code:
SELECT hbs.bf_debit_note.note_oid,
hbs.bf_debit_note.note_no,
hbs.bf_debit_note.pocy_oid
FROM hbs.bf_debit_note
WHERE EXISTS (select 1 from hbs.bf_txn where hbs.bf_txn.bftx_oid = hbs.bf_debit_note.bftx_oid)
AND (hbs.bf_debit_note.bill_date BETWEEN
TO_DATE ('12/01/2009', 'MM/DD/YYYY') AND TO_DATE ('12/01/2009', 'MM/DD/YYYY')
OR
EXISTS (SELECT 1
FROM hbs.bf_settlement_note
LEFT OUTER
JOIN hbs.bf_settlement
ON hbs.bf_settlement.sett_oid = hbs.bf_settlement_note.sett_oid
WHERE hbs.bf_debit_note.note_oid = hbs.bf_settlement_note.note_oid_settled
AND ( hbs.bf_settlement.settle_date BETWEEN
TO_DATE ('12/01/2009', 'MM/DD/YYYY') AND TO_DATE ('12/01/2009', 'MM/DD/YYYY')
OR hbs.bf_settlement.crt_date BETWEEN
TO_DATE ('12/01/2009', 'MM/DD/YYYY') AND TO_DATE ('12/01/2009', 'MM/DD/YYYY')
OR hbs.bf_settlement.reverse_date BETWEEN
TO_DATE ('12/01/2009', 'MM/DD/YYYY') AND TO_DATE ('12/01/2009', 'MM/DD/YYYY'))))
(there are probably parens off somewhere in there ....)
|
|
| Thread Tools |
|
|
| Display Modes |
 Linear Mode
|
Posting Rules
|
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts
HTML code is Off
|
|
|
|
|
dBforums
>
Problem with join query
