|
Hi,
Could some one help me with loading the TABLE2 like the way it is shown below.
SELECT COL1, COL2 FROM TABLE1
COL1 COL2
1 Tier 1 (QL) (ST) (PA)
2 Tier 2 (QL) (ST)
3 Tier 1 (ST)
4 Tier 1 (ST) (PA)
5 Tier 1
-----------------------------------------------------
INSERT INTO TABLE2 (COL1,COL2,QTY_FLG,STP_THPY_FLG,PRIOR_AUTHN_FLG)
SELECT COL1,COL2, , , FROM TABLE1
------------------------------------------------------
SELECT * FROM TABLE2
COL1 COL2 QL_FLG ST_FLG PA_FLG
1 Tier 1 (QL) (ST) (PA) 1 1 1
2 Tier 2 (QL) (ST) 1 1 0
3 Tier 1 (ST) 0 1 0
4 Tier 1 (ST) (PA) 0 1 1
5 Tier 1 0 0 0
Basically from Table1(COL1, COL2) i want to copy data to Table2(Col1,Col2)..while inserting, i want check if COL2 is having any specification ((QL) (ST) (PA)) accoringly flag should be updated with 1 to the respective _FLG column,if there is no specility (example : 5th row) all the _FLG columns should be updated with 0.
Please let me know if there are any related posts.
Thanks
|
|
|
|
|
Looking at your example it is not possible to identify the col1/2 content. Assuming the fist number is not in col1 I would break it into 2 queries
The first query should group by the col1 and count the instances of the different specifications.
The second query should build the string based on that information.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Hi, Thank you so much for your reply.
Could you please find the table information clearly with each row and data separated with pipe delimeter (|).
As you suggested, Group the column by Col1.. As the Col1 Information is unique.. i think it will give the same data even after grouping.
SELECT COL1, COL2 FROM TABLE1
COL1|COL2
1| Tier 1 (QL) (ST) (PA)
2| Tier 2 (QL) (ST)
3| Tier 1 (ST)
4| Tier 1 (ST) (PA)
5| Tier 1
-----------------------------------------------------
INSERT INTO TABLE2 (COL1,COL2,QL_FLG,ST_FLG,PA_FLG)
SELECT COL1,COL2, , , FROM TABLE1
------------------------------------------------------
SELECT * FROM TABLE2
COL1| COL2| QL_FLG| ST_FLG| PA_FLG
1| Tier 1 (QL) (ST) (PA)| 1 |1| 1
2| Tier 2 (QL) (ST)| 1 |1| 0
3| Tier 1 (ST)| 0 |1 |0
4| Tier 1 (ST) (PA)| 0| 1| 1
5| Tier 1 |0 |0 |0
|
|
|
|
|
Ok so this is not structured data it is rubbish, you are going to have to parse col2 of table1 into proper data. Then create a normalised table where you have 3 records for 1|Tier1.
After that it is a positional or possibly a pivot.
OR
You can create a cursor spit to process each record into the desired table2 format by parsing the col2 of table1.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
HI, Thank you for the reply.
Actaully the data is pretty much structered... since i cannot able to show the data here with proper horizantal and vertical bars,i kept Pipe delimeter so that data of each column will be separate from each other.
My source table is having rows in arround 500 - 600K using a cursor will be costly in performence point of view.
Could u please suggest if there is any other alternative.
|
|
|
|
|
HI, this is how it worked for me
INSERT INTO TABLE2 (COL1,
COL2,
QL_FLG,
ST_FLG,
PA_FLG)
(SELECT COL1,
COL2,
DECODE(INSTR(COL2,'QL'),0,0,1) QL_FLG,
DECODE(INSTR(COL2,'ST'),0,0,1) ST_FLG,
DECODE(INSTR(COL2,'PA'),0,0,1) PA_FLG
FROM TABLE1);
Thank you so much for your help.
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
