What's wrong with my syntax ???

static int open_cursor() { EXEC SQL DECLARE C1 CURSOR FOR SELECT a.id, a.line_id, a.prod_id, b.name, b.description,a.ship_date FROM "dba".sales_order_items as a, "dba".product as b WHERE "dba".a.id="dba".b.id AND "dba".b.id=2262; EXEC SQL OPEN C1; return( TRUE ); } I also have a cursor for this to be fetched in but when I run it's empty, If I remove the join it has the data, just too much ...! Sent via Deja.com http://www.deja.com/ Before you buy.

belairj@my-deja.com wrote: > > static int open_cursor() > { > EXEC SQL DECLARE C1 CURSOR FOR > SELECT a.id, a.line_id, a.prod_id, b.name, b.description,> a.ship_date > FROM "dba".sales_order_items as a, "dba".product as b > WHERE "dba".a.id="dba".b.id AND "dba".b.id=2262; > EXEC SQL OPEN C1; > return( TRUE ); > } > > I also have a cursor for this to be fetched in but when I run it's > empty, If I remove the join it has the data, just too much ...! You should do error checking in your code (unless there's a WHENEVER ERROR STOP statement that isn't in scope. Your detailed problem is that once you've given aliases to the tables, you should use just the alias in the WHERE clause, just as you did in the select-list: WHERE a.id = b.id AND b.id = 2262; Given the constraint, you might be able to add 'a.id = 2262' and the result might be quicker -- or it might be irrelevant since the optimizer might infer the additional condition itself. -- Yours, Jonathan Leffler (Jonathan.Leffler@Informix.com) #include <disclaimer.h> Guardian of DBD::Informix v1.00.PC1 -- http://www.perl.com/CPAN "I don't suffer from insanity; I enjoy every minute of it!"