CODEWITHSUNDEEP
mysqlsql
Rostyslav Fedyk
Rostyslav Fedyk

Reputation: 307

Return 1 or 0 in SQL depending on the multiple statements

If I find that some of the user exists with such a parameters, I want to get 1 otherwise 0. In the future I'll have to add more blocks. But it doesn't seem to work now. What am I doing wrong?

 SELECT CAST(CASE WHEN EXISTS(SELECT 1
                              FROM Customers
                              WHERE Country = 'France' AND PostalCode%2 = 0)
                              OR (WHERE Country = 'Germany' AND PostalCode%2 = 0))
                              ) 
                  THEN 1 
                  ELSE 0 
              END AS BIT)

Upvotes: 1

Views: 56

Answers (2)

Sturgus
Sturgus

Reputation: 686

It looks to me like you're just having issues with your bracketing:

SELECT CAST(
    CASE WHEN EXISTS(
        SELECT 1
        FROM Customers
        WHERE (Country = 'France' AND PostalCode%2 = 0)
        OR (Country = 'Germany' AND PostalCode%2 = 0)
    ) THEN 1 ELSE 0 END AS BIT)

Building on Gordon's assumption that PostalCode%2 = 0 for all tested 'sets' of conditionals (you haven't said as much yet), you could likewise shorten this to:

SELECT CAST(
    CASE WHEN EXISTS(
        SELECT 1
        FROM Customers
        WHERE PostalCode%2 = 0 
        AND Country IN ('France', 'Germany')
    ) THEN 1 ELSE 0 END AS BIT)

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1270391

You need two separate exists:

SELECT CAST(CASE WHEN EXISTS (SELECT 1
                              FROM Customers
                              WHERE Country = 'France' AND PostalCode%2 = 0
                             )
                 THEN 1
                 WHEN EXISTS (SELECT 1
                              FROM Customers
                              WHERE Country = 'Germany' AND PostalCode%2 = 0
                             )
                 THEN 1
                 ELSE 0
            END AS BIT)

Actually, I broke this into two separate THEN clauses. This is almost equivalent to using OR, but because the logic is inside a CASE, THEN seems more natural. (The difference is that the optimizer could choose to re-arrange the OR conditions, but the THEN conditions are executed in lexical order.)

If your statements are actually this simple, you can combine them as:

SELECT CAST(CASE WHEN EXISTS (SELECT 1
                              FROM Customers
                              WHERE Country IN ('France', 'Germany') AND PostalCode%2 = 0
                             )
                 THEN 1
                 ELSE 0
            END AS BIT)

Upvotes: 2

Related Questions