Help building a #temp table

I have the following query:

--build query for #temp table
select 'L' as RECTYPE, identity(int, 1, 1) as JELINENO, max(i.jeno) + 1 as JENO, 'AJE' as JECODE,
--build the fiscal year, when month = October, November or December add 1 to actual year
FY = case
when datepart(month, GetDate()) = '10' then datepart(year, GetDate()) + 1
when datepart(month, GetDate()) = '11' then datepart(year, GetDate()) + 1
when datepart(month, GetDate()) = '12' then datepart(year, GetDate()) + 1
else datepart(year, GetDate())
end,
-- The PD starts in April and increases by 1 each month
PD = case
when datepart(month, GetDate()) = '1' then '10'
when datepart(month, GetDate()) = '2' then '11'
when datepart(month, GetDate()) = '3' then '12'
when datepart(month, GetDate()) = '4' then '1'
when datepart(month, GetDate()) = '5' then '2'
when datepart(month, GetDate()) = '6' then '3'
when datepart(month, GetDate()) = '7' then '4'
when datepart(month, GetDate()) = '8' then '5'
when datepart(month, GetDate()) = '9' then '6'
when datepart(month, GetDate()) = '10' then '7'
when datepart(month, GetDate()) = '11' then '8'
when datepart(month, GetDate()) = '12' then '9'
else 'error'
end,
'1' as SUBPD, 'N' as REVFLAG, 'Journal Entry from MAXIMO' as HEADDESC,
--If the issuetype is a return the AMOUNT is a negative value, multiply by -1
AMOUNT = case
when m.linecost < 0 then m.linecost * -1
else m.linecost
end
into #temp
from matusetrans m, issuesreturns i
go
select * from #temp
drop table #temp

When I run this query, I get the following error:

Server: Msg 8118, Level 16, State 1, Line 2
Column 'm.linecost' is invalid in the select list because it is not contained in an aggregate function and there is no GROUP BY clause.
Server: Msg 8118, Level 16, State 1, Line 2
Column 'm.linecost' is invalid in the select list because it is not contained in an aggregate function and there is no GROUP BY clause.
Server: Msg 8118, Level 16, State 1, Line 2
Column 'm.linecost' is invalid in the select list because it is not contained in an aggregate function and there is no GROUP BY clause.
Server: Msg 208, Level 16, State 1, Line 1
Invalid object name '#temp'.

I can find no reason for this error. If I run the query without the AMOUNT = case statement, it works fine. Can anyone help?

Thanks,

Dave

The mistake only arises in the AMOUNT=CASE statement since it's the only one (except from the MAX(i.jeno) column) that's accesing a real column in the database. The rest of the columns are calculated.

So, as you already have an aggregate function (the MAX) the rest of the columns from real tables must either have an aggregate function or the SELECT clause must contain a GROUP BY clause.

You can't form a SELECT with only one MAX column and the rest of them normal columns without telling SQL how it should group the results from where to choose the MAX value from.

If you really want to achieve that you have to calculate the MAX column either before in a variable or in the same SELECT with a sub-SELECT query.

IE: SELECT var1, var2, var3, (SELECT MAX(othervar) FROM table)

Hope i was clear.
Good luck,
Alan.

|||

First of all I would compress the code and rewrite it as following.

Please post DDLs as it's not clear what do you want to do. the problem is clear as you have a reference to the column m.linecost that is not in a group by list. Just to get rid of the error you can add a group by abs(m.linecost), but it doesn't look like what you want to do.

select
'L' as RECTYPE,
identity(int, 1, 1) as JELINENO,
max(i.jeno) + 1 as JENO,
'AJE' as JECODE,

--build the fiscal year, when month = October, November or December add 1 to actual year

FY = datepart(year, GetDate()) + (datepart(month, GetDate()) + 2) / 12,

-- The PD starts in April and increases by 1 each month

PD = (datepart(month, GetDate()) + 8) % 12 + 1,

'1' as SUBPD,

'N' as REVFLAG,

'Journal Entry from MAXIMO' as HEADDESC,

--If the issuetype is a return the AMOUNT is a negative value, multiply by -1

AMOUNT = abs(m.linecost)

into #temp

from matusetrans m, issuesreturns i

|||Dave -

You can't use an Aggregate Function on only one column without using a Group By for the other columns

So you either need to group by m.linecost OR put an Agrregate around it - like max() or min().

To simpify your query

select
max(i.jeno) + 1 as JENO, -- YOU USED MAX HERE
AMOUNT = case
when m.linecost < 0 then m.linecost * -1
-- SO YOU EITHER NEED TO USE AN AGRREGRATE HERE OR
else m.linecost
end
into #temp
from matusetrans m, issuesreturns i
-- OR GROUP BY m.linecost HERE

Hope that helps

AWAL