Skip to Content

Need a help in query

Hello Experts,

select oscl.callid [Call Id#], OSCL.CREATEDATE [CALL DATE] , OSCL.custmrName [Service Customer],

oscl.itemName [Service Product] , OSCL.SUBJECT [FAULT] , o1.DocDate , NNM1.SeriesName + STR(o1.docnum) , OSCL.U_Loc , OSCS.Name

from OSCL inner join OSCS on OSCS.statusID = OSCL.status and oscs.name = 'Ready for delivery'

left join (select SCL4.srcvcallid , scl4.docabs , SCL4.object From scl4) s1 on oscl.callid = s1.srcvcallid

left join (select oinv.docentry , OINV.docdate , OINV.docnum , oinv.series from OINV) o1 on s1.DocAbs = o1.DocEntry

and s1.object=13

inner join NNM1 on NNM1.Series = o1.Series

group by oscl.callid , OSCL.CREATEDATE , OSCL.custmrName ,

oscl.itemName , OSCL.SUBJECT , o1.DocDate , NNM1.SeriesName + STR(o1.docnum) , OSCL.U_Loc , OSCS.Name

This is giving me a list of service calls whose status is "ready for delivery' with invoice punched in the system.

But there are calls with status as 'ready for delivery' but no invoice.

Please help me in listing all ready for delivery calls with and without invoicing.

Thanking You

Pradnya

Add a comment
10|10000 characters needed characters exceeded

Assigned Tags

Related questions

3 Answers

  • Posted on Sep 05, 2013 at 06:46 AM

    Please try below

    select oscl.callid [Call Id#], OSCL.CREATEDATE [CALL DATE] , OSCL.custmrName [Service Customer],

    oscl.itemName [Service Product] , OSCL.SUBJECT [FAULT] , o1.DocDate , NNM1.SeriesName + STR(o1.docnum) , OSCL.U_Loc , OSCS.Name

    from OSCL inner join OSCS on OSCS.statusID = OSCL.status and oscs.name = 'Ready for delivery'

    left join (select SCL4.srcvcallid , scl4.docabs , SCL4.object From scl4) s1 on oscl.callid = s1.srcvcallid

    left join (select oinv.docentry , OINV.docdate , OINV.docnum , oinv.series from OINV) o1 on s1.DocAbs = o1.DocEntry

    and s1.object=13

    left join NNM1 on NNM1.Series = o1.Series

    group by oscl.callid , OSCL.CREATEDATE , OSCL.custmrName ,

    oscl.itemName , OSCL.SUBJECT , o1.DocDate , NNM1.SeriesName + STR(o1.docnum) , OSCL.U_Loc , OSCS.Name

    Add a comment
    10|10000 characters needed characters exceeded

    • Hi,

      Please try below,

      select oscl.callid [Call Id#], OSCL.CREATEDATE [CALL DATE] , OSCL.custmrName [Service Customer],

      oscl.itemName [Service Product] , OSCL.SUBJECT [FAULT] , o1.DocDate , NNM1.SeriesName + STR(o1.docnum) , OSCL.U_Loc , OSCS.Name

      from OSCL inner join OSCS on OSCS.statusID = OSCL.status and oscs.name = 'Ready for delivery'

      left join (select distinct SCL4.srcvcallid , scl4.docabs , SCL4.object From scl4) s1 on oscl.callid = s1.srcvcallid

      left join (select oinv.docentry , OINV.docdate , OINV.docnum , oinv.series from OINV) o1 on s1.DocAbs = o1.DocEntry

      and s1.object=13

      left join NNM1 on NNM1.Series = o1.Series

      group by oscl.callid , OSCL.CREATEDATE , OSCL.custmrName ,

      oscl.itemName , OSCL.SUBJECT , o1.DocDate , NNM1.SeriesName + STR(o1.docnum) , OSCL.U_Loc , OSCS.Name

  • Posted on Sep 05, 2013 at 07:32 AM

    Hi,

    Try this:

    select oscl.callid [Call Id#], OSCL.CREATEDATE [CALL DATE] , OSCL.custmrName [Service Customer],

    oscl.itemName [Service Product] , OSCL.SUBJECT [FAULT] , o1.DocDate , NNM1.SeriesName + STR(o1.docnum) , OSCL.U_Loc , OSCS.Name

    from OSCL left join OSCS on OSCS.statusID = OSCL.status and oscs.name = 'Ready for delivery'

    left join (select SCL4.srcvcallid , scl4.docabs , SCL4.object From scl4) s1 on oscl.callid = s1.srcvcallid

    left join (select oinv.docentry , OINV.docdate , OINV.docnum , oinv.series from OINV) o1 on s1.DocAbs = o1.DocEntry

    and s1.object=13

    left join NNM1 on NNM1.Series = o1.Series

    group by oscl.callid , OSCL.CREATEDATE , OSCL.custmrName ,

    oscl.itemName , OSCL.SUBJECT , o1.DocDate , NNM1.SeriesName + STR(o1.docnum) , OSCL.U_Loc , OSCS.Name

    Thanks & Regards,

    Nagarajan

    Add a comment
    10|10000 characters needed characters exceeded

  • Posted on Sep 05, 2013 at 07:48 AM

    Hello Expert,

    Thank you very much for your time and hints.

    I did it.

    Add a comment
    10|10000 characters needed characters exceeded

    • Hi,

      Even that didn't work.

      I tried this way

      select oscl.callid [Call Id#], OSCL.CREATEDATE [CALL DATE] , OSCL.custmrName [Service Customer],

      oscl.itemName [Service Product] , OSCL.SUBJECT [FAULT] , o1.DocDate , NNM1.SeriesName + Str(o1.docnum),

      OSCL.U_Loc, OSCS.Name

      from OSCL inner join OSCS on OSCS.statusID = OSCL.status and oscs.name = 'Ready for delivery'

      left join scl4 AS s1 on s1.SrcvCallID = oscl.callID and s1.Line =

      (select max(scl4.line) from scl4 inner join oscl

      on oscl.callid = scl4.srcvcallid where scl4.srcvcallid = s1.srcvcallid)

      left join (select oinv.docentry , OINV.docdate , OINV.docnum , oinv.series from OINV)

      o1 on s1.DocAbs = o1.DocEntry and s1.object = 13

      left join NNM1 on NNM1.Series = o1.Series

      group by oscl.callid , OSCL.CREATEDATE , OSCL.custmrName ,

      oscl.itemName , oscl.Subject, o1.DocDate, NNM1.SeriesName + Str(o1.docnum), oscl.U_Loc, OSCS.Name

      Thanking you

      Pradnya

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.