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
 
DB2 Query

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

Active User


Joined: 03 Aug 2005
Posts: 306

PostPosted: Tue Jul 25, 2006 3:21 pm    Post subject: DB2 Query
Reply with quote

Hi

I am having a table suppose its having 3 fields which are integer .Let the length of each field FIELD1 is 8,FIELD1 is 9,FIELD3 is 7 .I am having values 3333 4444 5555 in the corresponding fields .i want to write an SQL query which will pad zeores to the left how can we do that

Eg

Input table A

FIELD1 FIELD2 FIELD3
3333 4444 5555

I want the o/p to be

FIELD1 FIELD2 FIELD3
00003333 000004444 000555

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

Hanfur

Active User


Joined: 21 Jun 2006
Posts: 104

PostPosted: Tue Jul 25, 2006 3:48 pm    Post subject: Hi
Reply with quote

Prsanth,

If you want to pad with zero to coumn values use 0 in single quotes as given below..

Here CMPY,LEDGR,NAME are column's of table TABLE1.

SELECT ('0000'||CMPY),('00000'||LEDGR),('000'||NAME)
FROM DB2TST.TABLE1

CMPY --> padded with 0000 zeros
LEDGR --> padded with 00000 zeros and
Name --->padded with 000 zeros.


-Han.
Back to top
View user's profile Send private message
DavidatK

Active Member


Joined: 22 Nov 2005
Posts: 700
Location: Troy, Michigan USA

PostPosted: Wed Jul 26, 2006 12:42 am    Post subject: Re: DB2 Query
Reply with quote

Hello Han,

See if this is what you are looking for. If not, please come back with furthur expination.

Code:

DECLARE GLOBAL TEMPORARY TABLE TEMP_TBL                   
  (                                                       
       FIELD1                   DEC(8),                   
       FIELD2                   DEC(9),                   
       FIELD3                   DEC(7)                     
   )                                                       
;                                                         
INSERT INTO SESSION.TEMP_TBL (FIELD1, FIELD2, FIELD3)     
VALUES (3333, 4444, 5555)                                 
;                                                         
INSERT INTO SESSION.TEMP_TBL (FIELD1, FIELD2, FIELD3)     
VALUES (26,72377, 0)                                       
;                                                         
SELECT DIGITS(FIELD1) AS FIELD1,                           
       DIGITS(FIELD2) AS FIELD2,                           
       DIGITS(FIELD3) AS FIELD3                           
  FROM SESSION.TEMP_TBL                                   
;                                                         


Results of Query

Code:

    +--------------------------------+
    |  FIELD1  |  FIELD2   | FIELD3  |
    +--------------------------------+
  1_| 00003333 | 000004444 | 0005555 |
  2_| 00000026 | 000072377 | 0000000 |
    +--------------------------------+
                                     


Dave
Back to top
View user's profile Send private message
ravi17s
Warnings : 1

New User


Joined: 15 Aug 2003
Posts: 57

PostPosted: Wed Jul 26, 2006 9:13 am    Post subject: Re: DB2 Query
Reply with quote

Prasanth,

Putting togather David's and Hanfur's Ideas......This is what i get...

SELECT DIGITS(CAST(COLUMNN1 AS DECIMAL(8,2))),
DIGITS(CAST(COLUMNN2 AS DECIMAL(9,2))),
DIGITS(CAST(COLUMNN1 AS DECIMAL(7,2))),

FROM <tablename>
WHERE <CONDITIOn> ;

Please let me know if you need further clarification...

Thanks,
Ravi..........
Back to top
View user's profile Send private message
ravi17s
Warnings : 1

New User


Joined: 15 Aug 2003
Posts: 57

PostPosted: Wed Jul 26, 2006 9:15 am    Post subject: Re: DB2 Query
Reply with quote

This is more appropiate........than my previous query...

SELECT DIGITS(CAST(COLUMNN1 AS DECIMAL(8,0))),
DIGITS(CAST(COLUMNN2 AS DECIMAL(9,0))),
DIGITS(CAST(COLUMNN1 AS DECIMAL(7,0))),

FROM <tablename>
WHERE <CONDITIOn> ;

Thanks,
Ravi.........
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 HEX value search in a DB2 query maxsubrat DB2 2 Wed Oct 04, 2017 3:04 pm
No new posts Create procedure issues -628 when add... chandraBE DB2 1 Mon Sep 18, 2017 12:16 pm
No new posts Can we limit length in concatenation ... balaji81_k DB2 7 Tue Aug 22, 2017 2:50 am
No new posts Need DB2 query to fetch previous row ! Chandan1993 DB2 10 Sat Jun 03, 2017 10:43 am
No new posts JCL to Set Return code based on DB2 S... vinu78 DB2 17 Mon Mar 13, 2017 9:47 pm

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