SQL & PL/SQL

Hi All,

We have a requirement to perform arithmetic operations on values of different rows of a column. Here is an illustration of the requirement.

There are 3 columns, Plant, Mineral and Value. Each plant has a set of minerals and each mineral has a value. Each plant can have any number of minerals, out of which M1 is mandatory. The requirement is to report a column(column 4 below), whose value is the value associated to the mineral - value of mineral M1 of that plant.

So coded the below SQL fulfills the requirement, but I used a temporary set q2. Is there any way I can put the same logic in a single query without inner queries as well. I'm trying to use a ROW_NUMBER() analytic function in LAG()'s offset but SQL throws an error as, nested analytic functions is not supported. Is there any other way to user dynamic offset value in LAG () without using analytic functions?

Current Working Solution

WITH q1 AS (

SELECT 'A' plant,'M1' mineral,'1' value FROM DUAL UNION ALL

SELECT 'A','M2','2' FROM DUAL UNION ALL

SELECT 'A','M3','3' FROM DUAL UNION ALL

SELECT 'A','M4','4' FROM DUAL UNION ALL

SELECT 'A','M5','5' FROM DUAL UNION ALL

SELECT 'A','M6','6' FROM DUAL UNION ALL

SELECT 'A','M7','7' FROM DUAL UNION ALL

SELECT 'A','M8','8' FROM DUAL UNION ALL

SELECT 'B','M1','21' FROM DUAL UNION ALL

SELECT 'B','M2','12' FROM DUAL UNION ALL

SELECT 'B','M3','13' FROM DUAL UNION ALL

SELECT 'B','M4','14' FROM DUAL UNION ALL

SELECT 'B','M5','15' FROM DUAL UNION ALL

SELECT 'B','M6','16' FROM DUAL UNION ALL

SELECT 'B','M7','17' FROM DUAL UNION ALL

SELECT 'B','M8','18' FROM DUAL UNION ALL

SELECT 'C','M1','181' FROM DUAL UNION ALL

SELECT 'C','M2','112' FROM DUAL UNION ALL

SELECT 'C','M3','113' FROM DUAL UNION ALL

SELECT 'C','M4','114' FROM DUAL UNION ALL

SELECT 'C','M5','115' FROM DUAL UNION ALL

SELECT 'C','M6','116' FROM DUAL UNION ALL

SELECT 'C','M7','117' FROM DUAL UNION ALL

SELECT 'C','M8','118' FROM DUAL

),q2 AS (

SELECT q1.\*,

       ROW\_NUMBER() OVER(PARTITION BY

        plant

        ORDER BY

            CASE

                WHEN mineral = 'MOISTURE' THEN 1

                ELSE

                    2

            END

    ) row\_id

FROM

    q1

) SELECT q2.*,

   LAG(

    q2.value,

    q2.row\_id - 1,

    0

) OVER(PARTITION BY

    plant

    ORDER BY

        q2.row\_id

) lag\_val

FROM

q2;