By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
437,557 Members | 1,067 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 437,557 IT Pros & Developers. It's quick & easy.

How to do more things in DB2

P: n/a
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
Share this Question
Share on Google+
12 Replies


P: n/a
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

P: n/a
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

P: n/a
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

P: n/a
> 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

P: n/a
> 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

P: n/a
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

P: n/a
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

P: n/a
> 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

P: n/a
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

P: n/a
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

P: n/a
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

P: n/a
> 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

This discussion thread is closed

Replies have been disabled for this discussion.