[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Convert a SQL case statement to an Oracle case statement

Posted on 2011-05-08
10
Medium Priority
?
438 Views
Last Modified: 2012-06-22
Hello,

I have Crystal SQL command report which uses a case statement.  The sql command works great on a MS SQL server database.  I need to run the same report on an Oracle 10g database.  

0
Comment
Question by:jrbledsoe001
  • 4
  • 3
  • 3
10 Comments
 

Author Comment

by:jrbledsoe001
ID: 35717314
The Crystal report uses two SQL Command parameters:  RptPeriod and RptYear.  The RptPeriod parameter allows the user to select current, previous, or fiscal report period 1 through 12.  The RptYear parameter allows the user to select current, last, or enter a year in YYYY format.

Here is the code:

SELECT
COMPANY,
ACCT_PERIOD =
      CASE '{?RptPeriod}'
      WHEN 'Current Period' THEN ACCT_PERIOD
      WHEN 'Previous Period' THEN
            CASE ACCT_PERIOD
            WHEN 1 THEN 12
            ELSE (ACCT_PERIOD - 1)
            END
      ELSE CAST('{?RptPeriod}' AS INTEGER)
      END,
FISCAL_YEAR =
      CASE '{?RptYear}'
      WHEN 'Current Period Year' THEN FISCAL_YEAR
      WHEN 'Previous Period Year' THEN
            CASE ACCT_PERIOD
            WHEN 1 THEN FISCAL_YEAR - 1
            ELSE FISCAL_YEAR
            END
      ELSE CAST('{?RptYear}' AS INTEGER)
        END,
FISCAL_YEAR_BASE =
      CASE '{?RptYear}'
      WHEN 'Current Period Year' THEN FISCAL_YEAR
      WHEN 'Previous Period Year' THEN
            CASE ACCT_PERIOD
            WHEN 1 THEN FISCAL_YEAR - 1
            ELSE FISCAL_YEAR
            END
      ELSE CAST('{?RptYear}' AS INTEGER)
        END
FROM GLSYSTEM
UNION ALL
SELECT
COMPANY,
ACCT_PERIOD =
      CASE '{?RptPeriod}'
      WHEN 'Current Period' THEN ACCT_PERIOD
      WHEN 'Previous Period' THEN
            CASE ACCT_PERIOD
            WHEN 1 THEN 12
            ELSE (ACCT_PERIOD - 1)
            END
      ELSE CAST('{?RptPeriod}' AS INTEGER)
      END,
FISCAL_YEAR =
      CASE '{?RptYear}'
      WHEN 'Current Period Year' THEN FISCAL_YEAR - 1
      WHEN 'Previous Period Year' THEN
            CASE ACCT_PERIOD
            WHEN 1 THEN FISCAL_YEAR - 2
            ELSE FISCAL_YEAR - 1
            END
      ELSE CAST('{?RptYear}' AS INTEGER) - 1
        END,
FISCAL_YEAR_BASE =
      CASE '{?RptYear}'
      WHEN 'Current Period Year' THEN FISCAL_YEAR
      WHEN 'Previous Period Year' THEN
            CASE ACCT_PERIOD
            WHEN 1 THEN FISCAL_YEAR - 1
            ELSE FISCAL_YEAR
            END
      ELSE CAST('{?RptYear}' AS INTEGER)
        END
FROM GLSYSTEM;
0
 
LVL 57

Expert Comment

by:Raja Jegan R
ID: 35717316
In Oracle, CASE statement syntax is same as that of SQL Server..
Hence it would be more helpful if you could post your existing code so that we can change it in case if it requires any other modifications.
0
 

Author Comment

by:jrbledsoe001
ID: 35717320
Oracle does not like the syntax where the field name is followed by the "=" sign; example
ACCT_PERIOD = .
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 23

Expert Comment

by:OP_Zaharin
ID: 35717329
- as I look into it, Oracle CASE expression is almost the same as in MSSQL. you can start with the oracle documentation on Case expression:
http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/expressions004.htm
0
 
LVL 57

Expert Comment

by:Raja Jegan R
ID: 35717333
Since you have RptPeriod and RptYear variables having datetime values, you need to separate month and year values from those variables using to_char function like shown below:

RptPeriod - to_char(RptPeriod, 'mm');
RptYear - to_char(RptYear, 'YYYY');
0
 
LVL 57

Expert Comment

by:Raja Jegan R
ID: 35717340
And kindly let me know the datatype of ACCT_PERIOD along with some sample values..
I think this is of datatype datetime and you need to use to_char as mentioned in my earlier comment
0
 
LVL 23

Accepted Solution

by:
OP_Zaharin earned 2000 total points
ID: 35717365
ACCT_PERIOD = 
      CASE '{?RptPeriod}'
      WHEN 'Current Period' THEN ACCT_PERIOD
      WHEN 'Previous Period' THEN
            CASE ACCT_PERIOD
            WHEN 1 THEN 12
            ELSE (ACCT_PERIOD - 1)
            END
      ELSE CAST('{?RptPeriod}' AS INTEGER)
      END,

in Oracle you might want to change it to as follows. note that i change "MSSQL ACCT_PERIOD =" to the closing END as ACCT_PERIOD or END ACCT_PERIOD. you need to change this to the rest.

  CASE '{?RptPeriod}'
      WHEN 'Current Period' THEN ACCT_PERIOD
      WHEN 'Previous Period' THEN
            CASE ACCT_PERIOD
            WHEN 1 THEN 12
            ELSE (ACCT_PERIOD - 1)
            END
      ELSE CAST('{?RptPeriod}' AS INTEGER)
      END as ACCT_PERIOD,
0
 

Author Comment

by:jrbledsoe001
ID: 35717384
Thank you both for the speedy responses.  I'll modify the code with your suggestions and post the results.
0
 

Author Comment

by:jrbledsoe001
ID: 35734714
Thank you P_Zaharin.  Your solution was perfect
0
 
LVL 23

Expert Comment

by:OP_Zaharin
ID: 35734734
glad to help :)
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Truncate is a DDL Command where as Delete is a DML Command. Both will delete data from table, but what is the difference between these below statements truncate table <table_name> ?? delete from <table_name> ?? The first command cannot be …
Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the d…
Via a live example, show how to take different types of Oracle backups using RMAN.
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.
Suggested Courses

834 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question