How to exclude zero from list of values.

Question

How to exclude zero from list of values.

Rod Martin 136

This statement works.. however, I want it to provide the min of the values, but NOT if it is zero. How would I exclude zero from this list?

CASE
    WHEN (Patindex('%-%', od._snvalve) > 0
          OR Patindex('%,%', od._snvalve) > 0) THEN Substring(od._snvalve, Len(od._snvalve)-
                                                                (SELECT min(xxx)
                                                                 FROM (
                                                                       VALUES (Patindex('%-%', Reverse(od._snvalve))), (Patindex('%,%', Reverse(od._snvalve))), (Patindex('% %', Reverse(od._snvalve)))) AS value(xxx)), Len(od._snvalve))
ELSE ''
END AS Last_SN

Rod

Olaf Helper 47,436 Reputation points

2021-09-14T11:35:36.27+00:00

That's to less on information.

Please post table design as DDL, some sample data as DML statement and the expected result.

Accepted answer

3 additional answers

Your answer

Olaf Helper 47,436 Reputation points

2021-09-14T11:35:36.27+00:00

That's to less on information.

Please post table design as DDL, some sample data as DML statement and the expected result.

Answer 1

Rod Martin 136

I need to exclude zeros from inside the values() statement. I need the minimum that is not zero.

Tom Phillips 17,771 Reputation points

2021-09-14T14:46:59.753+00:00

There is no way to exclude 0 from the values statement.

However, it looks from your code snippet, that statement has no meaning and you are only using xxx. So you can just replace your coded with SELECT min(xxx) FROM xxx WHERE xxx >0.
Viorel 122.5K Reputation points

2021-09-14T15:28:57.337+00:00

Did you try 'min(nullif(xxx, 0))' or 'where xxx <> 0', suggested before? Show your new query if it does not work.

Answer 2

Rod Martin 136

Well, i'm sure its a good idea. But, I dont have any idea what DDL and DML is.

in short, in a list of Values(), how do I exclude one that is zero?

Dan Guzman 9,401 Reputation points

2021-09-14T12:13:57.58+00:00

@Rod Martin , DDL and DML are subsets of the SQL language. DDL is "data definition language" (e.g. CREATE TABLE statements) and DML is "data manipulation language" (e.g. SELECT, INSERT, DELETE, UPDATE, and MERGE statements).

It's a good practice when asking questions to provide enough DDL/DML so that answerers can duplicate your problem and provide a tested solution. This will avoid ambiguity and greatly improve your chances of getting a quality answer.
Rod Martin 136 Reputation points

2021-09-14T12:16:49.233+00:00

Ok. I'll look into it. It hasn't been suggested before. Makes sense, though. I wouldnt have thought it necessary with this simple of a question. Well, its simple for someone. Just not me. I'm quite the amateur here. Its a development for me to even figure out how to do all that too.

Answer 3

Viorel 122.5K

To exclude zeroes from min(xxx), I think that there are two methods:

select min(xxx) from … where xxx <> 0

or

select min(nullif(xxx, 0)) from …

After you provide the details, I hope that these approaches will work.

Answer 4

Rod Martin 136

@Tom Phillips , @Viorel ,

Great help here. THe WHERE method did not work. I could not get the full sql statement to resolve. However, the min(nullif(xxx,0)) produced EXACTLY what was needed! Well done. Thank you!

Rod

Viorel 122.5K Reputation points

2021-09-15T01:50:34.043+00:00

Probably you did not put WHERE in right place.

Share via

How to exclude zero from list of values.

3 additional answers

Your answer