Informix - '||' operator not working over ODBC connection

Question

I am building a report in Reporting Services 2005, where the data is retrieved from an Informix database. The connection to the database is declared via ODBC. Here is the simpler version of the query:

select
    prodtype,
    familynum,
    family,
    sum(invested) invested,
    month(recevdate) month,
    year(recevdate) year,
    day(recevdate) day,
    'All Year' const
from
    sales_product
where
    (region not in ('15876','15852')) and
    (prodtype in ('4','7','50','1')) and
    (recevdate >= ('01/01/' || (year(?) - 1))) and
    (recevdate <= Date('12/31/' || (year(?) - 1)))
 group by 1,2,3,5,6,7

If you look at the where clause, you will see that I am taking the year from the parameter and then adding it to a string month and day. The problem here is that the '||' operator works directly in Informix, but not over ODBC. When I execute this query, I get a syntax error. Is there any other way to concatenate two strings?

Answer 1

In the circumstances, I think your best choice is to use the MDY() function instead of string concatenation:

SELECT prodtype, familynum, family,
       sum(invested) invested,
       month(recevdate) month,
       year(recevdate) year,
       day(recevdate) day,
       'All Year' const
  FROM sales_product
 WHERE (region NOT IN ('15876','15852'))
   AND (prodtype in ('4','7','50','1'))
   AND (recevdate BETWEEN MDY(1, 1, YEAR(?) - 1) AND MDY(12, 31, YEAR(?) - 1))
 GROUP BY 1,2,3,5,6,7

I am assuming that the ? placeholder value is a full DATE value, so the YEAR function is needed. You can simplify the query if you pass in the exact year number that you are interested in:

SELECT prodtype, familynum, family,
       sum(invested) invested,
       month(recevdate) month,
       year(recevdate) year,
       day(recevdate) day,
       'All Year' const
  FROM sales_product
 WHERE (region NOT IN ('15876','15852'))
   AND (prodtype in ('4','7','50','1'))
   AND (recevdate BETWEEN MDY(1, 1, ?) AND MDY(12, 31, ?))
 GROUP BY 1,2,3,5,6,7

As to why the string concatenation is 'failing'...that is not clear. However, one of the advantages of the MDY() function is that its arguments are unambiguous independent of the locale (both client and server locales). One possible cause of your trouble is that the date format set by (or not set by) Reporting Services is different from the one you are forcing in your query - and different from the one that is set when you execute the query directly. That's a guess - a plausible but by no means definitive guess. Another possibility is that you only pass the reference date once, even though it is used twice in the condition clause of the query. If we had the error message given, we might be able to make a better guess at the source of the trouble.

Answer 2

Try using + for concat or maybe something like strcat()

Answer 3

I think I found a workaround to the issue ... Instead of using || operator I thought of using Replace function. So, instead of (recevdate >= Date('0101' + Year(?))) I used (recevdate >= Date(Replace('01/01/1900','1900',year(?)-1))) and it worked. I am still open to a better way of doing this, but I'll go with this until then.

Answer 4

Given the hard-coded 01/01 and 12/31 values in the predicate, why not use:

YEAR(recevdate) = YEAR(?) - 1

Wouldn't that be the simplest way to write it?