union all.. where clause query..what wrong..?

user613563

SELECT * FROM (

SELECT

O.DOC_RNW_NO,

O.DOC_RNW_COMP_CODE,

O.DOC_RNW_GDEPT_CODE,

O.DOC_RNW_ITEM_CODE,

O.DOC_RNW_START_DATE,

O.DOC_RNW_END_DATE,

O.DOC_RNW_AMOUNT,

O.DOC_RNW_DOC_CODE

FROM DOC_RENEW_TXN O

UNION ALL

SELECT

D.DC_LEASE_NO,

'2'  DC_LEASE_COMP_CODE,

'14' DOC_RNW_GDEPT_CODE,

D.DC_LEASE_PARTY_CODE,

D.DC_LEASE_FROM_PERIOD,

D.DC_LEASE_UPTO_PERIOD,

D.DC_LEASE_AMOUNT,

'4' DOC_RNW_DOC_CODE

FROM DC_LEASE_TXN D

UNION ALL

SELECT

R.DC_LEASE_NO,

'2'  DC_LEASE_COMP_CODE,

'14' DOC_RNW_GDEPT_CODE,

R.DC_LEASE_PARTY_CODE,

R.DC_LEASE_FROM_PERIOD,

R.DC_LEASE_UPTO_PERIOD,

R.DC_LEASE_AMOUNT,

'4' DOC_RNW_DOC_CODE

FROM REST.DC_LEASE_TXN R

) s

WHERE s.DOC_RNW_END_DATE BETWEEN ('01/01/2015','DD/MM/YYYY') AND TO_DATE('31/12/2015','DD/MM/YYYY')

i am getting ora-903 missing right parenthesis

when i run the 3 inside unions together, it works fine.problem only when i include all 3 union query in a subquery.

oracle 10g xe

thanks

Papageno

WHERE s.DOC_RNW_END_DATE BETWEEN ('01/01/2015','DD/MM/YYYY') AND TO_DATE('31/12/2015','DD/MM/YYYY')

↓

WHERE s.DOC_RNW_END_DATE BETWEEN TO_DATE('01/01/2015','DD/MM/YYYY') AND TO_DATE('31/12/2015','DD/MM/YYYY')