IF THEN

Question

Post reply

IF THEN

Investor_Ennui

SSC Veteran

Points: 272
More actions
March 6, 2009 at 8:04 am

#200574

Greetings,
I have a bit of a dumb question. What is the SQL equivilent for an IF THEN statement? I have been using CASE, but in a recent query - it just isn't getting the job done. I have a feeling that a good old IF THEN statement would work properly, but I can't find any information on creating them.

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply

spaghettidba SSC Guru Points: 105732 More actions · Answer 1

IF condition

BEGIN

code if true

END

ELSE

BEGIN

code if false

END

-- Gianluca Sartori

Investor_Ennui SSC Veteran Points: 272 More actions · Answer 2

Thanks, you have me on the correct track. However, I'm getting syntax errors. Am I using it wrong?

SELECTaccount_nbr, symbol, o_trade_nbr AS trade_nbr, o_record_type AS record, o_trade_date AS trade_date,

cusip_nbr, shares, o_price AS price, price_dt, closing_price_amt AS T

FROMdbo.fab_open_taxlot_vw,

dbo.ps_sec_price_hst_vw

WHEREaccount_nbr = '123456789'

ANDo_trade_date BETWEEN '06/30/07' AND '07/01/08'

IFo_trate_date = 1 OR o_trade_date = 2

BEGIN(price_dt BETWEEN (o_trade_date - 4) AND (o_trade_date + 3))

END

ELSE

BEGIN(price_dt BETWEEN (o_trade_date - 2) AND (o_trade_date + 2))

END

ANDcusip_nbr = cusip_num

ANDo_record_type = 'B'

ANDsymbol = 'EOG'

AND(o_CORP_ACTION_FLAG <> 'O'

ANDo_CORP_ACTION_FLAG <> 'S')

spaghettidba SSC Guru Points: 105732 More actions · Answer 3

I was posting the query when I saw Gail's reply.

Use her code and you'll never go wrong 🙂

-- Gianluca Sartori

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 4

IF.. ELSE is a control flow statement that affects what statements are run. It can't be used inside a SQL statement. With a SQL statement, use CASE.

So, I would use an if like this

IF @SomeVar = 1

SELECT * FROM Table1

ELSE

SELECT * FROM Table2

but if I wanted to do conditional evaluation within a select, I would do this

SELECT

CASE Column1 when 1 THEN 'One' WHEN 0 THEN 'ZERO' ELSE 'Unknown' END

FROM SomeTable

However CASE is for expressions, not for controlling whole pieces of the query. For what you want, try this (untested)

WHERE account_nbr = '123456789'

AND o_trade_date BETWEEN '06/30/07' AND '07/01/08'

AND price_dt BETWEEN

CASE WHEN o_trate_date = 1 OR o_trade_date = 2 THEN (o_trade_date - 4) ELSE (o_trade_date - 2) END

AND

CASE WHEN o_trate_date = 1 OR o_trade_date = 2 THEN (o_trade_date + 3) ELSE (o_trade_date + 2) END

AND ... Rest of where clause

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Investor_Ennui SSC Veteran Points: 272 More actions · Answer 5

Thank you very much. That is what I was working for. But for some reason I can't get the results for when the trade date is on either a Monday, or Tuesday (when it has to be trade_date - 4):

CASE WHEN o_trade_date = 2 OR o_trade_date = 3 THEN (o_trade_date - 4) ELSE (o_trade_date - 2) END

The total code is:

AND price_dt BETWEEN

CASE WHEN o_trade_date = 2 OR o_trade_date = 3 THEN (o_trade_date - 4) ELSE (o_trade_date - 2) END

AND

CASE WHEN o_trade_date = 5 OR o_trade_date = 6 THEN (o_trade_date + 4) ELSE (o_trade_date + 2) END

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 6

Can you post table structure (as create table), some sample data (formatted as insert statements) and your desired output please. Is hard to test without.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Investor_Ennui SSC Veteran Points: 272 More actions · Answer 7

I think that's beyond my skill level. But I do appreciate the help. I'll marinate on this problem for longer.

spaghettidba SSC Guru Points: 105732 More actions · Answer 8

No, it's easier than you think: take a look at this article: http://www.sqlservercentral.com/articles/Best+Practices/61537/ you'll find all the information you need.

Don't give up!

-- Gianluca Sartori

Investor_Ennui SSC Veteran Points: 272 More actions · Answer 9

I read through that, and I'm hopeless. I am thinking that trade_date = 2 OR trade_date = 3 isn't being read correctly. For my purpose, I'm using 2 for Monday, and 3 for Tuesday. Is it possible that the query isn't considering this?

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 10

Investor_Ennui (3/6/2009)
For my purpose, I'm using 2 for Monday, and 3 for Tuesday. Is it possible that the query isn't considering this?

The query's considering exactly what you write and nothing more. So if you're saying WHEN o_trade_date = 2, then SQL is looking for cases where o_trade_date has an exact value of 2. If you mean something else, you're going to have to specify exactly what you mean.

The Query Processor is not, unfortunately, capable of reading your mind. 😉 I often wish it was.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Investor_Ennui SSC Veteran Points: 272 More actions · Answer 11

GilaMonster (3/6/2009)

The Query Processor is not, unfortunately, capable of reading your mind. 😉 I often wish it was.

Sometimes, that is fortunate. I just figured it out - and it seems to be working just fine (doesn't account for holidays though, but for my purposes, that is acceptable.)

In case you are hanging on the edge of your seat, this is what I did:

AND price_dt BETWEEN

CASE WHEN o_trade_date > 3 THEN (o_trade_date - 2) ELSE (o_trade_date - 4) END

AND

CASE WHEN o_trade_date < 5 THEN (o_trade_date + 2) ELSE (o_trade_date + 4) END

Comes out nice and neat - as expected. But I have more work to do. Thank you so much for taking the time to walk me through this. I need to take some structured courses on this stuff (this is what happens when you add an IT function to a Poly Sci Major's job description).