Re: Informix sql - help
Jorge Silva <mop45352@mail.telepac.pt> wrote in message news:... > How can I solve this ? > > I have two tables one - clientes (customer) other codigos_postais. > In clientes one of the columns is cli_cidade (customer_city) and have > "LISBOA" as one of the cities, the other (codigos_postais) have the column > local with "Lisboa". > > If I want to use a condition like > ... where clientes.cli_cidade matches codigos_postais.local, it doesn't > work > because "LISBOA" don't match with "Lisboa" > > Is there one instruction in Informix Sql (7.20) to solve this ? > > > Thanks > Jorge > > >
Jorge Silva wrote: > Jorge Silva <mop45352@mail.telepac.pt> wrote in message news:... > > How can I solve this ? > > > > I have two tables one - clientes (customer) other codigos_postais. > > In clientes one of the columns is cli_cidade (customer_city) and have > > "LISBOA" as one of the cities, the other (codigos_postais) have the > column > > local with "Lisboa". > > > > If I want to use a condition like > > ... where clientes.cli_cidade matches codigos_postais.local, it doesn't > > work > > because "LISBOA" don't match with "Lisboa" > > > > Is there one instruction in Informix Sql (7.20) to solve this ? The only instruction available as standard in IDS 7.2x to deal with this is "Upgrade". The 7.3x servers provide UPPER and LOWER functions which would allow you to deal with the problem. If you mean Informix-SQL 7.20, then we need to know whether you're using a form or a report or the query language, and what server version you are using. In forms, I think you're stuck. In reports or query-language, you can use UPPER/LOWER if your server supports it. In the interests of completeness, I'll point out that the IIUG has some stored procedures which implement UPPER and LOWER for 7.2x servers, but they are excruciatingly slow because SPL is excruciatingly obtuse when it comes to substrings. You're still recommended to upgrade. -- Jonathan Leffler (jleffler@informix.com, jleffler@earthlink.net) Guardian of DBD::Informix v1.00.PC1 -- see http://www.perl.com/CPAN #include <disclaimer.h>
In article <39327C82.DB89EC5E@earthlink.net>, Jonathan Leffler <jleffler@earthlink.net> writes >Jorge Silva wrote: > >> Jorge Silva <mop45352@mail.telepac.pt> wrote in message news:... >> > How can I solve this ? >> > >> > I have two tables one - clientes (customer) other codigos_postais. >> > In clientes one of the columns is cli_cidade (customer_city) and have >> > "LISBOA" as one of the cities, the other (codigos_postais) have the >> column >> > local with "Lisboa". >> > >> > If I want to use a condition like >> > ... where clientes.cli_cidade matches codigos_postais.local, it doesn't >> > work >> > because "LISBOA" don't match with "Lisboa" >> > >> > Is there one instruction in Informix Sql (7.20) to solve this ? > Add an extra column which stores the field in uppercase, add an index on the uppercase column and search on that. The only quick solution. >The only instruction available as standard in IDS 7.2x to deal >with this is "Upgrade". The 7.3x servers provide UPPER and LOWER >functions which would allow you to deal with the problem. > >If you mean Informix-SQL 7.20, then we need to know whether you're >using a form or a report or the query language, and what server >version you are using. In forms, I think you're stuck. In reports >or query-language, you can use UPPER/LOWER if your server supports it. > >In the interests of completeness, I'll point out that the IIUG has >some stored procedures which implement UPPER and LOWER for 7.2x servers, >but they are excruciatingly slow because SPL is excruciatingly obtuse >when it comes to substrings. You're still recommended to upgrade. > > >-- >Jonathan Leffler (jleffler@informix.com, jleffler@earthlink.net) >Guardian of DBD::Informix v1.00.PC1 -- see http://www.perl.com/CPAN >#include <disclaimer.h> > > -- David Williams