Thread: Hi

    #1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2013
    Posts
    2
    Rep Power
    0

    Hi


    I have a table like below:

    tableA

    aid des
    1 concrete
    2 wood
    3 straw
    4 plastic
    5 fiber glass
    6 other

    tableB

    bid material
    01 1
    02 2
    03 3
    01 2
    01 2
    02 3
    01 5

    The result I need when updating another table with this info is:

    TableC

    edw_id bid Requirement

    021 1 concrete, wood, fiber glass
    032
    025
    123
    521


    I do not want :

    concrete, concrete, concrete, wood, wood, fiber glass

    SO far I am using the following but since I am dealing with hundreds of column that has the same material, when using listagg() from oracle 11.2g, they column width is too wide to fit into the required column.


    update eris_data_work e set E.flex37 =
    (select
    LISTAGG(CM.des, ',') WITHIN GROUP (ORDER BY CM.des) AS casing_material
    from CODE_CASING_MATERIAL CM, TBLCASING CA
    where CM.code=CA.MATERIAL and CA.well_id=E.owner_oid AND CM.DES IS NOT NULL
    GROUP BY CA.well_id)
    where E.source='WWIS_ON'


    I have even used the regexp_count() to try to eliminate duplicates however I have had no success so far.

    Any help or advice as to how to proceed is much appreciated.

    thanks in advance.

    anita
  2. #2
  3. Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Apr 2012
    Location
    spaceBAR Central
    Posts
    229
    Rep Power
    42
    Post sql to create tables and sample data.
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2013
    Posts
    2
    Rep Power
    0
    The below query has been modified to accomodate the sample data i provided in my earlier statement.

    Sorry I did not realise that I didnt modify the query to suit the sample data.

    The table that I have to update belongs to a data base with hundreds of table thus the need for "E.source = 'WWIS_ON' ".
    The table B has appro 400 records with the same value for material in some instances therefore, I end up getting the "value too big error" which is understandable because the database deisgn is not to accomodate values that are repeated into the fields. However, due to my need to have all the values that may b link to 1 bid concat in the same field to show user that there is more than 1 material allowcated there I need to find a way to remove duplicate values while applying necessary concat.


    My apologies and thanks in advcance for all suggestions and help provided.



    tableA

    aid des1 concrete
    2 wood
    3 straw
    4 plastic
    5 fiber glass
    6 other

    tableB

    bid material
    01 1
    02 2
    03 3
    01 2
    01 2
    02 3
    01 5

    The result I need when updating another table with this info is:

    TableC

    edw_id bid Requirement

    021 01 concrete, wood, fiber glass
    031 01 concrete, wood, fiber glass
    032 02 wood, straw





    update TableC e set E.Requirement=
    (select
    LISTAGG(CM.des, ',') WITHIN GROUP (ORDER BY CM.des) AS casing_material
    from TableA CM, TBLCASING CA
    where CM.aid=CA.material and CA.bid=E.edw_id AND CM.DES IS NOT NULL
    GROUP BY CA.bid)
    where E.source='WWIS_ON'


    Thanks and I really need some guidance for this because I am new to oracle development so pls go easy on me.
  6. #4
  7. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2013
    Posts
    5
    Rep Power
    0
    Hi Anita,
    I see only 3 table's des in the space. but you have used even E.
    Can you mention how did you use regexp_count().

    I too had a similar issue, and got it resolved. If you can give the query that you use and the table descriptions, I will try to help.

    - Epi R.
  8. #5
  9. Contributing User
    Devshed Novice (500 - 999 posts)

    Join Date
    Sep 2006
    Posts
    859
    Rep Power
    388

    Cool


    Originally Posted by AnitaP
    The below query has been modified to accomodate the sample data i provided in my earlier statement.

    Sorry I did not realise that I didnt modify the query to suit the sample data.

    . . . E t c . . .

    Thanks and I really need some guidance for this because I am new to oracle development so pls go easy on me.
    Perhaps this will help you:
    Code:
    SQL> WITH tablea (aid, des)
      2       AS (SELECT 1, 'concrete'    FROM DUAL UNION ALL
      3           SELECT 2, 'wood'        FROM DUAL UNION ALL
      4           SELECT 3, 'straw'       FROM DUAL UNION ALL
      5           SELECT 4, 'plastic '    FROM DUAL UNION ALL
      6           SELECT 5, 'fiber glass' FROM DUAL UNION ALL
      7           SELECT 6, 'other' FROM DUAL)
      8     , tableb (bid, material)
      9       AS (SELECT '01', 1 FROM DUAL UNION ALL
     10           SELECT '02', 2 FROM DUAL UNION ALL
     11           SELECT '03', 3 FROM DUAL UNION ALL
     12           SELECT '01', 2 FROM DUAL UNION ALL
     13           SELECT '01', 2 FROM DUAL UNION ALL
     14           SELECT '02', 3 FROM DUAL UNION ALL
     15           SELECT '01', 5 FROM DUAL)
     16     , tablec (edw_id, bid, requirement)
     17       AS (SELECT '021', '01', 'concrete, wood, fiber glass' FROM DUAL UNION ALL
     18           SELECT '031', '01', 'concrete, wood, fiber glass' FROM DUAL UNION ALL
     19           SELECT '032', '02', 'wood, straw' FROM DUAL)
     20  SELECT edw_id, cm.bid
     21       , LISTAGG ( des, ', ') WITHIN GROUP (ORDER BY cm.aid) AS casing_material
     22    FROM tablec ca
     23       , (SELECT DISTINCT ca.aid, cb.bid, ca.des
     24            FROM tablea ca, tableb cb
     25           WHERE ca.aid = cb.material
     26             AND ca.des IS NOT NULL) cm
     27   WHERE cm.bid = ca.bid
     28   GROUP BY edw_id, cm.bid
     29  /
    
    EDW_ID    BID    CASING_MATERIAL
    --------- ------ ----------------------------------------
    021       01     concrete, wood, fiber glass
    031       01     concrete, wood, fiber glass
    032       02     wood, straw
    Last edited by LKBrwn_DBA; September 30th, 2013 at 01:49 PM.

IMN logo majestic logo threadwatch logo seochat tools logo