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
 

 

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 Join in SQL Query vickey_dw DB2 2 Tue Jan 17, 2017 12:15 am
This topic is locked: you cannot edit posts or make replies. MQ setup query mohitsaini Java & MQSeries 4 Sun Jan 08, 2017 1:53 am
No new posts DB2 query Using Recursion, Converting... smilewithashu2 DB2 1 Tue Jan 03, 2017 12:50 pm
No new posts SQL query to run through list of valu... Ni3-db2 DB2 14 Wed Dec 14, 2016 9:52 am
This topic is locked: you cannot edit posts or make replies. Db2 SQL query to convert rows into co... subratarec DB2 5 Thu Nov 17, 2016 4:51 pm


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