SQL query WITH clause | Database

• The SQL WITH clause is used to provide a sub-query block which can be referenced in several places within the main SQL query.
• The usage of WITH clause is very similar to creating tables. When you create a table, you give it a name. Similarly, when you use the WITH caluse, you also give it a name  and this name essentially acts like a table name in the main SQL statement.
• WITH clasue dose not create a table or view, the object associated with the WITH statement disappears after the main SQL statement is executed, and there is nothing to clean up.

Syntax for single sub-query alias:

WITH <alias_name> AS (sql_sub-query_statement)

Sysntax for multiple sub query :

WITH <alias_name_1> AS (sql_sub-query_statement_1), <alias_name_2> AS (sql_sub-query_statement_2),.....

Example:

Suppose we want to list all the stores that have more sales than the average.

With t1 as 

(Select avg(sales) avg_sales from store_sales)

Select a1.* from store_sales a1, t1

Where a1.sales > t1.avg_sales;

Input:

Store name sales

A 15000

B 2500

C 3000

D 7000

Output:

Store_Name Sales

A 15000

D 7000

OIC - get ESS job status of the child process of the import job

Here, I will show you how to get child process id of the Import process and then to get the the ESS job status of the child process once the import job completed and you have the ess import request id.

Logic: 

we will create a loop and check the ess job status using the import request id whether the ess import job is successful or not(at each loop it will wait 59 sec). 

Follow the below steps:

Step1: Get the Child Process id calling BI report.

https://soalicious.blogspot.com/2022/01/oic-erp-how-to-get-child-process-id.html

Step2: Take an assign with the following variables:

I_inProgress : "true"

I_childRequestStatus : ""

I_childRequestID: decodebase64(reportBytes) << from BI report response

Step3: take a while activity and add the following condition.

$I_inProgress = "true"

Step4: Take a wait activity and assign some time to complete the child process lika value 59sec.

Step5: Drag and drop Erp cloud adapter and configure following:

Provide a name: essJobStatus

Select Query, Create, Update or delete information.

Select service: ErpIntegrationService

Operation: getESSJobStatus

Step6: Map the child request id to request id of the getEssJobstatus.

Step7: Take a assign named like updateProgress and do the following:

I_inProgress : returnStatus(result of getESSJobStatusResponse)

I_childRequestStatus: result of getESSJobStatusResponse.

Click here how to convert the status to boolean value:

oic-js-return-boolean-status

OIC - Create a header file based on sequential unique lines Transaction number | Oracle Integration cloud

Usecase: Here, the requirement is, we will have a source file which contains sequential duplicate transaction based lines or records. Based on sequential unique transaction numbers we have to fetch unique lines and write header file.

For instance,

Source file:

Transaction_Identifier,Transaction_number,Event_Type_Code,Ledger_Name,Date

Line,10,Payment,XX,08/09/2022

Line,10,Payment,XX,08/09/2022

Line,11,Reverse,XX,08/09/2022

Line,12,Payment,XX,08/09/2022

Line,12,Payment,XX,08/09/2022

Line,13,Payment,XX,08/09/2022

Line,13,Payment,XX,08/09/2022

Line,14,Payment,XX,08/09/2022

Expected output header file:

Transaction_number,Event_Type_Code,Ledger_Name,Date

10,Payment,XX,08/09/2022

11,Reverse,XX,08/09/2022

12,Reverse,XX,08/09/2022

13,Payment,XX,08/09/2022

14,Payment,XX,08/09/2022

Logic steps:

• Using FTP adapter connection, download the file to stage location and then read the file reference using sample csv filw format.
• Assign preTransactionNumber as empty string("").
• Write the header field names
• For each ledger line or record,
• Assign the current transaction number to variable transactionNumber
• Take a switch and put condition as preTransactionNumber != transactionNumber
• If its true, write the ledger record to header file to that stage location
• Otherwise, skip the duplicate line
• Assign the preTransactionNumber = transactionNumber
• After the loop, read the entire header file from the stage location
• Write the file to a FTP location.

Steps with screenshots:

Download the file

Created 2 schedule parameters to feed the file name and file directory.

Read the file reference 

Assign preTransactionNumner as "".

Write the header field names.

For each ledger record

Assign the transaction number

Take a switch and check if preTransactionNumber != transactionNumber

If the condition true, then add the transaction header line in the stage location. Otherwise skip the line.

Write the transaction header.

Assign the preTransactionNumber with last Transaction number

After the loop, read the entire header file and write to FTP location.

Integration flow:

Testing:

Source file:

Output:

Create a Decision model based on expressions | Process Cloud Service

Usecase: Here, we will create a decision model based on expressions and will expose as a REST service. 

Implementation Steps:

Oracle cloud Home >> Processes >> Decision models

This will land on graphical view.

Switch to list view 

Add input data

Add new decision 

Take Expressions

Add the expression.

Play app

Test

Creating a little complex expression with 2 input fields

Test

Expose as service

Activate

Similarly we can create decision models based on If Then else, Functions, Contexts, Lists, Relations, Loops and Decision tables.

Follow oracle docs to create with above ways:

https://docs.oracle.com/en/cloud/paas/integration-cloud/user-processes/model-decision-logic1.html