April 6, 2004 at 2:21 pm
I'm trying to convert another developer's Access query to a stored procedure or function. In either case, I get an error about the Group By expressions needing to refer to column names that appear in the select list. Is the problem with the aliased fields? This works fine in Access, though I've changed Nz() to IsNull() and form references to variables.
CREATE PROCEDURE [procPayroll_bell_trans_limo_bus_base]
@CoCode varchar,
@FmDate datetime,
@ToDate datetime
AS
SELECT Daily_charter.Driver_no, Daily_charter.Division_code,
Rates.Other_DOR_Code, Misc_entries.Value_num AS lb_percent,
Daily_charter.Company, Daily_charter.Dor_date, 'COM1' AS comit, Misc_entries.Type,
Sum(IsNull([Amount],0))-Sum(IsNull([Fuel_surcharge_amount],0)) AS lb_amount
FROM (Daily_charter INNER JOIN Rates ON (Daily_charter.Rate = Rates.Rate) AND (Daily_charter.Division_code = Rates.Division_code)
AND (Daily_charter.Company = Rates.Company_code)) INNER JOIN Misc_entries ON Rates.Company_code = Misc_entries.Company_code
GROUP BY Daily_charter.Driver_no, Daily_charter.Division_code, Rates.Other_DOR_Code, Misc_entries.Value_num,
Daily_charter.Company, Daily_charter.Dor_date, 'COM1', Misc_entries.Type
HAVING Daily_charter.Division_code=1 AND Rates.Other_DOR_Code=41 AND
Sum(IsNull([Amount],0))-Sum(IsNull([Fuel_surcharge_amount],0))<>0 AND
Daily_charter.Company=@CoCode AND
Daily_charter.Dor_date Between @FmDate And @ToDate AND
Misc_entries.Type=60
GO
TIA
Paul
April 6, 2004 at 7:15 pm
'COM1' is a constant and so does not need to appear in the group by.
Cursors never.
DTS - only when needed and never to control.
April 7, 2004 at 2:20 am
Yeah the hard and fast rule for Group By is that the condition should include only database fields however you can use key words like ALL.
Thanks
Prasad Bhogadi
www.inforaise.com
April 7, 2004 at 11:50 am
Thanks, that nailed it.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply