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
 
Identifying NULL values

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
View previous topic :: :: View next topic  
Author Message
shanudarling
Warnings : 1

New User


Joined: 20 Dec 2006
Posts: 55
Location: noida

PostPosted: Tue Jun 15, 2010 8:45 am    Post subject: Identifying NULL values
Reply with quote

Hi,
I am new to DB2.
My requirement is as follows -
I have a cursor which will select some rows from a table - let us say a row has 3 fields - A1, A2 & A3.
If A1 is NULL then i need to populate it with -999.
If A2 is NULL then i need to populate it with -999999.
If A3 is NULL then i need to populate it with -99999.
If the above fields are not NULL then whatever is fetched from the database should be used.

Can anyone please explain how this can be done.

Thanks
Back to top
View user's profile Send private message

CICS Guy

Senior Member


Joined: 18 Jul 2007
Posts: 2150
Location: At my coffee table

PostPosted: Tue Jun 15, 2010 8:50 am    Post subject:
Reply with quote

Look at the null indicator, if it is set, the column contains nulls.
Back to top
View user's profile Send private message
shanudarling
Warnings : 1

New User


Joined: 20 Dec 2006
Posts: 55
Location: noida

PostPosted: Tue Jun 15, 2010 9:04 am    Post subject:
Reply with quote

From the above link, i have got the following snippet -

EXEC SQL
SELECT PHONENO
INTO :CBLPHONE:INDNULL
FROM DSN8910.EMP
WHERE EMPNO = :EMPID
END-EXEC.

In this snippet - :INDNULL will contain a -ve value if the query returns a null. What would be the declaration of this (:INDNULL) variable and do i need to declare this in my working storage section?
Back to top
View user's profile Send private message
CICS Guy

Senior Member


Joined: 18 Jul 2007
Posts: 2150
Location: At my coffee table

PostPosted: Tue Jun 15, 2010 9:19 am    Post subject:
Reply with quote

PIC S9(4) COMP?
Back to top
View user's profile Send private message
Ragav86

New User


Joined: 27 Jan 2010
Posts: 37
Location: chennai

PostPosted: Tue Jun 15, 2010 12:31 pm    Post subject:
Reply with quote

Quote:

If A1 is NULL then i need to populate it with -999.
If A2 is NULL then i need to populate it with -999999.
If A3 is NULL then i need to populate it with -99999.

what is the problem in using coalesce function?

Code:

SELECT COALESCE(C1,-999),
            COALESCE(C2,-99999),
            COALESCE(C3,-999)
FROM TEMP
Back to top
View user's profile Send private message
Bharath Bhat

Active User


Joined: 20 Mar 2008
Posts: 283
Location: chennai

PostPosted: Tue Jun 15, 2010 2:10 pm    Post subject:
Reply with quote

We can use COALESCE/VALUE/CASE statements to achieve this. But, coding couple of lines in cobol after a simple sql would definitely save some CPU time.
Back to top
View user's profile Send private message
daveporcelan

Active Member


Joined: 01 Dec 2006
Posts: 683
Location: Pennsylvania

PostPosted: Tue Jun 15, 2010 9:56 pm    Post subject:
Reply with quote

Bharath Bhat said:

Quote:
We can use COALESCE/VALUE/CASE statements to achieve this. But, coding couple of lines in cobol after a simple sql would definitely save some CPU time.


I am not sure where you got that idea. I would like to see test results to that effect.

In fact, I think the opposite is true. If you speak with IBM, they will tell you to do as much as possible with DB2.
Back to top
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


Joined: 20 Oct 2006
Posts: 6968
Location: porcelain throne

PostPosted: Tue Jun 15, 2010 11:46 pm    Post subject:
Reply with quote

daveporcelan wrote:
they will tell you to do as much as possible with DB2


that's because IBM is in the business of selling iron,
to run the bloated applications.

Quote:
I am not sure where you got that idea. I would like to see test results to that effect.


db2 is going to populate the null indicators anyway.

now, trying to decide if db2 can populate the columns in the result set based on the value function faster than cobol code is not the issue.

the resource required to populate the columns
is either db2 or my cobol program
- either way, my task requires the resource.
If I use the time in my cobol program, my task is releasing db2 for other tasks.
the more time my task spends in db2, the less time for other tasks on the system to have db2 time.
Back to top
View user's profile Send private message
dick scherrer

Site Director


Joined: 23 Nov 2006
Posts: 19270
Location: Inside the Matrix

PostPosted: Tue Jun 15, 2010 11:55 pm    Post subject:
Reply with quote

Hello,

Quote:
Quote:
daveporcelan wrote:
they will tell you to do as much as possible with DB2


that's because IBM is in the business of selling iron,
to run the bloated applications.
And the deeper applications get entwined with db2 the less likely some other database system can ever be considered. . . icon_wink.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 Working with hex values in Filemanage... Emile Straker IBM Tools 0 Tue Sep 12, 2017 1:42 am
No new posts DB2 NULL Values display V S Amarendra Reddy DB2 3 Thu Sep 07, 2017 6:59 pm
No new posts Moving values to a variable of copybo... Vignesh Sid COBOL Programming 6 Wed Sep 06, 2017 1:04 pm
No new posts BI,TO=ZD Changing Values Tyler Dunbar DFSORT/ICETOOL 4 Tue Jul 25, 2017 12:12 am
No new posts Sorting of hex values Saurabh_mi DFSORT/ICETOOL 11 Thu May 25, 2017 3:49 pm

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