Portal | Manuals | References | Downloads | Info | Programs | JCLs | Mainframe wiki | Quick Ref
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Profile Log in to check your private messages Log in
 
Please help me with this DB2 sql query..

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
View previous topic :: :: View next topic  
Author Message
mijanurit
Currently Banned

New User


Joined: 26 Aug 2005
Posts: 33
Location: bangalore

PostPosted: Tue Sep 23, 2008 8:01 pm    Post subject: Please help me with this DB2 sql query..
Reply with quote

With DBADM authority on the database and given the statements:
CREATE TABLE t1 (c1 CHAR(1))
INSERT INTO t1 VALUES ('b')
CREATE VIEW v1 AS SELECT c1 FROM t1 WHERE c1='a' WITH CHECK OPTION
INSERT INTO v1 VALUES ('a')
INSERT INTO v1 VALUES ('b')
How many rows would be returned from the statement, SELECT c1 FROM t1?

A.0
B.1
C.2
D.3

please tell me , what will be the correct answer..?

I guess B. 1 would be the answer.
Back to top
View user's profile Send private message

ashimer

Active Member


Joined: 13 Feb 2004
Posts: 551
Location: Bangalore

PostPosted: Tue Sep 23, 2008 9:19 pm    Post subject:
Reply with quote

As your view has a CHECK your 2nd insert would fail because c1<>'a', but you first insert into V1 will be successful..so the total number of rows returned would be 2 .....
Back to top
View user's profile Send private message
rpuhlman

New User


Joined: 11 Jun 2007
Posts: 80
Location: Columbus, Ohio

PostPosted: Tue Sep 23, 2008 9:37 pm    Post subject:
Reply with quote

Mijanurit,

The answer is A. It will produce an empty result set.

Also, the last INSERT
Quote:
INSERT INTO v1 VALUES ('b')

will fail because it does not satisfy the view definition.

Rick
Back to top
View user's profile Send private message
ashimer

Active Member


Joined: 13 Feb 2004
Posts: 551
Location: Bangalore

PostPosted: Tue Sep 23, 2008 9:44 pm    Post subject:
Reply with quote

Quote:

The answer is A. It will produce an empty result set


Even before creating the view there is a row being inserted in the table ... so what about that ?


Quote:

INSERT INTO v1 VALUES ('b')


will fail because it does not satisfy the view definition.



second INSERT INTO v1 VALUES ('a')
definitely satisfies the view definition wht about that ?
Back to top
View user's profile Send private message
rpuhlman

New User


Joined: 11 Jun 2007
Posts: 80
Location: Columbus, Ohio

PostPosted: Tue Sep 23, 2008 9:46 pm    Post subject:
Reply with quote

Oops ... sorry about that ... One row will be returned ... Had the wrong select statement.

Rick
Back to top
View user's profile Send private message
mijanurit
Currently Banned

New User


Joined: 26 Aug 2005
Posts: 33
Location: bangalore

PostPosted: Tue Sep 23, 2008 9:53 pm    Post subject: Reply to: Please help me with this DB2 sql query..
Reply with quote

will that view be created on the base table if there is no row in the table satisfing view defination condition ? as for this example...

CREATE VIEW v1 AS SELECT c1 FROM t1 WHERE c1='a' WITH CHECK OPTION

thanks
Back to top
View user's profile Send private message
ashimer

Active Member


Joined: 13 Feb 2004
Posts: 551
Location: Bangalore

PostPosted: Tue Sep 23, 2008 9:56 pm    Post subject:
Reply with quote

For creating a view only the table needs to be existing ..its not mandatory that the table has satisfying data or it is empty .... if the table does not have data satisfying view definition the view will return empty result set ...
Back to top
View user's profile Send private message
rpuhlman

New User


Joined: 11 Jun 2007
Posts: 80
Location: Columbus, Ohio

PostPosted: Tue Sep 23, 2008 9:57 pm    Post subject:
Reply with quote

Ahimer,

That's twice I executed the wrong select. I was selecting against the VIEW ... my bad. The select against T1 would return 2 rows. However, the third INSERT failed with a -166 which was just an FYI and nothing to do with the results ... what about THAT!

Rick
Back to top
View user's profile Send private message
rpuhlman

New User


Joined: 11 Jun 2007
Posts: 80
Location: Columbus, Ohio

PostPosted: Tue Sep 23, 2008 9:59 pm    Post subject:
Reply with quote

Sorry Ashimer ... spelled your name wrong.

Rick
Back to top
View user's profile Send private message
rpuhlman

New User


Joined: 11 Jun 2007
Posts: 80
Location: Columbus, Ohio

PostPosted: Tue Sep 23, 2008 10:03 pm    Post subject:
Reply with quote

Ok ... last post from me today ... too busy and in too much of a hurry ... I received a -161 on the third INSERT ... look it up.

Rick
Back to top
View user's profile Send private message
ashimer

Active Member


Joined: 13 Feb 2004
Posts: 551
Location: Bangalore

PostPosted: Tue Sep 23, 2008 10:10 pm    Post subject:
Reply with quote

icon_biggrin.gif rick that is bound to happen because the third insert violates the view definition ..... and this will fail because of the CHECK option ...
Back to top
View user's profile Send private message
mijanurit
Currently Banned

New User


Joined: 26 Aug 2005
Posts: 33
Location: bangalore

PostPosted: Wed Sep 24, 2008 3:21 pm    Post subject: Reply to: Please help me with this DB2 sql query..
Reply with quote

Thanks all.. thank you so much..icon_smile.gif
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2 All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts HEX value search in a DB2 query maxsubrat DB2 2 Wed Oct 04, 2017 3:04 pm
No new posts Create procedure issues -628 when add... chandraBE DB2 1 Mon Sep 18, 2017 12:16 pm
No new posts Can we limit length in concatenation ... balaji81_k DB2 7 Tue Aug 22, 2017 2:50 am
No new posts Need DB2 query to fetch previous row ! Chandan1993 DB2 10 Sat Jun 03, 2017 10:43 am
No new posts JCL to Set Return code based on DB2 S... vinu78 DB2 17 Mon Mar 13, 2017 9:47 pm

Facebook
Back to Top
 
Job Vacancies | Forum Rules | Bookmarks | Subscriptions | FAQ | Polls | Contact Us