Hello,
I have a problem running some queries with access drivers. What I did is to run this query:
select
t.UNIDAD, t.RUTA, t.POSICION, t.FUERZA,
dr.[DM CODE] as DMCODE,
e.RUTA,
e.NOMBRE,
e.APELLIDOS,
e.EMPLEADO,
e2.EMPLEADO as DM_EMPLEADO,
e2.NOMBRE as DM_NOMBRE,
e2.APELLIDOS as DM_APELLIDOS,
str(ad.unidad) + ' ' + ad.area as areacode,
ad.ruta as amcode
from
[Territorios Unidades] t,
[Estructura MSD] e,
[0 Distritos Rutas] dr,
[Estructura MSD] e2,
[1 Areas Dtos] ad
where
t.ruta = e.ruta and
t.posicion = 'REP' and
dr.ruta = t.ruta and
e2.ruta = dr.[DM CODE] and
e2.posicion <> 'REP' and
iif(ad.ruta='31000', 'E3100',
iif(ad.ruta='32000', 'E3200', ad.ruta)) = dr.[DM CODE]
Then I saved the result in text format and compared to the result returned running the same query within MSAccess, the result is diffrent for almost all
records. Below is a CSV row from MSAccess and then from HXTT Access:
MSD2,MSD2,,,0,Vacante,32200720,Rolando Enrique Del Angel Meza,,,0,Vacante,,S1293,S1293
MSD2,MSD2,,,0,Vacante,32200720,Rolando Enrique Del Angel Meza,,,0,Vacante,,MSD129,MSD129
Is there some join in this query not supported by hxtt driver or do I have to consider some special condition in order to run it?
|
|
Please send us such a mdb sample so that we can recur your issue.
|
Hello,
unfortunately for security restrictions I'm not allowed to share that database, but trying to reproduce the problem in a db with similar data I've found two possible causes: first one is the fact that column names are identical in two cases: t.RUTA and e.RUTA, that can be solved easly with an alias, the second one is the string concatenation operation: "str(ad.unidad) + ' ' + ad.area as areacode" who hxtt driver is reporting as "2Sur" instead of the "2 Sur" reported in Access, is there some other operator I could use to solve this difference? thank you...
|
>is there some other operator I could use to solve this difference?
Try ODBCTrimBehavior=false connection property.
|
I ran the same query with the new property but it returned the same result. This is how I included de property:
Properties props = new Properties();
props.put("ODBCTrimBehavior", false);
Class.forName("com.hxtt.sql.access.AccessDriver");
Connection conn = DriverManager.getConnection("jdbc:access:/" + mdbFileName, props);
But the result reports "2Sur" instead of the "2 Sur" reported in Access in column "areacode"...
|
Main
webmaster@hxtt.com