Search results for query: *

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

The problem was in my joins. Thanks for the help!

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.

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

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.

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

Thank you sooo much! That did it! Nice one. Like a dork I had the coalesce and the sum backward. Thank you very much.

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

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

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

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

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

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

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

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

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

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