How to use ELSEIF condition in obiee?

User_N86UL

Hi All,

I need to convert this in to OBI :

If [Candidate UDF Value 06] InList("Part Time"; "Casual"; "Regular Full Time in a Temporary Part Time position"; "Job Share"; "Casual in a Temporary Full Time position"; "Casual in a Temporary Part Time position"; "OPT"; "Regular Part Time in a Temporary Full Time position"; "Regular Part Time in a Temporary Part Time position"; "PT"; "New Employee in a Temporary Part Time position") Then

  ToNumber([Candidate UDF Value 04])

ElseIf [Candidate UDF Value 02] InList("ONA Local 70";"SPH ONA") Then

  (If IsNull([Candidate UDF Value 01]) Then

  ToNumber([Candidate UDF Value 04])

  Else

  Round(DaysBetween(ToDate([Candidate UDF Value 01];"yyyy-MM-dd");CurrentDate()) / 365.25 * 1500;2))

ElseIf [Candidate UDF Value 02] InList("OPSEU Local 273";"SJHH OPSEU 206")Then

  (If IsNull([Candidate UDF Value 01]) Then

  ToNumber([Candidate UDF Value 04])

  Else

  Round(DaysBetween(ToDate([Candidate UDF Value 01];"yyyy-MM-dd");CurrentDate()) / 365.25 * 1650;2))

ElseIf [Candidate UDF Value 02] InList("CUPE 4800";"CUPE 7800";"SPH CUPE";"PIPSC Radiation Therapists") Then

  (If IsNull([Candidate UDF Value 01]) Then

  ToNumber([Candidate UDF Value 04])

  Else

  Round(DaysBetween(ToDate([Candidate UDF Value 01];"yyyy-MM-dd");CurrentDate()) / 365.25 * 1725;2))

ElseIf [Candidate UDF Value 02] InList("CUPE Trades") Then

  (If IsNull([Candidate UDF Value 01]) Then

  ToNumber([Candidate UDF Value 04])

  Else

  Round(DaysBetween(ToDate([Candidate UDF Value 01];"yyyy-MM-dd");CurrentDate()) / 365.25 * 1840;2))

ElseIf [Candidate UDF Value 02] InList("Prof Instit Public Service CAN") Then

  (If IsNull([Candidate UDF Value 01]) Then

  ToNumber([Candidate UDF Value 04])

  Else

  Round(DaysBetween(ToDate([Candidate UDF Value 01];"yyyy-MM-dd");CurrentDate()) / 365.25 * 1950;2))

ElseIf [Candidate UDF Value 02] InList("OPSEU Local 209";"OPSEU 209") Then

  (If IsNull([Candidate UDF Value 01]) Then

  ToNumber([Candidate UDF Value 04])

  Else

  Round(DaysBetween(ToDate([Candidate UDF Value 01];"yyyy-MM-dd");CurrentDate()) / 365.25 * 1950;2))

Else 0

Gianni Ceresa

Hi,

CASE WHEN .... THEN ....

WHEN .... THEN ....

WHEN .... THEN ....

ELSE ....

END

User_N86UL

Thanks Gianni.

But this formula contains two then's in one condition like:

ElseIf [Candidate UDF Value 02] InList("OPSEU Local 273";"SJHH OPSEU 206")

Then  (If IsNull([Candidate UDF Value 01])

Then  ToNumber([Candidate UDF Value 04])

  Else

  Round(DaysBetween(ToDate([Candidate UDF Value 01];"yyyy-MM-dd");CurrentDate()) / 365.25 * 1650;2))

How to write this in CASE statement?

Regards,

Gaurav

Gianni Ceresa

It doesn't contains 2 THEN in one condition !

It contains a IF ... ELSE as the THEN of one condition.

Just write the same if you want to keep it simple and not change anything else:

WHEN .... THEN

   CASE WHEN .... THEN .... ELSE .... END

ELSE ....

You can have a CASE WHEN into another CASE WHEN (and many times)

User_N86UL

Gianni can you give one demo on this little code, how to use CASE statement :

If [Candidate UDF Value 06] InList("Part Time"; "Casual"; "Regular Full Time in a Temporary Part Time position"; "Job Share"; "Casual in a Temporary Full Time position"; "Casual in a Temporary Part Time position")

Then

  ToNumber([Candidate UDF Value 04])

ElseIf [Candidate UDF Value 02] InList("ONA Local 70";"SPH ONA")

Then  (If IsNull([Candidate UDF Value 01])

Then   ToNumber([Candidate UDF Value 04])

Else   Round(DaysBetween(ToDate([Candidate UDF Value 01];"yyyy-MM-dd");CurrentDate()) / 365.25 * 1500;2))

Also can you help me on this "Round(DaysBetween(ToDate([Candidate UDF Value 01];"yyyy-MM-dd");CurrentDate()) / 365.25 * 1500;2))"?

Gianni Ceresa

CASE

WHEN  your condition on UDF Value 06 THEN your value UDF Value 04

WHEN your condition on UDF Value 02  THEN

   CASE WHEN your is null condition on UDF Value 01 THEN UDF Value 04

   ELSE your calculation on UDF Value 01

   END

....

Do you actually have OBIEE in front of you?

If you edit the formula of a column and look at the "help / list of functions" into the tool you find all you look for:

Selected	TimestampDiff‎
Syntax	TIMESTAMPDIFF(interval, expr, timestamp2)
Where	interval is the specified interval. Valid values are: SQL_TSI_SECOND,SQL_TSI_MINUTE, SQL_TSI_HOUR,SQL_TSI_DAY,SQL_TSI_WEEK,SQL_TSI_MONTH,SQL_TSI_QUARTER,SQL_TSI_YEAR. timestamp1 and timestamp2 are any valid timestamp.
Example	SELECT TIMESTAMPDIFF(SQL_TSI_DAY, TIMESTAMP'1998-07-31 23:35:00',TIMESTAMP'2000-04-01 14:24:00') FROM Employee WHERE employeeid = 2;
Description	Returns the total number of specified intervals between two timestamps.

Selected	Current_Date‎
Syntax	CURRENT_DATE
Where
Example
Description	Returns the current date. The date is determined by the system in which the Oracle BI Server is running.

Everything you need is there, just try, start with your column and next to it add the same column a second time and add little by little the pieces of logic and check the result.

User_N86UL

I need little help on "Round(DaysBetween(ToDate([Candidate UDF Value 01];"yyyy-MM-dd");CurrentDate()) / 365.25 * 1500;2))".

In above formula , Candidate UDF Value 01 is normal column which contains date and in the above calculation I have to calculate days between Candidate UDF Value 01 and Current_Date.

I tried CAST and Evaluate function still I am not able to convert Candidate UDF Value 01 in to date format.

Can you please help me on this?

Gianni Ceresa

It depends on the format of your date but try with TO_DATETIME('your value', 'the date format')

User_N86UL

The UDF column contains format yyyy-mm-dd.

Can you give lil demo on this?

cesar.advincula.o

User_N86UL

After doing this :

TO_DATETIME("Candidate UDFs"."Candidate UDF Value 01",'MM/dd/yyyy')

I am getting this error:

Error Codes: OPR4ONWY:U9IM8TAC:OI2DL65P

State: HY000. Code: 10058. [NQODBC] [SQL_STATE: HY000] [nQSError: 10058] A general error has occurred. [nQSError: 43113] Message returned from OBIS. [nQSError: 17001] Oracle Error code: 1843, message: ORA-01843: not a valid month at OCI call OCIStmtFetch. [nQSError: 17012] Bulk fetch failed. (HY000).

Please help me on this.