I have data that look like this: I would like to pivot these data so that for each set of rows with the same OH_IDNR and with the same common/non-unique part of OCV_VNAME, these columns are returned: OH_IDNR The common part of OCV_VNAME (e.g., ‘response_part_0_script_0_’) – n. One column for each …
Tag: oracle
ORACLE SQL How to use custom function iside insert cte
I am trying to use function inside insert SQL, but I am getting error ORA-32034. Looks like that I couldn’t access this function in the cte. Maybe someone can help? Answer If the query containing the PL/SQL declaration section is not the top level query, the top-level query must include the WITH_PLSQL h…
Grouped conditional sum in Oracle SQL
my_table shows the account balance of each person’s credits N months ago. From this table, I want to get the monthly sum of each person’s balances for the past 2 and 3 months and divide each sum by 2 …
How to SELECT all JOBS that using a DBLINK on oracle?
Is my pleasure to say helo to all of you. I need help on oracle to select all JOBS that using a DBLINK on oracle, to stop them during maintenance. I will appreciate your help.
Convert result of CASE Expression to a number in Oracle SQL
I have the following select query. I want to convert it from string into a number. If I wrap a TO_NUMBER around the case expression, I get expression must have same datatype as corresponding …
Get latest record after ordering columns based on sequence
I would like to order my table using two columns in manner of sequence as described: The current balance to start as the previous balance in next row. Below is my data ID_DATE PREV_BAL …
merging start and end date cycle dates
I have the below table. CUST_ID START_CYCLE END_CYCLE WORKER CUST_SUB_ID CUST_SUB_TYPE 101 1/1/2019 1/31/2019 ABC123 134 HIGH_SUB 101 2/1/2019 4/30/2019 ABC123 136 …
Issue using to_date giving ORA-01722: invalid number
I am trying to pull together two fields to create a mm/dd/yyyy date field using case-when, and it keeps giving me error ORA-01722: invalid number. Answer The problem is Oracle applies the concatenation operators before it does the arithmetic. So the ELSE branch of your CASE statement: is going to execute some…
CONNECT BY PRIOR and concatenation of parent – child hierarchy
I have this table: img Desired result: img What i’m trying: select SEG from my_table CONNECT BY PRIOR PARENT_SEG_ID = SEG_ID; So, in the column LEVEL_LOCATOR we can see f.e. that the LEVEL 5.9 has 6 childs, and 5.9.4 has 2 childs. And, f.e., the parent of the child 5.9.1 is Postpaid that has the SEG_ID …
Oracle: How fill in the gaps between rows using previous next row’s values using sql
I have the table: How fill in the gaps between rows using previous next row’s values in Oracle to get the following output: the values of previous and next columns should be the same to fill the rows. Answer If you want all rows to be the same for a given year and id, just use max(): There seems to