Select statement question

Posted on 2009-02-18
Last Modified: 2012-05-06
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

Question by:tobin46
    LVL 10

    Expert Comment

    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

    LVL 10

    Accepted Solution

    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

    LVL 1

    Author Comment

    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.
    LVL 1

    Author Comment

    Sorry didn't see your second post.  I'll try it out.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How to improve team productivity

    Quip adds documents, spreadsheets, and tasklists to your Slack experience
    - Elevate ideas to Quip docs
    - Share Quip docs in Slack
    - Get notified of changes to your docs
    - Available on iOS/Android/Desktop/Web
    - Online/Offline

    Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
    This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
    Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
    Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

    737 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    19 Experts available now in Live!

    Get 1:1 Help Now