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
 

 

LPAD Function

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

New User


Joined: 18 Sep 2007
Posts: 73
Location: Bangalore

PostPosted: Wed Nov 28, 2012 12:35 pm    Post subject: LPAD Function
Reply with quote

My requirement is to have two characters in column.
Here is the scenario :-
My table column is a int(2) which contains values ranging from 0(null) to 2 I want to have for values whose length is less than 2 should be padded with zero

example:-
null should be updated to 00
1 should be updated to 01
12 should be updated to 12



Help would be really appreciated.

Tried with LPAD function , but not working...
An expert help is appreciated
Back to top
View user's profile Send private message

Pandora-Box

Moderator


Joined: 07 Sep 2006
Posts: 1529
Location: Andromeda Galaxy

PostPosted: Wed Nov 28, 2012 12:47 pm    Post subject:
Reply with quote

Quote:
Tried with LPAD function , but not working...


Share us the query you had used.
Back to top
View user's profile Send private message
sun_job

New User


Joined: 18 Sep 2007
Posts: 73
Location: Bangalore

PostPosted: Wed Nov 28, 2012 12:51 pm    Post subject:
Reply with quote

Code:
SELECT
           CHAR(DEPT_I) AS DEPTID,
           LPAP(CHAR(CLA_I),2,'0') AS CLASSID
         FROM DB2PD.CLASS;
Back to top
View user's profile Send private message
Bill Woodger

DFSORT Moderator


Joined: 09 Mar 2011
Posts: 7228

PostPosted: Wed Nov 28, 2012 1:06 pm    Post subject: Reply to: LPAD Function
Reply with quote

And would it be possible for you to reveal to us the manner in which it "does not work"?
Back to top
View user's profile Send private message
Pandora-Box

Moderator


Joined: 07 Sep 2006
Posts: 1529
Location: Andromeda Galaxy

PostPosted: Wed Nov 28, 2012 1:42 pm    Post subject:
Reply with quote

Sorry to not say it first time

Please share the output yo get and also the ouput you expect for the set of data in table
Back to top
View user's profile Send private message
sun_job

New User


Joined: 18 Sep 2007
Posts: 73
Location: Bangalore

PostPosted: Wed Nov 28, 2012 1:51 pm    Post subject:
Reply with quote

It seems LPAD function does not work. I have tried to do it with CASE expression.
Code:
(CASE
     WHEN LENGTH(LTRIM(RTRIM(CHAR(C.CLA_I))))=1 then '0'||LTRIM(RTRIM(CHAR(C.CLA_I)))    
     WHEN LENGTH(LTRIM(RTRIM(CHAR(C.CLA_I))))=2 then      LTRIM(RTRIM(CHAR(C.CLA_I)))    
   END) AS CLASSID,


This is working as expected
Back to top
View user's profile Send private message
chandan.inst

Active User


Joined: 03 Nov 2005
Posts: 269
Location: Mumbai

PostPosted: Wed Nov 28, 2012 2:03 pm    Post subject:
Reply with quote

Hi,

Check for the function DIGITS in DB2 Manual

You may need to handle Null values using CASE statement

Regards,
Chandan
Back to top
View user's profile Send private message
GuyC

Senior Member


Joined: 11 Aug 2009
Posts: 1278
Location: Belgium

PostPosted: Wed Nov 28, 2012 2:05 pm    Post subject:
Reply with quote

afaik there is no such thing as int(2) in DB2 but you could just use :
Code:
right(digits(i2),2)

or if i2 is nullable :
Code:
right(digits(coalesce(i2,0)),2)
Back to top
View user's profile Send private message
chandan.inst

Active User


Joined: 03 Nov 2005
Posts: 269
Location: Mumbai

PostPosted: Wed Nov 28, 2012 2:12 pm    Post subject:
Reply with quote

Thanks Guyc

I have totally missed coalesce while suggesting CASE statement in my earlier post

Regards,
Chandan
Back to top
View user's profile Send private message
sun_job

New User


Joined: 18 Sep 2007
Posts: 73
Location: Bangalore

PostPosted: Thu Nov 29, 2012 11:17 am    Post subject:
Reply with quote

chandan.inst wrote:
Hi,

Check for the function DIGITS in DB2 Manual

You may need to handle Null values using CASE statement

Regards,
Chandan


Sure Chandan , will check the same
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
This topic is locked: you cannot edit posts or make replies. RANDOM Function in COBOL swapnil781 COBOL Programming 2 Tue Nov 15, 2016 6:17 pm
No new posts Check System time(Minute) Using TIME1... balaji81_k DFSORT/ICETOOL 5 Fri Nov 11, 2016 10:53 am
No new posts Strange EXEC function call in z/VM Willy Jensen CLIST & REXX 3 Wed Oct 05, 2016 2:07 pm
No new posts Replace function abdulrafi COBOL Programming 13 Mon Sep 12, 2016 10:12 am
No new posts Which function does the Ampersand (&a... Andi1982 PL/I & Assembler 3 Wed Aug 24, 2016 2:05 pm


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