• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 201
  • Last Modified:

Select statement question

I'm trying to select records where 1-(Field A/Field B) is less than 0.53.  I'll need to make sure that Field A and Field B are not null and not equal to zero as well.  They can be negative although.  

I want records where a discount was less than 53%.  I'm having issues with the decimal value in the select statement.  

SQL newbie, code below...
select recordid, (1 - ([Field A]/Field B)) < 0.53
from table
where [Field a] <> 0 and
	[Field B] <> '0'

Open in new window

0
tobin46
Asked:
tobin46
  • 2
  • 2
1 Solution
 
JPJ78Commented:
Try using the IsNull command to avoid all NULL and 0 values
where IsNull([Field a],0) <> 0 and
	IsNull([Field B],0) <> '0'

Open in new window

0
 
JPJ78Commented:
Sorry, I didn't read your whole question.
I believe this query should work.
It should return all recordId's for all roceord that have less than 53% discount.
select recordid
from table
where IsNull([Field A],0) <> 0 And
	IsNull([Field B],0) <> 0 And ((1 - ([Field A]/Field B))*100) < 53

Open in new window

0
 
tobin46Author Commented:
Can you provide the entire statement?   There are two issues I'm having?  The division by zero and it looks like I'm getting an error when using ".53" in the select statement.
0
 
tobin46Author Commented:
Sorry didn't see your second post.  I'll try it out.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Ruby Fundamentals

This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now