Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: problem with view with link

Re: problem with view with link

From: Brian Peasland <dba_at_remove_spam.peasland.com>
Date: Mon, 20 Oct 2003 19:22:24 GMT
Message-ID: <3F9435F0.4505353D@remove_spam.peasland.com>


You cannot return data from just one of the two SELECT statements in a SQL query should the other one fail. The system will treat the whole thing as one complete SQL statement. Should any part of it fail, the whole thing will fail. That is because the integrity of the data returned is invalid. Think about it this way.....look at the following view:

CREATE VIEW account_balances AS
SELECT account_number,balance FROM checking_account UNION
SELECT account_number,balance FROM savings_account

Now a person wants the total of all balances. So they issue the following query:

SELECT SUM(balance) AS total_balances FROM account_balances;

If the SAVINGS_ACCOUNT table was not accessible for some reason, then it would give totally different results than if it were available!!!!! Does one want to make decisions based on this inaccurate information? Probably not in this case. So what happens if the query were allowed to proceed? Would there be any indicator that the query worked, but it missed half of the UNION?

In short, the query fails, with an error, because it is supposed to. You asked for data from more than one source and some source was not available. Therefore, the entire query fails.

HTH,
Brian

cf wrote:
>
> Hi
> First execuse me for my poor english.
>
> I have a view
> Create view vw_sample as
> select * from sample
> union all
> select * from sample_at_link1
> /
>
> I would like just return select * from sample where the link faild.
> because i have an ora-02068 or ora-02051 or ora-02063 when the link is
> faild. (netwook down)
>
> Thanks in advances.
> Christophe.

-- 
===================================================================

Brian Peasland
dba_at_remove_spam.peasland.com

Remove the "remove_spam." from the email address to email me.


"I can give it to you cheap, quick, and good. Now pick two out of
 the three"
Received on Mon Oct 20 2003 - 14:22:24 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US