Conditional SQL 2

[TStriker]

Simple SQL question I think.

I'm trying to return the buyer's name for each item in the ITEM table. The buyer's name comes from the PERS table. Some items have a buyer and some don't. Here are the column names:

pers.first_name
pers.last_name

I want to return a result like this:

Doe, John

This SQL works:

SELECT pers.last_name || ', ' || pers.first_name FROM ...

However, sometimes there is no buyer; then it returns only a comma (,). I would like to lose the comma if the name column(s) are null.

How is this done?

Thanks in advance!

-Striker

 

[jstreich]

Your looking for case when(...) ... else ... end something like:

SELECT pers.last_name || case when(pers.last_name <> null) ', ' else 'Buyer Unknown' end || pers.first_name FROM ...

[plug=shameless]

http://game-master.us/phpx-3.4.0/

[/plug]

 

[TStriker]

jstreich,

You got me close. Here's what I have and it works:

last_name || CASE WHEN pers.last_name IS NULL THEN '' ELSE ', ' END || pers.first_name

Thanks a bunch!

-Striker

 

[SantaMufasa]

Striker,

There is an easier way:

SELECT ltrim(pers.last_name || ', ' || pers.first_name,', ') FROM...

Let us know if this is useful.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[TStriker]

Yes, that IS useful! The ltrim function isn't in the old Oracle University - Intro to SQL book that I have.

I'll have to file this away for future use.

Thanks Dave!

-Striker

 

[SantaMufasa]

Actually, Striker, after having taught Oracle University's Intro to SQL for nine years, I can verify that they never include everything in the Intro materials that you should know. If they did, then they couldn't hook you into their Advanced class. <grin>

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]