hi all,
consider the following table and data
CREATE TABLE ER (
name varchar2(20),
ln_no number,
quarter_00 number,
quarter_01 number,
quarter_02 number,
quarter_03 number,
quarter_04 number,
quarter_05 number,
quarter_06 number,
quarter_07 number,
quarter_08 number,
quarter_09 number
)
insert into er values('test',10, 2, 11,21,31,41,51,61,71,81,91)
I am trying to find the column with the minimum value. in the example above, quarter_01 has min value of all the quarters.
for this, I am unpivoting the data and then I do a min. here is my query
SELECT name, line_no, min(quarter), MIN(pr_value)
FROM (
select *
from er
UNPIVOT INCLUDE NULLS (pr_value FOR quarter IN (quarter_00 AS 0, quarter_01 AS 1, quarter_02 AS 2, quarter_03 AS 3, quarter_04 AS 4, quarter_05 AS 5, quarter_06 AS 6, quarter_07 AS 7, quarter_08 AS 8, quarter_09 AS 9));
) WHERE QUARTER <> 0 and line_no = 10
group by name, line_no,
for some reason, this query doesn't work. I get an unknow command error. when I execute the query inside the parenthesis () by itself then it works. it looks like I cannot select from a subquery that has a unpivot clause.
can someone tell me how to get around this or fix the query so that I get the min quarter with the minimum value where quarter > 0
my output should be:
name line_no quarter pr_value
======================
test 10 1 11
if I insert the following
insert into er values('test',10, 2, 91,51,31,41,51,61,71,81,91)
then my output should be
name line_no quarter pr_value
======================
test 10 4 31
im using oracle 11.2.0.4.0.