Here is the code, without the coomented executable lines, it is as it was when I ran it from the dataenvironment. When run as a prg, or one section at a time (without the WHERE for all but the first table) I get prompted whether or not I wish to overright the existing view, except for the ones that count, from them I get "Server returned no result set" Comments with *-* are explanitory notes regarding this problem. For other forms, I would need a different data environment, but with the where cluases this is the evironment I want for this form (Military.scx)
****************************************************
PRIVATE lSuccess
open database data/data1
&& Parent table to all others, filtered to military only for form military.scx
CREATE SQL VIEW master REMOTE ;
CONNECTION pers_data SHARE ;
AS SELECT Master.SEQUENCE_, Master.SERNUM, Master.INITIALS, Master.L_NAME,;
Master.MOC_SEQ, Master.RANK_SEQ, Master.HOME_PHONE1, Master.LOCAL,;
Master.STREET, Master.CITY, Master.POSTALCODE, Master.L_UPDATE,;
Master.SECTION_SEQ, Master.MARITAL_SEQ, Master.FORCE_SEQ,;
Master.SECURITY_SEQ, Master.SECURITY_NUM, Master.SECURITY_EXPDATE,;
Master.COS_DATE, Master.CLEAR_DATE, Master.ENLIST_DATE, Master.TXP_DATE,;
Master.GENDER, Master.DOB, Master.PAGER, Master.POSITION_SEQ,;
Master.UIC_SEQ, Master.PSID, Master.F_NAME, Master.HOME_PHONE2;
FROM persdat.MASTER Master;
WHERE master.FORCE_SEQ < 3 ;
ORDER BY Master.SEQUENCE_
&&USE master IN 0 ALIAS master
&&go top
&&lSuccess=CURSORSETPROP("Buffering", 5, "master")
&&lSuccess=CURSORSETPROP("FetchAsNeeded",.F.,"master")
&&lSuccess=CURSORSETPROP("SendUpdates",.T.,"Master")
*-* These have been tried with and without the WHERE clause to no availe
*-* This and the next two tables are the ones that VFP claims have been changed
&& Military table, 1:1 to master
CREATE SQL VIEW military REMOTE ;
CONNECTION pers_data SHARE ;
AS SELECT Military.SEQUENCE_, Military.PMAIN_SEQ, Military.ELECT_SEQ,;
Military.OLANG1, Military.OLANG2, Military.PLANG, Military.SLANG, Military.REL_SEQ;
FROM persdat.MILITARY Military;
WHERE Military.PMAIN_SEQ = Master.Sequence_
&&USE military IN 1 ALIAS military
&&lSuccess=CURSORSETPROP("Buffering", 5, "military")
&&lSuccess=CURSORSETPROP("FetchAsNeeded",.F.,"military")
&&lSuccess=CURSORSETPROP("SendUpdates",.T.,"Military")
&&lSuccess=CURSORSETPROP("Prepared",.T.,"MIlitary")
&& Meds table, 1:1 to master
CREATE SQL VIEW meds REMOTE ;
CONNECTION pers_data SHARE ;
AS SELECT Meds.SEQUENCE_, Meds.PERS_SEQ, Meds.MED_DATE, Meds.VISION,;
Meds.COLOUR, Meds.HEARING, Meds.GEOGRAPHICAL, Meds.AIR_FACTOR,;
Meds.LUPDATE, Meds.RTEST_DATE, Meds.OCCUPATIONAL;
FROM persdat.MEDS Meds ;
WHERE Meds.pers_seq = master.sequence_
&&USE meds IN 2 ALIAS meds
&&lSuccess=CURSORSETPROP("Buffering", 5, "meds")
&&lSuccess=CURSORSETPROP("FetchAsNeeded",.F.,"meds")
&&lSuccess=CURSORSETPROP("SendUpdates",.T.,"Meds")
&&lSuccess=CURSORSETPROP("Prepared",.T.,"Meds")
&& CFExpress, 1:1 to master
CREATE SQL VIEW cfexpress REMOTE ;
CONNECTION pers_data SHARE ;
AS SELECT Cfexpress.SEQUENCE, Cfexpress.PERS_SEQ, Cfexpress.TEST_DATE, Cfexpress.STATUS;
FROM persdat.CFEXPRESS Cfexpress ;
WHERE Cfexpress.pers_seq = master.sequence_
&&USE cfexpress IN 3 ALIAS cfexpress
&&lSuccess=CURSORSETPROP("Buffering", 5, "cfexpress")
&&lSuccess=CURSORSETPROP("FetchAsNeeded",.F.,"cfexpress")
&&lSuccess=CURSORSETPROP("SendUpdates",.T.,"cfexpress")
&&lSuccess=CURSORSETPROP("Prepared",.T.,"cfexpress")
&& The next four cursors have data processing handled within form code
&& Primary language, 1:1 to military
CREATE SQL VIEW perslang REMOTE ;
CONNECTION pers_data SHARE ;
AS SELECT Perslang.SEQUENCE, Perslang.LANG_SEQ, Perslang.ABILITY, perslang.TESTDATE;
FROM persdat.PERSLANG Perslang ;
WHERE perslang.sequence = military.PLANG
&&USE perslang IN 4 ALIAS perslang
&&lSuccess=CURSORSETPROP("Buffering", 5, "perslang")
&&lSuccess=CURSORSETPROP("FetchAsNeeded",.F.,"perslang")
&&lSuccess=CURSORSETPROP("SendUpdates",.T.,"perslang")
&&lSuccess=CURSORSETPROP("Prepared",.T.,"perslang")
&& Secondary language, 1: 0/1 to military
CREATE SQL VIEW perslang1 REMOTE ;
CONNECTION pers_data SHARE ;
AS SELECT Perslang.SEQUENCE, Perslang.LANG_SEQ, Perslang.ABILITY, perslang.TESTDATE;
FROM persdat.PERSLANG Perslang ;
WHERE perslang.sequence = military.SLANG
&&use perslang1 IN 5 ALIAS perslang1
&&lSuccess=CURSORSETPROP("Buffering", 5, "perslang1")
&&lSuccess=CURSORSETPROP("FetchAsNeeded",.F.,"perslang1")
&&lSuccess=CURSORSETPROP("SendUpdates",.T.,"perslang1")
&&lSuccess=CURSORSETPROP("Prepared",.T.,"perslang1")
&& Dependants, 1:M from Master also used by civilians
CREATE SQL VIEW Dependant_view REMOTE ;
CONNECTION pers_data SHARE ;
AS SELECT Dependant_view.SEQUENCE_, Dependant_view.PERS_KEY,;
Dependant_view.SPF_NAME, Dependant_view.SPS_SIN_NUM,;
Dependant_view.YOB, Dependant_view.RELATE_SEQ,;
Dependant_view.SP_LANAME, Dependant_view.STREET, Dependant_view.CITY,;
Dependant_view.P_CODE, Dependant_view.PHONE, Dependant_view.REL_CODE,;
Dependant_view.REL_NAME, Dependant_view.SEX;
FROM persdat.DEPENDANT_VIEW Dependant_view ;
WHERE Dependant_view.PERS_KEY = master.SEQUENCE_
&&use dependant_view IN 6 ALIAS dependant_view
&&lSuccess=CURSORSETPROP("Buffering", 5, "Dependant_view")
&&lSuccess=CURSORSETPROP("FetchAsNeeded",.F.,"Dependant_view")
&&lSuccess=CURSORSETPROP("SendUpdates",.T.,"Dependant_view")
&&lSuccess=CURSORSETPROP("Prepared",.T.,"Dependant_view")
&& Vehicles, 1:M from Master, also used by civilians
CREATE SQL VIEW Vehicles REMOTE ;
CONNECTION pers_data SHARE ;
AS SELECT Vehicles.SEQUENCE_, Vehicles.LIC_NUM, Vehicles.MODEL,;
Vehicles.VEH_COLOUR, Vehicles.PROV_REG, Vehicles.PERS_SEQ, Vehicles.MAKE;
FROM persdat.VEHICLES Vehicles ;
WHERE Vehicles.PERS_SEQ = Master.SEQUENCE_
&&USE vehicles IN 7 ALIAS vehicles
&&lSuccess=CURSORSETPROP("Buffering", 5, "Vehicles")
&&lSuccess=CURSORSETPROP("FetchAsNeeded",.F.,"Vehicles")
&&lSuccess=CURSORSETPROP("SendUpdates",.T.,"Vehicles")
&&lSuccess=CURSORSETPROP("Prepared",.T.,"Vehicles")
&& The rest are read only tables used to populate dropdown boxes
&& The two strictly military ones first
&& Religion, M:1 from military
CREATE SQL VIEW Religion REMOTE ;
CONNECTION pers_data SHARE ;
AS SELECT Religion.SEQUENCE_, Religion.RELIGION, Religion.REL_ABR;
FROM persdat.RELIGION Religion
&&use Religion IN 8 ALIAS Religion
&&go top
&& Electoral districts, M:1 from military
CREATE SQL VIEW Elctoral REMOTE ;
CONNECTION pers_data SHARE ;
AS SELECT Elctoral.SEQUENCE, Elctoral.DIST_NAME, Elctoral.ELCT_NUM;
FROM persdat.ELCTORAL Elctoral
&&USE Electoral IN 9 ALIAS electorl
&& Now the common ones
&& Rank first, M:1, table also holds civilian titles
CREATE SQL VIEW Rank REMOTE ;
CONNECTION pers_data SHARE ;
AS SELECT Rank.sequence_, Rank.Rank_Name ;
FROM persdat.RANK Rank
&&use rank IN 10 ALIAS rank
&&go top
&& Forcetype, M:1, could be an array, but s$%t happens, so in table
CREATE SQL VIEW forcetype REMOTE ;
CONNECTION pers_data SHARE ;
AS SELECT forcetype.sequence, forc_type ;
FROM persdat.forcetype forcetype
&&use forcetype IN 11 ALIAS forcetype
&&go top
&& Security clearences. M:1
CREATE SQL VIEW Security REMOTE ;
CONNECTION pers_data SHARE ;
AS SELECT security.secure_key, security.clearence ;
FROM persdat.security Security
&&use security IN 12 ALIAS security
&&go top
&& Languages table M:1 FROM MASTER
CREATE SQL VIEW languages REMOTE ;
CONNECTION pers_data SHARE ;
AS SELECT languages.sequence, languages.lang_abbr ;
from persdat.languages Languages
&&use languages IN 13 ALIAS languages
&&go top
&& positions table, M:1 from master
CREATE SQL VIEW positions REMOTE ;
CONNECTION pers_data SHARE ;
AS SELECT positions.sequence,positions.position,positions.title ;
positions.position_num ;
FROM persdat.positions positions
&&use positions IN 14 ALIAS positions
&&go top
&& MOC (military occupation codes) table M:1 from Master
CREATE SQL VIEW moc REMOTE ;
CONNECTION pers_data SHARE ;
AS SELECT moc.sequence, moc.moc_code, moc.moc_name ;
from persdat.moc moc
&& use moc IN 15 ALIAS moc
&& go top
&& Units table, M:1 from master
CREATE SQL VIEW units REMOTE ;
CONNECTION pers_data SHARE ;
AS SELECT units.sequence_, units.uic_code, units.unit ;
FROM persdat.units units
&& use units IN 16 ALIAS units
&& go top
&& Sections table, M:1 from master
CREATE SQL VIEW sections REMOTE ;
CONNECTION pers_data SHARE ;
AS SELECT sections.sequence_, sections.sec_name, sections.sec_code ;
FROM persdat.sections sections
&& use sections IN 17 ALIAS sections
&& go top
&& Marital table (marital status, may change ) M:1 from master
CREATE SQL VIEW marital REMOTE ;
CONNECTION pers_data SHARE ;
AS SELECT marital.sequence, marital.mar_code, marital.mar_name ;
FROM persdat.marital marital
&&use marital IN 18 ALIAS marital
&&go top
&&select master
Précédent
Suivant
Répondre
Voir le fil de ce thread
Voir le fil de ce thread à partir de ce message seulement
Voir tous les messages de ce thread
Voir tous les messages de ce thread à partir de ce message seulement