CODEWITHSUNDEEP
sqlfirebird
Toster
Toster

Reputation: 381

Connecting variable string for WHERE clause

How do I connect the variable string for where clause in Firebird?

I have two variables type of SMALLINT. These variables are incremented after one iteration in while loop.

From these variables I create DATE for where-> between selection in SQL, something like this (this is my ineffectual try) :

 yearmin=extract (year from DATAMIN);
 yearmax=extract (year from DATAMAX);
 monthmin=extract (month from DATAMIN);
 monthmax=extract (month from DATAMAX);
 aktyear=rokmin;
 actmonth=monthmin;

 while  (actyear<=yearmax and actmonth<=monthmax) DO          
 BEGIN
SELECT
[...]
 WHERE (g.GDATAP BETWEEN (:actyear || :actmonth || 01) AND ( :actyear || :actmonth || 30))
[...]

   INTO :Zaw,:Sum;
 actyear=actyear+1;
 actmonth=actmonth+1;
 SUSPEND;
 end

Upvotes: 0

Views: 116

Answers (1)

ain
ain

Reputation: 22749

The || is string concatenator operator but in the expression g.GDATAP BETWEEN (:actyear || :actmonth || 01) you don't have strings but ints. Firebird won't do type conversion for you. So to make it work you have to cast to (var)char, something like

cast(:actyear as varchar(4)) || cast(:actmonth as varchar(2)) || '01'

You might have to add date separators too, ie

cast(:actyear as varchar(4)) || '-' || cast(:actmonth as varchar(2)) || '-01'

I don't remember the formats Firebird accepts off the top of my head...

It looks like you construct these dates in the loop, so it might be better to initialize start date before the loop and then increment it by required amount using DATEADD() function.

Upvotes: 2

Related Questions