| Subject | Re: [firebird-support] Using a selectable stored procedure |
|---|---|
| Author | |
| Post date | 2018-01-06T17:32:27Z |
The sample I posted is selecting from a view which join four tables no problem I can call it directly without the SP. But my real problem is in another query which is complex and that I could not build a simply view. I meet problem when I build it in a only a view so I had to build firstly the view and call it SP with other tables.
I try to query an order table and product items tables (suppliers, adresses, etc...) executing the SP I get the right data but as my question was on only to select from SP I thought It was not necessary to post it. Here is my SP:
I try to query an order table and product items tables (suppliers, adresses, etc...) executing the SP I get the right data but as my question was on only to select from SP I thought It was not necessary to post it. Here is my SP:
BEGIN
FOR SELECT CF.COMMANDE_ID,CF.ORDRE_ID, CF.NUM_COMMANDE,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.FACTURE_ID,CF.NOTE,FO.NOM_FOUR,FO.CODE_FOUR,FO.PREFIXE,FO.SUFFIXE,FF.NUM_FACTURE,FF.ANNEE,
EM.EMPLOYE_NOM,EM.EMPLOYE_PRENOMS,CL.MONTANT_HT,CL.MONTANT_REMISE,CL.MONTANT_HT_NET,CL.MONTANT_TAXE,CL.MONTANT_NET,AD.TYPE_ADRESSE,AD.INTITULE_ADRESSE,AD.ADR_LIGNE1,
AD.ADR_LIGNE2,AD.ADR_LIGNE3,AD.VILLE,AD.CODE_POSTAL,AD.TEL,AD.FAX,AD.NOM_WILAYA,AD.NOM_PAYS,CF.TRANSFORMER_FACTURE,CF.TRANSFORMER_LIVRAISON,OD.MOTIF_ORDRE,
OD.UNITE_ID,U.INTITULE_UNITE,U.CODE_UNITE, P.NOM_PROJET,OD.PROJET_ID,CF.DELAI_LIVRAISON,CF.STATUS_COMMANDE,D.NOM_DEPOT,OD.NUM_ORDRE, OD.PREFIXE,OD.DATE_ORDRE,CF.COMMANDE_ANNULEE,OD.ANNEE_ORDRE
FROM COMMANDE_FOURNISSEUR AS CF
LEFT JOIN TOTAUX_COMMANDEFOUR_LIGNE(CF.COMMANDE_ID ) AS CL ON( 1 = 1 )
LEFT JOIN FOURNISSEUR FO ON CF.FOUR_ID=FO.FOUR_ID
LEFT JOIN ORDRE_ACHAT OD ON CF.ORDRE_ID=OD.ORDRE_ID
LEFT JOIN FACTURE_FOURNISSEUR FF ON CF.FACTURE_ID=FF.FACTURE_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 UNITE U ON CF.UNITE_ID=U.UNITE_ID
LEFT JOIN PROJET P ON OD.PROJET_ID=P.PROJET_ID
LEFT JOIN DEPOT D ON CF.DEPOT_LIVRAISON_ID=D.DEPOT_ID
INTO
:COMMANDE_ID,:ORDRE_ID,:NUM_COMMANDE,:FOUR_ID,:DATE_COMMANDE,:PREFIXE_NUM_COMMANDE,:VERROUILLER,:EMPLOYE_ID,:DEPOT_LIVRAISON_ID, :ADRESSE_ID,:UTILISATEUR,:LAST_UPDATE,:FACTURE_ID,:NOTE,:NOM_FOUR,:CODE_FOUR,:PREFIXE_FOUR,:SUFFIXE_FOUR,:NUM_FACTURE,:ANNEE_FACTURE, :EMPLOYE_NOM,:EMPLOYE_PRENOMS,:TOTAL_HT,:TOTAL_REMISE,:TOTAL_HT_NET,:TOTAL_TVA,:TOTAL_TTC,:TYPE_ADRESSE,:INTITULE_ADRESSE,:ADR_LIGNE1,
:ADR_LIGNE2,:ADR_LIGNE3,:VILLE,:CODE_POSTAL,:TEL,:FAX,:NOM_WILAYA,:NOM_PAYS, :TRANSFORMER_FACTURE,:TRANSFORMER_LIVRAISON,:MOTIF_ORDRE, :UNITE_ID,:INTITULE_UNITE,:CODE_UNITE,:NOM_PROJET,:PROJET_ID,:DELAI_LIVRAISON,:STATUS_COMMANDE,:INTITULE_DEPOT_LIVRAISON,:NUM_ORDRE,
:PREFIXE_ORDRE,:DATE_ORDRE,:COMMANDE_ANNULEE,:ANNEE_ORDRE
DO
SUSPEND;
END
TOTAUX_COMMANDEFOUR_LIGNE is a view which gives the sum of product items
VW_ADRESSE_TIERS is a view which gives the suppliers adresses
In that case, can I use stored procedure ? is that useful ?
The same code I have built in a view call it directly but I get double records so I had to use that SP.