Subject Re: [firebird-support] FB 3.0, Dialect 1 and SQL Error
Author Svein Erling Tysvær
Post date 2016-05-20T12:12:55Z
Have you tried 

(cast(:PAar as integer) * 100) + cast(:PMDR as integer))

I've never tried it the exact setting as you're using, but I've had to use cast when I've used a CTE to return a constant value (quite natural, there's no simple and general way for Firebird to guess what type your parameter is).

2016-05-20 12:59 GMT+02:00 Michael.Vilhelmsen@... [firebird-support] <firebird-support@yahoogroups.com>:


Hi

I have installed Firebird 3.0. I'm testing our program up against this one.
Our databases are in SQL dialect 1.

I have a table like this:

CREATE TABLE MYTABLE
(
  ID                      INTEGER         NOT NULL,
  NAME_                   VARCHAR(    30),
  VALUEFLOAT              NUMERIC( 18, 2) DEFAULT 0,
  VALUEINT                INTEGER         DEFAULT 0,
  AAR                     INTEGER         DEFAULT 0,
  MDR                     INTEGER         DEFAULT 0,
 CONSTRAINT PK_MYTABLE PRIMARY KEY (ID)
);


If I do this SQL, I get an error:

SELECT
  MYTABLE.NAME_,
  Sum(MYTABLE.VALUEFLOAT) AS SumFloat,
  Sum(MYTABLE.VALUEINT) AS SumInteger
FROM
  MYTABLE       
WHERE
  ((MYTABLE.AAR * 100) + MYTABLE.MDR) >= ((:PAar * 100) + :PMDR)
GROUP BY
  NAME_
 

ERROR:
Dynamic SQL Error expression evaluation not supported Invalid data type for multiplication in dialect 1

It works if I replace the parameters with numbers like this:


SELECT
  MYTABLE.NAME_,
  Sum(MYTABLE.VALUEFLOAT) AS SumFloat,
  Sum(MYTABLE.VALUEINT) AS SumInteger
FROM
  MYTABLE       
WHERE
  ((MYTABLE.AAR * 100) + MYTABLE.MDR) >= ((2016 * 100) + 5)
GROUP BY
  NAME_
 



So my question is, if there is a simple workaround to avoid this?

I have tried googling something on this, but havn't found anything so far.

I have read the release notes, but either cant find anything on this or I'm not looking good enough.


Michael