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 Column value change

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

New User


Joined: 11 Sep 2008
Posts: 29
Location: USA

PostPosted: Mon Feb 28, 2011 9:24 pm    Post subject: DB2 Column value change
Reply with quote

If the column DATE_ORIG_INCPT > ‘2011-12-31’ then the first two digits of the same column need to be changed to ‘19’. I’m not sure what the syntax would be for this type query. The column is a 10 character date field.

DB2 Table Name: CPXPDETT
Column Name: DATE_ORIG_INCPT

Please help me.
Back to top
View user's profile Send private message

sushanth bobby

Senior Member


Joined: 29 Jul 2008
Posts: 1013
Location: India

PostPosted: Mon Feb 28, 2011 9:37 pm    Post subject:
Reply with quote

Hi Subbu,
The column DATE_ORIG_INCPT is character data type column and you want first 2 digits to be changed to 19, when it satisifies the condition.

Output : 1911-12-31
is this the output you are expecting.....

Code:
select '19'||SUBSTR ( DATE_ORIG_INCPT, 3 ) from CPXPDETT
where DATE_ORIG_INCPT > ‘2011-12-31’


You need to be more descriptive....

Thanks,
Sushanth
Back to top
View user's profile Send private message
subbu1522

New User


Joined: 11 Sep 2008
Posts: 29
Location: USA

PostPosted: Mon Feb 28, 2011 9:48 pm    Post subject: Reply to: DB2 Column value change
Reply with quote

Hi Sushant,
Sorry for the confusion.

Requirement: If the DATE_ORIG_INCPT (FORMAT: CCYY-MM-DD) is GREATER THAN "2011-12-31" then that should be changed to "19YY-MM-DD" in the table.

YY-MM-DD SHOULD BE SAME AS ORIGINAL.
Back to top
View user's profile Send private message
sushanth bobby

Senior Member


Joined: 29 Jul 2008
Posts: 1013
Location: India

PostPosted: Mon Feb 28, 2011 10:29 pm    Post subject:
Reply with quote

Subbu,

You can use an UPDATE statement do that process

Code:
UPDATE  CPXPDETT
SET DATE_ORIG_INCPT  = '19'||SUBSTR ( DATE_ORIG_INCPT, 3 )
where DATE_ORIG_INCPT > ‘2011-12-31’
*Statement not tested

Just wondering why do you want to change the century ?


Thanks,
Sushanth
Back to top
View user's profile Send private message
Kjeld

Active User


Joined: 15 Dec 2009
Posts: 365
Location: Denmark

PostPosted: Tue Mar 01, 2011 12:30 pm    Post subject:
Reply with quote

If your column is in DATE format you could use the following construction (not tested):
Code:

UPDATE  CPXPDETT
SET DATE_ORIG_INCPT  = DATE_ORIG_INCPT - 100 YEARS
where DATE_ORIG_INCPT > ‘2011-12-31’
Back to top
View user's profile Send private message
subbu1522

New User


Joined: 11 Sep 2008
Posts: 29
Location: USA

PostPosted: Fri Mar 04, 2011 2:36 am    Post subject:
Reply with quote

Code:
UPDATE BATCHTCP.CPXPDETT
  SET DATE_ORIG_INCPT = 19||SUBSTR(DATE_ORIG_INCPT,3)
WHERE DATE_ORIG_INCPT > 2011-12-31;


The above query is working in TERADATA but this is not working in DB2. It is giving me an error.

Could you please help me.
Back to top
View user's profile Send private message
Akatsukami

Global Moderator


Joined: 03 Oct 2009
Posts: 1755
Location: Bloomington, IL

PostPosted: Fri Mar 04, 2011 3:09 am    Post subject:
Reply with quote

subbu1522 wrote:
Code:
UPDATE BATCHTCP.CPXPDETT
  SET DATE_ORIG_INCPT = 19||SUBSTR(DATE_ORIG_INCPT,3)
WHERE DATE_ORIG_INCPT > 2011-12-31;


The above query is working in TERADATA but this is not working in DB2. It is giving me an error.

Could you please help me.

Why did you leave out the single quotes that Sushanth specified? Why did you not copy and paste the error message, or at least give the SQLSTATE?
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 How to change 'K' or 'M' use Sort vice_versa DFSORT/ICETOOL 5 Thu May 18, 2017 7:11 am
No new posts Fail to change physical VSAM filename... jacobdng CICS 7 Fri Jan 20, 2017 12:36 pm
This topic is locked: you cannot edit posts or make replies. DB2 query Using Recursion, Converting... smilewithashu2 DB2 3 Tue Jan 03, 2017 12:50 pm
No new posts SQZ pipe delimited file with 1 column... Rick Silvers DFSORT/ICETOOL 6 Sun Nov 06, 2016 8:11 pm
This topic is locked: you cannot edit posts or make replies. Checking wether a string starts from ... sundaram.naveen CLIST & REXX 9 Thu Oct 06, 2016 11:56 am


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