What will be SQLCODE for the Select query.



What will be SQLCODE for the Select query.

Postby Chaitnya » Sat Feb 20, 2010 6:16 am

Hi,

What will be the SQLCODE for below select query

SELECT COUNT(*) FROM EMP WHERE EMPNO=9999

and suppose row with employee number 9999 is not present in table.

Thanks,
Chaitnya
Chaitnya
 
Posts: 18
Joined: Wed Mar 26, 2008 1:04 pm
Has thanked: 0 time
Been thanked: 0 time

Re: What will be SQLCODE for the Select query.

 

Re: What will be SQLCODE for the Select query.

Postby dick scherrer » Sat Feb 20, 2010 6:55 am

Hello,

Suggest you run the query on your system and post a reply if there is any question about what happens.
Hope this helps,
d.sch.
User avatar
dick scherrer
Global moderator
 
Posts: 6304
Joined: Sat Jun 09, 2007 8:58 am
Has thanked: 3 times
Been thanked: 91 times

Re: What will be SQLCODE for the Select query.

Postby Chaitnya » Sat Feb 20, 2010 3:07 pm

Hi Dick,

Thanks for suggetion, however I don't have system to run it. It was asked me in interview and I have given the answer as SQLCODE will be 000 but looked like he was not satisfied with answer. Please let me know was I correct?

Thanks,
Chaitnya
Chaitnya
 
Posts: 18
Joined: Wed Mar 26, 2008 1:04 pm
Has thanked: 0 time
Been thanked: 0 time

Re: What will be SQLCODE for the Select query.

Postby dick scherrer » Sat Feb 20, 2010 9:43 pm

Hello,

I'm not connected just now, but i suspect it will cause a -100. Which is the way to test for "not found".
Hope this helps,
d.sch.
User avatar
dick scherrer
Global moderator
 
Posts: 6304
Joined: Sat Jun 09, 2007 8:58 am
Has thanked: 3 times
Been thanked: 91 times

Re: What will be SQLCODE for the Select query.

Postby seansun » Fri Mar 19, 2010 8:40 am

Hi,
I have test it in real z/os evironment.it return sqlcode 100.
SELECT COUNT(*) FROM TESTDB.TABLE001
WHERE TEST_KEY = 100001;
---------+---------+---------+---------+---------+---------+--

---------+---------+---------+---------+---------+---------+--
2
DSNE610I NUMBER OF ROWS DISPLAYED IS 1
DSNE616I STATEMENT EXECUTION WAS SUCCESSFUL, SQLCODE IS 100
seansun
 
Posts: 1
Joined: Fri Mar 19, 2010 8:22 am
Has thanked: 0 time
Been thanked: 0 time

Re: What will be SQLCODE for the Select query.

Postby dick scherrer » Fri Mar 19, 2010 9:06 am

Thank you for letting us know the result of your test :)

d
User avatar
dick scherrer
Global moderator
 
Posts: 6304
Joined: Sat Jun 09, 2007 8:58 am
Has thanked: 3 times
Been thanked: 91 times

Re: What will be SQLCODE for the Select query.

Postby Chaitnya » Mon Mar 22, 2010 2:01 pm

Hi All,

First of all I apologize for late reply on the topic. I got system and I tried the query which resulted as below

1) If you run the query with count(*) in SPUFI, system will always show SQLCODE +100 and that is posted by seansun. If you try any other query in SPUFI, the SQLCODE shown is always +100 at the end.

2) I tried the query in COBOL with embedded SQL and displayed the SQLCODE which is found to be 000 for SELECT COUNT(*) FROM EMP WHERE EMPNO=9999; though the row with emp no. 9999 is not present in EMP table. And this is because of the scalar function, if you remove count function then the SQLCODE is +100 which is expected one.

Thanks for all your reply to the post!

Chaitnya :D
Chaitnya
 
Posts: 18
Joined: Wed Mar 26, 2008 1:04 pm
Has thanked: 0 time
Been thanked: 0 time

Re: What will be SQLCODE for the Select query.

Postby dick scherrer » Tue Mar 23, 2010 12:44 am

You're welcome - thank you for the update :)

d
User avatar
dick scherrer
Global moderator
 
Posts: 6304
Joined: Sat Jun 09, 2007 8:58 am
Has thanked: 3 times
Been thanked: 91 times


Return to Interview Questions

 


  • Related topics
    Replies
    Views
    Last post