Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups 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: 662
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: 6967
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 Fileaid Update Replace with null string descann Compuware & Other Tools 4 Mon May 15, 2017 3:00 pm
No new posts Replace values in the input data Vikas Maharnawar DFSORT/ICETOOL 10 Thu May 11, 2017 2:18 pm
No new posts Compare two files and subtract values ameetmund DFSORT/ICETOOL 7 Fri Mar 31, 2017 3:35 pm
No new posts Check Binary Values for symbols gandikk CA Products 3 Tue Mar 21, 2017 5:11 am
No new posts To display values of multiple rows in... vinu78 DB2 2 Thu Mar 09, 2017 1:20 pm


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us