|
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
| |
Share this Question
|
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. | |
|
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 | |
|
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. | |
|
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! :) | |
|
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 | |
|
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 | |
|
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 | |
|
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? | |
|
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 | |
|
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 | |
|
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 | |
|
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 :) | | This discussion thread is closed Replies have been disabled for this discussion. | |  Question stats - viewed: 1200
- replies: 12
- date asked: Mar 22 '06
|
