check if value is numeric in sql view

Question

What is wrong with my case statement below?

If the value is numeric then I want to check if its greater than or equal to 5 and return 1 if true else return 0. Is there any elegant way of doing this?

Below CASE is nested in another CASE

CASE
    WHEN(
        xyz <> a
        AND abc <> 3
        AND
            CASE 
                WHEN ISNUMERIC(LEFT(o.RepCode, 1)) = 1 THEN 
                    CASE 
                        WHEN CONVERT(INT, LEFT(o.RepCode, 1)) >= 5 THEN 1
                        ELSE 0
                    END
                ELSE 0
            END
    )
    THEN 1
    ELSE 0

I get the below error on the first CASE after the second AND before WHEN ISNUMERIC()

An expression of non-boolean type specified in a context where a condition is expected.

Hart CO · Accepted Answer · 2014-09-04 22:20:33Z

5

Missing criteria in your outermost case statement:

CASE
    WHEN(
        xyz <> a
        AND abc <> 3
        AND
            CASE 
                WHEN ISNUMERIC(LEFT(o.RepCode, 1)) = 1 THEN 
                    CASE 
                        WHEN CONVERT(INT, LEFT(o.RepCode, 1)) >= 5 THEN 1
                        ELSE 0
                    END
                ELSE 0
            END = ?  -- Missing criteria
    )  
    THEN 1
    ELSE 0

Edit: Not sure there's an ideal way to format this, but I find the following to be easier to follow:

CASE WHEN (    xyz <> a      
           AND abc <> 3  
           AND CASE WHEN ISNUMERIC(LEFT(o.RepCode, 1)) = 1 
                    THEN CASE WHEN CONVERT(INT, LEFT(o.RepCode, 1)) >= 5 
                              THEN 1
                              ELSE 0
                         END
                    ELSE 0
               END = ? -- Missing Criteria
           ) 
     THEN 1
     ELSE 0
END

edited Sep 4, 2014 at 22:20

answered Sep 4, 2014 at 21:41

Hart CO

34.9k6 gold badges52 silver badges66 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Conrad Frix Over a year ago

outermost case statement It's not the outer most. It's sorta in the middle

Hart CO Over a year ago

@ConradFrix I see it as the WHEN of the outermost case statement is missing a comparison.

user793468 Over a year ago

What should the last END equal to? 0 or 1?

Conrad Frix Over a year ago

@user793468 if you want to be true when RepCode > 5 then 1

user793468 Over a year ago

@GoatCO In the edited version, you have '= ?' after the parenthesis. I believe it should be inside the parenthesis after the END.

Collectives™ on Stack Overflow

check if value is numeric in sql view

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related