If you are unable to create a new account, please email support@bspsoftware.com

 

News:

MetaManager - Administrative Tools for IBM Cognos
Pricing starting at $2,100
Download Now    Learn More

Main Menu

Joins in Cognos Question

Started by que1983, 11 Apr 2017 02:43:59 PM

Previous topic - Next topic

que1983

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

bdbits

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?