Post reply

Using Virtual tables

  • July 19, 2005 at 12:22 am

    #167889

    I have written a stored proc which supports a crystal report. Though functional, the client does not want the use of *= in the where clause and would prefer join statements. I need help with the virtual table portion of the script. I can write the joins for the SV00200 and RM00101 tables but I am uncertain how to use the virtual tables w/o the current where clauses?

    Select

     SV00300.Service_Call_ID, SV00300.CUSTNAME, SV00300.DATE1, SV00300.USERID, SV00300.WS_Time_1, SV00300.Priority_of_Call, SV00300.Purchase_Order,

     SV00300.User_Define_2a, SV00300.User_Define_3a, SV00300.User_Define_4a, SV00300.Service_Date_1, SV00300.ADRSCODE, SV00200.ADDRESS1, SV00200.City,

     SV00200.State, SV00200.Zip, (left(SV00200.Phone1,3) + '-' + substring(SV00200.Phone1, 4,3) + '-' + substring(SV00200.Phone1, 7,4)),

     SV00300.Completion_Date, SV00200.LOCATNNM, SV00300.Billing_Amount_NTE,  SV00300.Service_Description, SV00300.Resolution_Description,

     Descr.Service_Notes, Resol.Resolution_Notes, Special.Special_Instructions, SV00300.Service_User_Define_9, SV00300.Technician,

     (left(RM00101.Phone3,3) + '-' + substring(RM00101.Phone3, 4,3) + '-' + substring(RM00101.Phone3, 7,4))

    from  SV00300, SV00200, RM00101,

     (Select Service_Call_ID, Note_Service_Index, Record_Notes Service_Notes from SV000805

      where /*Service_Call_ID = @Service_Call_ID AND */ Note_Service_Index = 'Description') Descr,

     (Select Service_Call_ID, Note_Service_Index, Record_Notes Resolution_Notes from SV000805

      where /* Service_Call_ID = @Service_Call_ID AND */ Note_Service_Index = 'Resolution') Resol,

     (Select CUSTNMBR, Note_Service_Index, Record_Notes Special_Instructions from SV000805

      where /* Service_Call_ID = @Service_Call_ID AND */ WS_Note_Type = 'C' AND Note_Service_Index like 'Special%') Special

    where  SV00300.Service_Call_ID = @ServiceCallID AND

     SV00300.CUSTNMBR = SV00200.CUSTNMBR AND SV00300.ADRSCODE = SV00200.ADRSCODE AND

     SV00300.Service_Call_ID *= Descr.Service_Call_ID AND

     SV00300.Service_Call_ID *= Resol.Service_Call_ID AND

     SV00300.CUSTNMBR *= Special.CUSTNMBR AND

     SV00300.CUSTNMBR *= RM00101.CUSTNMBR

    Any help would be greatly appreciated.

    Thank you



    "Some like to understand what they believe in. Others like to believe in what they understand."
    --Stanislaus J. Lec

  • July 19, 2005 at 2:06 am

    #575407

    Looks like you are in for a headache

    I would try something like

    Select * from

    SV00300

    inner join SV00200

                 on SV00300.CUSTNMBR = SV00200.CUSTNMBR AND SV00300.ADRSCODE = SV00200.ADRSCODE

    left join RM00101

                 on  SV00300.CUSTNMBR = RM00101.CUSTNMBR

    left join SV000805 S1

                 on SV00300.Service_Call_ID = S1.Service_Call_ID and S1.Note_Service_Index = 'Description'

    left join SV000805 S2

                 on SV00300.Service_Call_ID = S2.Service_Call_ID and S2.Note_Service_Index = 'Resolution'

    left join SV000805 S3

                 on  SV00300.Service_Call_ID = S3.Service_Call_ID and S3.WS_Note_Type = 'C' AND S3.Note_Service_Index like 'Special%'

    where  SV00300.Service_Call_ID = @ServiceCallID

    But I am only guessing. Maybe some of the join clauses should be where clauses instead

    Good luck

  • July 19, 2005 at 7:50 am

    #575483

    Can you try:

    Select

     SV00300.Service_Call_ID,

     SV00300.CUSTNAME,

     SV00300.DATE1,

     SV00300.USERID,

     SV00300.WS_Time_1,

     SV00300.Priority_of_Call,

     SV00300.Purchase_Order,

     SV00300.User_Define_2a,

     SV00300.User_Define_3a,

     SV00300.User_Define_4a,

     SV00300.Service_Date_1,

     SV00300.ADRSCODE,

     SV00200.ADDRESS1,

     SV00200.City,

     SV00200.State,

     SV00200.Zip,

     (left(SV00200.Phone1,3) + '-' + substring(SV00200.Phone1, 4,3) + '-' + substring(SV00200.Phone1, 7,4)),

     SV00300.Completion_Date,

     SV00200.LOCATNNM,

     SV00300.Billing_Amount_NTE, 

     SV00300.Service_Description,

     SV00300.Resolution_Description,

     Descr.Service_Notes,

     Resol.Resolution_Notes,

     Special.Special_Instructions,

     SV00300.Service_User_Define_9,

     SV00300.Technician,

     (left(RM00101.Phone3,3) + '-' + substring(RM00101.Phone3, 4,3) + '-' + substring(RM00101.Phone3, 7,4))

    from

        SV00300

        inner join SV00200

                 on SV00300.CUSTNMBR = SV00200.CUSTNMBR AND SV00300.ADRSCODE = SV00200.ADRSCODE

        left join SV000805  Descr

                 on SV00300.Service_Call_ID = Descr.Service_Call_ID and Descr.Note_Service_Index = 'Description'

        left join SV000805 Resol

                 on SV00300.Service_Call_ID = Resol.Service_Call_ID and Resol.Note_Service_Index = 'Resolution'

        left join SV000805 Special

                 on  SV00300.CUSTNMBR = Special.CUSTNMBR and Special.WS_Note_Type = 'C' AND Special.Note_Service_Index like 'Special%'

        left join RM00101

                 on  SV00300.CUSTNMBR = RM00101.CUSTNMBR

    where  SV00300.Service_Call_ID = @ServiceCallID

     


    * Noel

  • July 20, 2005 at 1:47 am

    #575728

    When I compare our suggestions, I see that you have renamed S1, S2 and S3 to Descr, Resol and Special, which of course is much better since it is more in agreement with the original query. And then you have moved the join

    left join RM00101

                 on  SV00300.CUSTNMBR = RM00101.CUSTNMBR

    such that it is the last join. Do you think it makes any difference?

    Maybe I should put my question more generally: In a join of three or more tables, when can you change the order of the joins (and still have the same output table)?

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply