Re: Problem with multiple clien numbers from a view

deepakvas_at_gmail.com wrote:
> 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

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