Fixing state, country missing with other records
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 7 of 7

Thread: Fixing state, country missing with other records

Hybrid View

  1. #1
    Join Date
    Oct 2004
    Location
    Warminster,PA
    Posts
    10

    Question Fixing state, country missing with other records

    I created a Select to identify the records with missing state and country. Then I upped it to find a valid substitute from another record. Unfortunately some cities are in more than one state and BOTH city state combos are showing up in my select. I tried a HAVING clause but I wasn't allowed to use it there in either place. [HAVING distinct STATE = 1].

    My data is collected from Aol Instant Messenger. Some of the fields are name, address, city, state, country, nickname, and other items. There is NO data checking on their part, so the data is really messy. I have to ignore many records because the fields contain worthless information. I created a table, STATES containing state or province name and their abbreviation for US and Canada. I use that to cut down on the junky words found in the STATE field.

    I am now working on my 9i CERT, and discovered long ago that just doing work in a database gets the learning juices flowing a lot faster. I study a while then when inspired with a complex challenge, I go for it and learn a bunch. Then resume the study.

    1. How can I modify this select so that the dual stated cities are not included ?

    2. Then I want to update the records. I see I have a nested select and don't quite see how to translate this action into an update yet, are you able to make some suggestions?


    Here is the updt.sql file contents that I have so far.
    SET LINESIZE 160;
    SET PAUSE ON;

    select A.FNAM, A.LNAM, A.CITY, B.STATE, B.CNTRY, A.SNAM, A.STATE
    from AIM A, (SELECT DISTINCT STATE, CNTRY, CITY FROM AIM
    WHERE STATE IS NOT NULL
    AND CNTRY IS NOT NULL
    AND CITY IS NOT NULL
    AND STATE IN (SELECT ST FROM STATES WHERE ST=UPPER(STATE))
    --HAVING COUNT(DISTINCT STATE) = 1
    ) B
    WHERE A.CITY = B.CITY
    AND (A.STATE IS NULL AND A.CNTRY IS NULL)
    AND A.CITY IS NOT NULL
    --AND COUNT(B.STATE) = 1;


    --GROUP BY STATE, CNTRY DISTINCT

  2. #2
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    In the in-line view select count column also for the state.

    Tamil

  3. #3
    Join Date
    Oct 2004
    Location
    Warminster,PA
    Posts
    10
    I must be thinking of the wrong thing with your suggestion of in-line view.

    select A.FNAM, A.LNAM, A.CITY, B.STATE, B.CNTRY, A.SNAM, A.STATE, COUNT(A.STATE)
    *
    ERROR at line 1:
    ORA-00937: not a single-group group function


    SQL>
    I added COUNT(A.STATE) to the select in orig note and got the same message that I keep getting.... not a single group function. I must STILL be barking up the wrong tree. NOTE, the -- in original note had gotten this same error when I tried them also.

  4. #4
    Join Date
    Jun 2005
    Location
    London, UK
    Posts
    159
    How about this (untested):

    Code:
    SELECT a.fnam
         , a.lnam
         , a.city
         , b.state
         , b.cntry
         , a.snam
         , a.state
    FROM   aim a
         , ( SELECT a2.city
                  , MIN(a2.state) AS state
                  , MIN(a2.cntry) AS cntry
             FROM   aim a2
             WHERE  a2.state IS NOT NULL
             AND    a2.cntry IS NOT NULL
             AND    a2.city  IS NOT NULL
             AND    a2.state IN
                    ( SELECT st
                      FROM   states
                      WHERE  st = UPPER(a2.state) )
             GROUP BY a2.city HAVING COUNT(*) = 1
           ) b
    WHERE  a.city IS NOT NULL
    AND    a.state IS NULL
    AND    a.cntry IS NULL
    AND    b.city = a.city;

  5. #5
    Join Date
    Oct 2004
    Location
    Warminster,PA
    Posts
    10
    Hey, Hey!!!

    I tried the untested code. I originally got 34 records selected. 3 duplicated twice. The new select got only 11, all a subset of the 34.

    I made one change: FROM COUNT(*) = 1 TO COUNT(*) > 0 and the select now comes up with 31 !!! Now I need to take the time to understand what I did and why it works that way. Thanks WilliamR for the clue to break the mental block!

  6. #6
    Join Date
    Jun 2005
    Location
    London, UK
    Posts
    159
    The inline view with GROUP BY city HAVING COUNT(*) = 1 represents all unique cities. If you change it to HAVING COUNT(*) > 1, it becomes a view of all non-unique cities. Presumably there are more unique city names than non-unique ones.

  7. #7
    Join Date
    Oct 2004
    Location
    Warminster,PA
    Posts
    10
    That is why I wanted to study further because that bothered me also. Here is what I developed to get what I really needed. I wanted all the unique cities that are NOT redundant in more than one state. i.e. if I had a Cleveland in two different states then OUT it goes. If I have Apex NC in five records, but ONLY one state then I keep it in select.

    Here is what I got now:
    SELECT A3.CITY, COUNT(A3.STATE) FROM (
    SELECT a2.city
    , MIN(a2.state) AS state
    , MIN(a2.cntry) AS cntry
    , COUNT(A2.STATE), COUNT(*)
    FROM aim a2
    WHERE a2.state IS NOT NULL
    AND a2.cntry IS NOT NULL
    AND a2.city IS NOT NULL
    AND a2.state IN
    ( SELECT st
    FROM states
    WHERE st = UPPER(a2.state) )
    and A2.CITY LIKE 'A%' -- temp to keep small
    and A2.CITY > 'Ao' -- temp to keep small
    GROUP BY a2.city,A2.STATE HAVING COUNT(*) > 0
    ) A3
    GROUP BY A3.CITY
    HAVING COUNT(A3.STATE) = 1;

    If I make this > 0 then I get 39 items like another select does. Then when I set to = 0, I get 15 and that matches the predicted count. NOW I am getting much closer.

    NOW I stuffed it into an update and made a mess of things. Any ideas while I rollback and break alternately ?

    I want to update the cities that have NO state and NO cntry with info from the above select just built. Hmm I am missing cntry from the select, may need to adjust.

Posting Permissions

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


Click Here to Expand Forum to Full Width