CODEWITHSUNDEEP
sqlvariablesselect
Tu Hoang
Tu Hoang

Reputation: 4712

SQL - Temporary variable inside SELECT

So I just found myself doing something like this:

SELECT
    Foo = 
    CASE
        WHEN a.Foo IS NULL THEN (SELECT x.Foo FROM x WHERE x.Id = a.Id)
        ELSE a.Foo
    END,
    Bar =
    CASE
        WHEN a.Bar IS NULL THEN (SELECT x.Bar FROM x WHERE x.Id = a.Id)
        ELSE a.Bar
    END,
    Alice = 
    CASE
        WHEN a.Alice IS NULL THEN (SELECT x.Alice FROM x WHERE x.Id = a.Id)
        ELSE a.Alice
    END
FROM a INNER JOIN b ON a.X = b.X

That doesn't look pretty. So I am wondering whether there is any way to create a temporary variable such as:

WITH Temp AS SELECT * FROM x WHERE x.Id = a.Id

inside the scope of SELECT and refer to it?

Upvotes: 1

Views: 1033

Answers (3)

Mudassir Hasan
Mudassir Hasan

Reputation: 28761

SELECT  CASE WHEN a.Foo IS NULL THEN x.Foo ELSE a.Foo End as Foo,
        CASE WHEN a.Bar IS NULL THEN x.Bar ELSE a.Bar End  as Bar,
        CASE WHEN a.Alice IS NULL THEN x.Alice ELSE a.Alice End as Alice
FROM    a 
        INNER JOIN b ON a.X = b.X
        INNER JOIN x ON a.ID = x.ID

Upvotes: 0

peterm
peterm

Reputation: 92795

Try

SELECT 
       Foo   = COALESCE(a.Foo, x.Foo),
       Bar   = COALESCE(a.Foo, x.Foo),
       Alice = COALESCE(a.Alice, x.Alice)
  FROM a JOIN 
       b ON a.X = b.X LEFT JOIN
       x ON x.Id = a.Id

or

SELECT 
       COALESCE(a.Foo, x.Foo) Foo,
       COALESCE(a.Foo, x.Foo) Bar,
       COALESCE(a.Alice, x.Alice) Alice
  FROM a JOIN 
       b ON a.X = b.X LEFT JOIN
       x ON x.Id = a.Id

Upvotes: 2

John Woo
John Woo

Reputation: 263783

how using join?

SELECT  COALESCE(a.Foo, x.Foo) Foo,
        COALESCE(a.Bar, x.Bar) Bar,
        COALESCE(a.Alice, x.Alice) Alice
FROM    a 
        INNER JOIN b ON a.X = b.X
        LEFT JOIN x ON a.ID = x.ID

Upvotes: 1

Related Questions