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 Go to next message
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 #677053 is a reply to message #677052] Tue, 13 August 2019 01:02 Go to previous messageGo to next message
John Watson
Messages: 8074
Registered: January 2010
Location: Global Village
Senior Member
Welcome to the forum. Please read the OraFAQ Forum Guide and How to use code tags and make your code easier to read

You need to post the code that you are stuck on. The definitions of the tables (including primary and foreign key constraints) would help, too.
Re: BORM tables join-peoplesoft [message #677054 is a reply to message #677053] Tue, 13 August 2019 01:44 Go to previous messageGo to next message
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 Go to previous message
John Watson
Messages: 8074
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

Previous Topic: Siebel management agent not starting
Next Topic: Siebel Database Upgrade Wizard Error
Goto Forum:
  


Current Time: Thu Dec 05 09:29:43 CST 2019