Problem with multiple clien numbers from a view

From: <deepakvas_at_gmail.com>
Date: 5 Sep 2006 12:13:03 -0700
Message-ID: <1157483583.024949.292680@p79g2000cwp.googlegroups.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 Received on Tue Sep 05 2006 - 14:13:03 CDT