Recent content by czab

fredericofonseca, I tested the query without using coalesce and only ITEM #1 had a value for REMAINING. ITEM #2 had a value for ITEM and AMOUNT, but REMAINING was NULL. If you didn't mention coalesce I would have spent today pulling my hair out trying to figure out why I didn't have a value...

Correction: Moving the condition AND TABLE_B.DATE1 <= '2009/01/15' to the join allows the left OUTER join to work the way I hoped it would work and brings in all the records for TABLE_A as long as they meet the conditions in the WHERE clause.

Thank you! It worked brilliantly! As you can tell, I'm a novice at SQL programming. I've never used the coalesce function before. I appreciate your help and will do my best to explain why this works where mine failed. The way I had the query written it would only select records that matched...

I understand what you're saying mikrom. I would have thought that for Item 2 the formula SUM(TABLE_B.PAYMT) would result in NULL, but the AMOUNT of 200.00 for ITEM #2 from TABLE_A would still be pulled in to the results and the REMAINING amount for ITEM 2 would be 200.00 - nothing or 200.00...

I need to join two tables to get the results I'm looking for. I've been working on this since yesterday and can't figure out whether or not I can achieve what I need in one query. I need to select ALL records that meet the DATE criteria from TABLE_A and ONLY the records that meet the DATE...

Thanks PHV. I just got a chance to try your suggestion this morning. It worked like a charm. I got exactly the results I wanted. I really appreciate your help.

I can't even run the query if I do that. It results in an error message: SQL0122 - Column CJBNO or expression in SELECT list not valid.State: HY000 Thanks for your persistance though. I've found something similar to what I'm trying to do in this topic...

I removed QPDATE from the GROUP BY statement, but still got the same result. 8 extra minutes to run and the same number of lines of output. :-(

Thanks for your reply johnherman. My data is a little more detailed than what I showed above, but I applied your suggestion to my query and came out with this code: SELECT CJBNO AS JOB, CJCDI AS COSTDISTR, QPDATE AS FRDATE, QCUPC AS PCOMPLETE, QESHM AS ESTIMATE FROM JCVQYD85 WHERE...

I need to select only the data in the rows with the most recent date. Sample data ITEM DATE ESTIMATE 0010 20070320 100.00 0010 20070321 65.00 0010 20070322 80.00 0020 20070316 75.00 0020 20070318 20.00 0030 20070316 30.00 0040 20070316 40.00 Desired result: 0010 20070322 80.00 0020 20070318...

Thank you all for your patience. MissyEd, I don't know if I'm using Microsoft SQL Server per se. If there's another SQL forum to go to, I'd love to be directed there. I'm using the SQL code to pull data into a Business Objects report using Freehand SQL. I'm not quite sure what that means...

OK, I'm completely embarassed. Thanks for your help. Is there a different SQL forum I should be posting in?

I tried changing my code to From JCPPST T1 inner join CSTMST T2 on (T1.TCONO=T2.CCONO and T1.TDVNO=T2.CDVNO and T1.TRF05=T2.CCUST) left join SECBI T3 on (T1.TCONO=T3.BICONO and T1.TDVNO=T3.BIDVNO and T3.BIA01D='Y' OR T3.BICONO=0 and T3.BIDVNO=0 and T3.BIA01D='Y' OR T3.BICONO=0 and...

I can handle joins when there are only 2 files involved, but I'm not sure what to do when I need to use 3 files. Using the code below, my problem is that I'm not getting records when T1.TRF05 is blank because it doesn't have a matching record in T2. I need some data fields from T1 and T2...

I decided to make two separate queries and let business objects bring them together. It was a piece of cake to do it that way, but I'm still frustrated with myself that I couldn't get it to work in one query.