CREATE TABLE AS STATEMENT (by copying from existing table)
Use SQL CREATE TABLE AS statement to create a table from an existing table by copying the existing table’s columns. What happens to the METADATA and DATA when creating a…
Read More19C LISTAGG DISTINCT
Overview The LISTAGG aggregate function now supports duplicate elimination by using the new DISTINCT keyword. The LISTAGG aggregate function orders the rows for each group in a query according to…
Read MoreOracle Analytical Functions
The easiest way to understand analytic functions is to start by looking at aggregate functions. An aggregate function aggregates data from several rows into a single result row. The aggregate…
Read MoreGet working days between two working date excluding weekends
This function which will help you to get number of working days between two date excluding the weekends. This will help other to get logic between two working date.This is…
Read Moreoracle unpivot function
The Oracle UNPIVOT clause allows you to transpose columns to rows. The UNPIVOT clause is opposite to the PIVOT clause except that it does not de-aggregate data during the transposing process. It’s Very helpful when you…
Read MoreHold Pending Concurrent Requests and release them
Here is an easy way to put all the Pending Concurrent requests in hold, if we are going through any situation(Mostly any Production Cutover/Go live scenarios). 1. First create a…
Read MoreOracle R12 Query To Find Customer Notes
SELECT hca.account_number, ( SELECT hp.party_name FROM apps.hz_parties hp WHERE hp.party_id = hca.party_id ) customer_name, jnb.creation_date notes_creation_date, jnb.note_type note_type, fls.meaning note_type_meaning, jnt.notes notes FROM apps.hz_cust_accounts hca, apps.jtf_notes_b jnb, apps.jtf_notes_tl jnt, apps.fnd_lookups…
Read MoreOracle R12 GL To AR Transaction Details
SELECT DISTINCT –gcck.concatenated_segments, gcck.segment1 company, gcck.segment2 location, gcck.segment3 product, gcck.segment4 function, gcck.segment5 account, gcck.segment6 intercompany, gcck.segment7 subaccount, gcck.segment8 future1, gcck.segment9 future2, rctld.gl_date, gjb.name batch_name, gjh.name journal_name, ( SELECT user_je_category_name FROM…
Read MoreOracle R12 AR Lockbox Query
SELECT hou.name operating_unit, xep.name legal_entity, ala.lockbox_number “NUMBER”, absa.name batch_source, cebb.bank_name, –cebb.bank_number, –cebb.bank_branch_name, –cebb.bank_branch_number, –cebb.bank_branch_type, — Branch type indicates which list the bank routing number is on. Valid types are ABA,…
Read MoreOracle SQL Query To Find Square Root For A Number
Syntax: SQRT(number); Query: select SQRT(25) from dual; Output: 5
Read More