[firebird-support] Problem with where clause
Hi all, I am using FB 3 and IBExpert and with the DDL below I get message and it works when I remove the where clause. I think the problem is with the between command. Can you please help me ? create or alter procedure test_commande ( from_date date, to_date date) returns ( commande_id integer, ordre_id integer, num_commande integer, four_id integer, date_commande date, prefixe_num_commande varchar(6), verrouiller smallint, employe_id integer, depot_livraison_id integer, adresse_id integer, utilisateur varchar(30), last_update timestamp, facture_id integer, note varchar(1000), nom_four varchar(150), code_four integer, prefixe_four varchar(6), suffixe_four varchar(6), num_facture varchar(20), annee_facture integer, employe_nom varchar(30), employe_prenoms varchar(30), total_ht numeric(15,2), total_remise numeric(15,2), total_ht_net numeric(15,2), total_tva numeric(15,2), total_ttc numeric(15,2), type_adresse varchar(1), intitule_adresse varchar(50), adr_ligne1 varchar(50), adr_ligne2 varchar(50), adr_ligne3 varchar(50), ville varchar(80), code_postal varchar(6), tel varchar(30), fax varchar(30), nom_wilaya varchar(100), nom_pays varchar(80), transformer_facture smallint, transformer_livraison smallint, motif_ordre varchar(50), unite_id integer, intitule_unite varchar(100), code_unite varchar(3), nom_projet varchar(200), projet_id integer, delai_livraison integer, status_commande varchar(2), intitule_depot_livraison varchar(50), num_ordre integer, prefixe_ordre varchar(2), date_ordre date, commande_annulee smallint, annee_ordre integer) as declare variable v1 varchar(1); begin V1= '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,'; V1=:V1 || '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,'; V1=:V1 || '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,'; V1=:V1 || '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,'; V1=:V1 || '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,'; V1=:V1 || 'OD.PREFIXE,OD.DATE_ORDRE,CF.COMMANDE_ANNULEE,OD.ANNEE_ORDRE'; V1=:V1 || ' FROM COMMANDE_FOURNISSEUR AS CF'; V1=:V1 || ' LEFT JOIN TOTAUX_COMMANDEFOUR_LIGNE(CF.COMMANDE_ID ) AS CL ON( 1 = 1 )'; V1=:V1 || ' LEFT JOIN FOURNISSEUR FO ON CF.FOUR_ID=FO.FOUR_ID'; V1=:V1 || ' LEFT JOIN ORDRE_ACHAT OD ON CF.ORDRE_ID=OD.ORDRE_ID'; V1=:V1 || ' LEFT JOIN FACTURE_FOURNISSEUR FF ON CF.FACTURE_ID=FF.FACTURE_ID'; V1=:V1 || ' LEFT JOIN EMPLOYE EM ON CF.EMPLOYE_ID=EM.EMPLOYE_ID'; V1=:V1 || ' LEFT JOIN VW_ADRESSE_TIERS AD ON CF.ADRESSE_ID=AD.ADRESSE_ID'; V1=:V1 || ' LEFT JOIN UNITE U ON CF.UNITE_ID=U.UNITE_ID'; V1=:V1 || ' LEFT JOIN PROJET P ON OD.PROJET_ID=P.PROJET_ID'; V1=:V1 || ' LEFT JOIN DEPOT D ON CF.DEPOT_LIVRAISON_ID=D.DEPOT_ID'; V1=:V1 || ' WHERE CF.DATE_COMMANDE'||' BETWEEN '||:FROM_DATE||' AND '||:TO_DATE; FOR EXECUTE STATEMENT v1 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
[firebird-support] Problem with where clause
Hi, I think there is a quote missing at the end of ":TO_DATE"; Regards Martin
Re: [firebird-support] Problem with where clause
Normally, a query would contain something like: WHERE CF.DATE_COMMANDE BETWEEN '1.1.2018' AND '2.1.2018' What you are doing is forgetting the apostrophes and trying to use: WHERE CF.DATE_COMMANDE BETWEEN 1.1.2018 AND 2.1.2018 which correctly receives a syntax error. I can think of three ways to fix this: Either V1=:V1 || ' WHERE CF.DATE_COMMANDE'||' BETWEEN '''||:FROM_DATE||''' AND '''||:TO_DATE||; or (named parameters): V1=:V1 || ' WHERE CF.DATE_COMMANDE BETWEEN :MY_FROM_DATE AND :MY_TO_DATE'; FOR EXECUTE STATEMENT (V1) (MY_FROM_DATE := FROM_DATE, MY_TO_DATE := TO_DATE) INTO ... or (positional parameters): V1=:V1 || ' WHERE CF.DATE_COMMANDE BETWEEN ? AND ?'; FOR EXECUTE STATEMENT (V1) (FROM_DATE, TO_DATE) INTO ... HTH, Set 2018-01-05 12:58 GMT+01:00 Köditz, Martin martin.koed...@it-syn.de [firebird-support] : > > > Hi, > > > > I think there is a quote missing at the end of „:TO_DATE“; > > > > Regards > > Martin > > > >
Re: [firebird-support] Problem with where clause
Thanks but adding a quote at the end doesn't work. In fact adding double quote because with one the SP will not be compiled
Re: [firebird-support] Problem with where clause
On 5-1-2018 12:52, m_brahi...@yahoo.fr [firebird-support] wrote: > I am using FB 3 and IBExpert and with the DDL below I get message > command> and it works when I remove the where clause. I think the > problem is with the between command. Can you please help me ? Why are you using `execute statement`, and not simply `for select ...`? There doesn't seem to be any reason why you need dynamically built SQL here, as the only dynamic thing are the from_date and to_date parameters, which would work just fine in `for select ...`. So, first try to convert this to a normal `for select` statement. Otherwise, if you really need dynamically generated SQL, change the last line to: V1=:V1 || ' WHERE CF.DATE_COMMANDE BETWEEN :FROM_DATE AND :TO_DATE'; and use `execute statement v1 (from_date := from_date, to_date := to_date) into ...` Your immediate problems is caused by missing quotes around the date values though. Mark -- Mark Rotteveel
Re: [firebird-support] Problem with where clause
On 5-1-2018 13:37, Mark Rotteveel m...@lawinegevaar.nl [firebird-support] wrote: > Your immediate problems is caused by missing quotes around the date > values though. Specifically, it would need to be: V1=:V1 || ' WHERE CF.DATE_COMMANDE'||' BETWEEN '''||:FROM_DATE||''' AND '''||:TO_DATE||; But that isn't the 'right' solution for this. Mark -- Mark Rotteveel
Re: [firebird-support] Problem with where clause
On 5-1-2018 13:23, m_brahi...@yahoo.fr [firebird-support] wrote: > Thanks but adding a quote at the end doesn't work. In fact adding double > quote because with one the SP will not be compiled That is because || '; would open a string but not end it, then ||''; doesn't work, because that is just an empty string. You'd need ||;, as '' is how you escape a single quote in a string literal. But as I answered elsewhere, this isn't the right solution for your problem. Mark -- Mark Rotteveel
Re: [firebird-support] Problem with where clause
Now with V1=:V1 || ' WHERE CF.DATE_COMMANDE'||' BETWEEN '''||:FROM_DATE||''' AND '''||:TO_DATE||; It works for me and I tried with V1=:V1 || ' WHERE CF.DATE_COMMANDE>='''||:FROM_DATE||''' AND CF.DATE_COMMANDE<='''||:TO_DATE||; It works too. In which case this isn't the right solution ?
Re: [firebird-support] Problem with where clause
Anyway, as it solved thanks to all
Re: [firebird-support] Problem with where clause
On 5-1-2018 14:35, m_brahi...@yahoo.fr [firebird-support] wrote: > > > Now with > > V1=:V1 || ' WHERE CF.DATE_COMMANDE'||' BETWEEN '''||:FROM_DATE||''' AND > '''||:TO_DATE||; > > It works for me and I tried with > > V1=:V1 || ' WHERE CF.DATE_COMMANDE>='''||:FROM_DATE||''' AND > CF.DATE_COMMANDE<='''||:TO_DATE||; > > It works too. In which case this isn't the right solution ? It works, but you should not be using execute statemen. Using for select should work just fine. In your case, execute statement only adds overhead and removes compile time syntax checking and makes it easier to make mistakes and harder to spot them. Changing this to use for select would simplify your code, as you would not be dealing with string concatenation, and you get a compile time syntax check in return. Mark -- Mark Rotteveel
Re: [firebird-support] Problem with where clause
I use FOR SELECT in some part of the DB but the problem is when I use multiple criteria for example for the same table order order_date, order_no, etc... then I had to use execute statement to build query for each criteria. According to your advise, do I need to build the SP with FOR SELECT and call it from the client (delphi) with criteria ?