How to do more things in DB2

Ian Boyd

The continuing saga of Ian trying to learn DB2-SQL

18. CASE Statements

Are these even possible:

SELECT
CASE
WHEN v.id=1 THEN 'Hello';
WHEN v.id=2 THEN ', ';
WHEN v.id=3 THEN 'world';
ELSE '!'
END CASE AS foo
FROM (VALUES 1, 2, 3, 4) AS v(id)

Desired output
foo
========
Hello
,
world
!

Another example:
SELECT
CustomerID,
SUM(
CASE
WHEN OrderValue > 1000 THEN OrderAmount;
ELSE 0
END CASE) AS TotalOfBigOrders
FROM Orders

Another example:

SELECT
i.InfractionID,
CASE
WHEN i.EmployeeID IS NOT NULL THEN (Employees.EmployeeName);
WHEN i.PatronID IS NOT NULL THEN (Patrons.PatronName);
ELSE 'Unknown Participant';
END CASE AS PersonDoingTheInfaction
FROM Infactions i
LEFT JOIN Patrons
ON i.PatronID = Patrons.PatronID
LEFT JOIN Employees
ON i.EmployeeID = Employees.EmployeeID

or another desired technique of the above:

SELECT
i.InfactionID,
CASE
WHEN i.EmployeeID IS NOT NULL THEN (
SELECT EmployeeName
FROM Employees
WHERE Employees.EmployeeID = i.EmployeeID);
WHEN i.PatronID IS NOT NULL THEN (
SELECT Patronname
FROM Patrons
WHERE Patrons.PatronsID = i.PatronID);
ELSE 'Unknown participant';
END CASE AS PersonDoingTheInfraction
FROM Infractions i

Mar 22 '06 #1

Gert van der Kooij

In article <dv*********@enews4.newsguy.com>, ia***********@avatopia.com
says...

The continuing saga of Ian trying to learn DB2-SQL

You can learn a lot reading the SQL Cookbook from Greame Birchall,
google for DB2 SQL Cookbook and check the first link.

Mar 22 '06 #2

Serge Rielau

Ian Boyd wrote:

The continuing saga of Ian trying to learn DB2-SQL

18. CASE Statements

Are these even possible:

SELECT
CASE WHEN v.id=1 THEN 'Hello'
WHEN v.id=2 THEN ', '
WHEN v.id=3 THEN 'world' ELSE '!' END AS foo FROM (VALUES 1, 2, 3, 4) AS v(id)

Desired output
foo
========
Hello
,
world
!

Another example:
SELECT
CustomerID,
SUM(
CASE WHEN OrderValue > 1000 THEN OrderAmount ELSE 0 END ) AS TotalOfBigOrders FROM Orders

Another example:

SELECT
i.InfractionID,
CASE WHEN i.EmployeeID IS NOT NULL THEN (Employees.EmployeeName)
WHEN i.PatronID IS NOT NULL THEN (Patrons.PatronName) ELSE 'Unknown Participant' END AS PersonDoingTheInfaction FROM Infactions i
LEFT JOIN Patrons
ON i.PatronID = Patrons.PatronID
LEFT JOIN Employees
ON i.EmployeeID = Employees.EmployeeID

or another desired technique of the above:

SELECT
i.InfactionID,
CASE
WHEN i.EmployeeID IS NOT NULL THEN (
SELECT EmployeeName
FROM Employees WHERE Employees.EmployeeID = i.EmployeeID) WHEN i.PatronID IS NOT NULL THEN (
SELECT Patronname
FROM Patrons WHERE Patrons.PatronsID = i.PatronID)
ELSE 'Unknown participant'
END AS PersonDoingTheInfraction FROM Infractions i

Works exactly the same in Oracle 9i and *squinthard* SQL Server 2000

Cheers
Serge

--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

Mar 22 '06 #3

Brian Tkatch

Ian Boyd wrote:

The continuing saga of Ian trying to learn DB2-SQL
That would explain quite a bit. :P

18. CASE Statements

Are these even possible:

SELECT
CASE
WHEN v.id=1 THEN 'Hello';
WHEN v.id=2 THEN ', ';
WHEN v.id=3 THEN 'world';
ELSE '!'
END CASE AS foo
FROM (VALUES 1, 2, 3, 4) AS v(id)

Desired output
foo
========
Hello
,
world
!

Drop the semi-colon after each clause, and the word "CASE" from "END
CASE"

SELECT
CASE
WHEN v.id=1 THEN 'Hello'
WHEN v.id=2 THEN ', '
WHEN v.id=3 THEN 'world'
ELSE '!'
END AS foo
FROM (VALUES 1, 2, 3, 4) AS v(id)

Also, is this particular case you can shorten it to:

SELECT
CASE v.id
WHEN 1 THEN 'Hello'
WHEN 2 THEN ', '
WHEN 3 THEN 'world'
ELSE '!'
END AS foo
FROM (VALUES 1, 2, 3, 4) AS v(id)
B.

Mar 23 '06 #4

Ian Boyd

> You can learn a lot reading the SQL Cookbook from Greame Birchall,

google for DB2 SQL Cookbook and check the first link.

Yeah yeah, i downloaded it. i'm getting to it! :)

Mar 23 '06 #5

Ian Boyd

> Drop the semi-colon after each clause, and the word "CASE" from "END

CASE"

i see my problem now. The DB2 documentation has "END CASE" and semi-colons.
i should have known that semi-colons are mandatory sometimes, optional
sometimes and forbidden sometimes.

As for the "END CASE", i have no explanation.

http://tinyurl.com/fq5vn

Mar 23 '06 #6

Serge Rielau

Ian Boyd wrote:

Drop the semi-colon after each clause, and the word "CASE" from "END
CASE"

i see my problem now. The DB2 documentation has "END CASE" and semi-colons.
i should have known that semi-colons are mandatory sometimes, optional
sometimes and forbidden sometimes.

As for the "END CASE", i have no explanation.

http://tinyurl.com/fq5vn

Because what you are using here is a CASE EXPRESSION, not a CASE STATEMENT.
CASE EXPRESSION is part of an expression and returns a scalar.
Since DB2 documentation apparently isn't good enough here is the link in
SQL Server 2005 BOL:
http://msdn2.microsoft.com/en-us/lib...5(SQL.90).aspx
Here it is in DB2, just in case:
http://publib.boulder.ibm.com/infoce...736.htm#casexp

Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

Mar 23 '06 #7

Knut Stolze

Brian Tkatch wrote:

Ian Boyd wrote:

The continuing saga of Ian trying to learn DB2-SQL

That would explain quite a bit. :P

18. CASE Statements

Are these even possible:

SELECT
CASE
WHEN v.id=1 THEN 'Hello';
WHEN v.id=2 THEN ', ';
WHEN v.id=3 THEN 'world';
ELSE '!'
END CASE AS foo
FROM (VALUES 1, 2, 3, 4) AS v(id)

Desired output
foo
========
Hello
,
world
!

Drop the semi-colon after each clause, and the word "CASE" from "END
CASE"

SELECT
CASE
WHEN v.id=1 THEN 'Hello'
WHEN v.id=2 THEN ', '
WHEN v.id=3 THEN 'world'
ELSE '!'
END AS foo
FROM (VALUES 1, 2, 3, 4) AS v(id)

You need an "ORDER BY id" at the end. Otherwise the order of the rows is
not guaranteed.

--
Knut Stolze
DB2 Information Integration Development
IBM Germany

Mar 23 '06 #8

Ian Boyd

> You need an "ORDER BY id" at the end. Otherwise the order of the rows is

not guaranteed.

The order of VALUES(1,2,3,4,5) is not guaranteed?

Mar 23 '06 #9

Knut Stolze

Ian Boyd wrote:

You need an "ORDER BY id" at the end. Otherwise the order of the rows is
not guaranteed.

The order of VALUES(1,2,3,4,5) is not guaranteed?

No, of course not. The only thing that guarantees the ordering of rows in a
result set is the ORDER BY clause. Everything else is just happenstance.

--
Knut Stolze
DB2 Information Integration Development
IBM Germany

Mar 23 '06 #10

Serge Rielau

Knut Stolze wrote:

Ian Boyd wrote:

You need an "ORDER BY id" at the end. Otherwise the order of the rows is
not guaranteed.

The order of VALUES(1,2,3,4,5) is not guaranteed?

No, of course not. The only thing that guarantees the ordering of rows in a
result set is the ORDER BY clause. Everything else is just happenstance.

There must be a German gene - nurture alone cannot do this.
Knut, I beg you. 1. Semester.. keep it simple. It was just an example.

Ian, read the thread "ORDER BY in view not working" over in
c.d.ms-sqlserver. It's been raging for weeks....
And let's pretend the topic of ORDER has never come up.

Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

Mar 24 '06 #11

Knut Stolze

Serge Rielau wrote:

Knut Stolze wrote:

Ian Boyd wrote:

You need an "ORDER BY id" at the end. Otherwise the order of the rows
is not guaranteed.
The order of VALUES(1,2,3,4,5) is not guaranteed?

No, of course not. The only thing that guarantees the ordering of rows
in a
result set is the ORDER BY clause. Everything else is just happenstance.

There must be a German gene - nurture alone cannot do this.
Knut, I beg you. 1. Semester.. keep it simple. It was just an example.

You're right. My apologies.

--
Knut Stolze
DB2 Information Integration Development
IBM Germany

Mar 24 '06 #12

Ian Boyd

> Ian, read the thread "ORDER BY in view not working" over in

c.d.ms-sqlserver. It's been raging for weeks....
And let's pretend the topic of ORDER has never come up.

i know how sets are by definition unordered lists. And i know
that order by does not work in a view. And you cannot guarantee
the return order of rows, even if the data is physically ordered by
some column.

But i thought, that even given all that, using
VALUES (1,2,3,4,5)

might,
just might,
happen to,
even though it isn't at all required to,
but internally the way it's done probably would,
but you still can't rely on it,
return values in the order:

1
2
3
4
5

But if it never will, okay then :)

Apr 3 '06 #13