Informix subselect

[Bruce Beaumont <br...@ucs-solutions.co.za>]

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

Re: Informix subselect

[Pinaki Poddar <pp...@apache.org>]

Looks like the generated SQL is structurally alright except that uses
SUBSTRING instead of SUBSTR.
If that is the case, the following configuration in persistence.xml should
address the problem:

<property name="openjpa.jdbc.DBDictionary"
value="informix(substringFunctionName='SUBSTR')"/>


-- 
View this message in context: http://www.nabble.com/Informix-subselect-tp18090628p18093406.html
Sent from the OpenJPA Users mailing list archive at Nabble.com.