Information générale
Catégorie:
Base de données, Tables, Vues, Index et syntaxe SQL
PS......
Here is the actual SQL select code I am using to build the final table to print from. You will notice the commented "Browse" commands. When I un-comment them, I can see that all of the temporary cursors have the exact data I am looking for in them. It is only the very last SQL select, which dumps everything into an actual table, that comes up empty, and then, only on NEW client records if they are not the first new client added in the datasession. And as always, all existing clients print fine. All of the SQL selects that build temporary cursors are selecting from physical tables, not views.
Sincerely,
Jerry Brunet
********************************
clear typeahead
this.selectcursor()
lovalues=this.GetValues()
SELECT Clients.cid, COUNT(Dependen.cid) AS ndependents;
FROM heap!clients INNER JOIN heap!dependen ;
ON Clients.cid = Dependen.cforeignkey;
WHERE (dependen.ddateactive<=?applicatformview.dappldate ;
OR dependen.ddateactive={}) ;
AND (Dependen.ddateended = { / / };
OR (Dependen.ddateended >= ?applicatformview.dappldate));
AND clients.cid=lovalues.cforeignkey ;
GROUP BY Clients.cid ;
INTO CURSOR ndependentaview NOFILTER
select ndependentaview
* BROWSE
SELECT Clients.cid, Income.cid, Income.cforeignkey, Income.cgrandfather,;
Income.csource, Income.cperiod, Income.namount, Income.nincome01,;
Income.nincome03, Income.nincome12, Income.dstartdate, Income.denddate,;
I_source.cincomesource, Period.cperiodname;
FROM heap!period,;
heap!clients LEFT OUTER JOIN heap!income;
INNER JOIN heap!i_source ;
ON Income.csource = I_source.cincomesourcecode ;
ON Clients.cid = Income.cgrandfather;
WHERE Income.cperiod = Period.cperiodcode AND Income.lcsbgcount=.T. ;
AND (income.dstartdate ={} OR income.dstartdate<=?applicatformview.dappldate) ;
AND (Income.denddate = {} ;
AND clients.cid=lovalues.cforeignkey ;
OR (Income.denddate >= ?applicatformview.dappldate AND clients.cid=lovalues.cforeignkey)) ;
INTO CURSOR incomeactive17aview NOFILTER
select incomeactive17aview
* BROWSE
SELECT Applicat.cid, Applicat.cforeignkey, Applicat.ccsbgyear,;
Applicat.cfilenumber, Applicat.dappldate,;
SUM(Incomeactive17aview.nincome01) AS yincome01,;
SUM(Incomeactive17aview.nincome03) AS yincome03,;
SUM(Incomeactive17aview.nincome12) AS yincome12, Clients.cssnr,;
Clients.cfirstname, Clients.clastname, Clients.cid;
FROM heap!applicat INNER JOIN heap!clients;
LEFT OUTER JOIN incomeactive17aview ;
ON Clients.cid = Incomeactive17aview.cid_a ;
ON Applicat.cforeignkey = Clients.cid;
WHERE applicat.cid=lovalues.cid ;
GROUP BY Clients.cid, Applicat.cid ;
INTO CURSOR incomeactive18aview NOFILTER
SELECT incomeactive18aview
* BROWSE
SELECT Applicat.cid, Dependen.cid, Dependen.cforeignkey, Dependen.cssnr,;
ALLTRIM(Dependen.cfirstname)+" "+ALLTRIM(Dependen.clastname) AS cname,;
Incomeactive17aview.nincome01, Incomeactive17aview.nincome03,;
Incomeactive17aview.nincome12, Incomeactive17aview.cincomesource,;
Incomeactive17aview.cperiodname, Incomeactive17aview.cid_b AS cid_c;
FROM heap!applicat INNER JOIN heap!clients;
INNER JOIN heap!dependen ;
LEFT OUTER JOIN incomeactive17aview ;
ON Dependen.cid = Incomeactive17aview.cforeignkey ;
ON Clients.cid = Dependen.cforeignkey ;
ON Applicat.cforeignkey = Clients.cid;
WHERE applicat.cid=lovalues.cid ;
GROUP BY Dependen.cid, Incomeactive17aview.cid_b;
ORDER BY Dependen.cforeignkey, Dependen.cid ;
INTO CURSOR INCOMEACTIVE19aview NOFILTER
select incomeactive19aview
* BROWSE
SELECT Incomeactive19aview.cssnr, Incomeactive19aview.cname,;
Incomeactive19aview.nincome01, Incomeactive19aview.nincome03,;
Incomeactive19aview.nincome12, Incomeactive19aview.cincomesource, Incomeactive19aview.cid_c, ;
Incomeactive19aview.cperiodname, Dependen.nage, Dependen.dbirthdate,;
Dependen.cgender, Dependen.ldisabled, Dependen.lveteran,;
Dependen.chealthinsurance, Dependen.cethnicity, Dependen.ceducation, dependen.ddateactive, ;
Dependen.ddateended, Educate.cname, Ethnicit.cethnicname, Health.chealthname,;
Clients.cfamilynumber, Clients.csite, Clients.cssnr, Clients.cfirstname,;
Clients.clastname, Clients.caddress, Clients.capartment, Clients.ccity,;
Clients.cstate, Clients.czip, Clients.cphone, Clients.ctown,;
Clients.ccounty, Clients.cmailaddress, Clients.cmailapt,;
Clients.cmailcity, Clients.cmailzip, Clients.lfoodstamp,;
Clients.cfamilytype, Clients.chousingtype, Clients.cfarmcode,;
Clients.lurbanarea, Clients.lpublichousing, Clients_townsview.cname, County.ccountyname,;
Sites.cname, Family.cfamilytype, House.chousingtype, Farmer.cfarmname,;
Applicat.cid, Applicat.ccsbgyear, Applicat.cfilenumber,;
Applicat.dappldate, Applicat.dmodidate, Dependen.cid, dependen.lheadofhouse,;
Ndependentaview.ndependents, Incomeactive18aview.yincome01,;
Incomeactive18aview.yincome03, Incomeactive18aview.yincome12 ;
FROM heap!educate, heap!ethnicit, heap!health, heap!county, heap!sites,;
heap!family, heap!house, heap!farmer, ndependentaview, ;
incomeactive18aview, heap!clients_townsview, ;
incomeactive19aview ;
INNER JOIN heap!dependen ;
ON Incomeactive19aview.cid_b = Dependen.cid ;
INNER JOIN heap!clients ;
ON Dependen.cforeignkey = Clients.cid ;
INNER JOIN heap!applicat ;
ON Clients.cid = Applicat.cforeignkey ;
WHERE Educate.ccode = Dependen.ceducation ;
AND Ethnicit.cethniccode = Dependen.cethnicity;
AND Health.chealthcode = Dependen.chealthinsurance;
AND County.ccountycode = Clients.ccounty;
AND Sites.ccode = Clients.csite;
AND Family.cfamilycode = Clients.cfamilytype;
AND House.chousingcode = Clients.chousingtype;
AND Farmer.cfarmcode = Clients.cfarmcode;
AND Ndependentaview.cid = Clients.cid;
AND Incomeactive18aview.cid_b = Clients.cid;
AND Clients_townsview.cid = Clients.cid;
AND applicat.cid=lovalues.cid ;
GROUP BY Applicat.cid, Dependen.cid, Incomeactive19aview.cid_c;
ORDER BY Applicat.ccsbgyear, Applicat.cfilenumber, Dependen.cid ;
INTO TABLE c:\report_csbgapp1view.dbf
select report_csbgapp1view
index on NOT lheadofhou to c:\report_csbgapp1view.ndx
* browse
RETURN
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