Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Trying to filter out Zero rows

Posted on 2011-09-27
9
Medium Priority
?
214 Views
Last Modified: 2012-06-22
Greetings again mates,

This is one of the simplest, yet one of the weirdest issues I have had to deal with.

I am still working on someone else's work.
 
When records are created and stored in the db, an extra row or two with zero records are stored.

Please see attached screenshot.

The problem is that most of the time, 2 or 3 rows have values either in the check AMount column or Cash column. The remaining 2 or so rows have 0 values.

We would like to show only the rows with data.

The screenshot shows only 3 rows have data 2 in check column and 1 row in cash column. The remaining rows have zeros (0).

The code below doesn't work because it either  shows 1 row or blank rows.

Any help is of course appreciated.


SELECT employee_ded_amts.ID, employee_ded_amts.employee_ID, employee_ded_amts.charity_code, employee_ded_amts.chcknum, employee_ded_amts.check_amt, 
                         employee_ded_amts.one_time, employee_ded_amts.bi_weekly, employee_ded_amts.cash, employee_ded_amts.donate_choice, 
                         employee_ded_amts.date_stamp, employee_ded_amts.amb_approved, active_empl.dept_code, active_empl.empl_first, active_empl.empl_last
                         FROM employee_ded_amts INNER JOIN active_empl ON employee_ded_amts.employee_ID = active_empl.employee_id
                         WHERE ((cash <> 0 and employee_ded_amts.check_amt <> 0) AND employee_ded_amts.employee_id = '00088899999' ORDER BY employee_ded_amts.employee_ID

Open in new window


donations.JPG
0
Comment
Question by:sammySeltzer
9 Comments
 
LVL 2

Expert Comment

by:ramkihardy
ID: 36714548
I understand your question sammy, Could you please post in what condition you want select the data or any sample result that you want..
Regards
Ramki
0
 
LVL 39

Accepted Solution

by:
appari earned 668 total points
ID: 36714557
try this
SELECT employee_ded_amts.ID, employee_ded_amts.employee_ID, employee_ded_amts.charity_code, employee_ded_amts.chcknum, employee_ded_amts.check_amt, 
                         employee_ded_amts.one_time, employee_ded_amts.bi_weekly, employee_ded_amts.cash, employee_ded_amts.donate_choice, 
                         employee_ded_amts.date_stamp, employee_ded_amts.amb_approved, active_empl.dept_code, active_empl.empl_first, active_empl.empl_last
                         FROM employee_ded_amts INNER JOIN active_empl ON employee_ded_amts.employee_ID = active_empl.employee_id
                         WHERE ((cash <> 0 or employee_ded_amts.check_amt <> 0) AND employee_ded_amts.employee_id = '00088899999' ORDER BY employee_ded_amts.employee_ID

Open in new window

0
 
LVL 10

Expert Comment

by:Umar Topia
ID: 36714640
Try this
SELECT employee_ded_amts.ID, employee_ded_amts.employee_ID, employee_ded_amts.charity_code, employee_ded_amts.chcknum, employee_ded_amts.check_amt, 
                         employee_ded_amts.one_time, employee_ded_amts.bi_weekly, employee_ded_amts.cash, employee_ded_amts.donate_choice, 
                         employee_ded_amts.date_stamp, employee_ded_amts.amb_approved, active_empl.dept_code, active_empl.empl_first, active_empl.empl_last
                         FROM employee_ded_amts INNER JOIN active_empl ON employee_ded_amts.employee_ID = active_empl.employee_id
                         WHERE (cash > 0 and employee_ded_amts.check_amt > 0) AND employee_ded_amts.employee_id = '00088899999' ORDER BY employee_ded_amts.employee_ID

Open in new window

0
NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

 
LVL 10

Assisted Solution

by:Umar Topia
Umar Topia earned 668 total points
ID: 36714644
Sorry about the previous post
SELECT employee_ded_amts.ID, employee_ded_amts.employee_ID, employee_ded_amts.charity_code, employee_ded_amts.chcknum, employee_ded_amts.check_amt, 
                         employee_ded_amts.one_time, employee_ded_amts.bi_weekly, employee_ded_amts.cash, employee_ded_amts.donate_choice, 
                         employee_ded_amts.date_stamp, employee_ded_amts.amb_approved, active_empl.dept_code, active_empl.empl_first, active_empl.empl_last
                         FROM employee_ded_amts INNER JOIN active_empl ON employee_ded_amts.employee_ID = active_empl.employee_id
                         WHERE (cash > 0 or employee_ded_amts.check_amt > 0) AND employee_ded_amts.employee_id = '00088899999' ORDER BY employee_ded_amts.employee_ID

Open in new window

0
 
LVL 19

Expert Comment

by:Bhavesh Shah
ID: 36714829
Hi,

Your query seems right.

But there could be possibilities that columns having NULL value, in that case your filter wont work.

Try using IsNull(Cash,0) > 0

- Bhavesh
0
 
LVL 50

Assisted Solution

by:Lowfatspread
Lowfatspread earned 664 total points
ID: 36715159
actually shouln't it be cash<>0 or check_amt <>0

and you need to allow for negative amts ?




SELECT DA.ID, DA.employee_ID, DA.charity_code, DA.chcknum, DA.check_amt, 
       DA.one_time, DA.bi_weekly, DA.cash, DA.donate_choice, 
       DA.date_stamp, DA.amb_approved, AE.dept_code, AE.empl_first, AE.empl_last
  FROM employee_ded_amts as DA
 INNER JOIN active_empl as AE
    ON DA.employee_ID = AE.employee_id
 WHERE (DA.cash <> 0 or DA.check_amt <> 0)
   AND DA.employee_id = '00088899999' 
 ORDER BY DA.employee_ID

Open in new window

0
 
LVL 29

Author Comment

by:sammySeltzer
ID: 36716443
Thanks for the tremendous responses.

I will start testing out the various codes.

This is really weird as I have tried a variation of codes.

Just a by the way, the checkamount and cashamount fields are of money data type.

I neglected to indicate that last night.

No possibility of negative amounts but you never know.
0
 
LVL 29

Author Comment

by:sammySeltzer
ID: 36716592
I tested your codes (Lowfatspread, umartopia,appari) and I must say that I am truly impressed.

Not only did I try this many different ways, I have a "sql guru" here who tried as well and we could not get it to work.

You guys did it.

I am grateful, very grateful.
0
 
LVL 29

Author Closing Comment

by:sammySeltzer
ID: 36716610
Thanks again all for your assistance. The response was overwhelming and accurate.
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
This course is ideal for IT System Administrators working with VMware vSphere and its associated products in their company infrastructure. This course teaches you how to install and maintain this virtualization technology to store data, prevent vuln…
Please read the paragraph below before following the instructions in the video — there are important caveats in the paragraph that I did not mention in the video. If your PaperPort 12 or PaperPort 14 is failing to start, or crashing, or hanging, …

926 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