How Can Used Union Statment According Condition In Report

[j1968s]

How can used UNION STATMENT ACCORDING CONDITION in REPORT?

I mean that I have two Table , If the date > year(2000) will use one table but if the date < year (2000) will use tow table
IN FORM THAT's NOT problme BUT in report how it's will do it ?

CASE WHEN DATE  > '01-JAN-2000' then
SELECT ALL FROM INVOICE WHERE INVOICE_DATE >= DATE
ELSE
SELECT ALL FROM INVOICE WHERE INVOICE_DATE >= DATE
       UNION ALL
SELECT ALL FROM ARCHIVE_INVOICE WHERE INVOICE_DATE >= DATE
END

ARE IT'S AVAILABLE ??
THANKS FOR ALL

[MMA]

ممكن تحدد مشكلتك بالعربي !!!

بعد إذنك

شكراً

[Om MAriam]

your report SQL statment will be as follows:

SELECT COL1,COL2,..,COLN

FROM INVOICE

WHERE TO_NUMBER(TO_CHAR(INVOICE_DATE,'YYYY')) > 2000

AND INVOICE_DATE >= _DATE

UNION

SELECT COL1,COL2,..,COLN

FROM ARCHIVE_INVOICE

WHERE TO_NUMBER(TO_CHAR(INVOICE_DATE,'YYYY')) < 2000

AND INVOICE_DATE >= _DATE



but note that:

1- _DATE is the parameter that you'll pass its value

2- the number of columns and datatypes must be identical

i.e. if you selected col1,col2and col3 from the first query then in the 2nd query you must select 3 columns with the same datatype of col1,col2 and col3 respectivly

3- Note on your statement as lon as that you retrieve all records for the year higer and lower than year2000 from the table 'INVOICE' so there's no need to added again in the union and the query I put is to retrieve all records regardless the year from 'INVOICE' table and only for year who's lower than 2000 from 'ARCHIVE_INVOICE' table

if what I'm syaing is wrong plz inform me