Re: Subquery or Join?

Hi Fernando, At the first look I like your solution, but I don't see how apply it over a real problem. See this example: create table father ( id int, name char(15)); create table son (idfather int, idson int, name char(15), dt_birth date); insert into father values (1, 'joao'); insert into father values (2, 'fabio'); insert into father values (3, 'lula'); insert into father values (4, 'obama'); insert into son values (1,1,'joazinho', '10/02/2000'); insert into son values (1,2,'maria', '19/05/2004'); insert into son values (1,3,'ana', '22/12/1998'); insert into son values (1,4,'antonia', '22/12/1998'); insert into son values (2,1,'fabiola', '05/03/1980'); insert into son values (3,1,'ricardo', '15/06/1995'); insert into son values (3,2,'icaro', '28/01/1994'); insert into son values (4,1,'julio', '10/09/1970'); insert into son values (4,2,'margaret', '17/01/1968'); -- how get the name of the father and the oldest son per line? select p.name, f.name from father p ,( select first 1 name, idfather from son order by dt_birth desc ) f where f.idfather = p.id -- this select don't work... they return only 1 line... -- Can't write the filter between the tables father and son in the subquery , -- return error 999 - Not implement yet. When this be implemented , then yes, will be a nice solution, but still undesired by developers, because you need to change a lot your SQL when you migrate from other database.... can't keep the SQL compatibility. For me,this is THE BIGGEST defect in IDS today ...  developers complain a lot about the need to rewrite theys SQLs... and I still don't see much effort of the IDS developer team to solve this situations... When a company study migrate the database and ask about Informix to  developers what is they opinion, frequently you see a "ugh" face...  :( I saw this face two weeks ago, exactly in this situation and coincidently this problem with first in subquery is one of others complains from developers to migrate the PHP code from SqlServer to Informix. I know.. already happen ten years ago with me before start my love with informix :) . We migrate from SqlServer 6.5 to IDS 7.31 ... I suffer a lot with limitations on 7.31 , like missing a clear way to cast a char to int Regards Cesar --- Em qui, 18/3/10, Fernando Nunes <domusonline@gmail.com> escreveu: De: Fernando Nunes <domusonline@gmail.com> Assunto: Re: Subquery or Join? Para: informix-list@iiug.org Data: Quinta-feira, 18 de Março de 2010, 20:20 This works on 11.50.xC6: CREATE TABLE TEST (         id INTEGER,        dt DATETIME YEAR TO SECOND ) LOCK MODE ROW; INSERT INTO TEST VALUES ( 1, "2009-01-01 00:00:01"); INSERT INTO TEST VALUES ( 1, "2009-01-01 00:00:02"); INSERT INTO TEST VALUES ( 1, "2009-01-01 00:00:03"); SELECT         t1.* FROM         test t1, (SELECT FIRST 1 dt FROM test t2 WHERE t2.id = 1 ORDER BY DT DESC) t3 WHERE         t1.id = 1 AND         t1.dt = t3.dt Regards. On Wed, Mar 17, 2010 at 10:34 PM, J. Hart <unleashedmaniac@gmail.com> wrote: I'm trying to query a table (lets call it "InvoicesTable") and I would like to include the invoices last update remark by the employee whose managing the invoice. In sql, I would probably do a subquery, using the TOP function, but informix doesn't seem to allow you to use the FIRST function within a subquery. In SQL, I would write it like: select i.invoice_number, (select top 1 u.remarks from user_comments u where u.invoice_number = i.invoice_number and u.employeeID = i.invoiceOwnerID order by u.input_date desc) from InvoicesTable i In informix, trying to use the same logic, I get the error "Cannot use 'first' in this context." I also tried doing a join, but I couldn't figure out how to get the last updated remark, it's just returning all remarks for that specific user. Sample Code Select i.invoice_number, u.remarks from InvoicesTable i left join user_comments u on u.invoice_number = i.invoice_number and u.employeeID = i.invoiceOwnerID Our informix database is a read only DR server. So, we are not able to create stored procedures/functions or temp tables... It's a pain in the neck dealing with the vendor and informix. Is this query possible or an I way off here? Thanks, James _______________________________________________ Informix-list mailing list Informix-list@iiug.org http://www.iiug.org/mailman/listinfo/informix-list -- Fernando Nunes Portugal http://informix-technology.blogspot.com My email works... but I don't check it frequently... -----Anexo incorporado----- _______________________________________________ Informix-list mailing list Informix-list@iiug.org http://www.iiug.org/mailman/listinfo/informix-list ____________________________________________________________________________________ Veja quais são os assuntos do momento no Yahoo! +Buscados http://br.maisbuscados.yahoo.com