[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 751
  • Last Modified:

SQL IF STATEMENT

I'm trying to use an if statement in my SELECT clause and I am having issues.  Here is an example:

Select field1, field2,
IF field3 is NULL
   BEGIN
       'TBD'
   END
ELSE
  BEGIN
      field3
  END  AS myField
From myTable

Above will not execute.  What is the fix?

Also field3 is a datefield and I want it to output as "Thu, 08/23/12".

Thanks
0
CipherIS
Asked:
CipherIS
  • 4
  • 3
2 Solutions
 
Kyle AbrahamsSenior .Net DeveloperCommented:
You can use a case statement instead:

IF field3 is NULL
   BEGIN
       'TBD'
   END
ELSE
  BEGIN
      field3
  END  AS myField
From myTable


becomes
  select case when field3 is null then 'TBD' else field3 as MYfield
 from <table>

that's the literal interpretation of "IF"


you can also just use
   select isnull(field3, 'TBD') MyField from <table>



The IF statement is allowed in SQL, but control's program code.


 if (select field3 from <table> where ...) is null
    select 'TBD' myField
else
   select field3 myfield from <table> where ...
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
Select field1, field2,
ISNULL( field3,'TBD') as MyField

From myTable
0
 
CipherISAuthor Commented:
@ged325

I used

select field1, field2, case when field3 is null then 'TBD' else field3 as MYfield, field4 from mytable Where blah blah blah

And I receive the below Error.

Msg 156, Level 15, State 1, Line 7
Incorrect syntax near the keyword 'AS'.
0
Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

 
Aneesh RetnakaranDatabase AdministratorCommented:
did you try mine ?

select field1, field2, case when field3 is null then 'TBD' else field3 end  as MYfield, field4 from mytable Where blah blah blah
0
 
CipherISAuthor Commented:
@aneeshattingal - thanks - that helped me see i was missing the "END".  Now I'm receiving this error.

Msg 241, Level 16, State 1, Line 2
Conversion failed when converting date and/or time from character string.

field3 is a date field.  Any idea how to handle?
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
select field1, field2, case when field3 is null then cast( 'TBD' as varchar(20) ) else cast(field3 as varchar(20) )end  as MYfield, field4 from mytable Where blah blah blah
0
 
CipherISAuthor Commented:
thx
0
 
CipherISAuthor Commented:
Awesome
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now