Search Forum Home   Visual FoxPro  Cod, sintaxa si comenzi  2 mici probleme...  2 mici probleme    1/10/2007 9:21:01 AM costin_mentor 492 posts www.accessoft.ro 2 mici probleme  (N/A) Problema 1. Descriere :am o baza de date in vfp 7.0 cu urmatoarele tabele incluse :contract, vanzari,plati,clienti. Mai am urmatoarea secventa de cod ,data1 si data2 sunt date de catre utilizator: SELECT nrcontract,sum(cantitate) as cantitate FROM vanzari WHERE !ALLTRIM(UPPER(denprodus))=='DISCOUNT' ; GROUP BY nrcontract INTO CURSOR crscantitate SELECT nrcontract,ROUND(SUM(cantitate*pret),2) as valoare FROM vanzari ; GROUP BY nrcontract INTO CURSOR crsvanzari SELECT nrcontract ,ROUND(sum(avans+valrata),2) as achitate,ROUND((sum(avans+valrata))*cursval,2) as achitatelei,; ROUND(sum(diferenta),2) as diferenta ; FROM plati INTO CURSOR crsplati urmeaza var. A: SELECT contract.nrcontract,nr_proiect,OPERATOr,nr_rate,codval,data_contr,cursval,avans,contract.CNP,; IIF(EMPTY(societate),ALLTRIM(nume)+' '+ALLTRIM(prenume),ALLTRIM(societate)) as nume,bl,ap,; crscantitate.cantitate,crsvanzari.valoare,ROUND(crsvanzari.valoare*contract.cursval,2) as vallei,; achitate ,achitatelei,diferenta ; FROM contract LEFT JOIN crsplati ON contract.nrcontract=crsplati.nrcontract ; LEFT JOIN crscantitate ON contract.nrcontract=crscantitate.nrcontract ,; clienti,crsvanzari ; where between(data_contr,data1,data2) AND contract.cnp=clienti.cnp ; AND contract.nrcontract=crsvanzari.nrcontract; into CURSOR CRScontracte readwrite REPLACE achitate WITH 0 FOR ISNULL(achitate) REPLACE achitatelei WITH 0 FOR ISNULL(achitatelei) REPLACE diferenta WITH 0 FOR ISNULL(diferenta) in loc de var.A vreau sa folosesc var.B: SELECT contract.nrcontract,nr_proiect,OPERATOr,nr_rate,codval,data_contr,cursval,avans,contract.CNP,; IIF(EMPTY(societate),ALLTRIM(nume)+' '+ALLTRIM(prenume),ALLTRIM(societate)) as nume,bl,ap,; crscantitate.cantitate,crsvanzari.valoare,ROUND(crsvanzari.valoare*contract.cursval,2) as vallei,; iif(isnull(crsplati.achitate),0,crsplati.achitate) ,iif(isnull(achitatelei),0,achitatelei),; iif(isnull(diferenta),0,diferenta) ; FROM contract LEFT JOIN crsplati ON contract.nrcontract=crsplati.nrcontract ; LEFT JOIN crscantitate ON contract.nrcontract=crscantitate.nrcontract ,; clienti,crsvanzari ; where between(data_contr,data1,data2) AND contract.cnp=clienti.cnp ; AND contract.nrcontract=crsvanzari.nrcontract; into CURSOR CRScontracte Problema consta in : var.A functioneaza tot timpul , var.B daca ii dau un interval mai mic , fara multe plati incluse , in crscontracte campurile achitate,achitatelei,diferenta contin *, daca rulez pe 1-2-3 ani sau 2-12 luni e ok . Aveti o explicatie ?  1/10/2007 10:05:38 AM aflorin 840 posts Re: 2 mici probleme  (N/A) e stufos codul tau, dar in loc de iif(isnull(crsplati.achitate),0,crsplati.achitate) incearca iif(isnull(crsplati.achitate),00000000000.00,crsplati.achitate) sau, ma rog, attatia de 0 cit au cimpurile tale cu valori din BD Florin Aparaschivei - Iasi  1/10/2007 3:48:44 PM costin_mentor 492 posts www.accessoft.ro Re: 2 mici probleme  (N/A)  aflorin27 wrote e stufos codul tau, dar in loc de iif(isnull(crsplati.achitate),0,crsplati.achitate) incearca iif(isnull(crsplati.achitate),00000000000.00,crsplati.achitate) sau, ma rog, attatia de 0 cit au cimpurile tale cu valori din BD Ai dreptate, am uitat complet de chestia cu zerourile.Multumesc Problema 2 de care am pomenit in titlu: aceeasi baza de date cu tabelele : contract,clienti,plati,vanzari si un select cu 2 "union" SELECT nrcontract,sum(pret*cantitate) as valtot FROM vanzari; GROUP BY nrcontract ORDER BY nrcontract INTO CURSOR crsVANZARI SELECT plati.valrata,plati.termenrata,plati.data_chit,plati.avans,plati.rata_nr,; plati.cursval,ROUND((plati.valrata+plati.avans)*plati.cursval,2) as vallei,; contract.nrcontract,contract.codval,Contract.nr_proiect,clienti.bl,; crsvanzari.valtot,ROUND(plati.valrata*plati.cursval,2) as resTLEI, 000000000.0000 ,000000000.0000 ; FROM plati,contract,crsvanzari,clienti; WHERE BETWEEN(plati.data_chit,data1,data2) ; AND contract.nrcontract=plati.nrcontract ; AND contract.nrcontract=crsvanzari.nrcontract; and (YEAR(termenrata) AND contract.cnp=clienti.cnp; UNION ; SELECT plati.valrata,plati.termenrata,plati.data_chit,plati.avans,plati.rata_nr,; plati.cursval,ROUND((plati.valrata+plati.avans)*plati.cursval,2) as vallei,; contract.nrcontract,contract.codval,Contract.nr_proiect,clienti.bl,; crsvanzari.valtot, 0 AS RESTLEI, ROUND((plati.valrata+plati.avans)*plati.cursval,2) as lunalei,0 as avanlei ; FROM plati,contract,crsvanzari,clienti; WHERE BETWEEN(plati.data_chit,data1,data2) ; AND contract.nrcontract=plati.nrcontract ; AND contract.nrcontract=crsvanzari.nrcontract; and MONTH(termenrata)=MONTH(data_chit) ; AND YEAR(termenrata)=year(data_chit) ; AND contract.cnp=clienti.cnp; union; SELECT plati.valrata,plati.termenrata,plati.data_chit,plati.avans,plati.rata_nr,; plati.cursval,ROUND((plati.valrata+plati.avans)*plati.cursval,2) as vallei,; contract.nrcontract,contract.codval,Contract.nr_proiect,clienti.bl,; crsvanzari.valtot, 0 , 0,ROUND(plati.valrata*plati.cursval,2) as avanlei; FROM plati,contract,crsvanzari,clienti; WHERE BETWEEN(plati.data_chit,data1,data2) ; AND contract.nrcontract=plati.nrcontract ; AND contract.nrcontract=crsvanzari.nrcontract; and (YEAR(termenrata)>year(data_chit) OR (MONTH(termenrata)>MONTH(data_chit) AND YEAR(termenrata)=year(data_chit)) ) ; AND contract.cnp=clienti.cnp; into TABLE crsincasari Problema de care ma lovesc : Pentru ca acest "select mai mare" sa fie corect ( sa-mi dea o suma pe care am calculat-o in alt mod , manual) a trebuit ca in acest select sa adaug un camp din tabela plati ,si anume plati.rata_nr , de care nu am nevoie in acest cursor. Am incercat si cu INCA 2 CAMPURI camp in afara de plati.rata_nr , tot din tabela plati , cu unul (care are cam aceleasi valori ) se comporta ciudat , altul cu valori unice e ok. Campul plati.rata_nr contine numarul ratei care a fost achitata, dec nu pot sa spun ca e unic , dar nici ca are acelaeasi valori . Pe scurt : cu campul plati.rata_nr imi da un rezultat , care e corect, fara acest camp imi da ceva gresit. Unde gresesc? Ce imi scapa ?  1/11/2007 12:30:11 PM aflorin 840 posts Re: 2 mici probleme  (N/A) Vrei tu musai sa rezolvi totul cu un singur select? Problema este probabil asa: poti avea o vinzare si mai multe documente de plata pe acea vanzare (sau nici unul), la fel cum un doc de plata poate inchide mai multe facturi. In asemenea cazuri join-urile sunt intotdeauna sensibile. Eu iti sugerez sa faci mai multe selecturi. Florin Aparaschivei - Iasi  1/11/2007 1:57:49 PM costin_mentor 492 posts www.accessoft.ro Re: 2 mici probleme  (N/A) problema ramane, inca nu i-am gasit o explicatie.  Page 1 of 1   Visual FoxPro  Cod, sintaxa si comenzi  2 mici probleme... Flat View Tree View Oldest To Newest Newest To Oldest