Hi all,
I created a view where I join 6 tables and 2 other views that contains 3 or 4
tables joined each one. Can you please, tell me if is this correct to join
mutliple tables and views and will not be slow when the server processes this
view ? and what will be the result when I will transfer the data for about 500
records of that view from the server to the client in the network ?
Here is the code and thanks for your help.
CREATE OR ALTER VIEW VW_COMMANDE_FOURNISSEUR(
COMMANDE_ID,
NUM_COMMANDE,
ANNEE_COMMANDE,
FOUR_ID,
DATE_COMMANDE,
PREFIXE_NUM_COMMANDE,
VERROUILLER,
EMPLOYE_ID,
DEPOT_LIVRAISON_ID,
ADRESSE_ID,
UTILISATEUR,
LAST_UPDATE,
PROJET_ID,
FACTURE_ID,
NOTE,
NOM_FOUR,
CODE_FOUR,
PREFIXE_FOUR,
SUFFIXE_FOUR,
NUM_FACTURE,
ANNEE_FACTURE,
NOM_PROJET,
EMPLOYE_NOM,
EMPLOYE_PRENOMS,
TOTAL_HT,
TOTAL_REMISE,
TOTAL_HT_NET,
TOTAL_TVA,
TOTAL_TTC)
AS
SELECT
CF.COMMANDE_ID, CF.NUM_COMMANDE, CF.ANNEE, CF.FOUR_ID, CF.DATE_COMMANDE,
CF.PREFIXE,CF.VERROUILLER, CF.EMPLOYE_ID,
CF.DEPOT_LIVRAISON_ID, CF.ADRESSE_ID,CF.UTILISATEUR, CF.LAST_UPDATE,
CF.PROJET_ID, CF.FACTURE_ID,CF.NOTE,
FO.NOM_FOUR,FO.CODE_FOUR,FO.PREFIXE,FO.SUFFIXE,FF.NUM_FACTURE,FF.ANNEE,PR.NOM_PROJET,EM.EMPLOYE_NOM,EM.EMPLOYE_PRENOMS,
CAST( COALESCE(CL.MONTANT_HT,0) AS NUMERIC(9,2)) AS TOTAL_HT,
SUM(CAST( COALESCE(CL.MONTANT_REMISE,0) AS NUMERIC(9,2))) AS TOTAL_REMISE,
SUM(CAST(COALESCE(CL.MONTANT_HT_NET,0) AS NUMERIC(9,2))) AS TOTAL_HT_NET,
SUM(CAST(COALESCE(CL.MONTANT_TAXE,0) AS NUMERIC(9,2))) AS TOTAL_TVA,
SUM(CAST(COALESCE(CL.MONTANT_NET,0) AS NUMERIC(9,2))) AS TOTAL_TTC
FROM COMMANDE_FOURNISSEUR CF
INNER JOIN FOURNISSEUR FO ON CF.FOUR_ID=FO.FOUR_ID
LEFT JOIN VW_LIGNECOMMANDEFOURNISSEUR CL ON CF.COMMANDE_ID=CL.COMMANDE_ID
LEFT JOIN FACTURE_FOURNISSEUR FF ON CF.FACTURE_ID=FF.FACTURE_ID
LEFT JOIN PROJET PR ON CF.PROJET_ID =PR.PROJET_ID
LEFT JOIN EMPLOYE EM ON CF.EMPLOYE_ID=EM.EMPLOYE_ID
LEFT JOIN VW_ADRESSE_TIERS AD ON CF.ADRESSE_ID=AD.ADRESSE_ID
LEFT JOIN STRUCTURE ST ON CF.STRUCTURE_ID=ST.STRUCTURE_ID
GROUP BY
CF.COMMANDE_ID, CF.NUM_COMMANDE, CF.ANNEE, CF.FOUR_ID, CF.DATE_COMMANDE,
CF.PREFIXE,
CF.VERROUILLER, CF.EMPLOYE_ID,CF.DEPOT_LIVRAISON_ID,
CF.ADRESSE_ID,CF.UTILISATEUR, CF.LAST_UPDATE, CF.PROJET_ID,
CF.FACTURE_ID,CF.NOTE,
FO.NOM_FOUR,FO.CODE_FOUR,FO.PREFIXE,FO.SUFFIXE,FF.NUM_FACTURE,FF.ANNEE,PR.NOM_PROJET,EM.EMPLOYE_NOM,EM.EMPLOYE_PRENOMS,
CL.MONTANT_HT,CL.MONTANT_REMISE,CL.MONTANT_HT_NET,CL.MONTANT_TAXE,CL.MONTANT_NET
;
