CODEWITHSUNDEEP
sqlt-sql
James123
James123

Reputation: 11652

Return 'Yes' or No' from select statement?

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?

Upvotes: 1

Views: 14123

Answers (3)

siride
siride

Reputation: 209655

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 WHENs match, then the result in the ELSE is used.

Upvotes: 0

Oli
Oli

Reputation: 103

In Oracle, you could also use

select NVL(s.Sample_Number, 'N')

to return N in case of null value

(of course you still need something to have Y in case of not null.)

Upvotes: 0

David
David

Reputation: 34563

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

Upvotes: 4

Related Questions