Division By Zero Error In View

  • This is so easy to bypass in Access queries with the IIF function. How do you get around this in Views?

  • SELECTCASE col_2

    WHEN 0

    <bla-bla-bla>

    ELSEcol_1 / col_2

    END

    FROMtbl_abc

  • Does this work in views? I tried it and it didn't work. Maybe I messed up the syntax.

  • Here is a complete test code for the pubs DB. You need to name the "calculated" column in the view explicitly:

    create view vw_test as

    SELECTCASE qty

    WHEN 0

    THENNULL

    ELSE1000 / qty

    END AS result

    FROMsales

    GO

    select result from vw_test

    GO

    drop view vw_test

  • You know what...it works when I use query analyzer, but does not work using the view editor. I get an error saying "Case is not supported". Don't you just love Microsoft! Thanks a lot!

  • Please never use Enterprise Manager except for read-only purposes. Always create your objects from Query Analizer and store them as script files.

Viewing 6 posts - 1 through 6 (of 6 total)

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