Results 1 to 8 of 8
  1. #1
    Join Date
    Nov 2004
    Posts
    51

    Unanswered: select row if exists else select all null rows

    This is what I want to do using a single query

    Table_A

    product_code Attribute Value
    null price 100
    null quantity 50
    null weight 10
    A01 price 35
    A01 quantity 25
    A02 quantity 200
    A02 quantity 10

    When I pass the product_code as 'A01', the output should be
    A01 price 35
    A01 quantity 25

    But when I pass the product_code as 'A98' (which doesnt exists), all null product_code should be shown
    null price 100
    null quantity 50
    null weight 10


    How can this be achieved?

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >This is what I want to do using a single query
    WHY? Homework assignment?
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Nov 2004
    Posts
    51
    No, its no a homework assignment
    I have to call this query from java program and it would be simple if I can call it using a single query.
    If its not possible, then I have to use 2 queries or stored procedure

    Thoughts?

  4. #4
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    How about this?

    Note: Replace 'A01' with your required product_code.
    Code:
    SELECT product_code , Attribute , Value
     FROM  (SELECT t.*
                 , RANK()
                      OVER( ORDER BY CASE
                                     WHEN product_code = 'A01' THEN
                                          1
                                     WHEN product_code IS NULL THEN
                                          2
                                     ELSE 3
                                     END  ASC
                          ) AS rank_prod_code
             FROM  Table_A AS t
           )
     WHERE rank_prod_code = 1
    ;

  5. #5
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Talking

    Or try this:
    Code:
    WITH Table_A (Product_Code, Attribute1, Value1)
         AS (SELECT null, 'price', 100 FROM DUAL   UNION ALL
             SELECT null, 'quantity', 50 FROM DUAL UNION ALL
             SELECT null, 'weight', 10 FROM DUAL UNION ALL
             SELECT 'A01', 'price', 35 FROM DUAL  UNION ALL
             SELECT 'A01', 'quantity', 25 FROM DUAL UNION ALL
             SELECT 'A02', 'quantity', 200 FROM DUAL UNION ALL
             SELECT 'A02', 'quantity', 10 FROM DUAL)
    SELECT *
      FROM Table_A
     WHERE NVL (Product_Code, '?') = NVL ('&&prod_in', '?')
        OR (NOT EXISTS
                (SELECT '?'
                   FROM Table_A
                  WHERE NVL (Product_Code, '?') = NVL ('&&prod_in', '?'))
        AND NVL (Product_Code, '?') = '?')
    ORDER BY 1;
    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

  6. #6
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    My previous query may show bad performance, because of necessity of scanning all rows(index may be useless).

    This might be more effective than my previous query.
    (But, I don't know this is better than LKBrwn_DBA's query.)
    Code:
    SELECT Product_Code
         , Attribute , Value
     FROM  (SELECT t.*
                 , RANK()
                      OVER(ORDER BY Product_Code ASC) AS rank_Product_Code
             FROM  Table_A AS t
             WHERE NVL(Product_Code , '?')
                   IN ( NVL('A01' , '?') , '?' )
           )
     WHERE rank_Product_Code = 1
     ORDER BY
           Product_Code
    ;

  7. #7
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Talking

    Quote Originally Posted by tonkuma View Post
    . . .
    (But, I don't know this is better than LKBrwn_DBA's query.)
    . . .
    Different plan cost but exactly the same statistics:
    Code:
    Statistics
    ----------------------------------------------------------
              0  recursive calls
              0  db block gets
              0  consistent gets
              0  physical reads
              0  redo size
            745  bytes sent via SQL*Net to client
            524  bytes received via SQL*Net from client
              2  SQL*Net roundtrips to/from client
              1  sorts (memory)
              0  sorts (disk)
              2  rows processed
    Explain plans:
    Code:
    -- tonkuma's query.
    ------------------------------------------------------------------------------------
    | Id  | Operation                | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
    ------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT         |         |     7 |   175 |    11  (10)| 00:00:01 |
    |*  1 |  VIEW                    |         |     7 |   175 |    11  (10)| 00:00:01 |
    |*  2 |   WINDOW SORT PUSHED RANK|         |     7 |    84 |    11  (10)| 00:00:01 |
    |   3 |    VIEW                  | TABLE_A |     7 |    84 |    10   (0)| 00:00:01 |
    |   4 |     UNION-ALL            |         |       |       |            |          |
    |*  5 |      FILTER              |         |       |       |            |          |
    |   6 |       FAST DUAL          |         |     1 |       |     2   (0)| 00:00:01 |
    |*  7 |      FILTER              |         |       |       |            |          |
    |   8 |       FAST DUAL          |         |     1 |       |     2   (0)| 00:00:01 |
    |*  9 |      FILTER              |         |       |       |            |          |
    |  10 |       FAST DUAL          |         |     1 |       |     2   (0)| 00:00:01 |
    |  11 |      FAST DUAL           |         |     1 |       |     2   (0)| 00:00:01 |
    |  12 |      FAST DUAL           |         |     1 |       |     2   (0)| 00:00:01 |
    |* 13 |      FILTER              |         |       |       |            |          |
    |  14 |       FAST DUAL          |         |     1 |       |     2   (0)| 00:00:01 |
    |* 15 |      FILTER              |         |       |       |            |          |
    |  16 |       FAST DUAL          |         |     1 |       |     2   (0)| 00:00:01 |
    ------------------------------------------------------------------------------------
    
    -- LKBrwn_DBA query:
    ----------------------------------------------------------------------------
    | Id  | Operation        | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
    ----------------------------------------------------------------------------
    |   0 | SELECT STATEMENT |         |     7 |    84 |    15   (7)| 00:00:01 |
    |   1 |  SORT ORDER BY   |         |     7 |    84 |    15   (7)| 00:00:01 |
    |*  2 |   VIEW           | TABLE_A |     7 |    84 |    14   (0)| 00:00:01 |
    |   3 |    UNION-ALL     |         |       |       |            |          |
    |   4 |     FAST DUAL    |         |     1 |       |     2   (0)| 00:00:01 |
    |   5 |     FAST DUAL    |         |     1 |       |     2   (0)| 00:00:01 |
    |   6 |     FAST DUAL    |         |     1 |       |     2   (0)| 00:00:01 |
    |   7 |     FAST DUAL    |         |     1 |       |     2   (0)| 00:00:01 |
    |   8 |     FAST DUAL    |         |     1 |       |     2   (0)| 00:00:01 |
    |   9 |     FAST DUAL    |         |     1 |       |     2   (0)| 00:00:01 |
    |  10 |     FAST DUAL    |         |     1 |       |     2   (0)| 00:00:01 |
    |  11 |    VIEW          | TABLE_A |     7 |    21 |     4   (0)| 00:00:01 |
    |  12 |     UNION-ALL    |         |       |       |            |          |
    |* 13 |      FILTER      |         |       |       |            |          |
    |  14 |       FAST DUAL  |         |     1 |       |     2   (0)| 00:00:01 |
    |* 15 |      FILTER      |         |       |       |            |          |
    |  16 |       FAST DUAL  |         |     1 |       |     2   (0)| 00:00:01 |
    |* 17 |      FILTER      |         |       |       |            |          |
    |  18 |       FAST DUAL  |         |     1 |       |     2   (0)| 00:00:01 |
    |  19 |      FAST DUAL   |         |     1 |       |     2   (0)| 00:00:01 |
    |  20 |      FAST DUAL   |         |     1 |       |     2   (0)| 00:00:01 |
    |* 21 |      FILTER      |         |       |       |            |          |
    |  22 |       FAST DUAL  |         |     1 |       |     2   (0)| 00:00:01 |
    |* 23 |      FILTER      |         |       |       |            |          |
    |  24 |       FAST DUAL  |         |     1 |       |     2   (0)| 00:00:01 |
    ----------------------------------------------------------------------------
    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

  8. #8
    Join Date
    Nov 2004
    Posts
    51
    Thanks for the response
    All of the queries work fine for me!!!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •