CODEWITHSUNDEEP
sqlsql-server
user5021612
user5021612

Reputation:

How to check whether a value exists?

we have a table (sales plan form MDS) like

> 20171201 2017 12 2.155.125 ...some other values 
> 20171101 2017 11 2.155.125 ...some other values

and I want to check wheter a record for current month exists (say 2018/01).

I've tried:

IF EXISTS
    (
        SELECT
            spc.DateID
        FROM
            dim.SalesPlanCountry spc
            JOIN dim.Calendar cal ON spc.DateID = cal.DateID
        WHERE
            cal.CalendarYear = @Year
            AND cal.MonthOfYear = @Month
    ) THEN 
        SELECT 'OK'
    ELSE SELECT 'Missing'

which returns

Incorrect syntax near the keyword 'THEN'.

Help would be appreciated.

Upvotes: 0

Views: 57

Answers (3)

Thom A
Thom A

Reputation: 95561

Could this not be simplified down to...

SELECT CASE COUNT(*) WHEN 0 THEN 'Missing' ELSE 'OK' END
FROM dim.SalesPlanCountry spc
     JOIN dim.Calendar cal ON spc.DateID = cal.DateID
WHERE cal.CalendarYear = @Year
  AND cal.MonthOfYear = @Month;

No need for an EXISTS. When doing a COUNT of a dataset, a value will always be returned, even if the data set returns no rows.

Upvotes: 0

Valerica
Valerica

Reputation: 1630

Like I said you don't need the THEN keyword, just use SELECT <values> or use a CASE WHEN statement (see phoniq answer)

IF EXISTS (
    SELECT
        spc.DateID
    FROM dim.SalesPlanCountry spc
    JOIN dim.Calendar cal
        ON spc.DateID = cal.DateID
    WHERE cal.CalendarYear = @Year
        AND cal.MonthOfYear = @Month
)
SELECT 'OK'
ELSE 
SELECT 'Missing'

Upvotes: 1

phoniq
phoniq

Reputation: 238

SELECT CASE WHEN spc.DateID = NULL THEN 'OK' ELSE 'Missing' END
FROM
  dim.SalesPlanCountry spc
  JOIN dim.Calendar cal ON spc.DateID = cal.DateID
WHERE
  cal.CalendarYear = @Year
  AND cal.MonthOfYear = @Month

Upvotes: 0

Related Questions