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
