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
 

 

Update the null values in a column to a constant value

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
View previous topic :: :: View next topic  
Author Message
Bhagyalakshmi C

New User


Joined: 24 Jul 2007
Posts: 38
Location: Bangalore

PostPosted: Wed Aug 22, 2007 4:47 pm    Post subject: Update the null values in a column to a constant value
Reply with quote

I want to update the null values in a column to a constant value.
ex:
empname empno addr
aaa 111 india
bbb 222 -
ccc 333 [blank]
ddd 444 -
eee 555 -

this is the table.
Here "addr" field can contain null values and it is indicated by '-'(in this table)
I should update this table as below

i need the output as
empname empno addr
aaa 111 india
bbb 222 NULL
ccc 333 [blank]
ddd 444 NULL
eee 555 NULL

I am using PLI.
Help me please!!!
Thank you!!!!!!!
Back to top
View user's profile Send private message

Craq Giegerich

Senior Member


Joined: 19 May 2007
Posts: 1512
Location: Virginia, USA

PostPosted: Wed Aug 22, 2007 4:51 pm    Post subject:
Reply with quote

Why would you want to put in the word 'NULL'? It is just a simple update
UPDATE TABLE SET ADDR = 'NULL' WHERE ADDR IS NULL
Back to top
View user's profile Send private message
Bhagyalakshmi C

New User


Joined: 24 Jul 2007
Posts: 38
Location: Bangalore

PostPosted: Wed Aug 22, 2007 5:09 pm    Post subject:
Reply with quote

Thank you.. thanks a lot!!!!!!

But....

let me add one more column to that table like phoneno.
This can also contain null values i need to update the both the columns "addr" and "phoneno" to 'noaddr' and 'nonum' respectively.

for eg before update
empname empno addr phoneno
aaa 111 india -
bbb 222 - [blank]
ccc 333 [blank] -
ddd 444 - -
eee 555 - [blank]

After updating it should be..

empname empno addr phoneno
aaa 111 india nonum
bbb 222 noaddr [blank]
ccc 333 [blank] nonum
ddd 444 noaddr nonum
eee 555 noaddr [blank]

Help me in this...
Back to top
View user's profile Send private message
Bhagyalakshmi C

New User


Joined: 24 Jul 2007
Posts: 38
Location: Bangalore

PostPosted: Wed Aug 22, 2007 5:18 pm    Post subject:
Reply with quote

I need to do this using a PLI program!!!!

EXEC SQL
DECLARE UPCUR CURSOR FOR
SELECT ADDR,PHONENO FROM EMPLOYEE
WHERE ADDR IS NULL OR
PHONENO IS NULL
FOR UPDATE OF ADDR,PHONENO;
EXEC SQL OPEN UPCUR;
DO WHILE(SQLCA.SQLCODE!=100);
EXEC SQL
UPDATE EMPLOYEE
SET ADDR='NOADDR' WHERE ADDR IS NULL;
EXEC SQL
UPDATE EMPLOYEE
SET PHONENO='NONUM' WHERE PHONENO IS NULL;
END;
EXEC SQL CLOSE UPCUR;

And this is my program. Please check and let me what changes i have to make. i am not getting the required out put..

Help me!!!

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

Senior Member


Joined: 19 May 2007
Posts: 1512
Location: Virginia, USA

PostPosted: Wed Aug 22, 2007 6:14 pm    Post subject:
Reply with quote

Bhagyalakshmi C wrote:
I need to do this using a PLI program!!!!

EXEC SQL
DECLARE UPCUR CURSOR FOR
SELECT ADDR,PHONENO FROM EMPLOYEE
WHERE ADDR IS NULL OR
PHONENO IS NULL
FOR UPDATE OF ADDR,PHONENO;
EXEC SQL OPEN UPCUR;
DO WHILE(SQLCA.SQLCODE!=100);
EXEC SQL
UPDATE EMPLOYEE
SET ADDR='NOADDR' WHERE ADDR IS NULL;
EXEC SQL
UPDATE EMPLOYEE
SET PHONENO='NONUM' WHERE PHONENO IS NULL;
END;
EXEC SQL CLOSE UPCUR;

And this is my program. Please check and let me what changes i have to make. i am not getting the required out put..

Help me!!!

Thanks!!!!!!!!!


I am not a PLI programmer but your CURSOR is not needed. The 2 update statements are all that are needed.
Back to top
View user's profile Send private message
Bhagyalakshmi C

New User


Joined: 24 Jul 2007
Posts: 38
Location: Bangalore

PostPosted: Wed Aug 22, 2007 7:35 pm    Post subject:
Reply with quote

Thank you!!

But how is it possible to update all the rows in a table with out using cursor.

I dont know how and what is to be done?

please tell me in details.

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

Senior Member


Joined: 19 May 2007
Posts: 1512
Location: Virginia, USA

PostPosted: Wed Aug 22, 2007 8:16 pm    Post subject:
Reply with quote

Bhagyalakshmi C wrote:
Thank you!!

But how is it possible to update all the rows in a table with out using cursor.

I dont know how and what is to be done?

please tell me in details.

Thanks!!!

EXEC SQL
UPDATE EMPLOYEE
SET PHONENO='NONUM' WHERE PHONENO IS NULL;
END;


In some cases you don't need a cursor to do updates or deletes.

That update statement will update all the rows where PHONENO is null, that is basic sql. Read the manual.
Back to top
View user's profile Send private message
stodolas

Active Member


Joined: 13 Jun 2007
Posts: 632
Location: Wisconsin

PostPosted: Wed Aug 22, 2007 11:34 pm    Post subject:
Reply with quote

Looks like Craq quoted a little too much.

This from him will take care of your PHONENO. You should be able to figure out the ADDR query yourself.
Code:

EXEC SQL
UPDATE EMPLOYEE
SET PHONENO='NONUM' WHERE PHONENO IS NULL;
END;
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: Wed Aug 22, 2007 11:40 pm    Post subject:
Reply with quote

Hello,

Not exactly part of your pli program question but
Quote:
But how is it possible to update all the rows in a table with out using cursor.

A cursor is used when the number of rows returned by a SELECT may be more than one.

As has already been mentioned, updates and deletes might be done without first using a cursor. This is true regardless of programming language - it is part of SQL.
Back to top
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


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

PostPosted: Thu Aug 23, 2007 12:03 am    Post subject:
Reply with quote

If you only have one condition (WHERE clause statment) you can use non-cursor sql.

all phono that are NULL are SET to 'nophone'.

A second non-cursor sql would SET all addr that are NULL to 'noaddr'.

If you have multiple conditions that are not interrelated, then you need to process with a CURSOR to update both phono and/or addr during the FETCH UPDATE CURRENT CURSOR logic loop. Here you would need application code to determine which was NULL and update the appropriate column.

Programmatically, the non-cursor solution is easier, but if you have 10ka-zillion rows and 8 ga-zillion have either NULL phonno or NULL addr, your two non-cursor sqls will probably bring a visit from the systems-boys.
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 BI,TO=ZD Changing Values Tyler Dunbar DFSORT/ICETOOL 1 Tue Jul 25, 2017 12:12 am
No new posts Loading data to table gives wrong for... Raghu navaikulam DB2 19 Thu Jul 13, 2017 2:11 pm
No new posts Sorting of hex values Saurabh_mi DFSORT/ICETOOL 11 Thu May 25, 2017 3:49 pm
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


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