Results 1 to 5 of 5
  1. #1
    Join Date
    Jan 2014
    Posts
    3

    Unanswered: Oracle Query through Gateway

    I am connecting to a IBM DB2 Mainframe through a Oracle DB Link.
    I am able to create a simple query that returns the data.
    The Database link is called "MAIN"
    I know that I have to use @MAIN in my from statement.
    My problem is I don't know where to put the @MAIN when using a more complicated query using outer Joins.
    Below is a sample of my Simple query and below that is my more complicated query.
    Could someone Please advise me where to put the @MAIN in the more complicated Query.

    SIMPLE Query that works.
    SELECT DBA1.TTHPS0_PEND_ASGMNT.FROM_ORG_OPCO_CD, DBA1.TTHPS0_PEND_ASGMNT.FROM_ORG_UNIT_ID, DBA1.TTHPS0_PEND_ASGMNT.TO_ORG_OPCO_CD,
    DBA1.TTHPS0_PEND_ASGMNT.TO_ORG_UNIT_ID
    FROM DBA1.TTHPS0_PEND_ASGMNT@MAIN


    More complicated Query.
    SELECT DBA1.TTHPS0_PEND_ASGMNT.FROM_ORG_OPCO_CD, DBA1.TTHPS0_PEND_ASGMNT.FROM_ORG_UNIT_ID, DBA1.TTHPS0_PEND_ASGMNT.TO_ORG_OPCO_CD,
    DBA1.TTHPS0_PEND_ASGMNT.TO_ORG_UNIT_ID, DBA1.TTHPS0_PEND_ASGMNT.CALL_STAT_CD, DBA1.TTHCS0_CALL_STATUS.CALL_STAT_DESC,
    DBA1.TTHCA0_CALL.PROB_SYMP_TEXT_1, DBA1.TTHCA0_CALL.PROB_SYMP_TEXT_2, DBA1.TTHCA0_CALL.CRT_DT, DBA1.TTHCA0_CALL.CALL_ID,
    DBA1.TTHCA0_CALL.EMPLOYEE_ONSITE_IN, DBA1.TTHCX0_CUST_LG_REQ.INTL_ADDR_LN_7, DBA1.TTHCX0_CUST_LG_REQ.CUST_EMPL_NAME,
    DBA1.TTHCX0_CUST_LG_REQ.PHN_NO, DBA1.TTHCX0_CUST_LG_REQ.LOG_ID
    FROM DBA1.TTHPS0_PEND_ASGMNT, DBA1.TTHCS0_CALL_STATUS, { oj DBA1.TTHCA0_CALL LEFT OUTER JOIN
    DBA1.TTHCX0_CUST_LG_REQ ON DBA1.TTHCA0_CALL.CALL_ID = DBA1.TTHCX0_CUST_LG_REQ.CALL_ID }
    WHERE DBA1.TTHPS0_PEND_ASGMNT.CALL_STAT_CD = DBA1.TTHCS0_CALL_STATUS.CALL_STAT_CD AND
    DBA1.TTHPS0_PEND_ASGMNT.FK_TTHCA0_CALLCALL = DBA1.TTHCA0_CALL.CALL_ID AND (DBA1.TTHPS0_PEND_ASGMNT.CALL_STAT_CD = '01') AND
    (DBA1.TTHPS0_PEND_ASGMNT.TO_ORG_UNIT_ID = 'EOOD')
    ORDER BY DBA1.TTHCA0_CALL.CRT_DT DESC

  2. #2
    Join Date
    Jan 2014
    Posts
    3

    Question Oracle Query using gateway

    I am connecting to a IBM DB2 Mainframe through a Oracle DB Link.
    I am able to create a simple query that returns the data.
    The Database link is called "MAIN"
    I know that I have to use @MAIN in my from statement.
    My problem is I don't know where to put the @MAIN when using a more complicated query using outer Joins.
    Below is a sample of my Simple query and below that is my more complicated query.
    Could someone Please advise me where to put the @MAIN in the more complicated Query.

    SIMPLE Query that works.
    SELECT DBA1.TTHPS0_PEND_ASGMNT.FROM_ORG_OPCO_CD, DBA1.TTHPS0_PEND_ASGMNT.FROM_ORG_UNIT_ID, DBA1.TTHPS0_PEND_ASGMNT.TO_ORG_OPCO_CD,
    DBA1.TTHPS0_PEND_ASGMNT.TO_ORG_UNIT_ID
    FROM DBA1.TTHPS0_PEND_ASGMNT@MAIN


    More complicated Query.
    SELECT DBA1.TTHPS0_PEND_ASGMNT.FROM_ORG_OPCO_CD, DBA1.TTHPS0_PEND_ASGMNT.FROM_ORG_UNIT_ID, DBA1.TTHPS0_PEND_ASGMNT.TO_ORG_OPCO_CD,
    DBA1.TTHPS0_PEND_ASGMNT.TO_ORG_UNIT_ID, DBA1.TTHPS0_PEND_ASGMNT.CALL_STAT_CD, DBA1.TTHCS0_CALL_STATUS.CALL_STAT_DESC,
    DBA1.TTHCA0_CALL.PROB_SYMP_TEXT_1, DBA1.TTHCA0_CALL.PROB_SYMP_TEXT_2, DBA1.TTHCA0_CALL.CRT_DT, DBA1.TTHCA0_CALL.CALL_ID,
    DBA1.TTHCA0_CALL.EMPLOYEE_ONSITE_IN, DBA1.TTHCX0_CUST_LG_REQ.INTL_ADDR_LN_7, DBA1.TTHCX0_CUST_LG_REQ.CUST_EMPL_NAME,
    DBA1.TTHCX0_CUST_LG_REQ.PHN_NO, DBA1.TTHCX0_CUST_LG_REQ.LOG_ID
    FROM DBA1.TTHPS0_PEND_ASGMNT, DBA1.TTHCS0_CALL_STATUS, { oj DBA1.TTHCA0_CALL LEFT OUTER JOIN
    DBA1.TTHCX0_CUST_LG_REQ ON DBA1.TTHCA0_CALL.CALL_ID = DBA1.TTHCX0_CUST_LG_REQ.CALL_ID }
    WHERE DBA1.TTHPS0_PEND_ASGMNT.CALL_STAT_CD = DBA1.TTHCS0_CALL_STATUS.CALL_STAT_CD AND
    DBA1.TTHPS0_PEND_ASGMNT.FK_TTHCA0_CALLCALL = DBA1.TTHCA0_CALL.CALL_ID AND (DBA1.TTHPS0_PEND_ASGMNT.CALL_STAT_CD = '01') AND
    (DBA1.TTHPS0_PEND_ASGMNT.TO_ORG_UNIT_ID = 'EOOD')
    ORDER BY DBA1.TTHCA0_CALL.CRT_DT DESC

  3. #3
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    what exactly is your problem?
    Code:
    SELECT dba1.tthps0_pend_asgmnt.from_org_opco_cd, 
           dba1.tthps0_pend_asgmnt.from_org_unit_id, 
           dba1.tthps0_pend_asgmnt.to_org_opco_cd, 
           dba1.tthps0_pend_asgmnt.to_org_unit_id, 
           dba1.tthps0_pend_asgmnt.call_stat_cd, 
           dba1.tthcs0_call_status.call_stat_desc, 
           dba1.tthca0_call.prob_symp_text_1, 
           dba1.tthca0_call.prob_symp_text_2, 
           dba1.tthca0_call.crt_dt, 
           dba1.tthca0_call.call_id, 
           dba1.tthca0_call.employee_onsite_in, 
           dba1.tthcx0_cust_lg_req.intl_addr_ln_7, 
           dba1.tthcx0_cust_lg_req.cust_empl_name, 
           dba1.tthcx0_cust_lg_req.phn_no, 
           dba1.tthcx0_cust_lg_req.log_id 
    FROM   dba1.tthps0_pend_asgmnt, 
           dba1.tthcs0_call_status, 
           dba1.tthca0_call 
           left outer join dba1.tthcx0_cust_lg_req 
                        ON dba1.tthca0_call.call_id = 
                           dba1.tthcx0_cust_lg_req.call_id 
    WHERE  dba1.tthps0_pend_asgmnt.call_stat_cd = 
           dba1.tthcs0_call_status.call_stat_cd 
           AND dba1.tthps0_pend_asgmnt.fk_tthca0_callcall = dba1.tthca0_call.call_id 
           AND ( dba1.tthps0_pend_asgmnt.call_stat_cd = '01' ) 
           AND ( dba1.tthps0_pend_asgmnt.to_org_unit_id = 'EOOD' ) 
    ORDER  BY dba1.tthca0_call.crt_dt DESC
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  4. #4
    Join Date
    Jan 2014
    Posts
    3

    My problem

    2 Mins before you responded I was able to fix my inital problem.
    I removed { oj } from the query and I added @MAIN at the end of each table name in the FROM Statement.

    Thank You for responding

    New problem is in the where statement, I want to add multiple
    AND ( dba1.tthps0_pend_asgmnt.to_org_unit_id = 'EOOD' )
    AND ( dba1.tthps0_pend_asgmnt.to_org_unit_id = 'ECCS' )
    AND ( dba1.tthps0_pend_asgmnt.to_org_unit_id = 'WCNT' )and so on but do not want to have to duplicate the complete Where statement for each org_unit_id.

    I think I can add the different org_unit_id's using an OR statement.

  5. #5
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713
    Quote Originally Posted by RhealD View Post
    2 Mins before you responded I was able to fix my inital problem.
    I removed { oj } from the query and I added @MAIN at the end of each table name in the FROM Statement.

    Thank You for responding

    New problem is in the where statement, I want to add multiple
    AND ( dba1.tthps0_pend_asgmnt.to_org_unit_id = 'EOOD' )
    AND ( dba1.tthps0_pend_asgmnt.to_org_unit_id = 'ECCS' )
    AND ( dba1.tthps0_pend_asgmnt.to_org_unit_id = 'WCNT' )and so on but do not want to have to duplicate the complete Where statement for each org_unit_id.

    I think I can add the different org_unit_id's using an OR statement.
    Try:
    Code:
    AND ( dba1.tthps0_pend_asgmnt.to_org_unit_id IN ('EOOD', 'ECCS' ,'WCNT' ))
    Also you may want to add this hint:

    Code:
    SELECT /*+ DRIVING_SITE(dba1.tthps0_pend_asgmnt) */
           dba1.tthps0_pend_asgmnt.from_org_opco_cd, 
           dba1.tthps0_pend_asgmnt.from_org_unit_id, 
    . . .   E t c   . . .
    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •