lundi 29 juin 2015

SELECT Statement in CASE

Please don't downgrade this as it is bit complex for me to explain. I'm working on data migration so some of the structures look weird because it was designed by someone like that.

For ex, I have a table Person with PersonID and PersonName as columns. I have duplicates in the table.

I have Details table where I have PersonName stored in a column. This PersonName may or may not exist in the Person table. I need to retrieve PersonID from the matching records otherwise put some hardcode value in PersonID.

I can't write below query because PersonName is duplicated in Person Table, this join doubles the rows if there is a matching record due to join.

SELECT d.Fields, PersonID
FROM Details d
JOIN Person p ON d.PersonName = p.PersonName

The below query works but I don't know how to replace "NULL" with some value I want in place of NULL

SELECT d.Fields, (SELECT TOP 1 PersonID FROM Person where PersonName = d.PersonName )
FROM Details d

So, there are some PersonNames in the Details table which are not existent in Person table. How do I write CASE WHEN in this case?

I tried below but it didn't work

 SELECT d.Fields, CASE WHEN (SELECT TOP 1 PersonID FROM Person where PersonName = d.PersonName) = null THEN 123 ELSE (SELECT TOP 1 PersonID FROM Person where PersonName = d.PersonName) END Name
    FROM Details d

This query is still showing the same output as 2nd query. Please advise me on this. Let me know, if I'm unclear anywhere. Thanks

Aucun commentaire:

Enregistrer un commentaire