nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

Oracle Communities

Oracle AI and Analytics

Child Item

How to use ELSEIF condition in obiee?

g_08-Oracle

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

Find more posts tagged with

Accepted answers

All comments

Gianni Ceresa

Hi,

CASE WHEN .... THEN ....

WHEN .... THEN ....

ELSE ....

END

g_08-Oracle

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)

g_08-Oracle

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

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.

g_08-Oracle

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')

g_08-Oracle

The UDF column contains format yyyy-mm-dd.

Can you give lil demo on this?

cesar.advincula.o

g_08-Oracle

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.

Upcoming Event

Analytics - Fusion Data Intelligence: Semantic Model Extensions in FDI: Implementation Patterns, Security, and Best Practices
Build on the foundational concepts from Part 1 with a deeper look at implementation patterns for extending the FDI semantic model. This session covers common use cases such as modifying subject areas, creating custom subject areas, combining external application models, and supporting custom security requirements. It also…

Top Leaders