1 2 Previous Next 23 Replies Latest reply on Dec 12, 2017 3:11 PM by Frank Kulash Go to original post
      • 15. Re: Need to handle NVL in FROM clause query
        3482623

        attached the results in word document.

         

        we are not getting the results for quote revision id 5 & 6  which is highlighted in the results from above query.

         

        please let me know if required any other details.

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

          Hi,

          3482623 wrote:

           

          attached the results in word document.

          ...

          Not everyone who wants to help you can (or will) open attachments.  Post everything right in this forum.

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

            Results:

             

            RESALE  |  COST | Adj. | Quote_Rev_idPM  |    NM       |  PR |   PQ    | PNQ

            1000        |  1000   |  12  |     1                  |   0     |   -0.8       |    1   |     1     |     0

            2200        |  2200   |  22  |     2                  |   0     |   -0.82     |    1   |     1     |     0

            1200        |  1200   |  12  |     3                  |   0     |   -0.66     |    1   |     1     |     0

            3900        |  3900   |   2   |     4                  |   0     |    0.05     |    1   |     1     |     0

            0              |  2800   |  22  |     5                  |   0     |   -70450  |    1   |     0     |     1

            1500        |  1500   |  12  |     6                  |   0     |   -0.53     |    1   |     1     |     0

             

            Need above results including records of quote_rev_id 5 & 6 but with given query we are not getting results for quote_rev_id 5 & 6.

             

            please suggest.

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

              Results:

               

              RESALE  |  COST | Adj. | Quote_Rev_idPM  |    NM       |  PR |   PQ    | PNQ

              1000        |  1000   |  12  |     1                  |   0     |   -0.8       |    1   |     1     |     0

              2200        |  2200   |  22  |     2                  |   0     |   -0.82     |    1   |     1     |     0

              1200        |  1200   |  12  |     3                  |   0     |   -0.66     |    1   |     1     |     0

              3900        |  3900   |   2   |     4                  |   0     |    0.05     |    1   |     1     |     0

              0              |  2800   |  22  |     5                  |   0     |   -70450  |    1   |     0     |     1

              1500        |  1500   |  12  |     6                  |   0     |   -0.53     |    1   |     1     |     0

               

              Need above results including records of quote_rev_id 5 & 6 but with given query we are not getting results for quote_rev_id 5 & 6.

               

              please suggest.

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

                please ignore previous results:

                 

                The results should be as per below table including quote_rev_id 5 & 6,

                 

                Results:

                 

                RESALE  |  COST | Adj. | Quote_Rev_idPM  |    NM       |  PR |   PQ    | PNQ

                1000        |  1000   |  12  |     1                  |   0     |   -0.8       |    1   |     1     |     0

                2200        |  2200   |  22  |     2                  |   0     |   -0.82     |    1   |     1     |     0

                1200        |  1200   |  12  |     3                  |   0     |   -0.66     |    1   |     1     |     0

                3900        |  3900   |   2   |     4                  |   0     |    0.05     |    1   |     1     |     0

                0              |  0         |  0     |     5                  |   0     |   0  |    1   |     0     |     1

                0              |  0         |   0    |    6                  |   0     |  0     |    1   |     1     |     0

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

                  please let me know if any other details required.

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

                    Hi,

                    3482623 wrote:

                     

                    please let me know if any other details required.

                    Always explain how you get the desired results.

                    What does each row of the output represent?  What keys are unique, and what are the one-to-any (or many-to-many) relationships?

                     

                    In this particular case, it seems you want 0 in some columns (like resale) even when numbers are available in the tables.  How do you decide whether the output should be 0 or not?

                    Is PM always 0?  Then why include it in the query?

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

                      What does each row of the output represent?  What keys are unique, and what are the one-to-any (or many-to-many) relationships?

                      -- each row of the output represents Resale, Cost, Total adjustments, Quote revision id, Parts Margin, Net margin, Total Parts Requested, Total Parts Quoted, Total Parts Not Quoted values. Quote revision id will be unique for each and every row.

                       

                      In this particular case, it seems you want 0 in some columns (like resale) even when numbers are available in the tables.  How do you decide whether the output should be 0 or not?

                      Is PM always 0?  Then why include it in the query?

                      -- For quote revision ids 5 &6 there are no values from Query A hence resale, cost, total adjustments, parts margin, net margin values will be 0 for those ids. PM won't be 0 always, it has the values. but we need to display quote revision ids 5 & 6 values in such way that if for particular quote revision ids, values will not be there in Query A then take latest quote revision ids from Query B and display the records such as total parts requested, total parts quoted and total parts not quoted for those ids.

                       

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

                        Hi,

                        3482623 wrote:

                         

                        What does each row of the output represent? What keys are unique, and what are the one-to-any (or many-to-many) relationships?

                        -- each row of the output represents Resale, Cost, Total adjustments, Quote revision id, Parts Margin, Net margin, Total Parts Requested, Total Parts Quoted, Total Parts Not Quoted values. Quote revision id will be unique for each and every row.

                        You're describing what each column represents.

                        I need to know what each row represents, for example "Each row of output represents a unique value of t1.quote_revision_id.  I need exactly 1 row of output for each distinct value of t1.quote_revision_id, regardless of what the other columns of t1 contain, and regardless of what is in the other tables."

                         

                        If that happens to be what you want, then you don't want conditions like this in WHERE clauses:

                        AND         t1.resale is not null

                         

                        A WHERE clause excludes rows from the result set.  If you want all rows from t1 to be included in the result set, then you want to include them, not exclude them.  You may want conditions like the one above to apply to certain columns (e.g., display 0 in place of total_cost when that condition is true), so you might use conditions like that in a CASE expression, but not in a WHERE clause.

                         

                        With just a few changes, the query you posted in reply #13 can get the results you posted in reply #19:

                        SELECT   NVL ( a.mask
                                     , ROUND(SUM(NVL(a.total_resale,0)),2)
                                     )  AS total_resale,
                                 NVL ( a.mask
                                     , ROUND(SUM(NVL(a.total_cost,0)),2)
                                     )  AS total_cost,
                                 NVL ( a.mask
                                     , ROUND(SUM(nvl(e.total_adjustments,0)),2)
                                     )  AS 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,
                                 NVL ( a.mask
                                     , ROUND(SUM(nvl(a.net_margin,0)),2)
                                     )  AS 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
                                     ,    MIN ( CASE
                                                    WHEN  t1.resale    IS NULL
                                                    OR    t1.item_tag  = '5.0'
                                                 -- OR    ...  -- if needed
                                                    THEN  0
                                                END
                                              )   AS mask
                                     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
                                 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
                        ,        a.mask
                        order by a.quote_revision_id;        

                        Output:

                                                                                          TOTAL_
                                                QUOTE_                    TOTAL_  TOTAL_  NO_OF_
                                       TOOTAL_      RE                    NO_OF_  NO_OF_   PART_
                        TOTAL_  TOTAL_  ADJUST  VISION  PARTS_    NET_    PARTS_  PARTS_    NOT_
                        RESALE    COST   MENTS     _ID  MARGIN  MARGIN REQUESTED  QUOTED  QUOTED
                        ------ ------- ------- ------- ------- ------- --------- ------- -------
                          1000    1000      12       1       0     -.8         1       1       0
                          2200    2200      22       2       0    -.82         1       1       0
                          1200    1200      12       3       0    -.66         1       1       0
                          3900    3900       2       4       0     .05         1       1       0
                             0       0       0       5       0       0         1       0       1
                             0       0       0       6       0       0         1       1       0

                         

                        This gets the right results from this sample data.  I can't say if it meets your requirements, because I don't understand what those requirements are.  With any other data this may not produce the results you want.

                         

                        Whatever your requirements are, making small changes to the query in reply #13 is not the right way to meet them.  I don;'t know what you need to do, but I'll bet you don't need to do 6 different GROUP BYs to do it, especially when 4 of the 6 are   GROUP BY quote_revision_id.  Also, it looks like you're computing  

                         

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

                         

                        over and over again.  That can't be right; there must be a better way, where you only need to do it once.  Even if the query above does meet your requirements, it's a bad query.  It's inefficient, but that may not be important to you.  What's more important is that it's very hard to maintain.  If you ever need to change the query at all, it will be hard to test the changes, and extremely hard to tell if, when you fixed one thing, you didn't break something else.

                        1 2 Previous Next