1 2 Previous Next 23 Replies Latest reply on Dec 12, 2017 3:11 PM by Frank Kulash

    Need to handle NVL in FROM clause query

    3482623

      Hi,

       

      we have a requirement as mentioned below with the details,

       

      Create Table:

       

      create table emp_test (emp_id number, cost number,resale number,item_tag varchar2(10))

      /

      create table dep_test (emp_id number,dept_id number)

      /

       

      Insert:

       

      insert into emp_test values (1,100,100,'1.0')

      /

      insert into emp_test values (2,200,200,'2.0')

      /

      insert into emp_test values (3,300,300,'3.0')

      /

      insert into emp_test values (4,400,400,'5.0')

      /

      insert into emp_test values (5,500,500,'5.0')

      /

       

       

      insert into dep_test values (1,10)

      /

      insert into dep_test values (2,20)

      /

      insert into dep_test values (3,30)

      /

      insert into dep_test values (4,40)

      /

      insert into dep_test values (1,10)

      /

      insert into dep_test values (2,20)

      /

      insert into dep_test values (1,10)

      /

      insert into dep_test values (1,10)

      /

       

      commit

      /

       

      Query:

       

      SELECT a.emp_id,

        a.total,

        b.item_tag

      FROM

        (SELECT e.emp_id,

          (e.cost*e.resale) total

        FROM emp_test e,

          dep_test d

        WHERE e.emp_id  = d.emp_id

        AND e.item_tag <> '5.0'

        AND e.resale   IS NOT NULL

        )a,

        (SELECT e.emp_id,

          COUNT(e.item_tag) item_tag

        FROM emp_test e,

          dep_test d

        WHERE e.emp_id = d.emp_id

        GROUP BY e.emp_id

        )b

      WHERE a.emp_id(+) = b.emp_id

      order by a.emp_id;

       

      Result:

       

      emp_id | Total | Item_Tag

      1            | 1000 |  4

      1            | 1000 |  4

      1            | 1000 |  4

      1            | 1000 |  4

      2            | 4000 |  2

      2            | 4000 |  2

      3            | 9000 |  1

      Null       | Null  |  1

      Null       | Null  |  1

       

      But we need result as we emp_id is null then it should fetch emp_id from query B and result should be as below,

       

      Result:

      emp_id | Total | Item_Tag

      1            | 1000 |  4

      1            | 1000 |  4

      1            | 1000 |  4

      1            | 1000 |  4

      2            | 4000 |  2

      2            | 4000 |  2

      3            | 9000 |  1

      4            | Null  |  1

      5            | Null  |  1

       

      Please suggest.

       

      Thanks

        • 1. Re: Need to handle NVL in FROM clause query
          L. Fernigrini

          Using the old style JOIN format:

           

          SELECT nvl(a.emp_id,b.emp_id) AS emp_id,
            a.total,
            b.item_tag
          FROM
            (SELECT e.emp_id,
              (e.cost*e.resale) total
            FROM emp_test e,
              dep_test d
            WHERE e.emp_id  = d.emp_id
            AND e.item_tag <> '5.0'
            AND e.resale  IS NOT NULL
            )a
            FULL OUTER JOIN
            (SELECT e.emp_id,
              COUNT(e.item_tag) item_tag
            FROM emp_test e,
              dep_test d
            WHERE e.emp_id = d.emp_id(+)
            GROUP BY e.emp_id
            ) b
          ON a.emp_id = b.emp_id
          order by a.emp_id;
          

           

          Edit: Forgot to include the output:

           

          • 2. Re: Need to handle NVL in FROM clause query
            mathguy

            Not sure what the problem is, that this query is supposed to solve, since you didn't state it. (And I am not going to reverse-engineer it back from the query to the problem statement.)

             

            In any case, the problem you are asking about seems to have to do with having NULL in the result of an outer join query. If that's all you need help with, then in the outer query, at the top of your whole query, SELECT b.emp_id, not a.emp_id. Then make the same change in ORDER BY.

            • 3. Re: Need to handle NVL in FROM clause query
              L. Fernigrini

              Using the ANSI standard JOIN syntax:

               

              SELECT nvl(a.emp_id,b.emp_id) AS emp_id,

                a.total,

                b.item_tag

              FROM

                (SELECT e.emp_id,

                  (e.cost*e.resale) total

                FROM emp_test e

                  JOIN dep_test d ON e.emp_id  = d.emp_id

                WHERE e.item_tag <> '5.0'

                  AND e.resale  IS NOT NULL )a

              FULL OUTER JOIN

                (SELECT e.emp_id,

                  COUNT(e.item_tag) item_tag

                FROM emp_test e

                   LEFT JOIN dep_test d ON e.emp_id = d.emp_id

                GROUP BY e.emp_id

                ) b ON a.emp_id = b.emp_id

              order by a.emp_id;

               

              I marked in bold the sections I changed from the  original query

              • 4. Re: Need to handle NVL in FROM clause query
                3482623

                if we have more than 2 queries then can we use full outer join with it. I mean if we have query C and Query D also then how can we use full outer join and NVL?

                 

                please suggest.

                • 5. Re: Need to handle NVL in FROM clause query
                  Solomon Yakobson

                  SELECT  e.emp_id,

                          CASE

                            WHEN e.item_tag <> '5.0' THEN e.cost * e.resale

                          END total,

                          COUNT(e.item_tag) OVER(PARTITION BY e.emp_id) item_tag

                    FROM  emp_test e,

                          dep_test d

                    WHERE e.emp_id = d.emp_id(+)

                  /

                   

                   

                      EMP_ID      TOTAL  ITEM_TAG

                  ---------- ---------- ----------

                           1      10000          4

                           1      10000          4

                           1      10000          4

                           1      10000          4

                           2      40000          2

                           2      40000          2

                           3      90000          1

                           4                     1

                           5                     1

                   

                  9 rows selected.

                   

                  SQL>

                   

                  SY.

                  • 6. Re: Need to handle NVL in FROM clause query
                    Cookiemonster76

                    The full outer join should be a left right join.

                    The left join should be a join.

                     

                    And the nvl should be replace with b.emp_id as Mathguy already mentioned.

                    a.emp_id is either null or equal to b.emp_id so the nvl is waste of time.

                    • 7. Re: Need to handle NVL in FROM clause query
                      Cookiemonster76

                      I suspect the outer join in your version doesn't belong there. The link between emp and dep is mandatory in the original.

                       

                      EDIT: The OP does have empi_id 5 in the expected output though. OP really needs to explain the logic.

                      • 8. Re: Need to handle NVL in FROM clause query
                        Frank Kulash

                        Hi,

                        3482623 wrote:

                         

                        Hi,

                         

                        we have a requirement as mentioned below with the details,

                         

                        Create Table:

                         

                        create table emp_test (emp_id number, cost number,resale number,item_tag varchar2(10))

                        /

                        create table dep_test (emp_id number,dept_id number)

                        /

                         

                        Insert:

                         

                        insert into emp_test values (1,100,100,'1.0')

                        /

                        insert into emp_test values (2,200,200,'2.0')

                        /

                        insert into emp_test values (3,300,300,'3.0')

                        /

                        insert into emp_test values (4,400,400,'5.0')

                        /

                        insert into emp_test values (5,500,500,'5.0')

                        /

                         

                         

                        insert into dep_test values (1,10)

                        /

                        insert into dep_test values (2,20)

                        /

                        insert into dep_test values (3,30)

                        /

                        insert into dep_test values (4,40)

                        /

                        insert into dep_test values (1,10)

                        /

                        insert into dep_test values (2,20)

                        /

                        insert into dep_test values (1,10)

                        /

                        insert into dep_test values (1,10)

                        /

                         

                        commit

                        /

                         

                        Query:

                         

                        SELECT a.emp_id,

                        a.total,

                        b.item_tag

                        FROM

                        (SELECT e.emp_id,

                        (e.cost*e.resale) total

                        FROM emp_test e,

                        dep_test d

                        WHERE e.emp_id = d.emp_id

                        AND e.item_tag <> '5.0'

                        AND e.resale IS NOT NULL

                        )a,

                        (SELECT e.emp_id,

                        COUNT(e.item_tag) item_tag

                        FROM emp_test e,

                        dep_test d

                        WHERE e.emp_id = d.emp_id

                        GROUP BY e.emp_id

                        )b

                        WHERE a.emp_id(+) = b.emp_id

                        order by a.emp_id;

                         

                        Result:

                         

                        emp_id | Total | Item_Tag

                        1 | 1000 | 4

                        1 | 1000 | 4

                        1 | 1000 | 4

                        1 | 1000 | 4

                        2 | 4000 | 2

                        2 | 4000 | 2

                        3 | 9000 | 1

                        Null | Null | 1

                        Null | Null | 1

                        Are you sure those are the results you're getting from the given sample data?  When I run that query with that data, I get:

                        EMP_ID  TOTAL ITEM_TAG
                        ------ ------ --------
                             1  10000        4
                             1  10000        4
                             1  10000        4
                             1  10000        4
                             2  40000        2
                             2  40000        2
                             3  90000        1
                                             1

                        The totals are 10 times greater than what you posted, and there is only 1 row with NULL emp_id.

                        But we need result as we emp_id is null then it should fetch emp_id from query B and result should be as below,

                         

                        Result:

                        emp_id | Total | Item_Tag

                        1 | 1000 | 4

                        1 | 1000 | 4

                        1 | 1000 | 4

                        1 | 1000 | 4

                        2 | 4000 | 2

                        2 | 4000 | 2

                        3 | 9000 | 1

                        4 | Null | 1

                        5 | Null | 1

                         

                        Please suggest.

                         

                        Thanks

                        Here's one way to get the results you want:

                        SELECT    e.emp_id

                        ,         CASE

                                       WHEN  e.item_tag  <> '5.0'

                                       THEN  e.cost * e.resale

                                  END                           AS total

                        ,         COUNT (e.item_tag) OVER (PARTITION BY  e.emp_id)

                                                                AS item_tag

                        FROM             emp_test  e

                        LEFT OUTER JOIN  dep_test  d  ON  d.emp_id  = e.emp_id

                        ORDER BY  e.emp_id

                        ;

                        Output:

                        EMP_ID  TOTAL ITEM_TAG
                        ------ ------ --------
                             1  10000        4
                             1  10000        4
                             1  10000        4
                             1  10000        4
                             2  40000        2
                             2  40000        2
                             3  90000        1
                             4               1
                             5               1

                        I recommend ANSI join syntax for all joins, especially outer joins.

                        • 9. Re: Need to handle NVL in FROM clause query
                          L. Fernigrini

                          SELECT b.emp_id,

                            a.total,

                            b.item_tag

                          FROM

                            (SELECT e.emp_id,

                              (e.cost*e.resale) total

                             FROM emp_test e

                               JOIN dep_test d ON e.emp_id  = d.emp_id

                             WHERE e.item_tag <> '5.0'

                              AND e.resale   IS NOT NULL )a

                          RIGHT OUTER JOIN

                            (SELECT e.emp_id,

                              COUNT(e.item_tag) item_tag

                             FROM emp_test e JOIN dep_test d ON e.emp_id = d.emp_id

                             GROUP BY e.emp_id

                            ) b ON a.emp_id = b.emp_id

                          order by a.emp_id;

                           

                          Output:

                           

                          Row with emp_id = 5 is missing...

                          • 10. Re: Need to handle NVL in FROM clause query
                            3482623

                            in above query, if we have more than 2 queries. I mean other than Query A and Query B then how can we right from clause query?

                             

                            please suggest.

                            • 11. Re: Need to handle NVL in FROM clause query
                              Cookiemonster76

                              It would be a lot easier if you just showed us all the queries you're trying to combine and told us what they do and what you expect once they're combined.

                              • 12. Re: Need to handle NVL in FROM clause query
                                Frank Kulash

                                Hi,

                                3482623 wrote:

                                 

                                in above query, if we have more than 2 queries. I mean other than Query A and Query B then how can we right from clause query?

                                 

                                please suggest.

                                Sorry, I don't understand what you want.

                                 

                                What Cookiemonster said in reply #11 is what you should do whenever you have a problem: post a little sample data (CREATE TABLE and INSERT statements, relevant columns only) from all tables involved, so that the people who want to help you can re-create the problem and test their ideas.
                                Also post the exact results you want from that data, and an explanation of how you get those results from that data, with specific examples.

                                Always say which version of Oracle you're using (for example, 12.2.0.1.0).
                                See the forum FAQ: Re: 2. How do I ask a question on the forums?

                                • 13. Re: Need to handle NVL in FROM clause query
                                  3482623

                                  Oracle Version: 12.1.0

                                   

                                  Please find below the detailed requirement and necessary details,

                                   

                                  Tables:

                                   

                                  create table t1 (quote_revision_id number, qty number,item_parts varchar2(100),cost number, resale number, item_tag varchar2(10))

                                  /

                                  create table t2 (opportunity_id number,rebate number)

                                  /

                                  create table t3 (revision_id number,quote_id number)

                                  /

                                  create table t4 (quote_id number,opportunity_id number)

                                  /

                                  create table t5 (opportunity_id number,shipping_fee_taxes number, agent_commissions number, total_program_costs number, service_fees number)

                                  /

                                   

                                  Insert:

                                   

                                  insert into t1 values (1,10,'P1',100,100,null)

                                  /

                                  insert into t1 values (2,20,'P2',110,110,null)

                                  /

                                  insert into t1 values (3,10,'P3',120,120,null)

                                  /

                                  insert into t1 values (4,30,'P4',130,130,null)

                                  /

                                  insert into t1 values (5,20,'P5',140,null,null)

                                  /

                                  insert into t1 values (6,10,'P6',150,150,'5.0')

                                  /

                                   

                                   

                                  insert into t2 values (100,10)

                                  /

                                  insert into t2 values (101,20)

                                  /

                                  insert into t2 values (102,10)

                                  /

                                  insert into t2 values (103,0)

                                  /

                                  insert into t2 values (104,20)

                                  /

                                  insert into t2 values (105,10)

                                  /

                                   

                                   

                                  insert into t3 values (1,10)

                                  /

                                  insert into t3 values (2,20)

                                  /

                                  insert into t3 values (3,30)

                                  /

                                  insert into t3 values (4,40)

                                  /

                                  insert into t3 values (5,50)

                                  /

                                  insert into t3 values (6,60)

                                  /

                                   

                                   

                                  insert into t4 values (10,100)

                                  /

                                  insert into t4 values (20,101)

                                  /

                                  insert into t4 values (30,102)

                                  /

                                  insert into t4 values (40,103)

                                  /

                                  insert into t4 values (50,104)

                                  /

                                  insert into t4 values (60,105)

                                  /

                                   

                                   

                                  insert into t5 values (100,1,2,3,4)

                                  /

                                  insert into t5 values (101,1,2,3,4)

                                  /

                                  insert into t5 values (102,1,2,3,4)

                                  /

                                  insert into t5 values (103,1,2,3,4)

                                  /

                                  insert into t5 values (104,1,2,3,4)

                                  /

                                  insert into t5 values (105,1,2,3,4)

                                  /

                                   

                                  commit

                                  /

                                   

                                  Query:

                                   

                                  SELECT ROUND(SUM(NVL(a.total_resale,0)),2) total_resale,

                                           ROUND(SUM(NVL(a.total_cost,0)),2) total_cost,

                                           ROUND(SUM(nvl(e.total_adjustments,0)),2) total_adjustments,

                                             a.quote_revision_id,

                                           CASE WHEN SUM(NVL(a.total_resale,0)) <> 0  THEN

                                              ROUND(((SUM(NVL(a.total_resale,0)) -

                                                     SUM(NVL(a.total_cost,0))) /

                                                     SUM(NVL(a.total_resale,0))*100),2)

                                              ELSE 0

                                           END parts_margin,

                                           ROUND(SUM(nvl(a.net_margin,0)),2) net_margin,

                                           SUM(nvl(c.total_no_of_parts_requested,0))  total_no_of_parts_requested,

                                           SUM(nvl(d.total_no_of_parts_quoted,0)) total_no_of_parts_quoted,

                                           (SUM(nvl(c.total_no_of_parts_requested,0))- SUM(nvl(d.total_no_of_parts_quoted,0))) total_no_of_part_not_quoted

                                      FROM

                                  (SELECT  SUM(NVL(t1.resale,0) * NVL(t1.qty,0)) total_resale,

                                                    SUM(NVL(t1.cost,0) * NVL(t1.qty,0)) total_cost,

                                                    t1.quote_revision_id quote_revision_id,

                                                    CASE WHEN (SUM(nvl(t1.resale,0) * nvl(t1.qty,0)) +

                                                               SUM(nvl(t5.service_fees,0))) <> 0 THEN

                                                      (((SUM(NVL(t1.resale,0) * NVL(t1.qty,0)) +

                                                          SUM(NVL(t5.service_fees,0))) -

                                                          (SUM(NVL(t1.cost,0) * NVL(t1.qty,0)) +

                                                           (SUM(NVL(t2.rebate,0) + NVL(t5.shipping_fee_taxes,0) +

                                                               NVL(t5.agent_commissions,0) + NVL(t5.total_program_costs,0)))- (sum(nvl(t5.service_fees,0))))) /

                                                        (SUM(NVL(t1.resale,0) * NVL(t1.qty,0)) +

                                                         SUM(nvl(t5.service_fees,0))) *100) 

                                                    ELSE 0 END net_margin

                                               FROM t1 ,

                                                    t2,

                                                    t3,

                                                    t4,

                                                    t5

                                              WHERE t1.quote_revision_id = t3.revision_id AND

                                                    t3.quote_id = t4.quote_id AND

                                                    t2.opportunity_id = t4.opportunity_id AND

                                                    t5.opportunity_id = t4.opportunity_id AND

                                                    t1.resale is not null AND

                                                    ((t1.item_tag <> '5.0' and t1.item_tag <> 5) or (t1.item_tag is null)) --added constraint for excluding parts with item_tag 5 for both alphanumeric and number values of item tag

                                           GROUP BY t1.quote_revision_id)a,

                                           (SELECT  t2.opportunity_id,

                                                    MAX(t1.quote_revision_id) quote_revision_id

                                              FROM  t2,

                                                    t4,

                                                    t3,

                                                    t1

                                             WHERE  t2.opportunity_id = t4.opportunity_id AND

                                                    t4.quote_id = t3.quote_id AND

                                                    t3.revision_id = t1.quote_revision_id   

                                          GROUP BY  t2.opportunity_id) b,

                                          (SELECT  t1.quote_revision_id,

                                                    COUNT(t1.item_parts) total_no_of_parts_requested

                                              FROM  t1

                                          GROUP BY  t1.quote_revision_id) c,

                                          (SELECT  t1.quote_revision_id,

                                                  COUNT(t1.item_parts)

                                               total_no_of_parts_quoted

                                              FROM  t1

                                              where t1.COST is not null

                                              and t1.RESALE is not null

                                          GROUP BY  t1.quote_revision_id) d,

                                                  (SELECT  t2.opportunity_id,

                                                   ((SUM(NVL(t2.rebate,0) +

                                                        NVL(t5.shipping_fee_taxes,0) +

                                                        NVL(t5.agent_commissions,0) +

                                                        NVL(t5.total_program_costs,0))) -

                                                    SUM(nvl(t5.service_fees,0))) total_adjustments

                                                    FROM  t2,

                                                    t5

                                              where t2.opportunity_id = t5.opportunity_id

                                          GROUP BY  t2.opportunity_id

                                          ) e

                                        

                                  where a.quote_revision_id = b.quote_revision_id AND      

                                         c.quote_revision_id(+) = b.quote_revision_id AND

                                         d.quote_revision_id(+) = b.quote_revision_id AND

                                         e.opportunity_id(+) = b.opportunity_id

                                  group by a.quote_revision_id

                                  order by a.quote_revision_id;        

                                   

                                  we want the results for all quote revision ids.

                                   

                                  In above query  we are getting results for 4 quote revision ids only but quote revision id 5 and 6 records have not come. we need results for those 2 quote revision ids as well.

                                   

                                  Please suggest with above query how to fetch records for other 2 ids as well?

                                   

                                  thanks

                                   

                                  • 14. Re: Need to handle NVL in FROM clause query
                                    Frank Kulash

                                    Hi,

                                    3482623 wrote:

                                     

                                    ...

                                    we want the results for all quote revision ids.

                                     

                                    In above query we are getting results for 4 quote revision ids only but quote revision id 5 and 6 records have not come. we need results for those 2 quote revision ids as well.

                                     

                                    ...

                                     

                                    Please post the exact results you want from the given sample data.

                                    Describing the results is fine, but describe them in addition to (not instead of) actually posting them.

                                    Explain, with specific examples, how you get the desired results from the given data.

                                     

                                    Feel free to simplify the problem.  That is, you may need 12 columns in the output, but can you show what the problem is using only 3 or 4 columns in the output?  If so, only include those columns.  Once you learn how to get the right rows, including more columns will be easy.

                                    1 2 Previous Next