Home » SQL & PL/SQL » SQL & PL/SQL » SQL join problem (Oracle 10.2.0.3)
SQL join problem [message #316036] Wed, 23 April 2008 09:41 Go to next message
imdreaming
Messages: 11
Registered: April 2008
Junior Member
Am hoping to get some help on a query which is returning wrong data. Query joins 6 tables. All tables return just 1 row, with the exception of TableE, which can return 0 to N rows. Query does sums and counts on TableQ. Those calculations are wrong if TableE returns data, by a factor of the number of rows in TableE. So, if the count(Q.foo) should be 1, but there are 3 rows in TableE, count(Q.foo) returns a value of 3.
Here's the somewhat-simplified query:

select v_user_id user_id,
c.name,
count(q.foo),
sum(q.foo2),
from table_rt rt,
table_rb rb,
table_fd fd,
table_n n,
table_q q,
table_e e
where rt.user_id = v_user_id
and rt.rb_id=rb.rb_id
and q.a_q = 'Y'
and rb.rb_id=fd.rb_id
and rb.rb_id=q.rb_id
and n.a_id=q=rb.ib
and rb.rb_id=e.rb_id(+) -- remove
and e.status(+)='xyz' -- remove
group by c.name;


Table_e contains 3 rows, all other tables contain 1 row. The count and sum of Table_q *should* return values of 1, but instead return values of 3. If I remove the lines marked 'remove', the query returns OK values, but is missing the info from Table_e that I need.

Suggestions? TIA



Re: SQL join problem [message #316043 is a reply to message #316036] Wed, 23 April 2008 10:05 Go to previous messageGo to next message
BlackSwan
Messages: 25042
Registered: January 2009
Location: SoCal
Senior Member
>Query joins 6 tables.
Why, when data is only returned from 3 tables?
Subordinate the "extra" tables into the the WHERE clause & things might work better for you.
Re: SQL join problem [message #316045 is a reply to message #316043] Wed, 23 April 2008 10:09 Go to previous messageGo to next message
imdreaming
Messages: 11
Registered: April 2008
Junior Member
Thanks. I have gone that road.

Business rules in this case dictate that the data must exist in those other tables that return no data to this query. However, to simplify the problem I *did* remove those tables in my work on solution and the problem still exists. The problem happens when the join to Table_E happens, where there are 3 rows vs. the 1 row in Table_Q.

I have inherited this odd/bad database design. Unfortunately, it can't be corrected overnight.

select v_user_id user_id,
c.name,
count(q.foo),
sum(q.foo2),
from table_rt rt,
table_rb rb,
table_q q,
table_e e
where rt.user_id = v_user_id
and rt.rb_id=rb.rb_id
and q.a_q = 'Y'
and rb.rb_id=q.rb_id
and rb.rb_id=e.rb_id(+) -- remove
and e.status(+)='xyz' -- remove
group by c.name;

[Updated on: Wed, 23 April 2008 10:10]

Report message to a moderator

Re: SQL join problem [message #316046 is a reply to message #316043] Wed, 23 April 2008 10:10 Go to previous messageGo to next message
skooman
Messages: 912
Registered: March 2005
Location: Netherlands
Senior Member
Furthermore, you're selecting from c, which is not in the from clause. Are you sure this is the exact statement?
Re: SQL join problem [message #316047 is a reply to message #316046] Wed, 23 April 2008 10:12 Go to previous messageGo to next message
imdreaming
Messages: 11
Registered: April 2008
Junior Member
I've simplified the query a bit for this posting. Let me post the entire thing.
INSERT INTO REPORT_TEMP(USER_ID, REC_TYPE, VFIELD1, NFIELD1, NFIELD2, NFIELD3, NFIELD4, NFIELD5, NFIELD6, NFIELD7)
SELECT /*+ index (FD)
index (N)
index (CO) */
V_USER_ID USER_ID,
'DETAIL' REC_TYPE,
CO.COMMON_NAME VFIELD1,
COUNT(QT.RISK_BODY_ID) NFIELD1,
SUM(QT.REINSURANCE_OFFERED) NFIELD2,
SUM(QT.GROSS_PREMIUM) NFIELD3,
SUM(QT.CEDING_COMMISSION_AMOUNT) NFIELD4,
SUM(QT.NET_PREMIUM) NFIELD5,
NFIELD6 NFIELD6,
SUM(DECODE(ENDORSEMENT.PREMIUM_ADJUSTMENT_TYPE, 1, ENDORSEMENT.PREMIUM_ADJUSTMENT, 2, (ENDORSEMENT.PREMIUM_ADJUSTMENT*-1))) NFIELD7
FROM REPORT_TEMP RT,
RISK_BODY RB,
FINANCIAL_DETAIL FD,
NEGOTIATION N,
QUOTE QT,
COMPANY CO,
ENDORSEMENT
WHERE RT.USER_ID = V_USER_ID
AND RT.REC_TYPE = 'DATASET'
AND RT.RISK_BODY_ID = RB.RISK_BODY_ID
AND QT.ACCEPT_QUOTE = 'Y'
AND RB.RISK_BODY_ID = FD.RISK_BODY_ID
AND RB.RISK_BODY_ID = N.RISK_BODY_ID
AND RB.RISK_BODY_ID = QT.RISK_BODY_ID
AND N.ASSUMER_COMPANY_ID = CO.COMPANY_ID
AND N.ASSUMER_COMPANY_ID NOT IN (153822,153842,153862)
AND RB.RISK_BODY_ID = ENDORSEMENT.RISK_BODY_ID(+)
AND ENDORSEMENT.STATUS_ID(+) = 1302
GROUP BY CO.COMMON_NAME, NFIELD6;

[Updated on: Wed, 23 April 2008 10:13]

Report message to a moderator

Re: SQL join problem [message #316050 is a reply to message #316036] Wed, 23 April 2008 10:13 Go to previous messageGo to next message
BlackSwan
Messages: 25042
Registered: January 2009
Location: SoCal
Senior Member
http://www.orafaq.com/forum/t/88153/0/
Please read & follow posting guidelines as stated in URL above.

Since we don't have your tables & don't have your data, there is not a whole lot anybody other than you can do anything to solve this mystery.

Yes, you seem to have a problem.
Good luck at find the solution.
You're On Your Own (YOYO)!
Re: SQL join problem [message #316052 is a reply to message #316050] Wed, 23 April 2008 10:20 Go to previous messageGo to next message
skooman
Messages: 912
Registered: March 2005
Location: Netherlands
Senior Member
One wild guess: should the count perhaps be a count distinct?? (since you stated it gave a higher result then it should once you have more rows in another table)
Re: SQL join problem [message #316053 is a reply to message #316052] Wed, 23 April 2008 10:23 Go to previous message
imdreaming
Messages: 11
Registered: April 2008
Junior Member
ding ding ding!

Thanks! I think that fixed.

Going back and testing with my full dataset.
Previous Topic: Need help validating a credit card
Next Topic: Materialized view -- insufficient privileges
Goto Forum:
  


Current Time: Wed Dec 07 03:17:06 CST 2016

Total time taken to generate the page: 0.14727 seconds