My IF statement is huge - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

My IF statement is huge

Author

Topic

Jim77
Constraint Violating Yak Guru

440 Posts

Posted - 2006-11-06 : 12:21:29

Hello guys, My below IF statement is the following :

IF (isnull(@costvalue,cast(.000 as dec(10,3))) = cast(.000 as dec(10,3))) and (@isl <> 'N') and (isnull(@value,cast(.000 as dec(10,3))) <> cast(.000 as dec(10,3))) or (@prodcode in ('492451','492445','492530', '451811','402043','590106')and (@isl <> 'N') and (isnull(@value,cast(.000 as dec(10,3))) <> cast(.000 as dec(10,3)) and @store in (181,113,270,210))
BEGIN

---- my code here

END

I want to clarify what I am wanting to achieve from the above if I may please:
If (cost = 0 and isl <> N and val <> 0) -- condition1 or (prodcode in('492451','492445','492530', '451811','402043','590106') and @store in (181,113,270,210) and isl <> N and val <> 0) -- condition2
run the code below.

Does this make sense please ?

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2006-11-06 : 12:33:55

You don't need to do this everywhere

cast(.000 as dec(10,3))

in the isnull, because isnull returns the same type as the check expression, so this will work just as well and is much simpler.

isnull(@costvalue, 0)

So removing those everywhere

IF (isnull(@costvalue, 0) = 0 
    and (@isl <> 'N') 
    and (isnull(@value, 0) <> 0)) 
or (@prodcode in ('492451','492445','492530', '451811','402043','590106') 
    and (@isl <> 'N') 
    and (isnull(@value, 0) <> 0) 
    and @store in (181,113,270,210))
BEGIN

---- my code here

END

Jim77
Constraint Violating Yak Guru

440 Posts

Posted - 2006-11-06 : 13:26:46

CHEERS SN THAT MAKES MORE SENSE.

Kristen
Test

22859 Posts

Posted - 2006-11-06 : 13:53:13

I reckon that can be rearranged as follows:


IF
(
        @isl <> 'N'
    AND isnull(@value, 0) <> 0
    AND 
    (
        (
                isnull(@costvalue, 0) = 0 
            AND @prodcode in ('492451','492445','492530', '451811','402043','590106')
        ) OR
        (
            @store in (181,113,270,210))
        )
    )
)

Kristen

Jim77
Constraint Violating Yak Guru

440 Posts

Posted - 2006-11-07 : 08:51:02

as always Kristen thanks for your advice but I don't get my head around it, especially this part

(
isnull(@costvalue, 0) = 0
AND @prodcode in ('492451','492445','492530', '451811','402043','590106')
)

expl: when these product codes occur the costvalue is not always zero (very rarely is it zero!)
and only the above product codes sold in the above stores will be allowed to go in the BEGIN...END when the value is <> 0.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-11-07 : 08:55:06

Re-state your original query. Make sure you isolate the OR's and AND's properly.

Peter Larsson
Helsingborg, Sweden

Kristen
Test

22859 Posts

Posted - 2006-11-07 : 09:14:29

"when these product codes occur the costvalue is not always zero (very rarely is it zero!)"

Well, I reckon I've rewritten your query correctly, so if it now reads wrongly I reckon the original syntax wasn't correct.

I may very well have screwed up though! but I expect someone would have picked me up on it ... so probably worth double checking just in case!

Kristen

Jim77
Constraint Violating Yak Guru

440 Posts

Posted - 2006-11-07 : 19:54:40

cheers Kristen Ran my original (sn's version) if statement today and it brought back correct results, i found it hard to get my thick head round yours but I have no doubt it works, maybe a bit more time spent looking at it will twig something : )

Kristen
Test

22859 Posts

Posted - 2006-11-08 : 10:12:30

"it brought back correct results"

Well, just a word of caution. It may have worked because the data is NOT pushing any edge conditions. I would suggest caution if reading my version suggests the loophole ("costvalue is not always zero") - perhaps try pushing some edge-condition data deliberately at the query.

I may be worrying about nothing though ...

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources