Output counting infinite records

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)

Output counting infinite records

Author

Topic

sjb19020
Starting Member

6 Posts

Posted - 2008-09-19 : 13:11:24

Hi, hope you can help me with this. The view I am using is going through 6 tables. The Reader table has multiple ID's connected to each location, and apparently it is listing the output in an infinite loop. I don't know why. The output should not be more than between 21-22,000 records.

To help with this, here is the code.

SELECT DISTINCT dbo.CardHolderTable.LastName, dbo.CardHolderTable.FirstName, dbo.CardHolderTable.CardID, MAX(CASE WHEN CardHolderPersonalDataXrTable.PersonalDataID = 0 THEN CardHolderPersonalDataXrTable.PersonalDataItem END) AS Department,
MAX(CASE WHEN CardHolderPersonalDataXrTable.PersonalDataID = 1 THEN CardHolderPersonalDataXrTable.PersonalDataItem END) AS Supervisor,
MAX(CASE WHEN BadgeFormatTable.BadgeFormatID = 1 THEN BadgeFormatTable.BadgeName WHEN BadgeFormatTable.BadgeFormatID = 2 THEN BadgeFormatTable.BadgeName
WHEN BadgeFormatTable.BadgeFormatID = 3 THEN BadgeFormatTable.BadgeName WHEN BadgeFormatTable.BadgeFormatID = 4 THEN BadgeFormatTable.BadgeName
WHEN BadgeFormatTable.BadgeFormatID = 5 THEN BadgeFormatTable.BadgeName WHEN BadgeFormatTable.BadgeFormatID = 6 THEN BadgeFormatTable.BadgeName
WHEN BadgeFormatTable.BadgeFormatID = 7 THEN BadgeFormatTable.BadgeName WHEN BadgeFormatTable.BadgeFormatID = 8 THEN BadgeFormatTable.BadgeName
WHEN BadgeFormatTable.BadgeFormatID = 15 THEN BadgeFormatTable.BadgeName WHEN BadgeFormatTable.BadgeFormatID = 16 THEN BadgeFormatTable.BadgeName
WHEN BadgeFormatTable.BadgeFormatID = 17 THEN BadgeFormatTable.BadgeName WHEN BadgeFormatTable.BadgeFormatID = 18 THEN BadgeFormatTable.BadgeName
WHEN BadgeFormatTable.BadgeFormatID = 19 THEN BadgeFormatTable.BadgeName WHEN BadgeFormatTable.BadgeFormatID = 21 THEN BadgeFormatTable.BadgeName
WHEN BadgeFormatTable.BadgeFormatID = 22 THEN BadgeFormatTable.BadgeName END) AS BadgeName, dbo.ReaderTable.ReaderID,
dbo.ReaderTable.ReaderName

FROM dbo.CardHolderTable INNER JOIN
dbo.CardImageTable ON dbo.CardHolderTable.CardID = dbo.CardImageTable.CardID INNER JOIN
dbo.CardHolderPersonalDataXrTable ON dbo.CardHolderTable.CardID = dbo.CardHolderPersonalDataXrTable.CardID INNER JOIN
dbo.BadgeFormatTable ON dbo.CardImageTable.BadgeFormatID = dbo.BadgeFormatTable.BadgeFormatID INNER JOIN
dbo.AccessGroupReaderXrTable ON dbo.BadgeFormatTable.RegionID = dbo.AccessGroupReaderXrTable.RegionID INNER JOIN
dbo.ReaderTable ON dbo.AccessGroupReaderXrTable.ReaderID = dbo.ReaderTable.ReaderID

GROUP BY dbo.CardHolderTable.LastName, dbo.CardHolderTable.FirstName, dbo.CardHolderTable.CardID, dbo.ReaderTable.ReaderID,
dbo.ReaderTable.ReaderName

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-19 : 13:16:58

What do you mean infinite loop?the query does not have any obvious problems.

SELECT DISTINCT dbo.CardHolderTable.LastName, dbo.CardHolderTable.FirstName, 
dbo.CardHolderTable.CardID, 
MAX(CASE WHEN CardHolderPersonalDataXrTable.PersonalDataID = 0 THEN CardHolderPersonalDataXrTable.PersonalDataItem END) AS Department, 
MAX(CASE WHEN CardHolderPersonalDataXrTable.PersonalDataID = 1 THEN CardHolderPersonalDataXrTable.PersonalDataItem END) AS Supervisor, 
MAX(CASE WHEN BadgeFormatTable.BadgeFormatID IN (1, 2 , 3 , 4 ,5 ,6,7,8,15,16, 17,18 ,19,21 , 22) THEN BadgeFormatTable.BadgeName END) AS BadgeName, dbo.ReaderTable.ReaderID, 
dbo.ReaderTable.ReaderName

FROM dbo.CardHolderTable INNER JOIN
dbo.CardImageTable ON dbo.CardHolderTable.CardID = dbo.CardImageTable.CardID INNER JOIN
dbo.CardHolderPersonalDataXrTable ON dbo.CardHolderTable.CardID = dbo.CardHolderPersonalDataXrTable.CardID INNER JOIN
dbo.BadgeFormatTable ON dbo.CardImageTable.BadgeFormatID = dbo.BadgeFormatTable.BadgeFormatID INNER JOIN
dbo.AccessGroupReaderXrTable ON dbo.BadgeFormatTable.RegionID = dbo.AccessGroupReaderXrTable.RegionID INNER JOIN
dbo.ReaderTable ON dbo.AccessGroupReaderXrTable.ReaderID = dbo.ReaderTable.ReaderID

GROUP BY dbo.CardHolderTable.LastName, dbo.CardHolderTable.FirstName, dbo.CardHolderTable.CardID, dbo.ReaderTable.ReaderID, 
dbo.ReaderTable.ReaderName

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources