Re: Esql/c and OUTER JOIN

hobbes wrote: > "Jonathan Leffler" wrote: >> hobbes wrote: >>> Is there any limitation for number of OUTER join that is used in a query >>> in sqlc ?? >> >>No. > ============ > This is the new code... : > I will try this time to include everything that is needed :) > ERROR checking is .... really basic :) (but quick for this test) Fixing the error checking for test programs is trivial: EXEC SQL WHENEVER ERROR STOP; :-) The good news is I was able to reproduce your problem with the sample code; the bad news is that it means there's a bug in the ESQL/C compiler - 99.5% probability. The trouble is somehow related to the placeholders that appear in the FROM clause; the compiler reorganizes the variable list so that in your example, the reference to categorie is listed before the two references to large_det.id_langue. That's the bug. It also occurs with inner joins in the FROM clause. I don't have a bug number yet; the last 0.5% needs to be removed from doubt. It took me a while to make this into working code. I had to steal your schema from the original post, interpolate the two header files, and so on. That's more work than I should have had to go through. You actually don't use the 'large' structure, and could have done without the large_det structure. You don't define USERPASS or CONNEXION -- and it is always bad security to use the username as the password. I ended up modifying the code to create two temporary table, and various other simplifications, so it became a free-standing program that only requires you to specify which database to use on the command line (defaulting to 'stores' if you don't say otherwise). If you're reporting a problem, it is worth getting people onto your side by spending enough effort to remove every extraneous line of code, and making it as easy as possible for someone else to run the code on their system. For advice in general on this process, see: http://www.catb.org/~esr/faqs/smart-questions.html Don't take that personally, Hobbes - I'm hoping to educate others at least as much as you. -- Jonathan Leffler #include <disclaimer.h> Email: jleffler@earthlink.net, jleffler@us.ibm.com Guardian of DBD::Informix v2003.04 -- http://dbi.perl.org/

"Jonathan Leffler" wrote: > > ============ > > This is the new code... : > > I will try this time to include everything that is needed :) > > ERROR checking is .... really basic :) (but quick for this test) > > Fixing the error checking for test programs is trivial: > EXEC SQL WHENEVER ERROR STOP; > > :-) Well i know it , but .... i'm too lazy too modify our source code :) (sorry for that) > The good news is I was able to reproduce your problem with the sample > code; the bad news is that it means there's a bug in the ESQL/C > compiler - 99.5% probability. Not a good news.. at all ... bu thks :) > The trouble is somehow related to the placeholders that appear in the > FROM clause; the compiler reorganizes the variable list so that in > your example, the reference to categorie is listed before the two > references to large_det.id_langue. That's the bug. It also occurs > with inner joins in the FROM clause. I don't have a bug number yet; > the last 0.5% needs to be removed from doubt. > > It took me a while to make this into working code. I had to steal > your schema from the original post, interpolate the two header files, > and so on. That's more work than I should have had to go through. > You actually don't use the 'large' structure, and could have done > without the large_det structure. You don't define USERPASS or > CONNEXION -- and it is always bad security to use the username as the > password. I ended up modifying the code to create two temporary > table, and various other simplifications, so it became a free-standing > program that only requires you to specify which database to use on the > command line (defaulting to 'stores' if you don't say otherwise). > > If you're reporting a problem, it is worth getting people onto your > side by spending enough effort to remove every extraneous line of > code, and making it as easy as possible for someone else to run the > code on their system. > > For advice in general on this process, see: > http://www.catb.org/~esr/faqs/smart-questions.html > > Don't take that personally, Hobbes - I'm hoping to educate others at > least as much as you. > No problem... When i read my messages after writing it i saw it was already a mess and really dirty for a non-english reader :) I'm really sorry about that. This code was an exceprt from our code, i try to reduce a little, but i do not do it in a very clean way. Once again.... sorry :) I think i'm gonna write these query in another methode for the moment.... Thk you for your tests, patiences, kindness, and....advices :) Arnaud

Well adding just a little thing It seems that if you declare cariable this way => ':variable' it works.... For me it's just a workaround ... Don't think it's really ' a good way' of making the thing It seems working for me ... Can somebody (jonathan :) since you've have made my poor example work, i think it would be easier for you ) test this plz ?? Thank you ! Arnaud ==== exec sql declare democursor cursor for select l.num_lib, nvl(ll1.text,' '), nvl(ll2.text,' '), nvl(ll3.text,' ') from large l LEFT OUTER JOIN large_det ll1 ON ll1.num_lib = l.num_lib and ll1.id_langue = ':large_det.id_langue' LEFT OUTER JOIN large_det ll2 ON ll2.num_lib = l.num_lib and ll2.id_langue = '#' LEFT OUTER JOIN large_det ll3 ON ll3.num_lib = l.num_lib and ll3.id_langue = ':large_det.id_langue' where l.categ = :categorie;====