CASE PROBLEM
-
I am running the following code:
drop table #temp
select 'L' as RECTYPE, identity(int, 1, 1)as JELINENO, '800002' as JENO, 'AJE' as JECODE,
'2006' as FY, '7' as PD, '1' as SUBPD, 'Journal Entry from MAXIMO' as HEADDESC,
m.linecost as AMOUNT, m.issuetype,
ACCOUNT =
case left(m.gldebitacct, 6)
when '700048' then '40700-497-00'
when '011195' then '60700-470-05'
else 'error'
end,
substring (m.gldebitacct, 16, 3) as ORG,
'MAXIMO - MATUSETRANSID =' + convert(varchar(4), m.matusetransid)as TRANSDESC,
substring (m.gldebitacct, 1, 6)+ '.' + w.wo2 + '.' + w.wolo5 + '.' + substring (m.gldebitacct, 8, 14) + '.' +
w.wonum as PROJ,
'Y' as BILLABLEFLAG,
'From LOSC issues and returns to CostPoint to charge WOs' as NOTES
into #temp
from matusetrans m, workorder w
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)
go
select * from #temp
I need to alter the case statement to allow for another parameter such as:
ACCOUNT =
case left(m.gldebitacct, 6)
when '700048' then '40700-497-00'
when '011195' then '60700-470-05'
else 'error'
end,
case left(m.glcreditacct, 6)
when '700048' then '40700-497-00'
when '011195' then '60700-470-05'
else 'error'
end,
When I try to run this code I get an error. Is there a way to do this?
Thanks,
Dave
-
Yes, there are two forms of the case statement. There is the tradional case statement (which you are using), where you have an expression - left(m.xyz, 6) - and a set of values to which it is compared. The other form of the case statement is more like
if [expr1]
else if [expr2]
else if [...]
etc
You want to write
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,
And you should get what you need..
This might be *slightly* less efficient as SQL might evaluate the left expression 4 times rather then 2, but I doubt this would even be noticed on thousands of executions and isn't worth worrying about... (prove me wrong if I am wrong though :cool -
That did the trick
many thanks
-
No probs - glad to help
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply