asked on

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

ASKER CERTIFIED SOLUTION

Sharath S

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

jrbledsoe001

ASKER

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

jrbledsoe001

ASKER

Anyone have any other ideas that might be helpful to fellow learners?

Sharath S

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

Mike McCracken

This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.