Home » Applications » PeopleSoft, JD Edwards & Siebel » BORM tables join-peoplesoft (Peoplesoft delivered BORM tables )
| BORM tables join-peoplesoft [message #677052] |
Mon, 12 August 2019 13:06  |
 |
Neha_1
Messages: 2
Registered: August 2019
|
Junior Member |
|
|
Hi ,
I was trying to join 8 -9 tables but got stuck.i have to join the below tables from CRM peoplesoft-BO,BO_NAME,CM,BO_CM,CM_PHONE,RD_PERSON,RD_PARTNER tables.
Any help will be highly appreciated.
|
|
|
|
|
|
| Re: BORM tables join-peoplesoft [message #677054 is a reply to message #677053] |
Tue, 13 August 2019 01:44  |
|
Neha_1
Messages: 2
Registered: August 2019
|
Junior Member |
|
|
Structure of the Tables with Keys-
Table:
1.BO_NAME------BO_ID (Key), SEQ_NBR (Key), FIRST_NAME,LAST_NAME etc
2.CM-----------CM_ID(KEY), EMAIL_ADDR etc
3.CM_PHONE-----CM_ID,COUNTRY_CODE etc
4.BO_CM--------BO_ID(Key),PROFILE_CM_SEQ(key),START_DT (key)
5.BO_ROLE------BO_ID(key),ROLE_TYPE_ID(key),ROLE_START_DT(Key)
6.BO_REL-------BO_ID_1(Key),REL_TYPE_ID(key),BO_ID_2(key),ROLE-TYPE_ID_1(key),ROLE_TYPE_ID_2(key)
7.RD_PERSON----PERSON_ID(key)
8.RD_PARTNER----BO_ID(key)
Please help me getting the query corrected. i think CM has to be left joined with CM_PHONE but not sure
----------------------------------------------------------------------
The query that i constructed but I would like to know the joins
----------------------------------------------------------------------
SELECT DISTINCT D.PERSON_ID, 'True', 'False', 'False', A.FIRST_NAME, A.LAST_NAME, B.EMAIL_ADDR, REGEXP_REPLACE(CONCAT(concat( C.COUNTRY_CODE, C.PHONE), C.EXTENSION), '[^0-9]+', ''), 'BE', F.BGC_SFID, D.TITLE, F.BGC_MOF_CHANNEL, F.BGC_MOF_DIVISION, F.BGC_MOF_SEGMENT, 'Indirect Sales', F.BGC_PR_NAME, 'Indirect Sales'
FROM PS_BO_NAME A, PS_CM B, PS_CM_PHONE C, PS_RD_PERSON D, PS_BO_CM E, PS_RD_PARTNER F, PS_BO_REL G, PS_BO_ROLE H
WHERE ( C.CM_ID = B.CM_ID
AND ( A.BO_ID = D.BO_ID
AND A.BO_ID = E.BO_ID
AND A.BO_ID = F.BO_ID
AND H.ROLE_TYPE_ID = 11
AND A.BO_ID = H.BO_ID
AND E.PROFILE_CM_SEQ = I.PROFILE_CM_SEQ
AND E.BO_CM_START_DT < = TRUNC(SYSDATE)
AND E.BO_CM_END_DT > TRUNC(SYSDATE)
AND B.CM_TYPE_ID = 4
AND A.BO_ID = H.BO_ID
AND G.BO_ID_1 = D.BO_ID
AND G.START_DT < = TRUNC(SYSDATE)
AND G.END_DT > TRUNC(SYSDATE)
AND H.BO_ID = G.BO_ID_2
AND H.ROLE_START_DT < =TRUNC(SYSDATE)
AND H.ROLE_END_DT > TRUNC(SYSDATE)
AND E.CM_ID = E.CM_ID
AND G.START_DT < =TRUNC(SYSDATE)
AND G.END_DT > TRUNC(SYSDATE)
AND E.CM_ID = B.CM_ID
AND B.CM_ID = C.CM_ID
AND A.BO_NAME = D.BO_ID
AND D.ACTIVE_FLAG = 'A'
AND A.BO_ID = F.BO_ID
AND F.PARTNER_STATUS = '1'
AND F.BGC_MOF_DIVISION IN ('CBU','EBU')
AND F.BGC_MOF_CHANNEL IN ('Indirect Sales','Indirect Sales SME')
AND F.BGC_SLS_TRAINING = 'Y' ))
|
|
|
|
| Re: BORM tables join-peoplesoft [message #677055 is a reply to message #677054] |
Tue, 13 August 2019 01:58 |
John Watson
Messages: 8960
Registered: January 2010
Location: Global Village
|
Senior Member |
|
|
You are not going to like this, but it is meant to be helpful advice.
First, use [code] tags. I have already asked you to do this.
Second, format your code. If you dont want to do it yourself, use a code formatter. There's one here, http://www.dpriver.com/pp/sqlformat.htm
Third, describe your tables with the SQL*Plus DESCRIBE command.
Fourth, detail the primary and foreign key constraints. "key" by itself is not very helpful.
Fifth, use sensible table aliases. You may know what a, b, ,c, etc mean. Anyone else has to keep referring back.
Sixth, use ANSI join syntax. A clean separation between the join clauses and the filter clauses will make it easier to understand and less prone to error.
Overall, the code is not readable. I don't think anyone can work with it until you tidy it up. Just following my suggestions above may let you see the problem.
[Updated on: Tue, 13 August 2019 01:58] Report message to a moderator |
|
|
|
Goto Forum:
Current Time: Thu Nov 21 06:49:56 CST 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
