Outer Join Issues - Please Help

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.

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!Actually, I think I'm on the right path here using a derived table, please correct me if I'm wrong:

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.|||This is correct, I got it to work!!!

osql in a cmd file, with SQL statements coming from the same cmd f

I want to run osql in a command file. Instead of having the SQL statements
in a separate .sql file, using "osql -i file.sql..." I want to have the SQL
statements right there in the command file. This way my command file is
self-contained; only one file to worry about instead of separate cmd and sql
files.
In unix (or more precisely in the bash shell) this would be done by what is
called a "here document". Conceptually:
osql <<END_OF_SQL
select * from customers
select * from suppliers
END_OF_SQL
Any way to do this with OSQL, or by some CMD.EXE trick?
forestial wrote:
> I want to run osql in a command file. Instead of having the SQL
> statements in a separate .sql file, using "osql -i file.sql..." I
> want to have the SQL statements right there in the command file.
> This way my command file is self-contained; only one file to worry
> about instead of separate cmd and sql files.
> In unix (or more precisely in the bash shell) this would be done by
> what is called a "here document". Conceptually:
> osql <<END_OF_SQL
> select * from customers
> select * from suppliers
> END_OF_SQL
> Any way to do this with OSQL, or by some CMD.EXE trick?
Sure. You can create a CMD file to run the batch with contents like
this: Substitute -E to use a trusted connection which is recommended
over putting user id and password in the file. Use a capital Q to exit
OSQL immediately. Separate batches with Go and use -O for an output
file.
osql -Uuser -Ppassword (or -E) -Sserver -Q"Select id from sysobjects go
select id from sysindexes" -ooutput.txt
David Gugick
Imceda Software
www.imceda.com