Re: Please Help!! I'm going to JOIN a mental asylum!!

Your code is a bitch to read! Needless double quotes and uppercase, illegal characters in aliases. It looks as if you are printing an end user report in SQL.

Can I assume that:

invh = invoice headers
invd = invoice details
crdh = credit headers
crdd = credit detail

Without DDL, all I can do is guess. The usual pattern is to build a header and its details together (invoices and credits?) then do the outer join. Let me pull off the prefixes (OE = Oxford English?) and clean it up a bit:

SELECT invd.item, invd.qtyshipped, invd.extinvmisc, invd.exticost,

       invh.inv_nbr, invh.customer, crdh.crd_nbr, 
       SUM(crdd.qtyreturn) AS total_quantity_returned,
       SUM(crdd.extcrdmisc) AS total_return, 
       SUM(crdd.extccost) AS total_cogs
 FROM ((invh 
        INNER JOIN 
         invd 
        ON invd.inv_nbr = invh.inv_nbr) 
       LEFT OUTER JOIN 
       (crdh 
        INNER JOIN 
        crdd 
        ON crdh.crduniq = crdd.crduniq)
       ON invh.inv_nbr = crdh.inv_nbr) 
WHERE invd.qtyshipped > 0 

GROUP BY invd.item, invd.qtyshipped, invd.extinvmisc, 
         invd.exticost, invh.inv_nbr, invh.customer, 
         invh.invdate, crdh.crd_nbr;

>> What I'm trying to do is list all the invoice detail lines (OEINVD)
with credit note information if applicable. My first problem is that the detail level information for credit notes is in OECRDD. <<

I'd be inclined to do a sum of invoice items, a sum of credits and then union them for a final report. Received on Tue Jun 15 2004 - 04:46:29 CEST