Problem with multiple clien numbers from a view

[deep...@gmail.com]

Hi Gurus,
I have a problem with a view

Created a view with a UNION ALL stmt
=====================================
Create view vw_benifits
as
SELECT
Client_num, -- can have multiple values like 200,201,250
PERNR,
OBJPS,
ENDDA,
BEGDA,
AEDTM,
UNAME,
COB_MNTH_AMT
FROM
STG_SAP_PA9211_TB
UNION ALL
SELECT
null, -- no client number for legacy data
PERNR,
OBJPS,
ENDDA,
BEGDA,
AEDTM,
UNAME,
COB_MNTH_AMT
from
LEG_STG_SAP_PA9211_TB;
==============================

The second table contains legacy data (LEG_STG_SAP_PA9211_TB). The
first table now contains multiple client data (ie the client_num can be
201,202,250 like that.

Now if the users qery the view they will only get that clients data.

eg selet * from vw_benifits where client_num=250 results only client
250 data. But I want to add the legacy data also with that.

I don't want to propose
selet * from vw_benifits where client_num in (250,NULL) since the users
will be confused.

Is there any other way to do this . my requirement is like
If they query

select * from vw_benifits where client_num=250, the data should include
all the records satisfying client=250 + the records from the legacy
data. The view need to be created like that.
Appreciate your help

Deepak

[DA Morgan]

If the legacy data does not contain a client number then how do you plan
to determine which legacy data corresponds with which client data?

And unless I misunderstand your example, quite likely, why were client
numbers stripped from the legacy data?
--
Daniel Morgan
University of Washington
Puget Sound Oracle Users Group