|
View previous topic :: View next topic
|
| Author |
Message |
shanudarling
Warnings : 1
New User
.jpg)
Joined: 20 Dec 2006
Posts: 55
Location: noida
|
|
|
|
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 |
|
 |
CICS Guy
Senior Member
Joined: 18 Jul 2007
Posts: 2146
Location: At my coffee table
|
|
| Back to top |
|
|
shanudarling
Warnings : 1
New User
Joined: 20 Dec 2006
Posts: 55
Location: noida
|
|
|
|
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 |
|
|
CICS Guy
Senior Member
Joined: 18 Jul 2007
Posts: 2146
Location: At my coffee table
|
|
| Back to top |
|
|
Ragav86
New User
Joined: 27 Jan 2010
Posts: 37
Location: chennai
|
|
|
|
| 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 |
|
|
Bharath Bhat
Active User
Joined: 20 Mar 2008
Posts: 283
Location: chennai
|
|
|
|
| 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 |
|
|
daveporcelan
Active Member
Joined: 01 Dec 2006
Posts: 792
Location: Pennsylvania
|
|
|
|
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 |
|
|
dbzTHEdinosauer
Global Moderator
Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne
|
|
|
|
| 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 |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006
Posts: 19244
Location: Inside the Matrix
|
|
|
|
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. . .  |
|
| Back to top |
|
|
|
|
