Oracle Analytics Publisher Idea Lab

Home› Oracle Analytics› Oracle Analytics Idea Labs› Oracle Analytics Publisher Idea Lab

Categories

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

BI Publisher For Cloud Apps - Start query "WITH"

Submitted
13
Views
3
Comments
Scott Hillier-149889
Scott Hillier-149889 Rank 1 - Community Starter
edited May 8, 2025 3:57PM in Oracle Analytics Publisher Idea Lab

Organization Name

AWE plc

Description

Allow BI Publisher in Cloud Apps to have queries which start with the WITH statement

e.g :

 

WITH a AS

(SELECT 1, 2 FROM DUAL)

SELECT *

FROM a

 

Should work (in the same way as SELECT 1,2 FROM DUAL)

 

 

Use Case and Business Need

If we have big legacy prewritten queries, which begin WITH, then it would be good to just cut and paste them into BI Publisher.

Original Idea Number: 1d4801c5c2

Tagged:
1
Up
1 votes

Submitted · Last Updated

Comments

  • Marybeth Snodgrass
    Marybeth Snodgrass Rank 4 - Community Specialist

    I don't think starting query with "WITH" is the problem, as this is already a capability. Perhaps copy and pasting from big legacy prewritten queries is including SQL functions that have slightly different syntax and aren't compatible with PL/SQL.

    For example, the formula syntax to replace nulls with a certain value in oracle PL/SQL is  "NVL()"

    With SQL server it's "isnull()", while for MySQL it's "ifnull()"

  • Marybeth Snodgrass
    Marybeth Snodgrass Rank 4 - Community Specialist

    Here is a list of some common functions and whether the syntax differs between PL/SQL and other SQL types.

    http://troels.arvin.dk/db/rdbms/

  • Valeriy Gorbunov-80491
    Valeriy Gorbunov-80491 Rank 3 - Community Apprentice

    I didn't want just to click "thumbs-down", looks like Scott misunderstood the idea.

    WITH clause is available for all flavors of BI Publisher Data Models SQL for ages, somewhere from 10.9 or so. It works fine in standalone (BIPEE) and (Fusion) Cloud.

    In case if you're using BIP running queries on non-Oracle database, I wish you luck.