Home » SQL & PL/SQL » SQL & PL/SQL » SQL coding question
| SQL coding question [message #20417] |
Wed, 22 May 2002 07:29  |
Debbie Hofstot
Messages: 1
Registered: May 2002
|
Junior Member |
|
|
I am trying to query two tables to produce a report. One table contains all the transaction counts needed and the other contains all the character counts needed in the report. When I run the query my output counts are "n" times the amount they should be, where "n" represents the number of rows found on each table. If you can offer any suggestions I would appreciate it.
i.e. trans table: 820, RA, IN, EDI, 14 (row 1)
820, RA, IN, EDI, 20 (row 2)
char table: RA, IN, EDI, 514 (row 1)
RA, IN, EDI, 624 (row 2)
result wanted : 820, IN, EDI, 34, 1138
result getting: 820, IN, EDI, 68, 2276
select t.document_type,'-',
t.process_type,',',
sum(t.doc_count) "TRANS",',',
sum(g.char_count) "CHARS"
from tran_statistics_history t,
group_statistics_history g
where t.direction = 'IN' and
t.data_format = 'EDI' and
t.data_format = g.data_format and
t.process_type = g.process_type and
t.group_type = g.group_type and
t.statistics_month_ccyymm = g.statistics_month_ccyymm and
t.statistics_month_ccyymm = to_char(SYSDATE,'yyyymm')
group by t.data_format,
t.process_type,
t.document_type,
t.direction;
|
|
|
|
| Re: SQL coding question [message #20431 is a reply to message #20417] |
Thu, 23 May 2002 05:56  |
John R
Messages: 156
Registered: March 2000
|
Senior Member |
|
|
I think you are missing an additional join condition between the two tables.
At the moment it is performing a cartesian join between the two tables and returning every row on table G for each row on table T.
If you had 3 rows in each table the results would be 9 times too high.
You need some way to uniquely identify a row on G from the data on T.
Hope this helps.
|
|
|
|
| Re: SQL coding question [message #20459 is a reply to message #20417] |
Sat, 25 May 2002 12:31 |
Su
Messages: 154
Registered: April 2002
|
Senior Member |
|
|
As John mention, it is producing a curtasian product, which in turn returns the number of rows multiplied by another. So there should be a unique condition which distinguishes one from onther. Here in your rows, the only difference is, doc_count and char_count. So you include those columns in the GROUP BY clause of your SELECT. It should work.
The query should look like this:
select t.document_type,'-',
t.process_type,',',
sum(t.doc_count) "TRANS",',',
sum(g.char_count) "CHARS"
from tran_statistics_history t,
group_statistics_history g
where t.direction = 'IN' and
t.data_format = 'EDI' and
t.data_format = g.data_format and
t.process_type = g.process_type and
t.group_type = g.group_type and
t.statistics_month_ccyymm = g.statistics_month_ccyymm and
t.statistics_month_ccyymm = to_char(SYSDATE,'yyyymm')
group by t.data_format,
t.process_type,
t.document_type,
t.direction,
T.DOC_COUNT,
G.CHAR_COUNT
;
It should work, if this does not work, please mail me immediately.
Good luck :)
|
|
|
|
Goto Forum:
Current Time: Fri Apr 26 14:06:58 CDT 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
