OraFAQ Forum: SQL & PL/SQL » Problem with view

Home » SQL & PL/SQL » SQL & PL/SQL » Problem with view

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Problem with view [message #191323]

Tue, 05 September 2006 13:56

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

Report message to a moderator

Re: Problem with view [message #191335 is a reply to message #191323]

Tue, 05 September 2006 15:11

Littlefoot
Messages: 21823
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.

Report message to a moderator

Re: Problem with view [message #191370 is a reply to message #191335]

Wed, 06 September 2006 01:01

rleishman
Messages: 3728
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

Report message to a moderator

Previous Topic:	Extract only Alphabets.
Next Topic:	demobld

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Wed Dec 04 19:01:57 CST 2024