1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Distinct Values

Discussion in 'Oracle Apps Technical' started by kiran.marla, May 14, 2014.

  1. kiran.marla

    kiran.marla Forum Genius

    Messages:
    403
    Likes Received:
    52
    Trophy Points:
    505
    Location:
    Khammam
    Hi ,
    There is one requirement..
    Please have a look at the below items..


    ITEM DESCRIPTION
    ------------------------------------------------
    04761054 Battery charger
    0A0012268P04 Shaft Sun - Heavy Duty
    0L1169 BOLT (5/8-11*3.75)
    0L1351 BOLT
    0L1558 BOLT(1/2-20*3.25)
    0L2070 BOLT
    0S0509 BOLT
    0S1566 BOLT (5/8 - 11 X 1.50)
    0S1569 BOLT (5/8-11*4.00)


    I need a query to display only these items , like different item numbers with same description...

    ITEM DESCRIPTION
    ------------------------------------------------
    0L1351 BOLT
    0L2070 BOLT
    0S0509 BOLT
     
  2. krasnoslobodtsev_si

    krasnoslobodtsev_si Forum Genius

    Messages:
    626
    Likes Received:
    129
    Trophy Points:
    810
    Location:
    Russian Federation
    Hi.

    For items 0L1351,0L2070 ,0S0509
    the description contains only BOLT ?

    Code (SQL):


    WITH
    simple_data AS
    (

    SELECT '04761054' ITEM ,  'Battery charger' descr FROM dual UNION ALL
    SELECT '0A0012268P04','Shaft Sun - Heavy Duty' FROM dual UNION ALL
    SELECT '0L1169','BOLT (5/8-11*3.75)' FROM dual UNION ALL
    SELECT '0L1351','BOLT' FROM dual UNION ALL
    SELECT '0L1558','BOLT(1/2-20*3.25)' FROM dual UNION ALL
    SELECT '0L2070','BOLT' FROM dual UNION ALL
    SELECT '0S0509','BOLT' FROM dual UNION ALL
    SELECT '0S1566','BOLT (5/8 - 11 X 1.50)' FROM dual UNION ALL
    SELECT '0S1569' ,'BOLT (5/8-11*4.00)' FROM dual
    )
    SELECT
         s.item,
         s.descr
    FROM simple_data s
    WHERE s.descr ='BOLT';


    SQL>
     
    ITEM         DESCR
    ------------ ----------------------
    0L1351       BOLT
    0L2070       BOLT
    0S0509       BOLT
     
    SQL>
     
     
  3. kiran.marla

    kiran.marla Forum Genius

    Messages:
    403
    Likes Received:
    52
    Trophy Points:
    505
    Location:
    Khammam
    Thanks Sergey,

    There are 1 lakh items... we do not know for which item description will be same...
     
  4. krasnoslobodtsev_si

    krasnoslobodtsev_si Forum Genius

    Messages:
    626
    Likes Received:
    129
    Trophy Points:
    810
    Location:
    Russian Federation
    ...
    Code (SQL):

    WITH
    simple_data AS
    (
    SELECT '04761054' ITEM ,  'Battery charger' descr FROM dual UNION ALL
    SELECT '0A0012268P04','Shaft Sun - Heavy Duty' FROM dual UNION ALL
    SELECT '0L1169','BOLT (5/8-11*3.75)' FROM dual UNION ALL
    SELECT '0L1351','BOLT' FROM dual UNION ALL
    SELECT '0L1558','BOLT(1/2-20*3.25)' FROM dual UNION ALL
    SELECT '0L2070','BOLT' FROM dual UNION ALL
    SELECT '0S0509','BOLT' FROM dual UNION ALL
    SELECT '0S1566','BOLT (5/8 - 11 X 1.50)' FROM dual UNION ALL
    SELECT '0S1569' ,'BOLT (5/8-11*4.00)' FROM dual
    )
    ,res AS
    (
    SELECT
         s.item,
         s.descr,
         COUNT(*) OVER (partition BY  s.descr) cc
    FROM simple_data s
    )
    SELECT
         r.item,
         r.descr
     FROM res r
    WHERE cc > 1;


    SQL>

    SQL>
     
    ITEM         DESCR
    ------------ ----------------------
    0L1351       BOLT
    0L2070       BOLT
    0S0509       BOLT
     

     



    Is possible duplicating in the data : item and description ?
    For example :
    '0L1351','BOLT'
    '0L1351','BOLT'
     
  5. kiran.marla

    kiran.marla Forum Genius

    Messages:
    403
    Likes Received:
    52
    Trophy Points:
    505
    Location:
    Khammam
    Thanks Sergey,

    but i know where find the logic which satisfies my requirement... The data which i have provided is for sample only which you are using WITH ...

    I want the data with different item numbers with same description , from item master in oracle apps r12...
     
  6. krasnoslobodtsev_si

    krasnoslobodtsev_si Forum Genius

    Messages:
    626
    Likes Received:
    129
    Trophy Points:
    810
    Location:
    Russian Federation
    necessary will be to replace the table name.
    If the name of fields differ, then necessary will replaced too ...

    Code (SQL):


    SELECT
         item,
         descr
     FROM (
           SELECT
                 s.item,
                 s.descr,
                 COUNT(*) OVER (partition BY  s.descr) cnt_rec
           FROM your_table  s
          )
    WHERE cnt_rec > 1;