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
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?