Home » SQL & PL/SQL » SQL & PL/SQL » Problem with view
Problem with view [message #191323] Tue, 05 September 2006 13:56 Go to next message
deepakv
Messages: 3
Registered: September 2006
Junior Member
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
Re: Problem with view [message #191335 is a reply to message #191323] Tue, 05 September 2006 15:11 Go to previous messageGo to next message
Littlefoot
Messages: 20888
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Basically, this is what you are saying:

... WHERE client_num = 250

should return both client and legacy data.

How would you (or your confused user) write a query which would return only client data? Or only legacy data?

I'd say that that's what the WHERE clause is for: write a condition which would enable you to return exactly those records that satisfy given condition. To me, it seems that trying to "fool" a user with such a logic will result in more mess than using the appropriate WHERE clause.
Re: Problem with view [message #191370 is a reply to message #191335] Wed, 06 September 2006 01:01 Go to previous message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
You could CROSS JOIN the legacy table with a table that contained every client number. This would - of course - return multiple copies of the legacy data if no client number were included in the WHERE clause.

Ross Leishman
Previous Topic: Extract only Alphabets.
Next Topic: demobld
Goto Forum:
  


Current Time: Fri Dec 02 16:52:19 CST 2016

Total time taken to generate the page: 0.22965 seconds