1

The issue here is that while this works in SSMS, it fails when I post it onto a website that uses an SQL processing tool. I have narrowed it down to the first bolded segment in the C3 section. Use the code before that and it gives an error (like it should). Add that line and the site crashes. I have used other queries as an experiment and no problem. While I can't guarantee that it will work, it is my guess that it does not like that my periodyear field is char(4) and it can't do the implicit conversion from char to int and then back to char. So my question is where would I put the second bolded line in the C3 section (or before)?

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 = ( cast ( cast(c2.periodyear as int) -1 ) as nvarchar(10) )**
And c.supprecord='0'
and c.stfips='32'
and c.adjusted='1'
and c.areatype='01'
),
Calflamesfann
  • 133
  • 4
  • 16
  • 1
    Run the query using SQL directly - what happens? Don’t waste time “debugging” in the dark. – user2864740 Feb 13 '20 at 18:02
  • 1
    `CAST` is a function, not a statement. – Thom A Feb 13 '20 at 18:03
  • @user2864740, it works fine in SSMS just not in the DotNetNuke Module – Calflamesfann Feb 13 '20 at 18:05
  • 2
    Why do you need those extra parenthesis around `cast`? – Radagast Feb 13 '20 at 18:08
  • 1
    “it works fine in SSMS” - If it is indeed the same query against the same SQL database / data, and assuming the input is the same, the problem is in how the result set is being used. It would be useful to enable/view logs and exceptions in the usage application. – user2864740 Feb 13 '20 at 18:10
  • 1
    If the problem is in "DotNetNuke Module" then perhaps that is what you should be tagging and asking about; not SSMS when it's working fine in the IDE. – Thom A Feb 13 '20 at 18:15
  • 1
    That is: work to isolate and rule out each component. – user2864740 Feb 13 '20 at 18:16
  • @user2864740, "And c.periodyear = (c2.periodyear-1)" This used to work using the code above in place of the second bolded line. Now it does not. What I am inquiring about is another way to accomplish the same goal as before. This time using the cast function to convert my periodyear to integer temporarily – Calflamesfann Feb 13 '20 at 18:27

1 Answers1

1

You can use the year function rather than cast or implicit conversion

year(c.periodyear) = year(c2.periodyear)-1

EDIT: (adding C3 section)

Select
    Case When c2.period='01' Then (c2.period + 11) Else (c2.period-1) END) As 'month',
    Case When c2.period='01' Then year(c2.periodyear) -1 Else year(c2.periodyear) END As 'year'
From C2
Morpheus
  • 1,616
  • 1
  • 21
  • 31
  • where in the query would I put this item? – Calflamesfann Feb 13 '20 at 18:32
  • 1
    I'm suggesting it as a replacement for the line: `And c.periodyear = ( cast ( cast(c2.periodyear as int) -1 ) as nvarchar(10) )` – Morpheus Feb 13 '20 at 19:08
  • I replaced the second bolded line with "And... your code". It runs in SSMS but no results. It fails on the SSMS module. – Calflamesfann Feb 13 '20 at 19:17
  • Is the code that you posted the same code for which you stated "this works in SSMS"? Or does the code that worked in SSMS (and not in DNN) also need additional modification? I didn't understand that from the question. – Morpheus Feb 13 '20 at 19:54
  • The code that starts the question works in SSMS but not DNN. However, I have discerned that the issue is not the C4 section but rather the C3 section. Trying to put the cast function (or whatever is needed) in Section C3. – Calflamesfann Feb 13 '20 at 20:04
  • I've edited the answer to include Section C3. Are both `CASE` statements supposed to examine `c2.period='01'`? – Morpheus Feb 13 '20 at 20:24
  • Yes, both case statements are meant to examine when period = '01' or in plain English, when the month in question is January . – Calflamesfann Feb 13 '20 at 20:29
  • Let us [continue this discussion in chat](https://chat.stackoverflow.com/rooms/207800/discussion-between-morpheus-and-calflamesfann). – Morpheus Feb 14 '20 at 00:24