cognos reportnet vs psql odbc 07_03_0200

hi.

we've been developing client reporting solutions using cognos reportnet
(1.1mr2) working nicely with informix (jenzabar/cars implementations) via
both the informix odbc driver and the native driver (even more stable with
the native driver, of course).

we thought we'd put together a sample database on postgres for
demonstrating to prospects, and we've hit a snag--

we downloaded the odbc (for version psqlodbc-07_03_0200.zip) from
http://www.postgresql.org/ftp/odbc/versions/msi just yesterday.

$ psql -V
psql (PostgreSQL) 7.4.7

with a mere two-table select, the reportnet error is:


QE-DEF-0177 An error occurred while performing operation
'sqlPrepareWithOptions' status='-9'.

UDA-SQL-0107 A general exception has occurred during the operation "prepare".

ERROR:  syntax error at or near "." at character 108


once we set "log_min_error_statement = error" in
/etc/postgresql/postgresql.conf and restarted the postmaster, we found the
syntax snag:

STATEMENT:  select "Donor_ID_Fields"."fullname", "Donor_Fields"."id" from
"public"."id_view" "Donor_ID_Fields""public"."donor_rec" "Donor_Fields" on
"Donor_ID_Fields"."id" = "Donor_Fields"."id"   order by
"Donor_Fields"."id" asc

with indentation, that's:

select
  "Donor_ID_Fields"."fullname",
  "Donor_Fields"."id"
from
  "public"."id_view" "Donor_ID_Fields"
  "public"."donor_rec" "Donor_Fields"
on
  "Donor_ID_Fields"."id" = "Donor_Fields"."id"
order by
  "Donor_Fields"."id" asc

there's no join clause between the tables in the 'from' clause. somewhere
between reportnet and postgres (i.e. odbc) there's a clause that goes
missing.

here's the result with a three-table join:

select
  "Gift_Fields"."desg",
  "Designation_Lookup"."txt",
  sum("Gift_Fields"."amt")
from
  "public"."gift_rec" "Gift_Fields" LEFT OUTER JOIN
  "public"."desg_table" "Designation_Lookup"
[missing comma/join clause]
  "public"."desg_rec" "Designation_Fields"
on "Designation_Lookup"."desg" = "Designation_Fields"."desg"
on "Gift_Fields"."desg" = "Designation_Fields"."desg" and
"Gift_Fields"."camp" = "Designation_Fields"."camp"
group by
  "Gift_Fields"."desg", "Designation_Lookup"."txt"

we'll download other versions of the driver tomorrow and see where the
chips fall then. thought you'd like to know, in case you didn't already...

--
Liberty > Security