Please help me with this From Clause

  • FROM dbo.PS_BAN_WARRANT_TBL INNER JOIN

    dbo.PS_CUSTOMER AS Customer ON dbo.PS_BAN_WARRANT_TBL.BAN_CUSTOMER_ID = Customer.CUST_ID INNER JOIN

    dbo.PS_CUSTOMER AS AdjustingDealer ON dbo.PS_BAN_WARRANT_TBL.BAN_ADJ_DEALER_ID = AdjustingDealer.CUST_ID INNER JOIN

    dbo.PS_CUSTOMER AS ManufacturingDealer ON dbo.PS_BAN_WARRANT_TBL.BAN_MANUF_DEALR_ID = ManufacturingDealer.CUST_ID LEFT OUTER JOIN

    dbo.PS_BAN_WARRT_F_TBL ON dbo.PS_BAN_WARRANT_TBL.BAN_CLAIM_NO = dbo.PS_BAN_WARRT_F_TBL.BAN_CLAIM_NO AND

    dbo.PS_BAN_WARRANT_TBL.CLAIM_DT = dbo.PS_BAN_WARRT_F_TBL.CLAIM_DT AND

    dbo.PS_BAN_WARRANT_TBL.BAN_WARRANTY_TYPE = dbo.PS_BAN_WARRT_F_TBL.BAN_WARRANTY_TYPE AND

    dbo.PS_BAN_WARRANT_TBL.SETID = dbo.PS_BAN_WARRT_F_TBL.SETID AND

    dbo.PS_BAN_WARRANT_TBL.BUSINESS_UNIT = dbo.PS_BAN_WARRT_F_TBL.BUSINESS_UNIT LEFT OUTER JOIN

    dbo.PS_BAN_WR_FAIL_TBL ON dbo.PS_BAN_WARRT_F_TBL.BAN_CAUSE_FAILURE = dbo.PS_BAN_WR_FAIL_TBL.BAN_CAUSE_FAILURE AND

    dbo.PS_BAN_WARRT_F_TBL.BAN_FAIL_TYPE_CD = dbo.PS_BAN_WR_FAIL_TBL.BAN_FAIL_TYPE_CD INNER JOIN

    dbo.PS_CUSTOMER AS CorpCustomer ON Customer.CORPORATE_CUST_ID = CorpCustomer.CUST_ID INNER JOIN

    dbo.PS_CUST_ADDRESS AS CustomerAddress ON Customer.CUST_ID = CustomerAddress.CUST_ID AND

    Customer.SETID = CustomerAddress.SETID INNER JOIN

    dbo.PS_CUST_ADDRESS AS AdjustingDealerAddress ON AdjustingDealer.CUST_ID = AdjustingDealerAddress.CUST_ID AND

    AdjustingDealer.SETID = AdjustingDealerAddress.SETID INNER JOIN

    dbo.PS_CUST_ADDRESS AS ManufacturingDealerAddress ON ManufacturingDealer.CUST_ID = ManufacturingDealerAddress.CUST_ID AND

    ManufacturingDealer.SETID = ManufacturingDealerAddress.SETID

    The above from clause needs to be modified in a manner that

    1) “PS_BAN_WARRANT_TBL.BAN_CUSTOMER_ID” IS REPLACED with “IRR_ADJ_PEOPLESOFT_CUST_ID”

    2)”PS_BAN_WARRANT_TBL.BAN_ADJ_DEALER_ID” .. “IRR_ADJ.BAN_ADJ_DEALER_ID”

    3) “PS_BAN_WARRANT_TBL.BAN_CLAIM_NO”.......”IRR_ADJ.BAN_CLAIM_NO”

    4) “PS_BAN_WARRANT_TBL.CLAIM_DT”.........”IRR_ADJ.CLAIM_DT”

    5) “PS_BAN_WR_FAIL_TBL.BAN_CAUSE_FAILURE”...”IRR_ADJ.BAN_CAUSE_FAILURE”

    We are supposed to modify the inner joins so as to add the table IRR_ADJ as well...

    Please help, I am not perfect with it and I have been trying since morning... Couldn't do it, have to finish this by tonight.

    Thanks a lot

  • To replace a string in SSMS you could use {STRG}+H. This will allow you to do search and replace.

    You have to make sure that the new table will have columns with identical names. Otherwise you'd have to change those as well.

    Without knowing the table structure and the business case it's hard to say what else need to be done.

    If you have to finish it by tonight you might want to try one of your (hopefully existing) emergency phone numbers to call the vendors you've got the original software from.

    If it's developed in house, contact the person who did it. If that person is no longer available, get the manual to check for any hint on how to modify the query.

    If none of the above will work, tell the person who gave you the due date (rsp. due time) you won't be able to make it...



    Lutz
    A pessimist is an optimist with experience.

    How to get fast answers to your question[/url]
    How to post performance related questions[/url]
    Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

  • This is a little easier to read. I am still unsure how you want us to help you though I don't think you have given us enough information about your environment.

    FROM

    dbo.PS_BAN_WARRANT_TBL

    INNER JOIN dbo.PS_CUSTOMER AS Customer

    ON

    dbo.PS_BAN_WARRANT_TBL.BAN_CUSTOMER_ID = Customer.CUST_ID

    INNER JOIN dbo.PS_CUSTOMER AS AdjustingDealer

    ON

    dbo.PS_BAN_WARRANT_TBL.BAN_ADJ_DEALER_ID = AdjustingDealer.CUST_ID

    INNER JOIN dbo.PS_CUSTOMER AS ManufacturingDealer

    ON

    dbo.PS_BAN_WARRANT_TBL.BAN_MANUF_DEALR_ID = ManufacturingDealer.CUST_ID

    LEFT OUTER JOIN dbo.PS_BAN_WARRT_F_TBL

    ON

    dbo.PS_BAN_WARRANT_TBL.BAN_CLAIM_NO = dbo.PS_BAN_WARRT_F_TBL.BAN_CLAIM_NO

    AND dbo.PS_BAN_WARRANT_TBL.CLAIM_DT = dbo.PS_BAN_WARRT_F_TBL.CLAIM_DT

    AND dbo.PS_BAN_WARRANT_TBL.BAN_WARRANTY_TYPE = dbo.PS_BAN_WARRT_F_TBL.BAN_WARRANTY_TYPE

    AND dbo.PS_BAN_WARRANT_TBL.SETID = dbo.PS_BAN_WARRT_F_TBL.SETID

    AND dbo.PS_BAN_WARRANT_TBL.BUSINESS_UNIT = dbo.PS_BAN_WARRT_F_TBL.BUSINESS_UNIT

    LEFT OUTER JOIN dbo.PS_BAN_WR_FAIL_TBL

    ON

    dbo.PS_BAN_WARRT_F_TBL.BAN_CAUSE_FAILURE = dbo.PS_BAN_WR_FAIL_TBL.BAN_CAUSE_FAILURE

    AND dbo.PS_BAN_WARRT_F_TBL.BAN_FAIL_TYPE_CD = dbo.PS_BAN_WR_FAIL_TBL.BAN_FAIL_TYPE_CD

    INNER JOIN dbo.PS_CUSTOMER AS CorpCustomer

    ON

    Customer.CORPORATE_CUST_ID = CorpCustomer.CUST_ID

    INNER JOIN dbo.PS_CUST_ADDRESS AS CustomerAddress

    ON

    Customer.CUST_ID = CustomerAddress.CUST_ID

    AND Customer.SETID = CustomerAddress.SETID

    INNER JOIN dbo.PS_CUST_ADDRESS AS AdjustingDealerAddress

    ON

    AdjustingDealer.CUST_ID = AdjustingDealerAddress.CUST_ID

    AND AdjustingDealer.SETID = AdjustingDealerAddress.SETID

    INNER JOIN dbo.PS_CUST_ADDRESS AS ManufacturingDealerAddress

    ON

    ManufacturingDealer.CUST_ID = ManufacturingDealerAddress.CUST_ID

    AND ManufacturingDealer.SETID = ManufacturingDealerAddress.SETID

  • I'd suggest you reformat the query as Bryan has, but use aliases for the tables. It will make it much easier to read.

    Then if you can't get it, explain more what you are trying to change. If you are replacing table names with new tables, are the designs the same? What error are you getting?

  • All I did was to throw what the OP gave us into http://www.sqlinform.com/ with a select * added on (wouldn't beutify without it). I was hoping to make some sense of what the OP was trying to accomplish. It made it clear that it is probably only looks more complicated than the OP realizes and with a little more info I am sure we could help in some way.

  • I know, I was a little nervous and it was really stupid of me to actually expect help without providing the required info about the schema and the View itself. I am trying to work on it, If I do not get it, I will come back to you guys again tomorrow.

    Thanks a lot guys for trying

    Sorry..

    🙁

Viewing 6 posts - 1 through 5 (of 5 total)

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