I have BO report with select query that pulls data from 4 different select statements (either use WITH or put select statements at WHERE clause). How do I set up in BO design. Custom query script shown below. I have tables defined in UDT (Universe Design Tool) already, understand how join defined between table, but could figure out how set up select statements at where clause in BO report design
WITH Book as
(Select B.Borr_SSN,
B.CSL_Rec_ID,
B.Awrd_ID
From CSNL_Book B
WHere B.CREA_LGON_ID != 'INFA_9999'
and B.CSL_Rec_UD <>
and Substr(B.AWRD, 1,1) <> 'P'
group by CSL_Rec_ID, AWRD_ID, BORR_SSN
),
Funding as
(Select F.CSL_Rec_Id,
MIN(F.PRCS_DT_ROW_ID) as PRCS_DT_ROW_ID
From CNSL_UNLY_LOAN F
Where F.REC_TYPE_CD = 'F'
and F.Loan_Type_cd= 'M'
Group by CSL_Rec_Id
),
Consol_src as
(Select CSL_Rec_Id,
CSL_Lon_Pgm_cd
From EDCSL_SRC
),
Var_rpt as
(Select row_id,
borr_snn,
csl_rec_id,
Fund_tran_amt,
Fund_Rbte_amt
from Rpt_Fund_to_Book
where Vrnc_Stat in ('FBB')
)
SELECT F.Prcs_Dt_Row_id
V.borr_ssn,
V.CSL_Rec_id,
V.Fund_Tran_amt,
V.Fund_Rbte_amt,
B.Awrd_Id,
C.CSL_lon_pgm_cd
FROM Book B,
Funding F,
Consol_src C,
Var_rpt V
WHERE
B.CSL_Rec_Id(+) = V.CSL_Rec_Id
B.Borr_ssn(+) = V.borr_ssn
B.CSL_Rec_Id = C.CSL_Rec_Id(+)
F.CSL_Rec_Id(+) = C.CSL_Rec_Id
)
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.