Author Topic: Joins in Cognos Question  (Read 1256 times)

Offline que1983

  • Full Member
  • ***
  • Join Date: Sep 2015
  • Posts: 26
  • Forum Citizenship: +0/-0
Joins in Cognos Question
« on: 11 Apr 2017 02:43:59 pm »
SELECT  LN_EVNTDATE_1.LNKEY 
      , LN_EVNTDATE_1.IDX 
      , LN_MTGTERMS.APP_DATE 
      , LN_DOCGEN.SETTLEMENT_DATE 
      , LN_MTGTERMS.CONTRACTDATE 
      , U_LN_POST_CLOSING.U_REVIEW_STARTED 
      , U_LN_POST_CLOSING.U_REVIEW_COMPLETED 
      , DAYS(CURRENT DATE) - DAYS(LN_DOCGEN.SETTLEMENT_DATE) AS Age
   FROM
      ((LZ_EMP_MRTG_EMPOWER_RDX.LN_MTGTERMS LN_MTGTERMS
      RIGHT OUTER JOIN
      LZ_EMP_MRTG_EMPOWER_RDX.LN_EVNTDATE LN_EVNTDATE_1
      ON (LN_MTGTERMS.LNKEY = LN_EVNTDATE_1.LNKEY))
      LEFT OUTER JOIN
      LZ_EMP_MRTG_EMPOWER_RDX.LN_DOCGEN LN_DOCGEN
      ON (LN_DOCGEN.LNKEY = LN_EVNTDATE_1.LNKEY))
      LEFT OUTER JOIN
      LZ_EMP_MRTG_EMPOWER_RDX.U_LN_POST_CLOSING U_LN_POST_CLOSING
      ON (U_LN_POST_CLOSING.LNKEY = LN_EVNTDATE_1.LNKEY)
   WHERE (LN_EVNTDATE_1.LNKEY LIKE '4%')
      AND (LN_EVNTDATE_1.IDX = 339)
      AND (U_LN_POST_CLOSING.U_REVIEW_COMPLETED IS NULL))

I received the above code in TOAD which pulls 12,000 records.  I am attempting to replicate it in cognos
I have 1 query with the following filters
[Presentation Layer].[LOAN EVENT DATE].[Loan Key]like '4%'
[File Review Complete]is null
[IDX]=339
This matches the code in TOAD.  However I get 60000 records.  I believe its because the code in TOAD has a right join.  Would I need to break up some of the fields and attempt a right join.  Is there a link that explains the different joins.  I know 1:1 is equal join and 1:n is a 1 to many join.  I have not seen a right outer join
select distinct "LOAN_MORTGAGE_TERMS"."Loan_Number" "Loan_Number" , "LOAN_MORTGAGE_TERMS"."APP_DATE" "APP_DATE" , "LOAN_DOCUMENT_GENERAL"."Settlement_Date" "Settlement_Date" , cast("LOAN_POST_CLOSING"."File_Review_Start" as date) "File_Review_Start" , cast("LOAN_POST_CLOSING"."File_Review_Complete" as date) "File_Review_Complete" , "LOAN_EVENT_DATE"."IDX" "IDX"
 from ((((
select "LN_CONTACTS"."LNKEY" "Loan_Key"
 from "LZ_EMP_MRTG_EMPOWER_RDX"."LN_CONTACTS" "LN_CONTACTS") "LOAN_CONTACTS" inner join (
select "LN_DOCGEN"."LNKEY" "Loan_Key" , "LN_DOCGEN"."SETTLEMENT_DATE" "Settlement_Date"
 from "LZ_EMP_MRTG_EMPOWER_RDX"."LN_DOCGEN" "LN_DOCGEN") "LOAN_DOCUMENT_GENERAL" on "LOAN_CONTACTS"."Loan_Key" = "LOAN_DOCUMENT_GENERAL"."Loan_Key") LEFT OUTER JOIN (
select "LN_EVNTDATE"."LNKEY" "Loan_Key" , "LN_EVNTDATE"."IDX" "IDX"
 from "LZ_EMP_MRTG_EMPOWER_RDX"."LN_EVNTDATE" "LN_EVNTDATE") "LOAN_EVENT_DATE" on "LOAN_CONTACTS"."Loan_Key" = "LOAN_EVENT_DATE"."Loan_Key") LEFT OUTER JOIN (
select "LN_MTGTERMS"."LOAN_NUM" "Loan_Number" , "LN_MTGTERMS"."LNKEY" "Loan_Key" , "LN_MTGTERMS"."APP_DATE" "APP_DATE"
 from "LZ_EMP_MRTG_EMPOWER_RDX"."LN_MTGTERMS" "LN_MTGTERMS") "LOAN_MORTGAGE_TERMS" on "LOAN_CONTACTS"."Loan_Key" = "LOAN_MORTGAGE_TERMS"."Loan_Key") LEFT OUTER JOIN (
select "U_LN_POST_CLOSING"."U_FILE_REVIEW_COMPLETE" "File_Review_Complete" , "U_LN_POST_CLOSING"."U_FILE_REVIEW_START" "File_Review_Start" , "U_LN_POST_CLOSING"."LNKEY" "Loan_Key"
 from "LZ_EMP_MRTG_EMPOWER_RDX"."U_LN_POST_CLOSING" "U_LN_POST_CLOSING") "LOAN_POST_CLOSING" on "LOAN_CONTACTS"."Loan_Key" = "LOAN_POST_CLOSING"."Loan_Key"
 where "LOAN_EVENT_DATE"."Loan_Key" like '4%' and cast("LOAN_POST_CLOSING"."File_Review_Complete" as date) is null and "LOAN_EVENT_DATE"."IDX" = 339 FOR FETCH ONLY

Offline bdbits

  • Super Moderator
  • Statesman
  • ******
  • Join Date: Feb 2010
  • Posts: 1,787
  • Forum Citizenship: +105/-0
Re: Joins in Cognos Question
« Reply #1 on: 11 Apr 2017 04:38:56 pm »
Outer join is 1.n<->0.n which without looking I believe is shown in the user guide but... if there is a relationship defined in the package, that is where the outer join really should be defined. Are you also the FM modeler? Or can you work with them to get it changed in the model?

 


       
Twittear