SQL & PL/SQL :: How To Split Multi-delimited Field Into Several Rows And Add New Fields
Mar 7, 2013
I have a query that produces around 11 fields, and one of which is a multi-delimited field and the other 10 are dimension fields. I would like to split that field into several rows, and have the other 10 fields just repeated for each one. Here is an example of the data in the 11th field :
Column 11
34^56^78,59
There are two delimiters in the field, a carat and a comma. This field is used to reference document numbers that are needed to be sent in. The carat represents the word "Or" and the comma represents the word "And". I would like to have the output of each field to be a repeat of the 10 dimension fields, plus 3 new fields. The first new field would be the document number, the second new field would be the position within the original delimited field(1, 2, 3, etc.) , and the last field would be one of three logic words :First (if it is the first value), Or (if the value followed a carat), And (If the value followed a comma). Example of the output from the above value would be :
Column 11 Column 12 Column 13
34_______ 1_______ First
56_______ 2_______ Or
78_______ 3_______ Or
59_______ 4_______ And
Any thoughts on this? I have found a few solutions online on how to break up the delimited field into rows, but never with multiple delimiters or with extra logic for the added fields.
View 13 Replies
ADVERTISEMENT
Aug 6, 2013
I have a table that has about 20,000 rows.
There is a column called Keyword which has values like below:
File_IDKeyword1SMITH;ALLEN;WARD;JONES; BRADY2S&P500;TOPIX3SMALL;LARGE;MEDIUM
I want to output the data like this: FILE_IDKEYWORD1SMITH1ALLEN1WARD1BRADY2S&P5002TOPIXetc
I'm using this query and it works: SELECT STG.FILE_ID, REGEXP_SUBSTR(STG.KEYWORD,'[^;]+', 1, LEVEL) AS KEYWORD FROM STG_TABLE STGCONNECT BY REGEXP_SUBSTR(STG.KEYWORD,'[^;]+', 1, LEVEL) IS NOT NULL
But its sooooo slow, its unusable. Is there a quicker way to return this output? Other info:KEYWORD is varchar2(4000) but rarely more than 100 bytes are usedOracle 11g2 !
View 5 Replies
View Related
Mar 22, 2010
I need to separate 1 field into 2 fields. The source is varchar2 and is like:
[VOUCHER]
CGJ0000617
CG0001442
CGJ0001444
CMOV0000200
CXAR00000001
CXAR00000002
Result should seperate numeric value from characters so that the result would be:
[VOUCHER_char] [VOUCHER_num]
CGJ 0000617
CG 0001442
CGJ 0001444
CMOV 0000200
CXAR 00000001
CXAR 00000002
I need one or two SQL statement.
View 9 Replies
View Related
Jan 13, 2012
I Want to make a query to select finished goods product in sales having product code greater than 280 but i have face a problem that order by is not working because products column have character code as well as number. how to sort that column.
View 2 Replies
View Related
Aug 8, 2012
using SQLLDR: Looking for a control file solution to move past or bypass extra data fields which are not on destination table. Basically if you have 8 tab delimited fields(terminated by ' ') on a data record; but only need to load 5 of the values from the delimited record; is there a way to ignore/bypass the not needed data. Obviously, the answer would be to massage the data at the OS and removed the 3 unnecessary fields.
However my hands are tied by volume,time, and compliancy. I am familiar with using 'FILLER' for the reverse scenario; but not where you have more data available on the record then exists on the table.
View 1 Replies
View Related
May 31, 2011
how can I convert
select 1 as id, 'role1,role2,role3' as roles from dual union all
select 2 as id, 'role1' as roles from dual
to
select 1 as id, 'role1' as roles from dual union all
select 1 as id, 'role2' as roles from dual union all
select 1 as id, 'role3' as roles from dual union all
select 2 as id, 'role1' as roles from dual
?
I would prefer sql then plsql. Script for creating a test table:
create table CONVERT_LIST(id integer, roles varchar2(100));
insert into CONVERT_LIST values(1,'role1,role2,role3');
insert into CONVERT_LIST values(2,'role1');
View 3 Replies
View Related
Jul 31, 2013
In employees table I am having employee_name column data as follows
Aaron, Mrs. Jamie (Jamie)Aaron, Mrs. Jenette (Jenette)Abbott, Ms. Rachel (Rachel) Breton, Mr. Jean Britz, Mrs. Sarie (Sarie) --> Now, I want to display the employee name like "Mrs. Jamie" (with out Surname and with out bracket included text),-->
How to achieve this by SQL query.
View 3 Replies
View Related
Apr 29, 2013
i have one multiple record field in frm....contain 5 field.....now i display the values...3 fields have values and two are empty...
the value of 1st field is A
2nd field is B
3rd field is C
i want if i click B it will open another form,
i want if i click c it will open another form,
View 9 Replies
View Related
Nov 3, 2010
Consider below is a multi record block rows, i want to hide "23". Is it possible using Set_Item_Instance_Property or any other built-in is there in oracle forms to hide a single row field in a multi record block.
11 12 13
21 22 23
31 32 33
. . .
View 3 Replies
View Related
May 2, 2012
I have data like :
ID NAME1 NAME2 NAME3
JJ AD MED
VI TIBO PH TIBO
I want output like
ID NAME
JJ AD
JJ MED
VI TIBO
VI PH
VI TIBO
View 4 Replies
View Related
Mar 4, 2011
SELECT 'TEST','F1,F2,F3,F4' from dual
I want to split the rows by comma separated as below
TEST F1
TEST F2
TEST F3
TEST F4
View 5 Replies
View Related
Aug 15, 2013
I have a table which stores Employees and their Phone numbers. Each employee can have multiple numbers e.g.
Employee, Number
Adam, 123
Adam, 456
John, 123
John, 456
I am trying to write a select statement that will split the rows into columns and group by each employee e.g.
Employee, Number1, Number 2
Adam, 123, 456
John, 123, 456
View 19 Replies
View Related
Nov 26, 2010
I have a table like this:
ID1 ID2 Ini_date End_date
1 1 2008-05-14 2010-09-16
1 2 2010-01-21 2010-08-26
..... ..... ............. ...................
and I would like to have a row for each year between ini_date and end_date.
ID1 ID2 YEAR
1 1 2008
1 1 2009
1 1 2010
1 2 2010
View 2 Replies
View Related
Jul 13, 2011
I am having a problem with auto populating different fields based on inventory no. field.. This is a bug giving to me to work on and i not able to figure out how to populate the other fields.
How to set any triggers for the items to auto populate and i am suppose to finish this work today.
View 2 Replies
View Related
Aug 13, 2011
I need to delete all the registers where the table 1 does join with table 2 in 3 fields... for example:
delete taba1 t1
where t1.campo1 in ( select distinct(tr.campo1)
from tabla1 tr,
tabla2 t2
where t2.error = 0
tr.campo1 = t2.campo1
and tr.campo2 = t2.campo2
[Code]...
View 4 Replies
View Related
Nov 19, 2010
i have multi data block filed. and checkbox field which based on control block...My task is when i check checkbox only one field should enabled and my mouse goes to that field
e.g
item11 item21 item31 chkbox1
item12 item22 item32 chkbox2
Scenario like this :
My item field based on data block and checkbox based on control block,while i checked chkbox1 , only item31 on that current record should be enabled and i changed value only on that field
when i checked chkbox1 , my cursor goes to item31...not item32
View 12 Replies
View Related
Aug 20, 2012
select the rows from a table with the same particular field in PL/SQL. Actually I don't want to write two loops one inserted into another.
View 7 Replies
View Related
Feb 24, 2012
I have a table TP having following data (Dashes used for space as i am unable to have proper alignment)
ID1-----TOT
1 ------- 5
2 ------- 7
I need a query that repeat the records depending on the field TP.TOT
e.g.
ID1 -----TOT
1 ------- 5
1 ------- 5
1 ------- 5
1 ------- 5
1 ------- 5
2 ------- 7
2 ------- 7
2 ------- 7
2 ------- 7
2 ------- 7
2 ------- 7
2 ------- 7
View 5 Replies
View Related
Aug 10, 2011
I want to create a SELECT, that shall give back only a special amount of rows, depending on the sum of one of the selected fields.
At first a code sample of the complete selection:
SELECT DISTINCT mnr, ktxt,
(SELECT Sum(meng_4)FROM reldb d1 WHERE d1.mnr=d.mnr)qty
FROM reldb d
WHERE mnr IN (SELECT mnr FROM relac WHERE Lower(rlnr) NOT LIKE 'platte geprägt%')
AND saext='M'
ORDER BY qty DESC,ktxt;
This selection produces some lines of output (in my case i.e. like 300). What I want to see is only that much lines that the condition 'sum of all items listed below meng_4<=sum of all items meng_4 of the whole selection * 0.9' is fulfilled.
So, if the whole selection produces a total of 10000 as sum for all items meng_4, I want to see only that amount of rows that sums a total of at least 9000 for all items meng_4.
I hope, this specification is exactly enough to understand my intent.
1. Can I do this in a query?
2. If yes, what would this query look like?
View 19 Replies
View Related
Mar 29, 2013
generate the number of rows based on table field.
Sample code is given below.
CREATE TABLE T
(
docno VARCHAR2(10),
CODE VARCHAR2(8),
QTY NUMBER(3)
)
LOGGING
View 4 Replies
View Related
Apr 1, 2010
I need to list a count of rows where a DATE field is not null and group those counts by day.
Here's my sql so far...
SELECT
COUNT(DQ_DISTRBTN_DATE) as DQR_DIST,
DQ_DISTRBTN_DATE as DIST_DATE
from
ETL_PROCESS.BATCH
group by
DQ_DISTRBTN_DATE;
Because DQ_DISTRBTN_DATE contains time, how do I modify to achieve the desired result?
View 2 Replies
View Related
Aug 28, 2013
How can I compare two rows from the same table and show the field and the difference?
Example:
select ename, job, mgr, hiredate, sal, deptno
from EMP t
where t.empno = 7782
or t.empno = 7788
ENAME JOB MGR HIREDATE SAL DEPTNO
1 CLARK MANAGER 7839 6/9/1981 2450.00 10
2 SCOTT ANALYST 7566 6/9/1981 3000.00 20
My report comparing fields should be:
FIELD BASE COMPARE DIFFERENCE
ENAME CLARK SCOTT --
JOB MANAGER ANALYST --
MGR 7839 7566 --
SAL 2450 3000 550 --(Show difference only for numbers)
DEPTNO 10 20 --
View 9 Replies
View Related
Mar 15, 2013
I know how to select the last N sets of rows, using DENSE_RANK - where multiple rows have the same timestamp but I want to only select those rows which do NOT have the top 2 unique timestamps.
i.e.:
SELECT *
FROM ( SELECT DENSE_RANK() OVER (ORDER BY myTimestamp DESC) DENSE_RANK, HISTORYID, USER_ID, myTimestamp, STATUS, FROM TXN_HIST)
WHERE DENSE_RANK > 2 order by myTimestamp DESC, HISTORYID, USER_ID;
But how do I DELETE these same rows?
View 3 Replies
View Related
Sep 26, 2012
I try to find out how export data from table to Excel file format and save the result to BLOB field in some other table.I know how to download report from Page by submit, but I need to process data and instead of returning result to user as Excel file - save it in BLOB.
Also I found implementation on JAVA for the issue but actually I wanna study out - Is it possible to resolve this issue by PL/SQL and APEX API methods?
View 4 Replies
View Related
Jul 18, 2013
I need to update a column with tab delimited ie that column value should be tab delimited. Eg:- Url_name is the column and column values =[URL]. THe space between [URL] should be tab delimited. I wanted to use a update query to update this values with tab delimited.
View 2 Replies
View Related
Sep 4, 2013
I have a requirement, where i need to find and replace values in delimited string. For example, the string is
"GL~1001~157747~FEB-13~ CREDIT~ A~N~ USD~ NULL~".
The 4th column gives month and year. I need to replace it with previous month name. For example:
"GL~1001~ 157747~ JAN-13~ CREDIT~ A~N~USD~NULL~".
I need to do same for last 12 months. I thought of first devide the values and store it in variable and then after replacing it with required value, join it back. I just wanted to know if there is any better way to do it?
View 10 Replies
View Related
Mar 17, 2013
A big table of size more than 4 GB from 10g DB needed to be extracted/exported into a text file,the column delimiter is "&|" and row delimiter is "$#".I cannot do it from TOAD as it is hanging while extraction of big table.
View 9 Replies
View Related
Jan 10, 2012
I want to convert a column value to a delimited string using a query.
Example
TableA
Col1 Col2 Col3
1 x200 MIS-X
2 x200 BTS-X
3 x200 TYR-X
4 x100 YRY-X
Select Col3 From TableA where Col2 = 'x200'
Expected Output:
'MIS-X','BTS-X','TYR-X'
View 4 Replies
View Related
Mar 3, 2010
I have a requirement to get a delimited output file by executing a select query.
For e.g.
select id, name, age from customers;
i need the output as,
id,name,age
123,devi,23
34,abi,20
4900,infy,23
i tried select id||','||name||','||age from customers;
but am getting the following output....
id||','||name||','||age
123,devi,23
34,abi,20
4900,infy,23
But i want to remove those pipes in between the column name.
I tried colsep also... but there am getting the output as.,
id,name,age
123, devi, 23
34, abi, 20
4900, infy, 23
some unwanted spaces in between...but i want the output as this...
id,name,age
123,devi,23
34,abi,20
4900,infy,23
the query which am using is stored in a .sql file.
View 10 Replies
View Related
Feb 15, 2010
I am trying to Spool the data in pipe delimitted csv file but some of the records going on another line from the same records. Currently some of the data going to next line as below oulined in the 2nd and 3rd line (in bold - |Home & Family) . I have following sql setting in my spool file:
set linesize 4000 pagesize 0 trimspool on feedback off verify off echo off
set define off
spool Stk_hold_Sec_Tsk.csv
I tried increase linesize to 5000 but its not working.
Ex.
PSS:Production Manager|ZS:PsS:PP:PROD_TCODES|P2S: PP - Production Transactions|House & street
PSS:Production Manager|ZC:BW:PsS_RPT_MGR|BW PsS Reports Manager
[b]|House & street[/b]
PsS:Production Manager|ZC:BW:PsS_RPT_USER|BW PsS Reports User
[b]|House & street[/b]
PsS:Master Data (PDMs)|ZS:GEN:GENERAL_USER|GEN: General User|House & street
Data should be like into the file:
PSS:Production Manager|ZS:PsS:PP:PROD_TCODES|P2S: PP - Production Transactions|House & street
PSS:Production Manager|ZC:BW:PsS_RPT_MGR|BW PsS Reports Manager|House & street
PsS:Production Manager|ZC:BW:PsS_RPT_USER|BW PsS Reports User|House & street
PsS:Master Data (PDMs)|ZS:GEN:GENERAL_USER|GEN: General User|House & street
I think it should be something with linesize or pagesize but not sure
View 18 Replies
View Related