[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 198
  • 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

Featured Post

NEW Veeam Backup for Microsoft Office 365 1.5

With Office 365, it’s your data and your responsibility to protect it. NEW Veeam Backup for Microsoft Office 365 eliminates the risk of losing access to your Office 365 data.

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