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

In the in-line view select count column also for the state.

Tamil

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.

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;

---

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!

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.

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.