I am migrating an Oracle database to SQL. I can't the following query to
work
insert into JDE911Z1
(vnani,vnaa,vndgm,vndgd,vndgy,vnctry,vnd
ct,vnexa,vnlt,vnedbt)
select approp_no + '.1354', sum(isnull(tot_cost,0)),
datepart(mm, GETDATE()) mm, datepart(dd, GETDATE()) dd,
substring( cast(DATEPART(yy, GETDATE()) as varchar(4)), 3,2) yy ,
'0','JE','DASNY CHARGEBACK','AA','AA'+@.client+cast (@.pay_no as varchar(2))
from perptot
where pay_no = @.pay_no and client = @.client'
and approp_no is not null
and b_cat = 'B'
group by approp_no + '.1354', datepart(mm, getdate()),
datepart(dd, getdate()), substring( cast(DATEPART(yy, GETDATE()) as
varchar(4)), 3,2),
'O', 'JE','DASNY CHARGEBACK','AA','AA','AA'+ @.client + cast (@.pay_no as
varchar(2))
The error I get is "GROUP BY expressions must refer to column names that
appear in the select list"
Group by approp_no + '.1354', datepart(mm, getdate()), datepart(dd,
getdate()) works. As soon as I add substring( cast(DATEPART(yy, GETDATE())
as varchar(4)), 3,2) it breaks.That value just uses getdate() so should be a constant as far as the query i
s concerned and so does not need to appear in the group by clause.sql
No comments:
Post a Comment