Re: Subquery or Join?
Check bellow if my way to workaround can be useful for you.. they have some datatype limitation... but you can create others procedures for another datatypes...
And this solution you gain one more nice resource, they include all lines in the same field (remove the "first 1").
Hey , Informix Developers! When you guys will include this resource in IDS!??? For years I suffers with this limitation and I know about a lot of others developers with the same problem... I don't know how this resource don't appear in the IIUG Survey as most requested..
--------------------
CREATE procedure show_dados( pSet multiset(varchar(254) not null ))
RETURNING char(800) ;
DEFINE vNome char(20) ;
DEFINE vRes varchar(200) ;
let vRes="" ;
let vNome="" ;
FOREACH cursor_a FOR
SELECT tSet.nome INTO vNome
FROM table(pSet) as tSet(nome)
order by 1
let vRes=trim(vRes)||" "||trim(vNome);
END FOREACH ;
RETURN trim(vRes) ;
end procedure ;
select t.tabname , show_dados(MULTISET(select first 1 item colname from syscolumns c where c.tabid = t.tabid )) as colname
from systables t
--------------------for more information :
http://www.imartins.com.br/informix/artigos/manipular-dados-tipo-collection-conjunto-set-list-multiset
(This site is in Portuguese, but you can translate with google, use the flag on upper right of the page).
--- Em qua, 17/3/10, J. Hart <unleashedmaniac@gmail.com> escreveu:
De: J. Hart <unleashedmaniac@gmail.com>
Assunto: Subquery or Join?
Para: informix-list@iiug.org
Data: Quarta-feira, 17 de Março de 2010, 19:34
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 andu.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
____________________________________________________________________________________
Veja quais são os assuntos do momento no Yahoo! +Buscados
http://br.maisbuscados.yahoo.com