openjpa-users mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Bruce Beaumont <bruce.beaum...@ucs-solutions.co.za>
Subject Informix subselect
Date Tue, 24 Jun 2008 13:04:02 GMT
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

Mime
View raw message