Thread: What err ???
What err ???
SELECT RazaoSocial + ' - ' + Iif(Bairro Is Null,'',Bairro + ' ') + CGCCli As Coluna FROM Clientes WHERE RazaoSocial Like '%A%'
On Thu, 6 Jun 2002, Robson Martins wrote: > What err ??? > > SELECT RazaoSocial + ' - ' + Iif(Bairro Is Null,'',Bairro + ' ') + CGCCli As Coluna FROM Clientes WHERE RazaoSocial Like'%A%' Messages like this are better sent to pgsql-general or pgsql-sql, by the way. Two problems: . Strings are concatenated using ||, not +. 'Cat' || 'Dog' => 'CatDog', whereas 'Cat' + 'Dog' => ASCII result of this (not what you want) . IIf() is a nonstandard Microsoft function used in Access. SQL databases use CASE. See the docs for examples on CASE. - J. -- Joel BURTON | joel@joelburton.com | joelburton.com | aim: wjoelburton Independent Knowledge Management Consultant
On Thu, Jun 06, 2002 at 05:43:28PM -0300, Robson Martins wrote: > What err ??? > > SELECT RazaoSocial + ' - ' + Iif(Bairro Is Null,'',Bairro + ' ') + CGCCli As Coluna FROM Clientes WHERE RazaoSocial Like'%A%' This looks like some other dialect of SQL, not SQL92 (nor PostGreSQL). I _think_ you want something like: SELECT RazaoSocial || ' - ' || COALESCE(Bairro || ' ', '') || CGCCli As Coluna FROM Clientes WHERE RazaoSocial Like '%A%'; The differences are that the text concatenation operator is '||', not '+', and COALESCE(), which returns it's first non-NULL parameter. Note that this is _not_ an ADMIN question: it belongs on NOVICE. Ross -- Ross Reedstrom, Ph.D. reedstrm@rice.edu Executive Director phone: 713-348-6166 Gulf Coast Consortium for Bioinformatics fax: 713-348-6182 Rice University MS-39 Houston, TX 77005
On Thu, 6 Jun 2002 17:43:28 -0300 "Robson Martins" <robson-martins@bol.com.br> wrote: > What err ??? > > SELECT RazaoSocial + ' - ' + Iif(Bairro Is Null,'',Bairro + ' ') + CGCCli As Coluna FROM Clientes WHERE RazaoSocial Like'%A%' > Uh, there is no error in MS Access, but are two errors in PG: "+"and "iif()". You need to use "||" and "CASE WHEN ... THEN ... END". See docs. Regard, Masaru Sugawara
On Thu, 2002-06-06 at 21:43, Robson Martins wrote:
> What err ???
>
> SELECT RazaoSocial + ' - ' + Iif(Bairro Is Null,'',Bairro + ' ') + CGCCli As Coluna FROM Clientes WHERE RazaoSocial
Like'%A%'
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
You should probably be using COALESCE() here.
The string concatenation operator is not + but ||
All those capital letters will be folded to lower-case. If you have
actually created things in the database whose names contain capitals,
you will need to enclose those names in double-quotes.
--
Oliver Elphick Oliver.Elphick@lfix.co.uk
Isle of Wight http://www.lfix.co.uk/oliver
GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839 932A 614D 4C34 3E1D 0C1C
"There is a way that seems right to a man, but in the
end it leads to death."
Proverbs 16:25
Attachment
Try: SELECT RazaoSocial || ' - ' || coalesce(Bairro || ' ','') || CGCCli As Coluna FROM Clientes WHERE RazaoSocial Like '%A%'; >SELECT RazaoSocial + ' - ' + Iif(Bairro Is Null,'',Bairro + ' ') + CGCCli As Coluna FROM >Clientes WHERE RazaoSocial Like '%A%' Regards, Nick -------------------------------------------------------------------------- Nick Fankhauser nickf@ontko.com Phone 1.765.935.4283 Fax 1.765.962.9788 Ray Ontko & Co. Software Consulting Services http://www.ontko.com/
On Fri, 7 Jun 2002 09:37:08 -0500 "Ross J. Reedstrom" <reedstrm@rice.edu> wrote: > > SELECT RazaoSocial + ' - ' + Iif(Bairro Is Null,'',Bairro + ' ') + > > CGCCli As Coluna FROM Clientes WHERE RazaoSocial Like '%A%' > > This looks like some other dialect of SQL, not SQL92 (nor PostGreSQL). . . . > Note that this is _not_ an ADMIN question: it belongs on NOVICE. I have a Postgres admin question. I have a large number of accounting and marketing users that feel Access is a perfectly legitimate database for them to set up, populate with data (and nasty wierd queries), then hand over to me to administer (complaining that it doesn't run fast is usually a first symptom). I feel "format c:" is a perfectly good response to this scenario. Now I'm relegated to a corner of the copy room, and my personal workstation is a Mac Classic which, BTW, I can't get Postgres to compile on. Is there some indexing strategy or backup regimen I could implement that would make all this better? -- Tim Ellis DBA, Gamet
> I have a Postgres admin question. ... > Is there some indexing strategy or backup regimen I could implement that > would make all this better? Tim- I don't understand the question- are you asking how to make databases created in access run better on postgresql, or how to move them to postgresql, or simply how to make the run better in access? Maybe if you restated the question you'd get some better responses. -Nick -------------------------------------------------------------------------- Nick Fankhauser nickf@ontko.com Phone 1.765.935.4283 Fax 1.765.962.9788 Ray Ontko & Co. Software Consulting Services http://www.ontko.com/
On Fri, 7 Jun 2002 16:44:16 -0500 "Nick Fankhauser" <nickf@ontko.com> wrote: > > Is there some indexing strategy or backup regimen I could implement > > that would make all this better? > > I don't understand the question- are you asking how to make databases > created in access run better on postgresql, or how to move them to > postgresql, or simply how to make the run better in access? > > Maybe if you restated the question you'd get some better responses. Sure! No problem. When boiled down to its essence, my problem is that I have users with conflicting requirements (runs fast, is stable, and is a Microsoft-written database). It drives me nuts, so I want to just format their C: drive and migrate their data & tables to Postgres (or Oracle or Sybase, it really doesn't matter -- could be SOLID for all I care). But I have a very low threshold of ability to communicate with these users, so I just format their C: drive. Then my bosses put me on a Mac Classic workstation. And gosh, Postgres doesn't compile on Mac Classic. Since I'm a Database Administrator, and I like to solve problems by analysing indexing strategies and modifying backup regimens, I was hoping there was some technical solution to this very personal administration problem (ie: users drive me nuts, I delete their data, my bosses relegate me to the back corner of the copy room). Okay. I'll fess up. I found it humorous that someone should ask a question that amounted to: "I copied SQL directly from a Microsoft product into a non-Microsoft product, and I'm surprised it didn't work." Thus, I attempted (perhaps more poorly than necessary) to humorously ask another completely off-base question that alluded to a common administrator's problem: inability to communicate with users. It was truly an administrative question, but presented possible technical solutions that are entirely inappropriate to solve the problem. Give me a break. It's a slow day. There are only 7 people left in the office. :) -- Tim Ellis DBA, Gamet
Ok, Nick.Masaru,Joel. Very Thanks! Nick. It helped sufficiently. :-) Brasil vai ganhar a Copa do Mundo! Robson Martins From Brazil ----- Original Message ----- From: "Nick Fankhauser" <nickf@ontko.com> To: "Robson Martins" <robson-martins@bol.com.br>; <pgsql-admin@postgresql.org> Sent: Friday, June 07, 2002 12:49 PM Subject: RE: [ADMIN] What err ??? > Try: > > SELECT RazaoSocial || ' - ' || coalesce(Bairro || ' ','') || CGCCli As > Coluna FROM Clientes WHERE RazaoSocial Like '%A%'; > > > >SELECT RazaoSocial + ' - ' + Iif(Bairro Is Null,'',Bairro + ' ') + CGCCli > As Coluna FROM > >Clientes WHERE RazaoSocial Like '%A%' > > > Regards, > > Nick > > > -------------------------------------------------------------------------- > Nick Fankhauser nickf@ontko.com Phone 1.765.935.4283 Fax 1.765.962.9788 > Ray Ontko & Co. Software Consulting Services http://www.ontko.com/ > >
> Okay. I'll fess up. Ok... it was Friday, I was a little slow. It's Monday now... I'm still a little slow, but I get it now! (chuckle.) In all seriousness, I usually give people the benefit of the doubt on some of the weird questions because we have many users who don't use English as their primary language, and an appropriate question often seems off the wall in translation. I also figure that as a recent "newbie" it is my responsibility to field some of these obvious newbie questions so the serious postgresql gurus can focus on the more challenging issues. (Like my silly questions!) -Nick <grinning> -------------------------------------------------------------------------- Nick Fankhauser nickf@ontko.com Phone 1.765.935.4283 Fax 1.765.962.9788 Ray Ontko & Co. Software Consulting Services http://www.ontko.com/