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 Analytics and AI

Child Item

Substring function is not working in advance sql filter

Helan Kumar

I have a prompt called fiscal_period which will have values like 2016R 1, 2016R 2,.......

I need to apply a filter on the report on fiscal_year = 2016. So i'm trying to get the first four letter from fiscal_period and pass it to fiscal_year.

Please find the screenshot below, please help me with any alternate solutions.

First screenshot is the advanced sql filter in the criteria tab, second is the result view in results.

Thanks, Helan

Find more posts tagged with

Accepted answers

All comments

Thomas Dodds

Pass the fiscal_period to a report that you don't put on the dashboard, accept the prompted value and put Year as the only criteria column. Then in your report filter Year by the results of another analysis ... skip the messy functions all together.

Gianni Ceresa

I first thought it was because of a wrong syntax, OBIEE substring is SUBSTRING(value FROM beginning FRO length) but it looks like the syntax with with (value, from, length) works as well ...

Is your Fiscal year a string? Do you have no data or an error?

How does the physical generated query looks like? (does it makes sense? is the condition correct? did you try to change the = for a LIKE ?)

Helan Kumar

Yes both the syntax works.

Its not a string but that can be handled using cast if required. There is no error, its just no data

SQL looks sense [and D6.c3 = substr('2016R 3' , 1 , 4)].

Have tried LIKE too, same results - no data

Helan Kumar

Will give a try. Thanks.

Gianni Ceresa

If you run the physical SQL (which looks good so it's already a good point), you also don't have any data (I hope), but do you see what makes the query to not return any data?

Maybe you just don't have data for 2016? (or not for that combination of filters and columns?)

SriniVEERAVALLI

Sorry for jumping in middle

Did you cast to int after substring

[Deleted User]

"Time"."T05 Per Name Year" = SUBSTRING('@{pvPeriod}{2012 R1}' FROM 1 FOR 4)

^--- Works perfectly fine as proven here:

http://slc02okg.oracle.com:7780/analytics/saw.dll?Answers&path=%2Fshared%2FOTN%2FSubstring%20in%20SQL%20Filter

Prodney / Admin123 to log on.

Helan Kumar

It has data, the same formula works when i write it in edit formula section

Helan Kumar

Working now!

Thank you