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, dbEINVD 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
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, dbEINVD 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
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