Question: `tbl_LoanSummary` has `Sample_Number` column. I have to check if `Sample_Number` column is not null the return 'Y' otherwise return return 'N' from below select statement.

```
  select a.Br_Loan_No ,a.Br_LookupKey, //return  IsNull(s.Sample_Number) ='N' or 'Y'                            
  from dbo.tbl_Br a left outer join dbo.tbl_LoanSummary s 
  on s.Loan_no = a.Br_Loan_No order by a.Br_Loan_No

```

How to do this?

Answer: 
You can use the `case` expression for this...

```
select a.Br_Loan_No,
    a.Br_LookupKey,
    CASE WHEN s.Sample_Number IS NULL THEN 'N' ELSE 'Y' END AS [HasSample]
from dbo.tbl_Br a left outer join dbo.tbl_LoanSummary s 
on s.Loan_no = a.Br_Loan_No order by a.Br_Loan_No

```
You'll want to use a `CASE` expression. It's like an embedded if-statement or switch-statement from traditional programming languages.

```
SELECT a.Br_Loan_No,
       a.Br_LookupKey
       CASE
           WHEN s.Sample_Number IS NULL THEN 'N'
           ELSE 'Y'
       END AS sample_number_is_not_null
  FROM dbo.tbl_Br a
  LEFT JOIN dbo.tbl_LoanSummary s
    ON s.Loan_no = a.Br_Loan_No
 ORDER BY a.Br_Loan_no

```

Note that you are creating a computed column here, rather than selecting the raw value of an existing column. It's generally required that you give this column a name, thus the use of the `AS sample_number_is_not_null`.

There are two forms of the `CASE` expression. One lets you compare a column or value against several choices. It is like using an implicit equals:

```
CASE foo
    WHEN 3 THEN 'foo is 3!'
    WHEN 4 THEN 'foo is 4!'
    ELSE 'foo is not 3 or 4'
END

```

The other form, in the example at the top, lets you use arbitrary expressions in each `WHEN` clause. It should be noted that each `WHEN` clause is evaluated in order and the first one to match is the one whose `THEN` is used as the result. If none of the `WHEN`s match, then the result in the `ELSE` is used.