Query returns wrong values... - 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)

Query returns wrong values...

Author

Topic

Dorffius
Starting Member

36 Posts

Posted - 2002-01-31 : 08:51:08

I have a query that grabs everything based on the current day but when I try to get only records with a certain Prod_Code I get all records from different days. Here is the statement. It can only return records for the current day.

SELECT * FROM traypack_scale.dbo.SPROD INNER JOIN Rawdata ON PCODE = Prod_Code WHERE Prod_Code = '52196' OR Prod_Code = '52194' OR Prod_Code = '52195' OR Prod_Code = '52108' OR Prod_Code = '52143' OR Prod_Code = '52106' OR Prod_Code = '52100' AND ProductionDate= convert(varchar(8),getdate(),1) ORDER BY ProductionTime DESC

Thanks for the help.

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-01-31 : 08:56:22

The multiple OR statements need to be grouped together in parentheses:

SELECT * FROM traypack_scale.dbo.SPROD INNER JOIN Rawdata ON PCODE = Prod_Code
WHERE (Prod_Code = '52196' OR Prod_Code = '52194' OR Prod_Code = '52195' OR Prod_Code = '52108' OR Prod_Code = '52143' OR Prod_Code = '52106' OR Prod_Code = '52100')
AND ProductionDate= convert(varchar(8),getdate(),1)
ORDER BY ProductionTime DESC

Actually, when you have multiple values for the same column, using IN is a lot smaller:

SELECT * FROM traypack_scale.dbo.SPROD INNER JOIN Rawdata ON PCODE = Prod_Code
WHERE Prod_Code IN('52196', '52194', '52195', '52108', '52143', '52106', '52100')
AND ProductionDate= convert(varchar(8),getdate(),1)
ORDER BY ProductionTime DESC

Dorffius
Starting Member

36 Posts

Posted - 2002-01-31 : 09:18:32

Thank you very much, that works perfectly.

Dorffius
Starting Member

36 Posts

Posted - 2002-01-31 : 09:38:45

One more thing, how would I set up the query to group by Prod_Code? I understand the GROUP BY function but I get constant errors of the requested properties not being supported.

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-01-31 : 09:42:41

You can't use SELECT * with GROUP BY. You have to explicitly list each column you want in the GROUP BY clause:

SELECT Prod_Code FROM traypack_scale.dbo.SPROD INNER JOIN Rawdata ON PCODE = Prod_Code
WHERE Prod_Code IN('52196', '52194', '52195', '52108', '52143', '52106', '52100')
AND ProductionDate= convert(varchar(8),getdate(),1)
GROUP BY Prod_Code

I don't know what other columns you want, but you'll need to put them in the SELECT list. If you are aggregating (Min, Max, Avg, Sum, Count) a column, then you don't need to include it in the GROUP BY clause, but all non-aggregated columns must be in GROUP BY.

Dorffius
Starting Member

36 Posts

Posted - 2002-01-31 : 10:07:03

Okay, all the stuff I have tried has worked but now I need to know something else. Here is the new statement:

SELECT Prod_Code, SUM(Net), SUM(Quantity), ShiftCode, MachineID FROM traypack_scale.dbo.SPROD INNER JOIN Rawdata ON PCODE = Prod_Code WHERE ProductionDate = CONVERT(varchar(8), GETDATE(), 1) AND Len(Prod_Code) <= 4 GROUP BY Prod_Code, ShiftCode, MachineID ORDER BY Prod_Code

Basically what is happening is that now the join no longer works to match up the Prod_Code so the description doesn't show up. The sums are being tallied according to the results but the column has no name and therefore I cannot add it into my repeated table.

Thanks and sorry for all the hassles.

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-01-31 : 10:17:58

Just add the Description column to the SELECT and GROUP BY clauses:

SELECT Prod_Code, Description, SUM(Net), SUM(Quantity), ShiftCode, MachineID FROM traypack_scale.dbo.SPROD INNER JOIN Rawdata ON PCODE = Prod_Code WHERE ProductionDate = CONVERT(varchar(8), GETDATE(), 1) AND Len(Prod_Code) <= 4
GROUP BY Prod_Code, Description, ShiftCode, MachineID ORDER BY Prod_Code

Dorffius
Starting Member

36 Posts

Posted - 2002-01-31 : 10:18:02

Got the descriptions working, just need the sum columns to have a heading, so I can input them in the table. They are still just blank heading bars.

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-01-31 : 10:20:25

Add a column alias to each one:

SELECT Prod_Code, Description, SUM(Net) AS Net, SUM(Quantity) AS Quantity, ShiftCode, MachineID FROM traypack_scale.dbo.SPROD INNER JOIN Rawdata ON PCODE = Prod_Code WHERE ProductionDate = CONVERT(varchar(8), GETDATE(), 1) AND Len(Prod_Code) <= 4
GROUP BY Prod_Code, Description, ShiftCode, MachineID ORDER BY Prod_Code

Dorffius
Starting Member

36 Posts

Posted - 2002-01-31 : 10:46:38

Thank you very much for all your help. The program runs perfectly now. Thanks again.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources