Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations derfloh on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
-
Making a temp table out of a union
I'm attempting to make a select union statement into it's own temp table. Essentially, the end of my query has SELECT * FROM PAY_BILL UNION SELECT * FROM BILL_ADJ_WE And I want to create a more consolidated select statement, because I don't need all of the fields these two selects are... -
Count/Sum not working properly, I think
The problem was in my joins. Thanks for the help! -
Count/Sum not working properly, I think
Doesn't an inner join bring back your results faster? That was always how I understood it. And yes, I do know why I get a bunch of dups when I don't use distinct. -
Count/Sum not working properly, I think
These are the results without the functions, but with the distinct. When I don't use distinct I get a bunch of repeats: NAME PAYGROUP DEPTID ERNCD OTH_HRS OTH_EARNS PAY_END_DT Torres,Efren CW3 799400 SKH 8.00 0.00 2007-01-07 Torres,Efren CW3 799400 SKH 16.00 0.00 2007-01-07... -
Count/Sum not working properly, I think
Thanks, but it is still returning the same incorrect data. The order of my requests need to be that way. It didn't like when I switched up the EMPLID and the DEPTID placement. It kept giving me an ON clause error. I'll keep digging. If you think of anything else, please let me know. -
Count/Sum not working properly, I think
Hello all, I've been working on a query and I'm attempting to shorten the results. Without the count or sum functions, I get 450 records. When I use the count or sum functions, I shorten the amount of records, but the function isn't doing what I need it to. I have a feeling it is the way I am... -
Returning NULL values for averages
Thank you sooo much! That did it! Nice one. Like a dork I had the coalesce and the sum backward. Thank you very much. -
Returning NULL values for averages
Thanks, I tried that, but it didn't like it. Gave me a Sysibm.COALESCE error. I don't know why, because coalesce worked fine before. And there weren't any tables missing values altogether, but they would be turned to NULL, and then I would turn the NULLS to 0's. If there are any 0's in any of... -
Returning NULL values for averages
I am attempting to write a code that will take the amounts for four weeks and average them out. Right now, if there are any zero values within any week, the average results comes up NULL. I have used Coalesce to replace the NULL values within the given weeks to 0, I don't know if this is... -
Removing duplicate records
Hello all, I am running a query, and it is returning a lot of duplicate records. Now when I change up the way I am joining the tables, I end up losing records. So I resolve the duplication issue, but I lose data. I have tried every combination of joins I could think of, and either I receive... -
Ignoring zeros to complete average calculation
Hey George, The initial query would return NULL for any areas where NET_PAY or PAY_END_DT were blank. I needed to convert the NULL values so I could calculate the averages. So I used the COALESCE function to replace the NULL's with 0's. After I did that, the average column would come up NULL... -
Ignoring zeros to complete average calculation
I'm positive the 0's were the cause for the NULL values being returned. With all the help you all have given me, I have managed to get it to work using COALESCE, so thank you very very much. Unfortunately, even though the query is working, it is changing the average results. Before I made the... -
Ignoring zeros to complete average calculation
Thank you gmmastros, But I need to ignore the 0's that are currently there so the total can average out. Right now if there are any fields that have a value of 0, then the end result comes up NULL. Basically all four weeks have to have actual numbers in order for the averaging to work. If any... -
Ignoring zeros to complete average calculation
Hello all, I am attempting to average out results of a query I created. Right now, if any of the fields contain a zero, the resulting average field comes up NULL. I need to somehow make it so the function ignores the zeros and only calculates the actual amounts. Any ideas? Here is the portion... -
Changing NULL values within SQL query
Thank you all so much for your help. I finally got it to work by using a combination of CASE and COALESCE functions. It's not the prettiest thing in the world, but it works. Oh and I had to do a bit of hardcoding for the PAY_END_DT. Take a look: SELECT A.COMPANY, A.PAYGROUP... -
Changing NULL values within SQL query
Thanks JarlH, I went ahead and made the changes, and now I am receiving an entirely different error. Here is the way the code looks now: NET_TMP_THREE (EMPLID, NET_PAY, PAY_END_DT) AS ( SELECT A.EMPLID, B.NET_PAY, B.PAY_END_DT FROM NET_PAY A LEFT OUTER JOIN PS_PAY_CHECK B ON... -
Changing NULL values within SQL query
Hello All, I am trying to run a comparison query on Net Pay results over a four week period. Right now the data the query returns contains NULL values for specific weeks. I would like to convert these NULL values into 0's so I can average the Net Pay for all four weeks. I have been trying to...
