Case conditions

[tomtalp]

Hey, I couldn't figure a way to do this - is there a way to have case conditions (case when x then ... etc) in a select query?

Thanks

[okv]

Hi, postgresql case condition currently is not supported.

[tomtalp]

I didn't mean case conditions for a specific engine, is this something that can be achieved for other things?

Is this something you guys are planning to add in the near future?

[okv]

If it's not for particular dialect, what exactly do you mean? Could you provide some examples?

[tomtalp]

I mean a query like this -

SELECT CASE WHEN a = 1 THEN 1 ELSE 0 END as col1

Here's an example -
https://www.1keydata.com/sql/sql-case.html

This is supported by many databases (SQL Server, Oracle, Postgres, MySQL)

[okv]

I didn't know that's kind of general thing, so now we could try to find general solution for that...
But currently we don't have time to do that.
Thank you anyway, issue will be open and I hope we will return back to it some day =)

[renjita]

Is there a way we can modify the existing code to implement the Case statements....

[artzhookov]

As a workaround you can do that with expression keyword.
Now you can use it in two ways:

1. like "select from expression":

var builder = require('json-sql')();
var sql = builder.build({
    type: 'select',
    expression: {
        pattern: 'case when {field} = {conditionValue} then {thenValue} else {elseValue} end',
        values: {
            field: {field: 'a'},
            conditionValue: 1,
            thenValue: 1,
            elseValue: 0
        }
    },
    alias: 'col1'
});
console.log('query:', sql.query)
// query: select * from case when "a" = 1 then 1 else 0 end as "col1";

Example above generates not valid query, there should be row-generator expression.

2. like "expression as field":

var builder = require('json-sql')();
var sql = builder.build({
    type: 'select',
    fields: [{
        expression: {
            pattern: 'case when {field} = {conditionValue} then {thenValue} else {elseValue} end',
            values: {
                field: {field: 'a'},
                conditionValue: 1,
                thenValue: 1,
                elseValue: 0
            }
        },
        alias: 'col1'
    }],
    table: 't'
});
console.log('query:', sql.query)
// query: select case when "a" = 1 then 1 else 0 end as "col1" from "t";

[artzhookov]

For more information take a look at the docs

[renjita]

Can we have

1. conditionValue as a.businessunit >= 600
2. Multiple when options for a single case
   like
   Case When i > 10 then "Yellow"
   When i > 20 then "Blue"
   When i > 30 then "Green"
   Else "Orange"
   End