asked on Create a grouping column
Create a Derived column
Table Input
Level Order
1 000001-01
2 000001.000001
2 000001.000002
1 000002-01
2 000002-01.000001-01
2 000002-01.000002-01
2 000002-01.000003-01
Desired Output
Order Grouping_Column (Derived Column)
000001-01 000001-01
000001.000001 000001-01
000001.000002 000001-01
000002-01 000002-01
000002-01.000001-01 000002-01
000002-01.000002-01 000002-01
000002-01.000003-01 000002-01
Table Input
Level Order
1 000001-01
2 000001.000001
2 000001.000002
1 000002-01
2 000002-01.000001-01
2 000002-01.000002-01
2 000002-01.000003-01
Desired Output
Order Grouping_Column (Derived Column)
000001-01 000001-01
000001.000001 000001-01
000001.000002 000001-01
000002-01 000002-01
000002-01.000001-01 000002-01
000002-01.000002-01 000002-01
000002-01.000003-01 000002-01
Oracle Database
Last Comment
awking00
What have you tried, or is this school work?
ASKER
The derived column is what I would like to get from the input data.
For example, the level 2s' rows should be populated with 000001-01 in the derived column.
The 000002-01.000001-01 row derived column value should be 000002-01
1 000001-01
2 000001-01.000001-01
2 000001-01.000002-01
1 000002-01
2 000002-01.000001-01
I just wanted to get an idea of how should I approach this. I'm using ORACLE version 10.
For example, the level 2s' rows should be populated with 000001-01 in the derived column.
The 000002-01.000001-01 row derived column value should be 000002-01
1 000001-01
2 000001-01.000001-01
2 000001-01.000002-01
1 000002-01
2 000002-01.000001-01
I just wanted to get an idea of how should I approach this. I'm using ORACLE version 10.
I still do not understand the question.
How do you get from input to derived?
I do not see how 000002-01.000001-01 becomes 000002-01. I can assume you just take everything from the left of the period bu then I don't get how you get from 000001.000002 to 000001-01.
>>'m using ORACLE version 10.
10gR1 or 10gR2?
How do you get from input to derived?
I do not see how 000002-01.000001-01 becomes 000002-01. I can assume you just take everything from the left of the period bu then I don't get how you get from 000001.000002 to 000001-01.
>>'m using ORACLE version 10.
10gR1 or 10gR2?
I'll give you the benefit of the doubt that this is not school work but I agree it does sound a little like it.
Since you never posted back with the requirements, I'll guess at them.
All derived columns must end with a '-01'. Take the left side if a decimal exists. Add the '-01' if it does not naturally exist.
See if this gives ou what you need:
select derived || case when instr(derived,'-') = 0 then '-01' end
from (
select regexp_substr(col1,'[0-9-]
);
Since you never posted back with the requirements, I'll guess at them.
All derived columns must end with a '-01'. Take the left side if a decimal exists. Add the '-01' if it does not naturally exist.
See if this gives ou what you need:
select derived || case when instr(derived,'-') = 0 then '-01' end
from (
select regexp_substr(col1,'[0-9-]
);
ASKER CERTIFIED SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
Sorry, part of the query got cut off during cut and paste -
SQL> select ord
2 ,first_value(ord) over (partition by substr(ord,1,6) order by levl) grouping_column
3 from input;
ORD GROUPING_COLUMN
-------------------- --------------------
000001-01 000001-01
000001.000001 000001-01
000001.000002 000001-01
000002-01 000002-01
000002-01.000001-01 000002-01
000002-01.000002-01 000002-01
000002-01.000003-01 000002-01
SQL> select ord
2 ,first_value(ord) over (partition by substr(ord,1,6) order by levl) grouping_column
3 from input;
ORD GROUPING_COLUMN
-------------------- --------------------
000001-01 000001-01
000001.000001 000001-01
000001.000002 000001-01
000002-01 000002-01
000002-01.000001-01 000002-01
000002-01.000002-01 000002-01
000002-01.000003-01 000002-01
I'm not seeing them from what you posted. How do you get the derived column?
Also provide your Oracle version.