July 20, 2009 at 4:08 pm
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
July 20, 2009 at 4:53 pm
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...
July 20, 2009 at 4:57 pm
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
July 20, 2009 at 5:44 pm
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?
July 20, 2009 at 5:50 pm
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.
July 20, 2009 at 5:54 pm
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