Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Dear All, In my following query

Status
Not open for further replies.

tsquare1975

IS-IT--Management
Apr 10, 2012
21
Dear All,

In my following query I join tables of sales and description of itemno

SELECT TOP (100) PERCENT O.EXPDATE, O.LOCATION, M.SEGMENT2,M.SEGMENT3, O.[DESC],
SUM(O.QTYSHIPPED) AS QTY, SUM(O.UNITPRICE * O.QTYSHIPPED) AS AMT,
dbo.ICLOC.[DESC] AS Loc, M.OPTFLD5,icsegv.[desc] as seg3des, csopt.data as seg2des
FROM icsegv, csopt, dbo_OEINVD AS O INNER JOIN dbo.ICITEM AS M ON O.ITEM = M.FMTITEMNO INNER JOIN dbo.ICLOC ON O.LOCATION = dbo.ICLOC.LOCATION
where ((segval=m.segment3 and icsegv.segment=3))
and (csopt.code =optfld5 and
= 'dept')
GROUP BY O.LOCATION, M.SEGMENT2, dbo.ICLOC.[DESC], O.[DESC], M.OPTFLD5,O.EXPDATE,M.SEGMENT3,icsegv.[desc], csopt.data ORDER BY O.EXPDATE

I want to display all associated value along with “AMT “ = 0, even if no record is found against an item in sale table “O” during specified tiem period. currently it doesn't display whole row if no sale is found. It must display 0 amt alongwity all descriptions.

Plz guide me the solution
thanks
 
Dear all,

I as per ur advise i use left outer join ...but still can't get desired results. plz tell me wat's wrong with this query as it doesn't give me all items from table "m" with null values in table "O"

SELECT TOP (100) PERCENT m.SEGMENT2, isnull(SUM(o.UNITPRICE * o.QTYSHIPPED),0) AS AMT, m.OPTFLD5
from dbo.oeinvd as o left outer join dbo.icitem as m on m.fmtitemno=o.item
where
optfld5 in
('1301','1302','1303','1304','1305','1306','1307')
and (o.EXPDATE = '20120616')
GROUP BY m.SEGMENT2, m.OPTFLD5


Thanks
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top