November 14, 2005 at 6:18 am
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
November 14, 2005 at 7:51 am
To use max(i.jeno) you must have a GROUP BY clause and you need to aggregate m.linecost or specify it in the GROUP BY
Far away is close at hand in the images of elsewhere.
Anon.
November 14, 2005 at 7:54 am
Just had a thought, if max(i.jeno) + 1 is to be the same for all records then put this in a variable first and use the variable in the select
Far away is close at hand in the images of elsewhere.
Anon.
November 14, 2005 at 7:58 am
In addition to what David has said above, please make sure that you have some join condition as well on the two tables -- right now, it is doing a cross join:
from matusetrans m, issuesreturns i
I am assuming that you do not want the cross join. This has nothing to do with the error that you are getting (the reasons for the error have been explained by David above) but this would lead to performance issues and un-desired results if the cross join was not intended.
November 14, 2005 at 8:22 am
Thanks for all the help. I figued it out. The final query follows:
--build the query
select 'L' as RECTYPE, identity(int, 1, 1)as JELINENO,
--Get the current JENO from the issuesreturns table
(select max(JENO) + 1 from issuesreturns) as JENO,
'AJE' as JECODE,
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,
--The ACCOUNT is the first six characters of the gldebitacct or glcreditacct field of the matusetrans table
--Limit the results to only those ACCOUNTS that begin with 700048 or 011195
ACCOUNT = case
when left(m.gldebitacct, 6) = '700048' then '40700-497-00'
when left(m.gldebitacct, 6) = '011195' then '60700-470-05'
when left(m.glcreditacct, 6) = '700048' then '40700-497-00'
when left(m.glcreditacct, 6) = '011195' then '60700-470-05'
else 'error'
end,
--The org number is 195.011.0001.002 plus the 16th, 17th and 18th characters of the gldebitacct field
ORG = case
when '195.011.0001.002.' + substring (m.gldebitacct, 16, 3) = '195.011.0001.002.402'
then '195.011.0001.002.401'
when '195.011.0001.002.' + substring (m.gldebitacct, 16, 3) = '195.011.0001.002.441'
then '195.011.0001.002.440'
when '195.011.0001.002.' + substring (m.gldebitacct, 16, 3) = '195.011.0001.002.442'
then '195.011.0001.002.440'
when '195.011.0001.002.' + substring (m.gldebitacct, 16, 3) = '195.011.0001.002.451'
then '195.011.0001.002.450'
else '195.011.0001.002.' + substring (m.gldebitacct, 16, 3)
end,
'MAXIMO - MATUSETRANSID = ' + convert(varchar(4), m.matusetransid)as TRANSDESC,
--The project is a 30 character field constructed from the gldebitacct field and two fields from the workorder table
substring (m.gldebitacct, 1, 6)+ '.' + w.wo2 + '.' + w.wolo5 + '.' + substring (m.gldebitacct, 8, 7) + '.' +
w.wonum as PROJ,
--more default column values
' ' as REF1, ' ' as REF2, ' ' as RECYCLE, ' ' as ORGABBR,
' ' as PROJABBR, ' ' as PAGABBR, 'Y' as BILLABLEFLAG,
'From LOSC issues and returns to CostPoint to charge WOs' as NOTES
--place results in a temporary table
into #temp
--tables used in the query
from matusetrans m, workorder w, issuesreturns i
--conditions
where (m.it1 <> 'Y' or m.it1 is null)
and m.issuetype in ('ISSUE', 'RETURN')
and m.storeloc in ('B631OT', 'B642GB', 'B641GB', 'B681AS', 'B681BS', 'B681CA', 'B681SP')
and (w.wonum = m.refwo)
and substring (m.gldebitacct, 1, 6)+ '.' + w.wo2 + '.' + w.wolo5 + '.' +
substring (m.gldebitacct, 8, 7) + '.' + w.wonum IS NOT NULL
--perform the transaction
go
--update the it1 field of the matusetrans table to prevent further pulls of this information
UPDATE matusetrans
SET IT1 = 'Y'
from matusetrans, workorder
WHERE matusetrans.refwo = workorder.wonum
and (substring(matusetrans.gldebitacct, 1, 6) = '700048'
OR substring(matusetrans.gldebitacct, 1, 6) = '011195'
OR substring(matusetrans.glcreditacct, 1, 6) = '700048'
OR substring(matusetrans.glcreditacct, 1, 6) = '011195')
and matusetrans.issuetype in ('ISSUE', 'RETURN')
and matusetrans.storeloc in ('B631OT', 'B642GB', 'B641GB', 'B681AS', 'B681BS', 'B681CA', 'B681SP')
and substring (matusetrans.gldebitacct, 1, 6)+ '.' + workorder.wo2 + '.' + workorder.wolo5 + '.' +
substring (matusetrans.gldebitacct, 8, 7) + '.' + workorder.wonum IS NOT NULL
--build the summary row
set identity_insert #temp on
insert into #temp(rectype,
jelineno,
jeno,
jecode,
fy,
pd,
subpd,
revflag,
headdesc,
amount,
account,
org,
transdesc,
proj,
ref1,
ref2,
recycle,
orgabbr,
projabbr,
pagabbr,
billableflag,
notes)
select max(rectype),
max(jelineno)+ 1,
max(jeno),
max(jecode),
max(fy),
max(pd),
max(subpd),
max(revflag),
max(headdesc),
sum(amount) * -1,
'20100-006-00',
'195.011',
'VENDOR OWNED STOCK USED',
'',
max(ref1),
max(ref2),
max(recycle),
max(orgabbr),
max(projabbr),
max(pagabbr),
max(billableflag),
max(notes)
from #temp
--display the results
select * from #temp
order by JELINENO
insert into issuesreturns
select * from #temp
--drop the temporary table
drop table #temp
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply