SQL Command help with nested case statement logic

Anyone have any input on how to correct my code.  I have a Crystal Report using a SQL command statement.  The SQL Command uses a SQL Command parameter to get the accounting period from the user input.  The SQL command parameter is a character string; example enter C for current, L for last or accounting Period.  If the user enters C the sql statement looks up the current period in a table called GLSYSTEM.  If the user enters L the sql statement subtracts 1 month from the current period.  However if the GLSYSTEM accounting period is 1 (January) then the sql statment needs to default to accounting period 12 (December).


SELECT
gla.COMPANY,
gla.ACCT_UNIT,
gla.ACCOUNT,
'{?Period_1}' as Param_1,
CASE '{?Period_1}'
WHEN 'C' THEN gls.ACCT_PERIOD
      WHEN 'L' THEN
                  CASE gls.ACCT_PERIOD
                        WHEN gls.ACCT_PERIOD = 1 THEN 12
                        WHEN gls.ACCT_PERIOD in (2 to 12) THEN gls.ACCT_PERIOD-1
                        END

else cast('{?Period_1}' as integer)
end as col3,
gla.FISCAL_YEAR,
gls.ACCT_PERIOD,
gls.FISCAL_YEAR
FROM GLAMOUNTS gla
LEFT OUTER JOIN GLSYSTEM gls
ON gla.COMPANY = gls.COMPANY
jrbledsoe001Asked:
Who is Participating?
 
SharathConnect With a Mentor Data EngineerCommented:
try this.
SELECT gla.COMPANY, 
       gla.ACCT_UNIT, 
       gla.ACCOUNT, 
       '{?Period_1}' AS Param_1, 
       CASE '{?Period_1}' 
         WHEN 'C' THEN gls.ACCT_PERIOD 
         WHEN 'L' THEN CASE 
                         WHEN gls.ACCT_PERIOD = 1 THEN 12 
                         WHEN gls.ACCT_PERIOD BETWEEN 2 AND 12 THEN 
                         gls.ACCT_PERIOD - 1 
                       END 
         ELSE CAST('{?Period_1}' AS INTEGER) 
       END           AS col3, 
       gla.FISCAL_YEAR, 
       gls.ACCT_PERIOD, 
       gls.FISCAL_YEAR 
  FROM GLAMOUNTS gla 
       LEFT OUTER JOIN GLSYSTEM gls 
         ON gla.COMPANY = gls.COMPANY 

Open in new window

0
 
jrbledsoe001Author Commented:
this seems to be working

SELECT
gla.COMPANY,
gla.ACCT_UNIT,
gla.ACCOUNT,
{?Period} as Param_1,
'{?Period_1}' as Param_2,
CASE '{?Period_1}'
WHEN 'C' THEN gls.ACCT_PERIOD
      WHEN 'L' THEN
      CASE gls.ACCT_PERIOD
      WHEN 1 THEN 12
                      ELSE gls.ACCT_PERIOD-1
      END
else cast('{?Period_1}' as integer)
END as col3,
gla.FISCAL_YEAR,
gls.ACCT_PERIOD,
gls.FISCAL_YEAR
FROM GLAMOUNTS gla
LEFT OUTER JOIN GLSYSTEM gls
ON gla.COMPANY = gls.COMPANY
0
 
jrbledsoe001Author Commented:
Anyone have any other ideas that might be helpful to fellow learners?
0
 
SharathData EngineerCommented:
The CASE condition on gls.ACCT_PERIOD BETWEEN 2 AND 12 is not required.
SELECT gla.COMPANY, 
       gla.ACCT_UNIT, 
       gla.ACCOUNT, 
       '{?Period_1}' AS Param_1, 
       CASE '{?Period_1}' 
         WHEN 'C' THEN gls.ACCT_PERIOD 
         WHEN 'L' THEN CASE 
                         WHEN gls.ACCT_PERIOD = 1 THEN 12 
                         ELSE gls.ACCT_PERIOD - 1 
                       END 
         ELSE CAST('{?Period_1}' AS INTEGER) 
       END           AS col3, 
       gla.FISCAL_YEAR, 
       gls.ACCT_PERIOD, 
       gls.FISCAL_YEAR 
  FROM GLAMOUNTS gla 
       LEFT OUTER JOIN GLSYSTEM gls 
         ON gla.COMPANY = gls.COMPANY

Open in new window

0
 
mlmccCommented:
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.