Data Science Made Easy : How to make Dynamic Query in Oracle

SELECT

DISTINCT

gcc.&segments code,

gcc.&detail_segments nat_account,

(case when &detail_segments=segment1 then w_dev_pkg.GET_COMPANY_DESC(gcc.code_combination_id)

when &detail_segments=segment2 then w_dev_pkg.GET_BUSINESS_DESC(gcc.code_combination_id)

when &detail_segments=segment3 then w_dev_pkg.GET_COST_CENTER_DESC(gcc.code_combination_id)

when &detail_segments=segment4 then W_DEV_PKG.GET_NATURAL_DESC(gCC.CODE_COMBINATION_ID)

when &detail_segments=segment5 then W_DEV_PKG.GET_Product_DESC(gCC.CODE_COMBINATION_ID)

when &detail_segments=segment6 then W_DEV_PKG.GET_CUSTOMER_DESC(gCC.CODE_COMBINATION_ID)

when &detail_segments=segment7 then w_dev_pkg.GET_Spare1_DESC(gcc.code_combination_id)

when &detail_segments=segment8 then w_dev_pkg.GET_Spare2_DESC(gcc.code_combination_id)

end ) nat_acct_des,

(case when &segments=segment1 then w_dev_pkg.GET_COMPANY_DESC(gcc.code_combination_id)

when &segments=segment2 then w_dev_pkg.GET_BUSINESS_DESC(gcc.code_combination_id)

when &segments=segment3 then w_dev_pkg.GET_COST_CENTER_DESC(gcc.code_combination_id)

when &segments=segment4 then W_DEV_PKG.GET_NATURAL_DESC(gCC.CODE_COMBINATION_ID)

when &segments=segment5 then W_DEV_PKG.GET_Product_DESC(gCC.CODE_COMBINATION_ID)

when &segments=segment6 then W_DEV_PKG.GET_CUSTOMER_DESC(gCC.CODE_COMBINATION_ID)

when &segments=segment7 then w_dev_pkg.GET_Spare1_DESC(gcc.code_combination_id)

when &segments=segment8 then w_dev_pkg.GET_Spare2_DESC(gcc.code_combination_id)

end ) fLEXdESCRIPTION,

-- round(nvl(gb.begin_balance_dr- gb.begin_balance_cr, 0) ,0) openingBal,

sum(jel.entered_dr) enter_dr,

sum(jel.entered_cr) enter_cr,

sum(nvl(jel.accounted_dr,0)) acdr,

sum(nvl(jel.accounted_cr,0)) accr

-- (gb.begin_balance_dr- b.begin_balance_cr)+nvl(jel.entered_dr,0)-nvl(jel.entered_cr,0) activity,

FROM

gl_je_batches jeb,

gl_je_headers jeh,

gl_je_lines jel,

gl_code_combinations gcc

WHERE

jeb.je_batch_id=jeh.je_batch_id

AND jeh.je_header_id=jel.je_header_id

AND jel.code_combination_id=gcc.code_combination_id

AND jeb.status = (case when :bstatus = 'P' then 'P' when :bstatus = 'U' then 'U' else jeb.status end)

AND jeh.default_effective_date between NVL(:from_date,jeh.default_effective_date) and NVL(:To_date, jeh.default_effective_date)

AND jeh.SET_OF_BOOKS_ID=0000

AND jeh.actual_flag='A'

AND gcc.&segments between :code_from and :code_to

group by

gcc.&segments,