SQL from the 4GL interface

[jgauthier]

Hey Folks,

I have a data clean up task before me. In short: I need to go through my customer master finding all the of the blank countries.

The I will take the state of each of those countries, and determine which country it is most likely, based on other customers.

A breif sample of this would be an SQL query:
select country, count(*) FROM custaddr WHERE state = "CA" GROUP BY country.

I realize, that's not doing exactly what I described, but it reports the countries by state. I can easily see that this is most likely a USA state.

My problem is now this:
Can I get the data returned from a select like that called from a 4gl program?
OR, how can I EASILY implement that same select in progress code.

To code it in progress sounds like a nightmare to me.

Thanks,

 

[TomBascom]

FUNCTION likely_country RETURNS character (
     INPUT stateabbrev AS CHARACTER ).

  IF LOOKUP( stateabbrev, "CA,AZ,..." ) > 0 THEN
	RETURN "USA".
      ELSE
	     /* figure out how to assign a country using the state field... */

RETURN.

FOR EACH custaddr WHERE custaddr.country = "":
  custaddr.country = likely_country( custaddr.state ).
END.

Given your description of the problem I see no particular advantage to the proposed sorting & grouping by "state" unless you're implying a manual review and update?

Or perhaps you could build a temp table of countries that have a certain state code and count the number of hits each non-blank country gets? Then use the "best" count as a guess? To do that would take two passes through the data unless you sort it so that blank countries are last. Something like:

/* completely untested, off the top of my head psuedo code... */

DEFINE TEMP-TABLE ttx
     FIELD country AS CHARACTER
     FIELD state    AS CHARACTER
     FIELD xcount  AS INTEGER.

FOR EACH custaddr BREAK-BY custaddr.country DESCENDING:

  IF cust-addr.country > "" THEN
	DO:
	  FIND ttx WHERE
		         ttx.country = cust-addr.country AND
		         ttx.state    = cust-addr.state.
	  IF NOT AVAILABLE( ttx ) THEN
		DO:
		              CREATE ttx.
		              ASSIGN
			                ttx.country = custaddr.country
			 ttx.state    = custaddr.state
		              .
	           END.
	         ttx.xcount = ttx.xcount + 1.
        END.
      ELSE
	DO:
	        FOR EACH ttx WHERE ttx.state = custaddr.state:
		         i = max( i, ttx.xcount ).
		         IF i = ttx.xcount THEN custaddr.country = ttx.country.
	  END.
       END.

END.