CODEWITHSUNDEEP
sqlsql-servercoalesce
Joojoo
Joojoo

Reputation: 105

SQL Server: more than two tables for coalesce function

I need help, I managed to get successful query for two tables, but I got error when I am trying to do more than two table. please help me.

This is the SQL:

select 
    coalesce(a.LOT_ID, b.LOT_ID,c.LOT_ID,d.LOT_ID) as LotId, 
    coalesce(a.CheckIn, b.CheckIn,c.CheckIn, d.CheckIn) as CheckIn,
    coalesce(a.CheckOut, b.CheckOut,c.CheckOut, d.CheckOut) as CheckOut,
    coalesce(a.StatusDesc, b.StatusDesc,c.StatusDesc, .StatusDesc) as StatusDesc
from 
    LOT_LOC_BOND a, LOT_LOC_IEBT b,LOT_LOC_MBT c,LOT_LOC_SEAL d
where   
    a.LOT_ID = b.LOT_ID,
    AND c.LOT_ID = d.LOT_ID

and the error stated

Incorrect syntax near ','.

Upvotes: 3

Views: 6070

Answers (1)

Pரதீப்
Pரதீப்

Reputation: 93734

Missing alias name at the end

coalesce(a.StatusDesc, b.StatusDesc,c.StatusDesc, .StatusDesc) --d is missing

start using INNER JOIN syntax to join tables

SELECT COALESCE(nullif(a.LOT_ID,''), nullif(b.LOT_ID,''), nullif(c.LOT_ID,''), nullif(d.LOT_ID,'')) AS LotId,
       COALESCE(a.CheckIn, b.CheckIn, c.CheckIn, d.CheckIn) AS CheckIn,
       COALESCE(a.CheckOut, b.CheckOut, c.CheckOut, d.CheckOut) AS CheckOut,
       COALESCE(a.StatusDesc, b.StatusDesc, c.StatusDesc, d.StatusDesc) AS StatusDesc --missing alias name d  
FROM   LOT_LOC_BOND a
       INNER JOIN LOT_LOC_IEBT b
               ON a.LOT_ID = b.LOT_ID
       INNER JOIN LOT_LOC_MBT c
               ON a.LOT_ID = c.LOT_ID
       INNER JOIN LOT_LOC_SEAL d
               ON c.LOT_ID = d.LOT_ID

Upvotes: 2

Related Questions