To begin with, I have a query that works in SSMS but not in DNN. The issue is that my query uses an implicit conversion from char to int and back to char which the DNN module can't handle. I am needing to reformulate it a bit. Needing to add some code to the c3 and c4 segments that cast/convert the periodyear field to int and then back to char. Further below is the code that I have constructed in hopes of doing this but when I place it in C3, I get an error about " [Incorrect syntax near 'CAST', expected 'AS'] ". I am probably incorrect but assuming that the last piece of code would go in C3 after the second case when statement and need where in front of it.
So, my first question is where (if at all) in the C3 section do I put this cast function (or some other convert statement). Am I wrong in thinking that it would need to go in both c3 and c4?
WITH c1 AS
(SELECT e.stfips, e.areatype, e.area, e. periodyear, e.period, e.seriescode,
e.empces
FROM ces as e
WHERE e.periodtype='03'
And e.supprecord='0'
and e.periodyear=
(Select Max(periodyear)
From ces)
and e.period=
(Select Top(1)period
From ces
Order by periodyear desc, period desc)
and e.stfips='32'
and e.adjusted='1'
and e.areatype='01'
),
C2 AS
(Select Distinct c1.periodyear, c1.period
From c1
),
C3 As
(Select
(Case When c2.period='01' Then (c2.period + 11) Else (c2.period-1) END) As
'month',
(Case When c2.period='01' Then (c2.periodyear -1) Else (c2.periodyear) END) As
'year'
From C2
),
C4 AS
(Select c.stfips, c.areatype, c.area, c.periodyear, c.period, c.seriescode, c.empces
From ces as c, c2
Where c.period = c2.period
And c.periodyear = (c2.periodyear-1)
And c.supprecord='0'
and c.stfips='32'
and c.adjusted='1'
and c.areatype='01'
),
And c.periodyear = ( cast ( cast(c2.periodyear as int) -1 ) as char(10) )