Re: [firebird-support] Problem with where clause

[m_brahi...@yahoo.fr [firebird-support]]

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 ?

Re: [firebird-support] Problem with where clause

[Mark Rotteveel m...@lawinegevaar.nl [firebird-support]]

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

[m_brahi...@yahoo.fr [firebird-support]]

Anyway, as it solved thanks to all

Re: [firebird-support] Problem with where clause

[m_brahi...@yahoo.fr [firebird-support]]

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

[Mark Rotteveel m...@lawinegevaar.nl [firebird-support]]

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

[Mark Rotteveel m...@lawinegevaar.nl [firebird-support]]

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

[Mark Rotteveel m...@lawinegevaar.nl [firebird-support]]

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

[m_brahi...@yahoo.fr [firebird-support]]

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

[Svein Erling Tysvær setys...@gmail.com [firebird-support]]

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
>
>
> 
>

[firebird-support] Problem with where clause

[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

[firebird-support] Problem with where clause

[m_brahi...@yahoo.fr [firebird-support]]

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