Re: Problem with inner queries

On 10/28/05, Andrea <csharpnet04@nospam.yahoo.com> wrote: > > I'm doing the following query > > SELECT codazorig, ( > SELECT cinnodo FROM nodi > WHERE nodi.codaznodo = codazorig) > AS cinnodoorig, codazdest, ( > SELECT cinnodo FROM nodi > WHERE nodi.codaznodo = codazdest)> AS cinnododest > FROM traduzionearchi You are? Against XPS? Or IDS? Or some other server? Which version? that is used to convert a field to another one (codazorig to > cinnodoorig and codazdest to cinnododest). > Then I want to use the obtained fiels to filter the data in another > table. I tried the following code but got an error message telling me > that the columns cinnododest and cinnodoorig could not be found. Which error number? Two column names - that doesn't sound like Informix. Here's the failing code: > > SELECT codazorig, na, nb, archi.cinarco, ( > SELECT cinnodo FROM nodi > WHERE nodi.codaznodo = codazorig) > AS cinnodoorig, codazdest, ( > SELECT cinnodo FROM nodi > WHERE nodi.codaznodo = codazdest)> AS cinnododest > FROM traduzionearchi,archi WHERE (archi.cinnodoor=cinnodoorig AND > archi.cinnododes=cinnododest); > > How can I fix this? Make sure you use a table name in front of every column name. You know where you think the columns are - the SQL compiler doesn't agree. In the first sub-query, which table contains codazorig? traduzionearchi or archi or nodi (probably not the latter, but ...). What about cinnodo? Similarly with codazdest? Where does cninnodoorig come from? In the last line, connododest looks more like a table reference than your current problem. -- Jonathan Leffler #include <disclaimer.h> Email: jleffler@earthlink.net, jleffler@us.ibm.com Guardian of DBD::Informix v2005.02 -- http://dbi.perl.org/ sending to informix-list