CASE statement not working correctly

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)

CASE statement not working correctly

Author

Topic

hueby
Posting Yak Master

127 Posts

Posted - 2004-10-27 : 11:58:40

Hey all, I have a SP that does some calculations for me...
There is a case statement in it (code below) that runs one of two formulas depending if the value in table 'ptaxable' is either '1' or '<NULL>' My problems is...

With my current code, only the 'WHEN 1' will execute, not the 'WHEN 2' (well, if it does execute it just returns NULL in the 'tax' table).


CASE IsNull(tbl_products.ptaxable, 2)
        WHEN 2 THEN ROUND(CAST((1+(.02))*(((1+(.06))*(tbl_products.pprice))*(tbl_material_used.mqty))as Decimal (10,2)),2) --taxed
        WHEN 1 THEN ROUND(CAST((1+(.02))*((tbl_products.pprice)*(tbl_material_used.mqty))as Decimal (10,2)),2) --no tax
        END as tax

...and If I switch the numbers around, again only the expression with '1' will execute. I have tried to replace the 2 with 'NULL' but that doesnt work.

I'm guessing for some reason, it is not picked up that the value in that table is <null> and then it never fires. But it does pick up when the value in the table is 1.

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-10-27 : 12:07:20

try


CASE WHEN IsNull(tbl_products.ptaxable, 2)= 2 THEN ROUND(CAST((1+(.02))*(((1+(.06))*(tbl_products.pprice))*(tbl_material_used.mqty))as Decimal (10,2)),2) --taxed
        WHEN IsNull(tbl_products.ptaxable, 2) = 1 THEN ROUND(CAST((1+(.02))*((tbl_products.pprice)*(tbl_material_used.mqty))as Decimal (10,2)),2) --no tax
        END as tax

Go with the flow & have fun! Else fight the flow

hueby
Posting Yak Master

127 Posts

Posted - 2004-10-27 : 13:11:22

Well now it only seems to fire the first WHEN and not the second .. no matter what.

Again, just to clarify: I have a table field named 'taxable', it is a bit, there is a 1 stored in it, or just NULL. Based from that, I want the case statement to either run TaxFormula or NoTaxFormula.

CASE 'taxable'
WHEN 1 THEN run NoTaxFormula
WHEN "NULL" THEN run TaxFormula
END

AndyB13
Aged Yak Warrior

583 Posts

Posted - 2004-10-27 : 13:25:13

Is something like this what you are after??

CASE
WHEN tbl_products.ptaxable IS NULL THEN ROUND(CAST((1+(.02))*(((1+(.06))*(tbl_products.pprice))*(tbl_material_used.mqty))as Decimal (10,2)),2) --taxed
WHEN tbl_products.ptaxable = 1 THEN ROUND(CAST((1+(.02))*((tbl_products.pprice)*(tbl_material_used.mqty))as Decimal (10,2)),2) --no tax
END as tax

Andy

hueby
Posting Yak Master

127 Posts

Posted - 2004-10-27 : 13:28:11

Yes that works perfectly! Thank you! I was trying '= NULL' and not 'IS NULL' at one point. That did it! Much thanks

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources