Discussion :

[captainamerica75]

Bonjour a tous,

voila mon petit problème... actuellement j'ai une requete qui me permet de faire des extractions d'ecritures vers excel ... ça il n'y a pas de problème.

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
SELECT v.*, ss.SOLDE_CTRV, ss.SOLDE_TRN FROM V_ETAT_BNK v,(select s.acc_code,(s.bank_bal_amount - sum(r.bank_cur_amount)- sum(nr.bank_cur_amount)) as  SOLDE_CTRV, 
                  (s.bank_bal_amount-sum(r.trn_amount)) as  SOLDE_TRN 
                  from rec_bank r,nrec_bank nr, spe_rap_accounts s 
                  Where s.acc_code = R.acc_code 
                  and (r.book_date>= {d '2004-01-19'}) 
                  group by s.acc_code, s.bank_bal_amount) ss 
                  Where v.acc_code = ss.acc_code

J'ai la seconde requete qui me permet de calculer des soldes

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
-- contrôle des soldes :
SELECT 'DETAILS' AS REC_TYPE, A.ACC_CODE, 0.0 AS ABK_SUMMA, 0.0 AS CMP_CUR_ABK_SUMMA, 0.0 AS BNK_SUMMA, A.CUR_CODE, A.ABK_CUR_CODE FROM ACCOUNTS A 
where A.CMP_CODE = 'Ma_société' and A.acc_code = 'Mon_compte'
UNION ALL SELECT REC_TYPE, U.ACC_CODE, U.ABK_SUMMA, U.ABK_SUMMA  AS CMP_CUR_ABK_SUMMA, U.BNK_SUMMA, A.CUR_CODE, A.ABK_CUR_CODE 
FROM (SELECT 'NREC_ACC_AFTER' AS REC_TYPE, ACC_CODE, SUM(ABK_CUR_AMOUNT) AS ABK_SUMMA, 0.0 AS BNK_SUMMA 
						FROM NREC_ACC N WHERE BOOK_DATE > {ts '2006-05-31 00:00:00.000'} GROUP BY ACC_CODE UNION ALL 
								SELECT 'NREC_ACC_BEFORE' AS REC_TYPE, ACC_CODE, SUM(ABK_CUR_AMOUNT) AS ABK_SUMMA, 0.0 AS BNK_SUMMA 
								FROM NREC_ACC N WHERE BOOK_DATE <= {ts '2006-05-31 00:00:00.000'} 
GROUP BY ACC_CODE UNION ALL 
SELECT 'NREC_BANK_AFTER' AS REC_TYPE, ACC_CODE, SUM(ABK_CUR_AMOUNT) AS ABK_SUMMA, SUM(BANK_CUR_AMOUNT) AS BNK_SUMMA 
FROM NREC_BANK N 
WHERE BOOK_DATE > {ts '2006-05-31 00:00:00.000'}  
GROUP BY ACC_CODE 
UNION ALL SELECT 'NREC_BANK_BEFORE' AS REC_TYPE, ACC_CODE, SUM(ABK_CUR_AMOUNT) AS ABK_SUMMA, SUM(BANK_CUR_AMOUNT) AS BNK_SUMMA FROM NREC_BANK N WHERE BOOK_DATE <= {ts '2006-05-31 00:00:00.000'} GROUP BY ACC_CODE 
UNION ALL SELECT 'REC_ACC_AFTER' AS REC_TYPE, ACC_CODE, SUM(ABK_CUR_AMOUNT) AS ABK_SUMMA, 0.0 AS BNK_SUMMA FROM REC_ACC N WHERE BOOK_DATE > {ts '2006-05-31 00:00:00.000'}  GROUP BY ACC_CODE 
UNION ALL SELECT 'REC_ACC_BEFORE' AS REC_TYPE, ACC_CODE, SUM(ABK_CUR_AMOUNT) AS ABK_SUMMA, 0.0 AS BNK_SUMMA FROM REC_ACC N WHERE BOOK_DATE <= {ts '2006-05-31 00:00:00.000'}  AND REC_DATE > {ts '2006-05-31 00:00:00.000'} GROUP BY ACC_CODE 
UNION ALL SELECT 'REC_BANK_AFTER' AS REC_TYPE, ACC_CODE, SUM(ABK_CUR_AMOUNT) AS ABK_SUMMA, SUM(BANK_CUR_AMOUNT) AS BNK_SUMMA FROM REC_BANK N WHERE BOOK_DATE > {ts '2006-05-31 00:00:00.000'} GROUP BY ACC_CODE 
UNION ALL SELECT 'REC_BANK_BEFORE' AS REC_TYPE, ACC_CODE, SUM(ABK_CUR_AMOUNT) AS ABK_SUMMA, SUM(BANK_CUR_AMOUNT) AS BNK_SUMMA FROM REC_BANK N WHERE BOOK_DATE <= {ts '2006-05-31 00:00:00.000'}  AND REC_DATE > {ts '2006-05-31 00:00:00.000'} GROUP BY ACC_CODE 
UNION ALL SELECT 'BAL_INIT_BANK' AS REC_TYPE, ACC_CODE, SUM(ABK_BAL_AMOUNT) AS ABK_SUMMA, SUM(BANK_BAL_AMOUNT)AS BNK_SUMMA FROM SPE_RAP_ACCOUNTS GROUP BY ACC_CODE 
UNION ALL SELECT 'BAL_INIT_GL' AS REC_TYPE, ACC_CODE, SUM(ABK_AMOUNT) AS ABK_SUMMA, 0.0 AS BNK_SUMMA FROM ACCOUNT_ID GROUP BY ACC_CODE ) U, ACCOUNTS A, COMPANIES C WHERE A.ACC_CODE = U.ACC_CODE AND A.CMP_CODE = C.CMP_CODE 
AND A.CMP_CODE = 'Ma_société' and A.acc_code = 'Mon_compte'

J'aimerais savoir je pourrais ajouter le résultat retourner par la seconde requete dans la première...

Merci pour votre aide

[SQLpro]

oui en faisant une sous requête corrélée dans la clause WHERE.

A +

[captainamerica75]

salut à toi,

oui en faisant une sous requête corrélée dans la clause WHERE.

c'est à -dire ????

[SQLpro]

SELECT Col1, Col2, (SELECT ... FROM ... AS T2 WHERE T1. ??? = T2.??)
FROM .... T1
WHERE ..

Lisez les articles que j'ai écrit à ce sujet :
http://sqlpro.developpez.com/cours/sqlaz/sousrequetes/