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

From: --CELKO-- <jcelko212_at_earthlink.net>
Date: 14 Jun 2004 19:46:29 -0700
Message-ID: <18c7b3c2.0406141846.2cdb71f6@posting.google.com>

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 

  AND invh.invdate > '2004-05-01'
  AND invd.item = crdd.item

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 Mon Jun 14 2004 - 21:46:29 CDT