Group by problem

Question

Post reply

Group by problem

pbaldy

SSC Eights!

Points: 956
More actions
April 6, 2004 at 2:21 pm

#108030

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