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 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: 1775
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 column with count of rows within dist... ronald wouterson DB2 4 Sun Sep 17, 2017 9:48 pm
No new posts Convert rows and column into JSON for... Dinesh Manivannan DB2 2 Sun Sep 03, 2017 6:50 pm
No new posts How to change a string containing the... chong.zhou TSO/ISPF 6 Tue Aug 29, 2017 4:54 pm
No new posts Select numeric portion from CHAR data... balaji81_k DB2 6 Sat Aug 19, 2017 1:51 am
No new posts change string before sertan string anatol DFSORT/ICETOOL 8 Tue Aug 15, 2017 5:43 pm

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