WITH clause using PL/SQL procedure and function
WITH clause using PL/SQL procedure and function in BIP Data Model SQL query
WITH clause
The WITH clause or subquery factoring is a clause in SQL that allows you to define a query before your SELECT statement and use that query definition inside your main SELECT statement. It is like temporary table, where you store the data once and read it multiple times in your SQL query.
You can define custom PL/SQL functions and Procedures which you can use in your main SELECT query. They don’t take up permanent storage space in your database and you don’t need to apply security to them to ensure that only your query accesses it.
Use of WITH clause
It is helpful in several reason.
WITH clause is used when a subquery is executed multiple times.
It is used to simplify complex SQL.
To improve the performance of the query.
Best use when the result of the WITH is required more than one time such as when one
aggregate value needs to be compared against two or three times.
Use Case
Let’s create a PL/SQL function and Procedure using WITH clause and use them in main SELECT query.
In this example we will create a PL/SQL Procedure to get all the Payment Numbers which are paid against the invoice, and we will create a PL/SQL Function to call the above Procedure and return the all the Payment Numbers in a single column.
PL/SQL Procedure
The procedure is taking Invoice ID as IN parameter and producing concatenated check numbers as OUT parameter.
PL/SQL Function
Now let’s define a Function which internally call our above procedure. This function will take the Invoice ID and returns check numbers.
SQL Query using WITH clause
Now prepare our SQL query using WITH clause and above PL/SQL function and Procedure.
BIP Data Model and Report
Now create BIP Data Model and Report using above WITH clause SQL Script.
Data Model :
Now create BIP Data Model and Report using above WITH clause SQL Script.
Report:
