Oracle Analytics Cloud and Server

Home› Oracle Analytics› Oracle Analytics Forums› Oracle Analytics Cloud and Server› Oracle Analytics Cloud and Server

Categories

Welcome to the Oracle Analytics Community: Please complete your User Profile and upload your Profile Picture

Substring function is not working in advance sql filter

Received Response
141
Views
9
Comments
Helan Kumar
Helan Kumar Rank 4 - Community Specialist
edited Aug 13, 2024 7:03PM in Oracle Analytics Cloud and Server

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

Untitled.jpg

Answers

  • Thomas Dodds
    Thomas Dodds Rank 8 - Analytics Strategist

    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
    Gianni Ceresa Mod

    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
    Helan Kumar Rank 4 - Community Specialist

    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
    Helan Kumar Rank 4 - Community Specialist

    Will give a try. Thanks.

  • Gianni Ceresa
    Gianni Ceresa Mod

    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
    SriniVEERAVALLI Rank 6 - Analytics Lead

    Sorry for jumping in middle

    Did you cast to int after substring

  • [Deleted User]
    [Deleted User] Rank 2 - Community Beginner

    "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
    Helan Kumar Rank 4 - Community Specialist

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

  • Helan Kumar
    Helan Kumar Rank 4 - Community Specialist

    Working now!

    Thank you