Jan 16, 2013 at 08:24 AM

Need A Help In Query

67 Views

Hello Experts

I want to have the details of service calls with the invoice if done.

Columns in my query are Callid , CallDate , Customer , ServiceProduct , Fault Reported , Call Status , Invoice Number , Invoice Date

My query is as follows

select oscl.callid [Call Id] , OSCL.createDate [Call Date] , OSCL.custmrName [Service Customer], OSCL.itemName [Service Product],

OSCL.subject [Fault Reported] , oscs.name [Call Status] ,

--SCL4.DocAbs , SCL4.Object ,

Oo.DocNum [Invoice No#], Oo.DOCDATE [Invoice Date], Oo.U_lrno , Oo.U_lrdate , Oo.DocEntry , SCL4.DocAbs

from OSCL inner join SCL4 on OSCL.callID = SCL4.SrcvCallID

inner JOIN

(select oinv.docentry , oinv.docnum , OINV.docdate , OINV.u_lrno , OINV.u_lrdate from oinv ) as Oo on Oo.DocEntry = SCL4.DocAbs and SCL4.Object = 13

inner join OSCS on OSCL.status = OSCS.statusID

WHERE OSCL.CREATEDATE >= '01/APR/2012'

ORDER BY OSCL.createDate

Above one is giving me the details of only those calls whose invoicing has been done.

If I use left outer join then the records are getting repeated per expense document.

Please help me.

Thanking You

Malhaar