Forum Stats

  • 3,837,912 Users
  • 2,262,306 Discussions
  • 7,900,431 Comments

Discussions

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

user613563
user613563 Member Posts: 201 Blue Ribbon
edited Sep 30, 2015 7:17AM in SQLとPL/SQL

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

Answers

  • Papageno
    Papageno Member Posts: 53 Bronze Badge
    edited Sep 30, 2015 7:17AM

    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')

This discussion has been closed.