Is there an easier way to create the final cursor _AGENTS (see code below)? I would like to make it an updatable view in a database.
CODE EXAMPLE:
*:-----------------------------------------------
*: Create a cursor containing the years in the
*: agents table.
*:-----------------------------------------------
SELECT DISTINCT ;
agents.cyr+agents.cmth AS pk ;
FROM (cDataDir)+"AGENTS.DBF" ;
INTO CURSOR _yrs ;
ORDER BY cyr ASC
*:-----------------------------------------------
*: Create a cursor containing the primary key to
*: match against the lookup cursor.
*:-----------------------------------------------
SELECT stcode+cyr+cmth AS pk,* FROM (cDataDir)+"AGENTS.DBF" INTO CURSOR _source ORDER BY 1
USE IN agents
*:-----------------------------------------------
*: Create a lookup cursor containing a state
*: record for every year found in the agents
*: table.
*:-----------------------------------------------
SELECT states.stcode+_yrs.pk AS pk FROM (cDataDir)+"STATES.DBF",_yrs INTO CURSOR _lookup ORDER BY 1
USE IN states
USE IN _yrs
*:-----------------------------------------------
*: Give me a RIGHT OUTER JOIN matching every
*: record in the lookup cursor. We will have NULL
*: values because not every state code has a
*: record in the AGENTS table and there could
*: be a year missing from the group of state
*: codes in the AGENTS table.
*:-----------------------------------------------
SELECT ;
SUBSTR(_LOOKUP.pk,1,3) AS Stcode,;
SUBSTR(_LOOKUP.pk,4,4) AS Cyr,;
SUBSTR(_LOOKUP.pk,8,2) AS Cmth,;
_SOURCE.Agt_totals,;
_SOURCE.Agt_lvl_40,;
_SOURCE.Agt_lvl_30,;
_SOURCE.Agt_lvl_20,;
_SOURCE.Agt_lvl_16,;
_SOURCE.Agt_lvl_15,;
_SOURCE.Agt_lvl_14,;
_SOURCE.Agt_lvl_13,;
_SOURCE.Agt_lvl_12,;
_SOURCE.Agt_lvl_11,;
_SOURCE.Agt_lvl_10,;
_SOURCE.Agt_lvl_01,;
_SOURCE.Agt_flex_1,;
_SOURCE.Agt_flex_2,;
_SOURCE.Agt_hisphh,;
_SOURCE.Agt_hisphp,;
_SOURCE.Agt_hispnh,;
_SOURCE.Agt_svc1,;
_SOURCE.Agt_svc2,;
_SOURCE.Agt_svc3,;
_SOURCE.Agt_svc4,;
_SOURCE.Agt_svc5,;
_SOURCE.Agt_age1,;
_SOURCE.Agt_age2,;
_SOURCE.Agt_age3,;
_SOURCE.Agt_age4,;
_SOURCE.M_totals,;
_SOURCE.M_lvl_40,;
_SOURCE.M_lvl_30,;
_SOURCE.M_lvl_20,;
_SOURCE.M_lvl_16,;
_SOURCE.M_lvl_15,;
_SOURCE.M_lvl_14,;
_SOURCE.M_lvl_13,;
_SOURCE.M_lvl_12,;
_SOURCE.M_lvl_11,;
_SOURCE.M_lvl_10,;
_SOURCE.M_lvl_01,;
_SOURCE.M_flex_1,;
_SOURCE.M_flex_2,;
_SOURCE.M_hisp_hh,;
_SOURCE.M_hisp_hp,;
_SOURCE.M_hisp_nh,;
_SOURCE.M_svc1,;
_SOURCE.M_svc2,;
_SOURCE.M_svc3,;
_SOURCE.M_svc4,;
_SOURCE.M_svc5,;
_SOURCE.M_age1,;
_SOURCE.M_age2,;
_SOURCE.M_age3,;
_SOURCE.M_age4;
FROM _SOURCE RIGHT OUTER JOIN _LOOKUP ;
ON _SOURCE.PK = _LOOKUP.PK;
INTO CURSOR _agents ;
ORDER BY _LOOKUP.Pk

USE IN _source
USE IN _lookup
SELECT _agents

END OF CODE EXAMPLE

Thanks