OraFAQ Forum: Business Intelligence » Case Statement Month Rank

Home » Fusion Middleware & Colab Suite » Business Intelligence » Case Statement Month Rank

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Case Statement Month Rank [message #639318]

Mon, 06 July 2015 03:28

chungiemo
Messages: 1
Registered: July 2015
Location: Scotland

Junior Member

Hi folks I am a newbie in OBIEE,and seeking help from the experts.

I was looking to rank my months e.g. Apr 2014 - Mar 2015, and display this in a cross tab by its Rank in my report.

In my mind I would do a case statement on a Column Rank, and then exclude the rank in my results.

But having problems with the the syntax or am doing this completely wrong......

I had proceeded to dupilcate the Start Date, and called it "Rank" with the formula case statement:-

CASE WHEN MONTHNAME ("- Attendance"."Start Date") = "april" THEN "Attendance"."Rank" = 1
CASE WHEN MONTHNAME ("- Attendance"."Start Date") = "may" THEN "Attendance"."Rank" = 2
CASE WHEN MONTHNAME ("- Attendance"."Start Date") = "june" THEN "Attendance"."Rank" = 3
CASE WHEN MONTHNAME ("- Attendance"."Start Date") = "july" THEN "Attendance"."Rank" = 4
CASE WHEN MONTHNAME ("- Attendance"."Start Date") = "august" THEN "Attendance"."Rank" = 5
CASE WHEN MONTHNAME ("- Attendance"."Start Date") = "september" THEN "Attendance"."Rank" = 6
CASE WHEN MONTHNAME ("- Attendance"."Start Date") = "october" THEN "Attendance"."Rank" = 7
CASE WHEN MONTHNAME ("- Attendance"."Start Date") = "novermber" THEN "Attendance"."Rank" = 8
CASE WHEN MONTHNAME ("- Attendance"."Start Date") = "december" THEN "Attendance"."Rank" = 9
CASE WHEN MONTHNAME ("- Attendance"."Start Date") = "january" THEN "Attendance"."Rank" = 10
CASE WHEN MONTHNAME ("- Attendance"."Start Date") = "febuary" THEN "Attendance"."Rank" = 11
CASE WHEN MONTHNAME ("- Attendance"."Start Date") = "march" THEN "Attendance"."Rank" = 12

It displays "Invalid Alias Format : Table_name.column required".

Any assistance would be greatly appreciated.

Regards

Man

Report message to a moderator

Re: Case Statement Month Rank [message #639320 is a reply to message #639318]

Mon, 06 July 2015 04:10

Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

I don't use OBIEE, but - generally speaking - you are looking for a correct ORDER BY clause. Here's an example, see if you can adjust it accordingly to your needs.

I'm selecting employees from Scott's EMP table, displaying different HIREDATE column values, as returned by different TO_CHAR function parameters (i.e. format masks).
- HIRE_MM - two characters, but numeric representation of a month
- HIRE_MONTH_CRO - month name in my default language (Croatian)
- HIRE_MONTH_ENG - month name in English

I prefer using numbers than characters because NLS settings might change, and query - which was working just fine - suddenly returns incorrect results. That's why I used 'MM' format mask in my ORDER BY clause.

As I said: have a look, pick an option you find the most suitable (if any).

SQL>   SELECT ename,
  2           TO_CHAR (hiredate, 'dd.mm.yyyy') hire_date,
  3           TO_CHAR (hiredate, 'mm') hire_mm,
  4           TO_CHAR (hiredate, 'month') hire_month_cro,
  5           TO_CHAR (hiredate, 'month', 'nls_date_language = english')
  6              hire_month_eng
  7      FROM emp
  8  ORDER BY CASE
  9              WHEN TO_CHAR (hiredate, 'mm') = '04' THEN 1
 10              WHEN TO_CHAR (hiredate, 'mm') = '05' THEN 2
 11              WHEN TO_CHAR (hiredate, 'mm') = '06' THEN 3
 12              WHEN TO_CHAR (hiredate, 'mm') = '07' THEN 4
 13              WHEN TO_CHAR (hiredate, 'mm') = '08' THEN 5
 14              WHEN TO_CHAR (hiredate, 'mm') = '09' THEN 6
 15              WHEN TO_CHAR (hiredate, 'mm') = '10' THEN 7
 16              WHEN TO_CHAR (hiredate, 'mm') = '11' THEN 8
 17              WHEN TO_CHAR (hiredate, 'mm') = '12' THEN 9
 18              WHEN TO_CHAR (hiredate, 'mm') = '01' THEN 10
 19              WHEN TO_CHAR (hiredate, 'mm') = '02' THEN 11
 20              WHEN TO_CHAR (hiredate, 'mm') = '03' THEN 12
 21           END;

ENAME      HIRE_DATE  HI HIRE_MON HIRE_MONT
---------- ---------- -- -------- ---------
JONES      02.04.1981 04 travanj  april
BLAKE      01.05.1981 05 svibanj  may
CLARK      09.06.1981 06 lipanj   june
TURNER     08.09.1981 09 rujan    september
MARTIN     28.09.1981 09 rujan    september
KING       17.11.1981 11 studeni  november
MILLER     23.12.1982 12 prosinac december
SCOTT      09.12.1982 12 prosinac december
JAMES      03.12.1981 12 prosinac december
FORD       03.12.1981 12 prosinac december
SMITH      17.12.1980 12 prosinac december
ADAMS      12.01.1983 01 siječanj january
WARD       22.02.1981 02 veljača  february
ALLEN      20.02.1981 02 veljača  february

14 rows selected.

SQL>

[Updated on: Mon, 06 July 2015 04:11]

Report message to a moderator

Re: Case Statement Month Rank [message #639323 is a reply to message #639318]

Mon, 06 July 2015 04:54

Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web

Senior Member

chungiemo wrote on Mon, 06 July 2015 13:58

I was looking to rank my months e.g. Apr 2014 - Mar 2015, and display this in a cross tab by its Rank in my report.

In my mind I would do a case statement on a Column Rank, and then exclude the rank in my results.

You need to use the "edit formula" and add the CASE function. The are two types available:

- CASE(switch)
- CASE(if)

Edit your analysis, go to formula, click on Fx, select CASE. Have a look at http://docs.oracle.com/cd/E12096_01/books/admintool/admintool_SQL8.html

By the way, if you want the months to be always associated with a rank, then you could do it in the BMM(Business model and mapping) using a logical column. Read http://docs.oracle.com/cd/E23943_01/bi.1111/e10540/busmodlayer.htm#BIEMG625

Report message to a moderator

Previous Topic:	Oracle Data Integrator, Customised Delete Operation
Next Topic:	XML Publisher Excel Template design

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sat Jan 04 17:11:07 CST 2025