Different results from .mdb vs .odb, why?

Question

I use the following query to retrieve data from a .mdb file through JDBC, however when I try it on an .odb file it goes does not throw any exceptions but there are no results at all. I am wondering is .odb case sensitive where .mdb is not or is there something else I am missing?

"SELECT DISTINCT column-one + ':' + column-two As ResultColumn FROM datatable;"

How can I go about creating one statement that will work on both these file types?

Answer 1

They would be differnt because they are two differnt products written by two differnt companies and the programmers made different choices as to how to handle things.

Have you tried using a column alias you specify, perhaps something more descriptive than Expr1000?

SELECT DISTINCT column-one + ":" + column-two As Expr1000 FROM datatable

That's how I would write it in SQL Server, check your database to see if this would work.

Answer 2

Some suggestions:

1) Try using single quotes in place of double quotes e.g.

SELECT DISTINCT column-one + ':' + column-two As ResultColumn FROM datatable;

2) Perhaps the .odb source's SQL syntax handles concatenation differently e.g. with the .mdb the '+' NULLs will propagate meaning that if at least one the the column's values is NULL then the result will be NULL; the '&' concatenation symbol will ignore NULL values e.g. this in .mdb land

SELECT DISTINCT column-one & ':' & column-two As ResultColumn FROM datatable;

is equivalent to this in Standard ANSI/ISO SQL-92 (which isn't supported in .mdb land)

SELECT DISTINCT COALESCE(column-one, '') + ':' + COALESCE(column-two, '') As ResultColumn FROM datatable;

3) If the two sources do not support the same syntax, can you use the .mdb's linked table functionality to link the tables(s) from the .odb source in the .mdb and only use the SQL code in the .mdb?