0

I have a sql query taking everything from one table (sdrcraw) and populating it in a 2nd table. The 2nd table has an additional column called 'local' and this gets populated with a join on country from the localelookup table.

This works as it should. I would like to add a default value in the event that a country is missing from the locallookup table. If the join returns a null then set that null local to 'AA' as an example.

sdrcraw.BIGuid, 
sdrcraw.SubsciberKey, 
sdrcraw.FIrstName, 
sdrcraw.Accountnumber, 
sdrcraw.Country, 
sdrcraw.MobileNo, 
localelookup.locale
FROM sdrcraw
LEFT JOIN [localelookup]
ON sdrcraw.country = localelookup.country

1
  • 1
    also got it work this way. COALESCE(localelookup.locale,'AA') AS locale
    – lyslexic
    Feb 15, 2023 at 7:38

2 Answers 2

1

You could use a case statement as following:

SELECT
    sdrcraw.BIGuid,
    sdrcraw.SubsciberKey,
    sdrcraw.FIrstName,
    sdrcraw.Accountnumber,
    sdrcraw.Country,
    sdrcraw.MobileNo,
    CASE
        WHEN localelookup.locale IS NULL THEN 'AA'
        ELSE localelookup.locale
    END AS locale
FROM
    sdrcraw
    LEFT JOIN [localelookup] ON sdrcraw.country = localelookup.country
1
  • thanks, I did try a version of case but got an error. copied and pasted and getting this error message. doesn't like the IS NULL. 'An error occurred while checking the query syntax. Errors: Incorrect syntax near the keyword 'IS'. '
    – lyslexic
    Feb 14, 2023 at 13:39
2

@Lyslexic - Should not the CASE logic be like this (see below)--basically, you are missing WHEN after CASE keyword:

CASE WHEN localelookup.locale IS NULL THEN 'AA' ELSE localelookup.locale END AS locale

1
  • Right, I should’ve tested my query before posting. Thanks for pointing that out, I’ve updated my answer. Feb 15, 2023 at 4:22

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .