UNABLE TO RETRIEVE DATA FROM SQL QUERY

India_vignesh

HI I WANT TO BUILT A REPORT IN ORACLE DISCOVERER USING BELOW QUERY BUT I'M FACING ERROR  PLEASE HELP

SQL QUERY:

SELECT OST_COMP_CODE,OST_MAIN_ACNT_CODE,OST_SUB_ACNT_CODE,ost_divn_code,ost_dept_code,OST_TRAN_CODE,OST_DOC_NO,OST_DOC_DT,

NVL(NVL(SUM(DECODE(OST_DRCR_FLAG,'D',NVL(OST_LC_ORG_AMT,0) - (F_GET_BASE_LC_AMT(OST_KEY_NO,'P',to_date('&da','mm/dd/yyyy')) +

F_GET_BASE_CROSS_LC_AMT(OST_KEY_NO,'C',to_date('&da','mm/dd/yyyy')) + F_GET_BASE_LC_AMT(OST_KEY_NO,'F',to_date('&da','mm/dd/yyyy'))))),0) -

NVL(SUM(DECODE(OST_DRCR_FLAG,'C',NVL(OST_LC_ORG_AMT,0) - (F_GET_BASE_LC_AMT(OST_KEY_NO,'P',to_date('&da','mm/dd/yyyy')) +

F_GET_BASE_CROSS_LC_AMT(OST_KEY_NO,'C',to_date('&da','mm/dd/yyyy')) + F_GET_BASE_LC_AMT(OST_KEY_NO,'F',to_date('&da','mm/dd/yyyy'))))),0),0) BALANCE,

to_date('&da','mm/dd/yyyy')-trunc(OST_DOC_DT) "DAYS"

FROM FT_OS

WHERE OST_MAIN_aCNT_CODE='A2501'

and trunc(ost_doc_dt)<= to_date('&da','mm/dd/yyyy')

AND NVL(OST_LC_ORG_AMT,0) - (F_GET_BASE_LC_AMT(OST_KEY_NO,'P',to_date('&da','mm/dd/yyyy')) +

F_GET_BASE_CROSS_LC_AMT(OST_KEY_NO,'C',to_date('&da','mm/dd/yyyy')) +F_GET_BASE_CROSS_LC_AMT(OST_KEY_NO,'D',to_date('&da','mm/dd/yyyy'))+ F_GET_BASE_LC_AMT(OST_KEY_NO,'F',to_date('&da','mm/dd/yyyy')))>0

AND EXISTS (SELECT 'X'

FROM FM_CUSTOMER X

WHERE X.CUST_CODE = OST_SUB_ACNT_CODE

UNION

SELECT 'X'

FROM FM_SUPPLIER Y

WHERE Y.SUPP_CODE = OST_SUB_ACNT_CODE)

GROUP BY ost_comp_code,OST_MAIN_aCNT_CODE,OST_SUB_aCNT_CODE,to_date('&da','mm/dd/yyyy')-trunc(OST_DOC_DT) ,ost_divn_code,ost_dept_code,OST_TRAN_CODE,OST_DOC_NO,OST_DOC_DT

REGARDS

VIGNESH

  

Srini Chavali-Oracle

UNABLE TO RETRIEVE DATA