Hi

Database Informix IDS 11.10

I am trying to joint 2 tables together using a part of one of the primary
tables fields as in the and clause below: (Where the first character of
t0.cs_dept = t1.hr5_code)


SELECT d.id, d.description, SUM(s.salesIncl), SUM(s.salesExcl),
SUM(s.depositIncl), SUM(s.depositExcl)
FROM ConsolidatedSales s, Division d
WHERE s.date = ?1 and s.store = ?2
and d.id = SUBSTRING(s.department,1,1)
GROUP BY d.id, d.description
ORDER BY d.id


This generates the following SQL

SELECT t1.hr5_code, t1.hr5_description, SUM(t0.cs_sales_incl),
SUM(t0.cs_sales_excl), SUM(t0.cs_dep_incl), SUM(t0.cs_dep_excl)
FROM cons_sales t0 CROSS JOIN art_hr5 t1
WHERE (t0.cs_date = ? AND t0.cs_st_number = ?
AND t1.hr5_code = SUBSTRING(t0.cs_dept, 1, 1))
GROUP BY t1.hr5_code, t1.hr5_description ORDER BY t1.hr5_code ASC

However the Informix SUBSTRING function is incorrect and the function SUBSTR
should be used as shown below:

SELECT t1.hr5_code, t1.hr5_description, SUM(t0.cs_sales_incl),
SUM(t0.cs_sales_excl), SUM(t0.cs_dep_incl), SUM(t0.cs_dep_excl)
FROM cons_sales t0 CROSS JOIN art_hr5 t1
WHERE (t0.cs_date = ? AND t0.cs_st_number = ?
AND t1.hr5_code = SUBSTR(t0.cs_dept, 1, 1))
GROUP BY t1.hr5_code, t1.hr5_description ORDER BY t1.hr5_code ASC


alternatively the syntax for using the SUBSTRING function in Informix is  

SELECT t1.hr5_code, t1.hr5_description, SUM(t0.cs_sales_incl),
SUM(t0.cs_sales_excl), SUM(t0.cs_dep_incl), SUM(t0.cs_dep_excl)
FROM cons_sales t0 CROSS JOIN art_hr5 t1
WHERE (t0.cs_date = ? AND t0.cs_st_number = ?
AND t1.hr5_code = SUBSTRING(t0.cs_dept FROM 1 FOR 1))
GROUP BY t1.hr5_code, t1.hr5_description ORDER BY t1.hr5_code ASC

Would it be possible for someone to validate the problem and suggest a
workaround or fix for this.


Bruce