how to fix repeating rows after a join? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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 2005 Forums

Transact-SQL (2005)

how to fix repeating rows after a join?

Author

Topic

DMarmolejos
Yak Posting Veteran

65 Posts

Posted - 2008-07-21 : 10:44:55

SELECT T0.[ItemCode], T1.[ItmsGrpNam], T2.[OnHand], MAX(case when T3.PriceList=3 then T3.Price end) as 'Retail'
FROM [dbo].[OITM] T0 INNER JOIN [dbo].[OITB] T1 ON T0.ItmsGrpCod = T1.ItmsGrpCod INNER JOIN [dbo].[OITW] T2 ON T0.ItemCode = T2.ItemCode INNER JOIN [dbo].[ITM1] T3 ON T0.ItemCode = T3.ItemCode
GROUP BY T0.[ItemCode], T1.[ItmsGrpNam], T2.[OnHand]
ORDER BY T0.[ItemCode] ASC

This is my code, when I execute this I get some rows to duplicate giving incorrect row.

Wrong code that I am getting:

ItemCode GroupName OnHand Retail

1 abc 0.00 (wrong value)
1 abc 22.10 1.23
2 abc 23.40 7.12
3 abc 0.00 (wrong value)3 abc 22.80 4.90

what I want is:
1 abc 22.10 1.23
2 abc 23.40 7.12
3 abc 22.80 4.90

Also, when I take out T2.OnHand out of the code, it executes perfectly, but when I add it in, I get duplicate rows

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-07-21 : 10:50:10

[code]SELECT [ItemCode],
[ItmsGrpNam],
[OnHand],
SUM(Retail) AS Retail
FROM (
SELECT T0.[ItemCode],
T1.[ItmsGrpNam],
T2.[OnHand],
MAX(case when T3.PriceList = 3 then T3.Price end) as Retail
FROM [dbo].[OITM] AS T0
INNER JOIN [dbo].[OITB] AS T1 ON T0.ItmsGrpCod = T1.ItmsGrpCod
INNER JOIN [dbo].[OITW] T2 ON T0.ItemCode = T2.ItemCode
INNER JOIN [dbo].[ITM1] T3 ON T0.ItemCode = T3.ItemCode
GROUP BY T0.[ItemCode],
T1.[ItmsGrpNam],
T2.[OnHand]
) AS d
GROUP BY [ItemCode],
[ItmsGrpNam],
[OnHand]
ORDER BY [ItemCode],
[ItmsGrpNam],
[OnHand][/code]

E 12°55'05.25"
N 56°04'39.16"

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-07-21 : 10:53:17

[code]SELECT T0.[ItemCode], T1.[ItmsGrpNam], T2.[OnHand], SUM(case when T3.PriceList=3 then T3.Price else 0 end) as 'Retail'
FROM [dbo].[OITM] T0 INNER JOIN [dbo].[OITB] T1 ON T0.ItmsGrpCod = T1.ItmsGrpCod INNER JOIN [dbo].[OITW] T2 ON T0.ItemCode = T2.ItemCode INNER JOIN [dbo].[ITM1] T3 ON T0.ItemCode = T3.ItemCode
GROUP BY T0.[ItemCode], T1.[ItmsGrpNam], T2.[OnHand]
ORDER BY T0.[ItemCode] ASC[/code]

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-07-21 : 10:56:27

What datatype is OnHand? Does it have a CASE SenSitIVe collation?

E 12°55'05.25"
N 56°04'39.16"

DMarmolejos
Yak Posting Veteran

65 Posts

Posted - 2008-07-21 : 10:56:45

I appreciate the help, but that code gave me the same result as before

DMarmolejos
Yak Posting Veteran

65 Posts

Posted - 2008-07-21 : 10:58:59

OnHand is a varchar.. for some reason it works perfect when I take it out the code so can i distinct that and group by?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-07-21 : 11:01:37

quote:
Originally posted by DMarmolejos

OnHand is a varchar.. for some reason it works perfect when I take it out the code so can i distinct that and group by?

then i think you have some character coming in one set of results (may be space or some special character which is causing this). just try trimming them and see

SELECT T0.[ItemCode], T1.[ItmsGrpNam], LTRIM(RTRIM(T2.[OnHand])), SUM(case when T3.PriceList=3 then T3.Price else 0 end) as 'Retail' 
FROM [dbo].[OITM] T0 INNER JOIN [dbo].[OITB] T1 ON T0.ItmsGrpCod = T1.ItmsGrpCod INNER JOIN [dbo].[OITW] T2 ON T0.ItemCode = T2.ItemCode INNER JOIN [dbo].[ITM1] T3 ON T0.ItemCode = T3.ItemCode 
GROUP BY T0.[ItemCode], T1.[ItmsGrpNam], LTRIM(RTRIM(T2.[OnHand]))
ORDER BY T0.[ItemCode] ASC

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-07-21 : 11:02:17

Most probably OnHand is Case Sensitive.

SELECT		UPPER(t0.ItemCode) AS ItemCode,
		UPPER(t1.ItmsGrpNam) AS ItmsGrpNam,
		UPPER(t2.OnHand) AS OnHand,
		MAX(CASE WHEN t3.PriceList = 3 THEN t3.Price END) AS Retail
FROM		dbo.OITM AS t0
INNER JOIN	dbo.OITB AS t1 ON t1.ItmsGrpCod = t0.ItmsGrpCod
INNER JOIN	dbo.OITW AS t2 ON t2.ItemCode = t0.ItemCode
INNER JOIN	dbo.ITM1 AS t3 ON t3.ItemCode = t0.ItemCode
GROUP BY	UPPER(t0.ItemCode),
		UPPER(t1.ItmsGrpNam),
		UPPER(t2.OnHand)
ORDER BY	UPPER(t0.ItemCode),
		UPPER(t1.ItmsGrpNam),
		UPPER(t2.OnHand)

E 12°55'05.25"
N 56°04'39.16"

DMarmolejos
Yak Posting Veteran

65 Posts

Posted - 2008-07-21 : 11:06:56

Thank you both for the help, but both gave me the same results as before

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-07-21 : 11:13:00

[code]SELECT UPPER(LTRIM(RTRIM(t0.ItemCode))) AS ItemCode,
UPPER(LTRIM(RTRIM(t1.ItmsGrpNam))) AS ItmsGrpNam,
UPPER(LTRIM(RTRIM(t2.OnHand))) AS OnHand,
MAX(CASE WHEN t3.PriceList = 3 THEN t3.Price END) AS Retail
FROM dbo.OITM AS t0
INNER JOIN dbo.OITB AS t1 ON t1.ItmsGrpCod = t0.ItmsGrpCod
INNER JOIN dbo.OITW AS t2 ON t2.ItemCode = t0.ItemCode
INNER JOIN dbo.ITM1 AS t3 ON t3.ItemCode = t0.ItemCode
GROUP BY UPPER(LTRIM(RTRIM(t0.ItemCode))),
UPPER(LTRIM(RTRIM(t1.ItmsGrpNam))),
UPPER(LTRIM(RTRIM(t2.OnHand)))
ORDER BY UPPER(LTRIM(RTRIM(t0.ItemCode))),
UPPER(LTRIM(RTRIM(t1.ItmsGrpNam))),
UPPER(LTRIM(RTRIM(t2.OnHand)))[/code]

E 12°55'05.25"
N 56°04'39.16"

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-07-21 : 11:13:28

try this query and post the results:-

SELECT T0.[ItemCode], T1.[ItmsGrpNam], T2.[OnHand],LEN(T2.[OnHand]), SUM(case when T3.PriceList=3 then T3.Price else 0 end) as 'Retail' 
FROM [dbo].[OITM] T0 INNER JOIN [dbo].[OITB] T1 ON T0.ItmsGrpCod = T1.ItmsGrpCod INNER JOIN [dbo].[OITW] T2 ON T0.ItemCode = T2.ItemCode INNER JOIN [dbo].[ITM1] T3 ON T0.ItemCode = T3.ItemCode 
GROUP BY T0.[ItemCode], T1.[ItmsGrpNam], T2.[OnHand],LEN(T2.[OnHand])
ORDER BY T0.[ItemCode] ASC

DMarmolejos
Yak Posting Veteran

65 Posts

Posted - 2008-07-21 : 11:28:57

quote:

Originally posted by visakh16

try this query and post the results:-

SELECT T0.[ItemCode], T1.[ItmsGrpNam], T2.[OnHand],LEN(T2.[OnHand]), SUM(case when T3.PriceList=3 then T3.Price else 0 end) as 'Retail' 
FROM [dbo].[OITM] T0 INNER JOIN [dbo].[OITB] T1 ON T0.ItmsGrpCod = T1.ItmsGrpCod INNER JOIN [dbo].[OITW] T2 ON T0.ItemCode = T2.ItemCode INNER JOIN [dbo].[ITM1] T3 ON T0.ItemCode = T3.ItemCode 
GROUP BY T0.[ItemCode], T1.[ItmsGrpNam], T2.[OnHand],LEN(T2.[OnHand])
ORDER BY T0.[ItemCode] ASC

ItemCode GroupName OnHand __ Retail

1 abc 0.0 8 (wrong value)
1 abc 22.1 11 0.00
2 abc 23.4 11 7.12
3 abc 0.0 8 (wrong value)
3 abc 22.8 11 4.90

The ItemCodes should not repeat and I am getting a wrong Retail value on the duplicate row

rohitkumar
Constraint Violating Yak Guru

472 Posts

Posted - 2008-07-21 : 11:34:11

try this

SELECT T0.[ItemCode], T1.[ItmsGrpNam], T2.[OnHand], SUM(case when T3.PriceList=3 then T3.Price else 0 end) as 'Retail'
FROM [dbo].[OITM] T0 INNER JOIN [dbo].[OITB] T1 ON T0.ItmsGrpCod = T1.ItmsGrpCod INNER JOIN [dbo].[OITW] T2 ON T0.ItemCode = T2.ItemCode INNER JOIN [dbo].[ITM1] T3 ON T0.ItemCode = T3.ItemCode
WHERE T2.[OnHand] <> 0
GROUP BY T0.[ItemCode], T1.[ItmsGrpNam], T2.[OnHand]
ORDER BY T0.[ItemCode] ASC

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-07-21 : 11:35:12

quote:
Originally posted by DMarmolejos

quote:
Originally posted by visakh16

try this query and post the results:-

SELECT T0.[ItemCode], T1.[ItmsGrpNam], T2.[OnHand],LEN(T2.[OnHand]), SUM(case when T3.PriceList=3 then T3.Price else 0 end) as 'Retail'
FROM [dbo].[OITM] T0 INNER JOIN [dbo].[OITB] T1 ON T0.ItmsGrpCod = T1.ItmsGrpCod INNER JOIN [dbo].[OITW] T2 ON T0.ItemCode = T2.ItemCode INNER JOIN [dbo].[ITM1] T3 ON T0.ItemCode = T3.ItemCode
GROUP BY T0.[ItemCode], T1.[ItmsGrpNam], T2.[OnHand],LEN(T2.[OnHand])
ORDER BY T0.[ItemCode] ASC

ItemCode GroupName OnHand __ Retail

1 abc 0 8 (wrong value)
1 abc 22.1 11 0.00
2 abc 23.4 11 7.12
3 abc 0 8 (wrong value)
3 abc 22.8 11 4.90

The ItemCodes should not repeat and I am getting a wrong Retail value on the duplicate row

so there's definitely some unknown characters appearing on your OnHand column. thats why you're getting different length values.Any special why your OnHand field is of type varchar rather than numeric?

DMarmolejos
Yak Posting Veteran

65 Posts

Posted - 2008-07-21 : 11:40:44

quote:
Originally posted by visakh16

quote:
Originally posted by DMarmolejos

quote:
Originally posted by visakh16

try this query and post the results:-

SELECT T0.[ItemCode], T1.[ItmsGrpNam], T2.[OnHand],LEN(T2.[OnHand]), SUM(case when T3.PriceList=3 then T3.Price else 0 end) as 'Retail'
FROM [dbo].[OITM] T0 INNER JOIN [dbo].[OITB] T1 ON T0.ItmsGrpCod = T1.ItmsGrpCod INNER JOIN [dbo].[OITW] T2 ON T0.ItemCode = T2.ItemCode INNER JOIN [dbo].[ITM1] T3 ON T0.ItemCode = T3.ItemCode
GROUP BY T0.[ItemCode], T1.[ItmsGrpNam], T2.[OnHand],LEN(T2.[OnHand])
ORDER BY T0.[ItemCode] ASC

ItemCode GroupName OnHand __ Retail

1 abc 0 8 (wrong value)
1 abc 22.1 11 0.00
2 abc 23.4 11 7.12
3 abc 0 8 (wrong value)
3 abc 22.8 11 4.90

The ItemCodes should not repeat and I am getting a wrong Retail value on the duplicate row

so there's definitely some unknown characters appearing on your OnHand column. thats why you're getting different length values.Any special why your OnHand field is of type varchar rather than numeric?

no im just lazy to fill it in...they are:

1 abc 0.00 8 (wrong value)
1 abc 22.10 11 0.00
2 abc 23.40 11 7.12
3 abc 0.00 8 (wrong value)
3 abc 22.80 11 4.90

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-07-21 : 11:43:02

quote:
Originally posted by DMarmolejos

quote:
Originally posted by visakh16

quote:
Originally posted by DMarmolejos

quote:
Originally posted by visakh16

try this query and post the results:-

SELECT T0.[ItemCode], T1.[ItmsGrpNam], T2.[OnHand],LEN(T2.[OnHand]), SUM(case when T3.PriceList=3 then T3.Price else 0 end) as 'Retail'
FROM [dbo].[OITM] T0 INNER JOIN [dbo].[OITB] T1 ON T0.ItmsGrpCod = T1.ItmsGrpCod INNER JOIN [dbo].[OITW] T2 ON T0.ItemCode = T2.ItemCode INNER JOIN [dbo].[ITM1] T3 ON T0.ItemCode = T3.ItemCode
GROUP BY T0.[ItemCode], T1.[ItmsGrpNam], T2.[OnHand],LEN(T2.[OnHand])
ORDER BY T0.[ItemCode] ASC

ItemCode GroupName OnHand __ Retail

1 abc 0 8 (wrong value)
1 abc 22.1 11 0.00
2 abc 23.4 11 7.12
3 abc 0 8 (wrong value)
3 abc 22.8 11 4.90

The ItemCodes should not repeat and I am getting a wrong Retail value on the duplicate row

so there's definitely some unknown characters appearing on your OnHand column. thats why you're getting different length values.Any special why your OnHand field is of type varchar rather than numeric?

no im just lazy to fill it in...they are:

1 abc 0.00 8 (wrong value)
1 abc 22.10 11 0.00
2 abc 23.40 11 7.12
3 abc 0.00 8 (wrong value)
3 abc 22.80 11 4.90

i was telling abt the length values. Are they all coming as you expect?

rohitkumar
Constraint Violating Yak Guru

472 Posts

Posted - 2008-07-21 : 12:10:00

can you post the table script?

DMarmolejos
Yak Posting Veteran

65 Posts

Posted - 2008-07-21 : 12:13:00

thank you everyone.. i found a problem in the onhand table

rohitkumar
Constraint Violating Yak Guru

472 Posts

Posted - 2008-07-21 : 12:19:13

mods, some community service for him?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources