need help with subqueries and odbc

Posted in 2000

Topics: SQL Development & Query Writing, Connectivity: ODBC / JDBC / .NET

Oliver K�hn — Mon, 16 Oct 2000 14:36:28 GMT — source: Informix-list mailing list archive (1991-1998)

Hey, if I am sending the following sql-command to an Informix Database via ODBC and receive an error message: SELECT Sum ( q1.Sales ) AS Sales1, Count ( q1.preis) AS Observations FROM ( SELECT hyma.haupttabelle.vksnr_nr , hyma.haupttabelle.woche_nr , hyma.haupttabelle.artikel_nr , Sum ( hyma.haupttabelle.anz ) AS Sales , hyma.haupttabelle.preis FROM hyma.haupttabelle WHERE ((( hyma.haupttabelle.aktion_nr ) = '00' ) ) GROUP BY hyma.haupttabelle.vksnr_nr , hyma.haupttabelle.woche_nr , hyma.haupttabelle.artikel_nr , hyma.haupttabelle.preis HAVING ((( hyma.haupttabelle.artikel_nr ) in ('4005808814145','4005808814206')) and ( hyma.haupttabelle.woche_nr) in (199901,199902) )) AS q1 -> Error: [Informix][Informix ODBC Driver][Informix]A syntax error has occurred. The same sql-statement is working if I use it in Access to the same (externally linked) informix database. Can anybody help??? Oliver Sent via Deja.com http://www.deja.com/ Before you buy.

↪ replying to Oliver K�hn

Sergey E. Volkov — Tue, 17 Oct 2000 16:03:30 +0400 — source: Informix-list mailing list archive (1991-1998)

Hi Oliver! Informix does not directly support 'select ... from ( select ... )' if you want, you can to write 'select ... from table( multiset( select ... ))' or 'select ... from table( multiset( select ... )) any_alias( column_list )' Sergey. Oliver K'hn <o_kuehn@my-deja.com> ''''' ' ''''''''':8sf3pb$fat$1@nnrp1.deja.com... > Hey, > > if I am sending the following sql-command to an Informix Database via > ODBC and receive an error message: > > SELECT Sum ( q1.Sales ) AS Sales1, Count ( q1.preis) AS Observations > FROM ( SELECT hyma.haupttabelle.vksnr_nr , hyma.haupttabelle.woche_nr , > hyma.haupttabelle.artikel_nr , Sum ( hyma.haupttabelle.anz ) AS Sales , > hyma.haupttabelle.preis FROM hyma.haupttabelle WHERE ((( > hyma.haupttabelle.aktion_nr ) = '00' ) ) GROUP BY > hyma.haupttabelle.vksnr_nr , hyma.haupttabelle.woche_nr , > hyma.haupttabelle.artikel_nr , hyma.haupttabelle.preis HAVING ((( > hyma.haupttabelle.artikel_nr ) in ('4005808814145','4005808814206')) > and ( hyma.haupttabelle.woche_nr) in (199901,199902) )) AS q1 > > -> > > Error: [Informix][Informix ODBC Driver][Informix]A syntax error has > occurred. > > The same sql-statement is working if I use it in Access to the same > (externally linked) informix database. > > Can anybody help??? > > Oliver > > > Sent via Deja.com http://www.deja.com/ > Before you buy.