IBM Mainframe Forum Index
 
Log In
 
IBM Mainframe Forum Index Mainframe: Search IBM Mainframe Forum: FAQ Register
 

Please help me with this DB2 sql query..


IBM Mainframe Forums -> DB2
Post new topic   Reply to topic
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
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
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
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
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
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
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
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
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
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
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
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
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 View Bookmarks
All times are GMT + 6 Hours
Forum Index -> DB2

 


Similar Topics
Topic Forum Replies
No new posts Issue with EXEC CICS QUERY SECURITY c... CICS 6
No new posts DB2, write report based on query outp... DB2 1
No new posts Using WITH UR in SELECT SUM query DB2 1
No new posts SQLCODE = -122 while using the scalar... DB2 4
No new posts DB2: Need helping below DB2 query DB2 13
Search our Forums:

Back to Top