MVSFORUMS.com

seekaysk · Beginner Joined: 31 Aug 2007 Posts: 49 Topics: 15

Hi,

I have to write an inner subquery. Its supposed to be small and requirements too are minimal. However, I tried a lot framing the query as well as find it here but in vain. Please help me.

table tab1 has one column Col1 of one character byte. This table may be empty at times or have duplicates values.

e.g. it may have four rows like
Col1:
B
B
A
C

or at times may be empty also.

Requirement is: to get the distinct values and if the table is empty to get the default value 'A'. Now this has to be done within the query itself. As I said in my subject that this is going to be my subquery and I have to join these output values with some other table, so I have to do it within query itself.

thanks for your time.
_________________
Thanks.

kolusu · Posted: Tue Jul 21, 2009 2:28 pm Post subject:

seekaysk,

Untested SQL but should work based of this topic

http://www.mvsforums.com/helpboards/viewtopic.php?t=7854&highlight=count

seekaysk · Beginner Joined: 31 Aug 2007 Posts: 49 Topics: 15

This is great. thanks a lot kolusu. I am learning IBMREQD parameter for the first time here. thank you so much for the prompt reply.

P.S. I was taking time to test with various scenarios and hence a delayed reply.
_________________
Thanks.

kolusu · Posted: Wed Jul 22, 2009 10:43 am Post subject:

seekaysk,

So did it work? Here is the explanation of Sysibm.sysdummy1 table

http://publibz.boulder.ibm.com/cgi-bin/bookmgr_OS390/BOOKS/DSNSQJ12/F.27?

Kolusu

seekaysk · Beginner Joined: 31 Aug 2007 Posts: 49 Topics: 15

great. thanks again for the extended help. This is a very handy doc.
_________________
Thanks.