IBM Mainframe Forum

[Chaitnya]

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

[dick scherrer]

Hello,

Suggest you run the query on your system and post a reply if there is any question about what happens.

[Chaitnya]

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

[dick scherrer]

Hello,

I'm not connected just now, but i suspect it will cause a -100. Which is the way to test for "not found".

[seansun]

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

[dick scherrer]

Thank you for letting us know the result of your test

d

[Chaitnya]

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

[dick scherrer]

You're welcome - thank you for the update

d