cancel
Showing results for 
Search instead for 
Did you mean: 

No data to retrieve

0 Kudos

I have query returns records in TOAD, but when set up on Bi4 business object report, I kept getting no data retrieve. My reports point to same database as i ran on TOAD, I am certain that there should be record returns. Do you have idea why? BTW, it is "custom query" for BO report.

Accepted Solutions (0)

Answers (1)

Answers (1)

Former Member
0 Kudos

Surely something wrong with BO filters or custom script. Try remove all the filter conditions and see if it runs or go one object by one and refresh inside to see what field is causing the issue. Give me more details so I can look at it.

Kris

0 Kudos

You are correct. I started cut out the complicate filter to make simple query and it works. It is my first report to create under Bi4 environment so I thought I misstepped while creating report.

My custom is complicate one (below) you have any clue how to make it works on BO

select ca.FACT_LOAN_MO_ROW_ID,

    ca.BORR_SSN,ca.BORR_ACCT_SFX_NBR,ca.LOAN_SEQ_NBR,

  (pca.LOAN_OSTD_PRIN_AMT + FA_PBO_AMT - ca.LOAN_OSTD_PRIN_AMT) as PBO_VAR_AMT,

(pca.LOAN_ACCR_INT_AMT + pca.IBR_ACCR_INT_AMT + FA_IRB_AMT) - (ca.LOAN_ACCR_INT_AMT + ca.IBR_ACCR_INT_AMT) as IRB_VAR_AMT,

  FA_PBO_AMT,

  FA_IRB_AMT,

  pca.LOAN_OSTD_PRIN_AMT,

pca.LOAN_ACCR_INT_AMT,

  pca.IBR_ACCR_INT_AMT,

  pca.LOAN_ACCR_LFEE_AMT,

  pca.RTI_FEE_OSTD_AMT,

  pca.LOAN_MO_END_ORIG_FEE_BAL_AMT,

  pca.LOAN_MO_END_RBTE_BAL_AMT,

 

  ca.LOAN_OSTD_PRIN_AMT,

  ca.LOAN_ACCR_INT_AMT,

  ca.IBR_ACCR_INT_AMT,

  ca.LOAN_ACCR_LFEE_AMT,

  ca.RTI_FEE_OSTD_AMT,

  ca.LOAN_MO_END_ORIG_FEE_BAL_AMT,

  ca.LOAN_MO_END_RBTE_BAL_AMT

from

  (

    select fa.BORR_SSN, fa.BORR_ACCT_SFX_NBR,fa.LOAN_SEQ_NBR,

        sum(PBO_amt) as FA_PBO_AMT ,

        sum(fa.IRB_amt) as FA_IRB_AMT

    from

    (

    select BORR_SSN,BORR_ACCT_SFX_NBR,LOAN_SEQ_NBR,

            fsa_amt_col_nm,

bi.mtj_tran_col_nm||fsa_amt_col_nm,

            Case when bi.mtj_tran_col_nm||fsa_amt_col_nm = 'JRNL1_AMTPBO_AMT' Then (l.jrnl_1_amt)

when bi.mtj_tran_col_nm||fsa_amt_col_nm = 'OFEE_AMTPBO_AMT' Then (l.LOAN_OFEE_AMT)

when bi.mtj_tran_col_nm||fsa_amt_col_nm = 'INS_FEE_AMTPBO_AMT' Then (l.LOAN_IFEE_AMT)

when bi.mtj_tran_col_nm||fsa_amt_col_nm ='JRNL1_LOAN_IFEE_OFEE_AMTPBO_AMT' Then (l.JRNL_1_AMT + l.LOAN_IFEE_AMT + l.LOAN_OFEE_AMT)

when bi.mtj_tran_col_nm||fsa_amt_col_nm ='LOAN_INS_FEE_OFEE_AMTPBO_AMT' Then (l.LOAN_IFEE_AMT + LOAN_OFEE_AMT)

else  0.00

end as PBO_amt ,

            Case when bi.mtj_tran_col_nm||fsa_amt_col_nm = 'JRNL2_AMTIRB_AMT' Then (l.jrnl_2_amt)

when bi.mtj_tran_col_nm||fsa_amt_col_nm = 'BR_AC_IN_AMTIRB_AMT' Then (l.BORR_ACCR_INT_AMT)

when bi.mtj_tran_col_nm||fsa_amt_col_nm = 'IBR_ED_BOR_AC_INT_AMTIRB_AMT' Then (l.BORR_ACCR_INT_AMT + l.LOAN_IBR_ED_INT_AMT)

else  0.00

end as IRB_amt

                              

        from fsa_ed.fsa_Btch_item bi,

        FSA_ED.map_btch_item_clas m, FSA_ED.fact_loan_tran l

        where bi.btch_item_tran_id = m.btch_item_tran_id

        and m.fact_loan_tran_row_id = l.fact_loan_tran_row_id

        and bi.fsa_loan_pgm_cd = 'PI1'

--and bi.btch_item_tran_id between 20150301000000 and 20150401999999

        AND BI.PRCS_DT between '01-MAY-15' AND  '31-MAY-15'

        AND BI.BTCH_ITEM_SRC_CD =  'C'

        AND BI.BTCH_ITEM_STAT_CD Not In  ( 'FR','IP' )

    ) fa

   

    group by fa.BORR_SSN, fa.BORR_ACCT_SFX_NBR, fa.LOAN_SEQ_NBR

  ) F,

   

  FSA_ED.FACT_LOAN_MONTHLY ca,

  FSA_ED.FACT_LOAN_MONTHLY pca

where ca.BORR_SSN = f.BORR_SSN

and ca.BORR_ACCT_SFX_NBR = f.BORR_ACCT_SFX_NBR

and ca.LOAN_SEQ_NBR = f.LOAN_SEQ_NBR

and ca.pstg_dt_row_id = 20150531

and pca.BORR_SSN = f.BORR_SSN

and pca.BORR_ACCT_SFX_NBR = f.BORR_ACCT_SFX_NBR

and pca.LOAN_SEQ_NBR = f.LOAN_SEQ_NBR

and pca.pstg_dt_row_id = 20150430

and ( (pca.LOAN_OSTD_PRIN_AMT + FA_PBO_AMT - ca.LOAN_OSTD_PRIN_AMT) <> 0 or

((pca.LOAN_ACCR_INT_AMT + pca.IBR_ACCR_INT_AMT + FA_IRB_AMT) - (ca.LOAN_ACCR_INT_AMT + ca.IBR_ACCR_INT_AMT ))  <> 0 )

ORDER BY 1

?