SQL Mental Block - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

SQL Mental Block

Author

Topic

mattarm
Starting Member

1 Post

Posted - 2004-02-20 : 22:21:57

Hi folks,

I have a SQL query in Coldfucion and for the life of me I cannot see the problem. the code:
---------------------------------------------------
SELECT t2.imlitm as stock_code,
t1.sddsc1 as item_description,
sum(t1.sduorg)/1000 as order_quantity,
sum(t1.sdaexp)/100 as Amount
FROM proddta.f4211 t1, proddta.f4101 t2, proddta.F0150 t3
WHERE t1.sdan8 = t3.MAAN8
AND t2.imlitm = 'B06'
AND t1.sditm = t2.imitm
AND t3.MAPA8 = #ARGUMENTS.customerID#
GROUP by t1.sddsc1, t2.imlitm
union
SELECT t2.imlitm as stock_code,
t1.sddsc1 as item_description,
sum(t1.sduorg)/1000 as order_quantity,
sum(t1.sdaexp)/100 as Amount
FROM proddta.f42119 t1, proddta.f4101 t2, proddta.F0150 t3
WHERE t1.sdan8 = t3.MAAN8
AND t2.imlitm = 'B06'
AND t1.sditm = t2.imitm
AND t3.MAPA8 = #ARGUMENTS.customerID#
GROUP by t1.sddsc1, t2.imlitm
---------------------------------------------------

The tables are
f4101 - the member list
F0150 - associating members in child/parent relationships
f4101 - item data (description, value etc)

This returns a figure for the sum amounts greater than if I check the values of each memberID individually.

Hopefully some one can point me inthe right direction. I'm sure there is something basic that is wrong with the methodology (or perhaps just a coding error)

Any and all guidance greatly appreciated.

Thanks Matt

simondeutsch
Aged Yak Warrior

547 Posts

Posted - 2004-02-21 : 21:58:33

Hard to say exactly. It could be either the data is bad, or you are missing a join condition or criteria.

First, I've rewritten the query using INNER JOINs rather than the legacy syntax


SELECT t2.imlitm as stock_code,
t1.sddsc1 as item_description, 
sum(t1.sduorg)/1000 as order_quantity, 
sum(t1.sdaexp)/100 as Amount
FROM proddta.f4211 t1 inner join proddta.F0150 t3 
on t1.sdan8 = t3.MAAN8 inner join proddta.f4101 t2
on t1.sditm = t2.imitm
WHERE t2.imlitm = 'B06' AND t3.MAPA8 = #ARGUMENTS.customerID#
GROUP by t1.sddsc1, t2.imlitm

The relationships are hard to understand. f4101 to f4211 looks like it should be one to one, so check that there is only one entry in f4101 whose imlitm is 'B06'. What is the relationship between F0150 to the others? If it is many to one, that will also throw off aggregates.

Sarah Berger MCSD

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources