March 4, 2010 at 12:12 pm
Hi, I'm trying to select records from an item table for certain classes and other filter criteria, 2 fields i'm using are sum() and max(). This sql statement only grabs some records that are null, still leaves out some.
SELECT icitem01.item, icitem01.itmdesc, ;
sum(icytrn01.tqty) as received_total, ;
MAX(icytrn01.tqty) as MAXTQTY ;
FROM icitem01 LEFT JOIN icytrn01 ON icitem01.item = icytrn01.item WHERE ;
BETWEEN(icitem01.itmclss, '0010', '0050') AND (icytrn01.applid = 'PO' OR ISNULL(icytrn01.applid)) AND ;
((icytrn01.trantyp = ' R' OR icytrn01.trantyp = ' I') OR ISNULL(icytrn01.trantyp)) AND ;
(YEAR(icytrn01.tdate) = 2009 OR ISNULL(icytrn01.tdate)) ;
ORDER BY icitem01.item, tqty DESC GROUP BY icitem01.item
there is one item that we've been checking, it has a class of 0010, it has applid's of PO and trantyp of ' R' and ' I' but there are no records for 2009, I still want to see this item but have it say null or something because there is no value to calculate. Any ideas?
March 4, 2010 at 12:36 pm
You could join it to either a solid calendar table or buid one on the fly and do a left join on it.
Something like
;WITH
calendar_yr AS
(
SELECT 2009 AS yr UNION ALL
SELECT 2010
)
SELECT
icitem01.item,
icitem01.itmdesc,
SUM(icytrn01.tqty) AS received_total,
MAX(icytrn01.tqty) AS MAXTQTY
FROM icitem01
LEFT JOIN icytrn01 ON icitem01.item = icytrn01.item
LEFT JOIN calendar_yr ON calendar_yr.yr=YEAR(icytrn01.tdate)
WHERE icitem01.itmclss BETWEEN '0010' AND '0050'
AND (icytrn01.applid = 'PO' OR (icytrn01.applid IS NULL))
AND ((icytrn01.trantyp = ' R' OR icytrn01.trantyp = ' I') OR (icytrn01.trantyp IS NULL))
AND (calendar_yr.yr = 2009 OR (icytrn01.tdate IS NULL))
GROUP BY icitem01.item
ORDER BY icitem01.item, tqty DESC
Side note: the code snippet you provided will not run at all. I tried to clean it up so it at least will be runable code...
March 5, 2010 at 9:17 am
A left join should work here to acheive your goal. Here is a great article about joins:[/url] by Walter Crivelini
In short a left join will return all of the data from the left hand table (the table after the from clause) as well as matches from the right table (the table in the join clause) . It will return a NULL value in from the right table where there are no matches.
Table1
Yellow
Green
Blue
Red
Left table
Table2
Yellow
Blue
Right Table
Syntax:
Select table1.color, table2.color
From table1 as table1
Left join table2 as table2 on table2.color = table1.color
The resulting dataset would look like this
Results
YellowYellow
GreenNULL
BlueBlue
RedNULL
Simple Musings From A Simple Developer
TonjaB
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply