Hi, folks...

I have query requirements for both inner and outer joins. Basically, I have a history file (i.e. invoice info) with transactions by acct, item, and date. I have ACCT and ITEM master files, and a COST RATE table with historical rates by ACCT, ITEM, and DATE.

In the query, I want to pull all history file records for certain accts (e.g. accounts in region 'X'), and for certain items (e.g. items in product group 'Y'). In addition, I want to pull any matching rates from the COSTRATE table....if there isn't a matching rate, the historical cost would be zero, but I still want other fields from the history transaction file.

Finally, I may need to specify a date range.

So in a nutshell, I want to do an inner join to match up HISTFILE records against ACCTFILE and ITEMFILE where ACCTFILE.REGION = 'X' and ITEMFILE.PRODGROUP = 'Y', and then an outer join to pick up any matching acct/item rates in COSTRATE. And I also need to filter on dates...

Here's the syntax that seems to work...

SELECT DISTINCT HistFile.*, NVL(CostRate.Rate,0) FROM HistFile ;
JOIN Acctfile ON HistFile.Acct = AcctFile.Acct ;
JOIN ItemFile ON HistFile.Item = ItemFile.Item ;
LEFT OUTER JOIN CostRate ON AcctFile.Acct + ItemFile.Item = CostRate.Acct + CostRate.Item ;
WHERE HistFile.Date > {^01/01/1999} ;
AND AcctFile.Region = 'X' AND ItemFile.ProdGroup = 'Y'

First, for all the 10 million times that the Hacker's Guide has saved me, there's something in the new book (bottom of page 739) that isn't consistent with my findings. It says you can't mix and match WHERE and JOIN...you can only do one or the other. However, I need to do additional filtering on dates and acct/item criterias....I'm mixing both here, and (seem) to get reliable results.

Also...is it legitimate to have a multi-field concatenation in the OUTER JOIN? (where I have CostRate.Acct + CostRate.Item)

Any thoughts?

Thanks in advance,
Kevin