case statement

[ilkergurelli]

usage of case statement in select fields.

eg.
select id,
case when id = 0 then 'zero'
when id = 1 then 'one'
else null end
from dual

or

select id,
case id
when 0 then 'zero'
when 1 then 'one'
else null end
from dual

[erayhanoglu]

Before making implementation we need research for other dialects. eg. postre, mssql, mysql..
Can you make a research for us?

[ilkergurelli]

postgreSql, firebird, oracle, mysql, db2 are using same syntax.
Also SQL Server and Azure SQL Database are using same syntax but
Azure SQL Data Warehouse and Parallel Data Warehouse are using only Searched CASE expression.

-- Syntax for SQL Server and Azure SQL Database

Simple CASE expression:
CASE input_expression
WHEN when_expression THEN result_expression [ ...n ]
[ ELSE else_result_expression ]
END
Searched CASE expression:
CASE
WHEN Boolean_expression THEN result_expression [ ...n ]
[ ELSE else_result_expression ]
END

-- Syntax for Azure SQL Data Warehouse and Parallel Data Warehouse

CASE
WHEN when_expression THEN result_expression [ ...n ]
[ ELSE else_result_expression ]
END

[erayhanoglu]

Oracle supports searched CASE expressions also.
http://docs.oracle.com/cd/B19306_01/server.102/b14200/expressions004.htm
I guess, PosgreSql too, They names it "Conditional Expressions"
https://www.postgresql.org/docs/8.2/static/functions-conditional.html

Can you lookup for others?

[ilkergurelli]

i mean all dbs that i listed above (postgreSql, firebird, oracle, mysql, db2, SQL Server, Azure SQL Server) support both expressions except Azure SQL Data Warehouse and Parallel Data Warehouse support only "Searched CASE expression"
https://docs.microsoft.com/en-us/sql/t-sql/language-elements/case-transact-sql

[erayhanoglu]

case expression support added.
Here is example usage
statement = sqb.select( sqb.case().when(['id', 1], 'or', ['id', 2]).then(5).when('col2', 3).else(100).as('col1') ).from('table1')