Обсуждение: What err ???

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

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/