Results 1 to 4 of 4
  1. #1
    Join Date
    Apr 2012
    Posts
    2

    Unanswered: PL/SQL Exception - duplicated data

    Hello, I'm new to this forum and am also new to Oracle. I hope anyone can be kindly to help me to solve my problem. Billion thanks.

    so I merge two tables (customer address & postcode) into one (so the postcode match the suburb of the address), which resulting in many duplicated rows due to the suburbs aren't unique. eg, Burwood NSW & Burwood VIC.
    I was asked to put the duplicated suburb into the exceptions table and comment "undefined".

    Do I need to create new variables to raise and handle the exception? if so, what the codes should be like?

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    post SQL & results that show the duplicates
    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
    Apr 2012
    Posts
    2
    I wrote,

    SELECT Customer.ID, Customer.Name, Customer.Suburb, PostOffice.State, PostOffice.Postcode
    FROM Cutomer, PostOffice
    WHERE Customer.Suburb = PostOffice.Suburb;

    attached is the result. so I need to make the 4 duplicated rows exception and comment "undefined"
    Attached Thumbnails Attached Thumbnails sample.JPG  

  4. #4
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    Code:
    WITH adr1 
         AS (SELECT customer.id, 
                    customer.NAME, 
                    customer.suburb, 
                    postoffice.state, 
                    postoffice.postcode 
             FROM   customer, 
                    postoffice 
             WHERE  customer.suburb = postoffice.suburb), 
         adr2 
         AS (SELECT customer.id, 
                    customer.NAME, 
                    customer.suburb, 
                    postoffice.state, 
                    postoffice.postcode 
             FROM   customer, 
                    postoffice 
             WHERE  customer.suburb = postoffice.suburb) 
    SELECT adr1.id, 
           adr1.NAME, 
           adr2.suburb 
    FROM   adr1, 
           adr2 
    WHERE  adr1.id = adr2.id 
           AND adr1.NAME = adr2.NAME 
           AND adr1.suburb = adr2.suburb 
           AND adr1.state != adr2.state 
    
    /
    if you had provided CREATE TABLE & INSERT, I could have actually tested the SQL
    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.

Posting Permissions

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