general

Question

Post reply

general

b4u_all

SSC Veteran

Points: 204
More actions
June 24, 2013 at 6:39 am

#279509

i have table with name orders and field as order_number,order_amt,order_date,order_customer,order_saleperson
i want to write a query that selects all orders save those with zeroes or NULLs in the order_amt field.
how can i do this?

Viewing 15 posts - 1 through 15 (of 21 total)

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

Luis Cazares SSC Guru Points: 183706 More actions · Answer 1

Hi,

What you're asking is not difficult but it sounds a lot like homework. Could you show us what have you tried?

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

Jeff Moden SSC Guru Points: 1004312 More actions · Answer 2

Hint: NULLs cannot be compared with the correct defaul sttings. Check just for <> 0.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Dird SSCertifiable Points: 5081 More actions · Answer 3

Dird

SSCertifiable

Points: 5081

June 25, 2013 at 2:19 am

#1627016

Wouldn't you use IS NOT NULL and > 0?

Dird

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 4

Dird (6/25/2013)
Wouldn't you use IS NOT NULL and > 0?

No, you would use <> 0

SELECT d.Amt

FROM (SELECT Amt = 1 UNION ALL SELECT 22 UNION ALL SELECT 0 UNION ALL SELECT NULL) d

WHERE d.Amt <> 0

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Afdsahauhan SSC Enthusiast Points: 118 More actions · Answer 5

select * from Orders

where isnull(order_amt,0) =0

or

select * from Orders

where order_amt is null or order_amt = 0

Dird SSCertifiable Points: 5081 More actions · Answer 6

Dird

SSCertifiable

Points: 5081

June 25, 2013 at 6:15 am

#1627070

Ah I see, <> ignores nulls too o:

Dird

Kingston Dhasian SSCoach Points: 19799 More actions · Answer 7

Adarsh Chauhan (6/25/2013)
select * from Orders
where isnull(order_amt,0) =0
or
select * from Orders
where order_amt is null or order_amt = 0

This is exactly the opposite of what the OP wants

Kingston Dhasian

How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/

Jeff Moden SSC Guru Points: 1004312 More actions · Answer 8

Dird (6/25/2013)
Wouldn't you use IS NOT NULL and > 0?

No. NULLs cannot be compared to so they would also be excluded from any check for <> 0. Try it an see. Same thing for checks for "NOT NULL AND NOT BLANK". All you would need is SomeColumn > '' and that covers both because you cannot compare nulls directly unless someone messed with the default settings.

[Edit] Sorry... didn't see your followup post. Yes, "it ignores nulls too". 🙂

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

david.alcock SSCertifiable Points: 6021 More actions · Answer 9

NULL is basically unknown, so for example, 1 cant be not equal to NULL as we dont know what NULL is, for that same reason and perhaps more to the point we cant do equal to NULL, where [column] = NULL, it wont return anything.

But we can say ISNULL, that is to say, is unknown.

'Only he who wanders finds new paths'

paul.knibbs SSCoach Points: 15320 More actions · Answer 10

Jeff Moden (6/25/2013)
No. NULLs cannot be compared to so they would also be excluded from any check for <> 0. Try it an see.

I would tend to use ISNULL(value, 0) <> 0 anyway, though, because I don't like things that works differently if you use a different comparison operator! 🙂

(By which I mean--if the requirement changed so you had to find all the items where the amount *was* 0 or NULL, you'd get invalid results if you forgot to change the 0 to the ISNULL version--safer to have it that way in the first place).

Sean Lange SSC Guru Points: 286590 More actions · Answer 11

paul.knibbs (6/26/2013)
Jeff Moden (6/25/2013)
No. NULLs cannot be compared to so they would also be excluded from any check for <> 0. Try it an see.
I would tend to use ISNULL(value, 0) <> 0 anyway, though, because I don't like things that works differently if you use a different comparison operator! 🙂
(By which I mean--if the requirement changed so you had to find all the items where the amount *was* 0 or NULL, you'd get invalid results if you forgot to change the 0 to the ISNULL version--safer to have it that way in the first place).

err...huh?

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

paul.knibbs SSCoach Points: 15320 More actions · Answer 12

Not sure how I can explain my reasoning (or lack thereof, probably) more clearly, sorry...

Luis Cazares SSC Guru Points: 183706 More actions · Answer 13

paul.knibbs (6/26/2013)
Jeff Moden (6/25/2013)
No. NULLs cannot be compared to so they would also be excluded from any check for <> 0. Try it an see.
I would tend to use ISNULL(value, 0) <> 0 anyway, though, because I don't like things that works differently if you use a different comparison operator! 🙂
(By which I mean--if the requirement changed so you had to find all the items where the amount *was* 0 or NULL, you'd get invalid results if you forgot to change the 0 to the ISNULL version--safer to have it that way in the first place).

So, basically, your logic is to give extra work to the server instead of analyzing any changes on the requirements that might not even happen?

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

paul.knibbs SSCoach Points: 15320 More actions · Answer 14

Is ISNULL such an expensive operation, then? (Genuinely curious, never actually sat down and analysed it)...