5 Replies Latest reply: Jul 25, 2013 8:20 AM by 6958fc69-24ba-4739-86f8-1c428f1fc67b

# OBIEE : combination column inside analysis

Hi everybody,

In my analysis, i have 4 columns , one of them must calculate  the 'age' based on a 'birth date' column , In addition , i want to show the results according to 4 range of age : ( 16-17 yo / 18-21 / 22-25 / +26 yo ).

I edited the column's formula to :  FLOOR( TIMESTAMPDIFF( SQL_TSI_MONTH , "Jeune"."Date de naissance", CURRENT_DATE ) / 12) , and in the combination tab , i want to add  the different cases but i don't know how .

I tried to do it and affect a name to each combination , for exemple  "16-17"

CASE  WHEN FLOOR( TIMESTAMPDIFF( SQL_TSI_MONTH , "Jeune"."Date de naissance", CURRENT_DATE ) / 12) BETWEEN 16 AND 17 THEN 16-17 WHEN FLOOR( TIMESTAMPDIFF( SQL_TSI_MONTH , "Jeune"."Date de naissance", CURRENT_DATE ) / 12) BETWEEN 18 AND 21 THEN 18-21 ELSE FLOOR( TIMESTAMPDIFF( SQL_TSI_MONTH , "Jeune"."Date de naissance", CURRENT_DATE ) / 12) END

but it show me in the resultats -1  instead  16-17 , and when i put it between quotes i have an error

thx

• ###### 1. Re: OBIEE : combination column inside analysis

Hi,

Once try using Bins which will automatically create a case statement.

Thanks.

• ###### 2. Re: OBIEE : combination column inside analysis

it's exaclty what i've used, but when they ask me for each Bin name I put 16-17 = -1 (it's not what i want) , and when i put "16-17" i have an error in the formula expression

• ###### 3. Re: OBIEE : combination column inside analysis

cant u use 16 to 17.

2nd option just a try am not sure

cast(16 as char)||'-'||cast(17 as char)

thanks

• ###### 4. Re: OBIEE : combination column inside analysis

-  try with    16 to 17

Formula syntax is invalid.

[nQSError: 10058] Une erreur générale s'est produite. [nQSError: 43113] Message renvoyé par OBIS. [nQSError: 27002] Vers  : erreur de syntaxe [nQSError: 26012] . (HY000)
Requête SQL émise : SELECT CASE WHEN FLOOR( TIMESTAMPDIFF( SQL_TSI_MONTH , "Jeune"."Date de naissance", CURRENT_DATE ) / 12) BETWEEN 16 AND 17 THEN 16 to 17 WHEN FLOOR( TIMESTAMPDIFF( SQL_TSI_MONTH , "Jeune"."Date de naissance", CURRENT_DATE ) / 12) BETWEEN 18 AND 21 THEN 18-21 WHEN FLOOR( TIMESTAMPDIFF( SQL_TSI_MONTH , "Jeune"."Date de naissance", CURRENT_DATE ) / 12) BETWEEN 22 AND 25 THEN 22-25 WHEN FLOOR( TIMESTAMPDIFF( SQL_TSI_MONTH , "Jeune"."Date de naissance", CURRENT_DATE ) / 12) >= 26 THEN +26 ELSE FLOOR( TIMESTAMPDIFF( SQL_TSI_MONTH , "Jeune"."Date de naissance", CURRENT_DATE ) / 12) END FROM "ActiviteMissionLocale"

- try with         cast(16 as char)||'-'||cast(17 as char)

Codes d'erreur : OPR4ONWY:U9IM8TAC:OI2DL65P

Etat : HY000. Code : 10058. [NQODBC] [SQL_STATE: HY000] [nQSError: 10058] Une erreur générale s'est produite. [nQSError: 43113] Message renvoyé par OBIS. [nQSError: 43119] Echec de la requête : [nQSError: 17001] Code d'erreur Oracle : 932, message : ORA-00932: inconsistent datatypes: expected CHAR got NUMBER at OCI call OCIStmtExecute. [nQSError: 17010] Echec de la préparation de l'instruction SQL. (HY000)
Requête SQL émise : SELECT s_0, s_1, s_2, s_3 FROM ( SELECT 0 s_0, CASE WHEN FLOOR(TIMESTAMPDIFF( SQL_TSI_MONTH ,"ActiviteMissionLocale"."Jeune"."Date de naissance", CURRENT_DATE )/12) BETWEEN 16 AND 17 THEN cast(16 as char)||'-'||cast(17 as char) WHEN FLOOR(TIMESTAMPDIFF( SQL_TSI_MONTH ,"ActiviteMissionLocale"."Jeune"."Date de naissance", CURRENT_DATE )/12) BETWEEN 18 AND 21 THEN 18-21 WHEN FLOOR(TIMESTAMPDIFF( SQL_TSI_MONTH ,"ActiviteMissionLocale"."Jeune"."Date de naissance", CURRENT_DATE )/12) BETWEEN 22 AND 25 THEN 22-25 WHEN FLOOR(TIMESTAMPDIFF( SQL_TSI_MONTH ,"ActiviteMissionLocale"."Jeune"."Date de naissance", CURRENT_DATE )/12)>=26 THEN +26 ELSE FLOOR(TIMESTAMPDIFF( SQL_TSI_MONTH ,"ActiviteMissionLocale"."Jeune"."Date de naissance", CURRENT_DATE )/12) END s_1

• ###### 5. Re: OBIEE : combination column inside analysis

I did it finally without using the bins but only  case when

CASE

WHEN FLOOR( TIMESTAMPDIFF( SQL_TSI_MONTH , "Jeune"."Date de naissance", CURRENT_DATE ) / 12) IN(16,17) THEN '16-17'

WHEN FLOOR( TIMESTAMPDIFF( SQL_TSI_MONTH , "Jeune"."Date de naissance", CURRENT_DATE ) / 12) IN(18,19,20,21) THEN '18-21'

WHEN FLOOR( TIMESTAMPDIFF( SQL_TSI_MONTH , "Jeune"."Date de naissance", CURRENT_DATE ) / 12) IN(22,23,24,25) THEN '22-25'

ELSE '26+'

END

but as you can see at the link below, there is some empty cases instead '0' !!

any suggestions to fix it ?