Oracle Communities

Oracle Analytics and AI

Child Item

BI Publisher For Cloud Apps - Start query "WITH"

Scott Hillier-149889

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

Find more posts tagged with

Submitted

BI Publisher

BI Publisher Data Models

Status: Submitted

Comments

Marybeth Snodgrass

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

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

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.