Results 1 to 3 of 3
  1. #1
    Join Date
    Feb 2004
    Posts
    126

    Unanswered: Outer Join Issues - Solved

    I am working on the record source for a report to produce monthly customer statements. Thanks to everyone here, I have been able to overcome many, many hurdles I have encountered. I just have one last issue I need to get resolved (famous last words, I know).

    At the heart of my record source are two entities:

    1. AROPNFIL - A table of all Accounts Receivable items (invoices, payments, credits, etc.)
    2. fnBalance(@StartDate) - A table valued function that gives me the starting balance for a customer on a particular date by summing the net Amounts of all items in the AROPNFIL that occured before the @startdate. (i.e. Invoices have a positive amount, payments have a negative amount, so they net out so that only unpaid invoices remain.)

    These are joined on customer_number. The query also selects only the rows in the AROPNFIL table that are between @StartDate and @EndDate for the month.

    Everything was going great until...I realized that no statement was being created for a customer if they didn't have any activity in the current month, even though they had a starting balance.

    So I tried an outer join, telling the query to select all rows from the fnBalance table. But that still wasn't returning the rows I wanted. After several hours of cursing and feeling the need for a drink, I realized why that wasn't working. Because the customer in question, let's call it "Coast01" had rows in the AROPNFIL table before the @StartDate, it was seeing that as having completed the join and hence no reason to return a row for that starting balance.

    So, this is what I need, a way to write what I am going to try to interpret as the following.
    Code:
    Select *
    From (AROPNFIL WHERE Doc_date BETWEEN @StartDate AND @EndDate) RIGHT OUTER JOIN fnBalance(@StartDate)
    Does that make sense? I need the join with fnBalance to take place after the query has selected only the rows between @startdate and @enddate so that the row for the balance for "Coast01" will appear even though there is no activity in the current month.

    But I am at a loss for how to write that in the FROM and WHERE clauses. I guess one way would be to create a query selecting rows from AROPNFIL WHERE doc_date BETWEEN @StartDate AND @EndDate and then create another query performing the outer join between that query and fnBalance, but I don't really want to do that because the rest of my record source query is actually a lot more complicated than what I've explained here, and I'd rather not have to create additional queries if I don't have to. But if you guys tell me there is no other way, I'll believe you.

    Thank you all!
    Last edited by grrr223; 03-01-04 at 16:07.

  2. #2
    Join Date
    Feb 2004
    Posts
    126
    Actually, I think I'm on the right path here using a derived table, please correct me if I'm wrong:

    Code:
    SELECT *
    FROM (SELECT * FROM 
          AROPNFIL WHERE Doc_date BETWEEN @StartDate AND @EndDate) RIGHT OUTER JOIN 
          fnBalance(@StartDate) StartBalance ON AROPNFIL.cus_no = StartBalance.cus_no
    Obviously, I'll get rid of the *s, but no one here cares about what columns I'm actually pulling out.

  3. #3
    Join Date
    Feb 2004
    Posts
    126
    This is correct, I got it to work!!!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •