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
 

 

How to add character to the value

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

New User


Joined: 21 Feb 2007
Posts: 70
Location: bangalore

PostPosted: Thu May 06, 2010 4:27 pm    Post subject: How to add character to the value
Reply with quote

Hi,

While running an SQL query i have a situation where I took date(timestamp). Now i need to concatenate ';' and t the end of the value.

I tried char(date(timestamp))||char(';'), but showing -181 error.

can you plesae let me know hos to do this concatenation?
Back to top
View user's profile Send private message

Escapa

Senior Member


Joined: 16 Feb 2007
Posts: 1399
Location: IL, USA

PostPosted: Thu May 06, 2010 4:31 pm    Post subject:
Reply with quote

post your complete query... Have you looked as what is mean by SQLCODE you are getting?
And why date of timestamp why not just use current date?
Code:

SELECT (CHAR(CURRENT DATE) || ';') FROM SYSIBM.SYSDUMMY1;   
Back to top
View user's profile Send private message
ashok_uddaraju

New User


Joined: 21 Feb 2007
Posts: 70
Location: bangalore

PostPosted: Thu May 06, 2010 4:39 pm    Post subject:
Reply with quote

select char(date(update_ts))||char(';') from xyz;

I want the output as 2010-05-06;(including ;)
Back to top
View user's profile Send private message
ashok_uddaraju

New User


Joined: 21 Feb 2007
Posts: 70
Location: bangalore

PostPosted: Thu May 06, 2010 4:41 pm    Post subject:
Reply with quote

i am working on different date not just current date
Back to top
View user's profile Send private message
guptae

Moderator


Joined: 14 Oct 2005
Posts: 1187
Location: Bangalore,India

PostPosted: Thu May 06, 2010 5:06 pm    Post subject:
Reply with quote

Hi Ashok,

Quote:
i am working on different date not just current date


So whats the problem use ur column name and table name instead of current date and sysibm.sysdummy table.

Code:
select char(char(date(update_ts))|| ';') from xyz;


I would suggest you to read Db2
Back to top
View user's profile Send private message
ashok_uddaraju

New User


Joined: 21 Feb 2007
Posts: 70
Location: bangalore

PostPosted: Thu May 06, 2010 5:09 pm    Post subject:
Reply with quote

it gives ma and error

---------+---------+---------+---------+---------+---------+---------+--------
DSNT408I SQLCODE = -171, ERROR: THE DATA TYPE, LENGTH, OR VALUE OF ARGUMENT 1
OF || IS INVALID
Back to top
View user's profile Send private message
ashok_uddaraju

New User


Joined: 21 Feb 2007
Posts: 70
Location: bangalore

PostPosted: Thu May 06, 2010 5:12 pm    Post subject:
Reply with quote

sorry this is the error message

DSNT408I SQLCODE = -181, ERROR: THE STRING REPRESENTATION OF A DATETIME VALUE
IS NOT A VALID DATETIME VALUE
Back to top
View user's profile Send private message
skkp2006

New User


Joined: 14 Jul 2006
Posts: 93
Location: Chennai,India

PostPosted: Thu May 06, 2010 5:14 pm    Post subject: Reply to: How to add character to the value
Reply with quote

Ashok,

Post the SQL that you have used. We have no idea whats the actual query that you have used.


try this also..

Code:
SELECT CHAR(DATE(CURRENT TIMESTAMP))||CHAR(';')   
                 FROM SYSIBM.SYSDUMMY1;           




Syam
Back to top
View user's profile Send private message
guptae

Moderator


Joined: 14 Oct 2005
Posts: 1187
Location: Bangalore,India

PostPosted: Thu May 06, 2010 5:19 pm    Post subject:
Reply with quote

Hello Ashok,

I tried it and its working fine for me

Code:
SELECT CHAR(CHAR(DATE(UPDATE_TS))||';')
FROM  ABC         
FETCH FIRST ROW ONLY;       


Just check ur code and post the Sql u tried
Back to top
View user's profile Send private message
ashok_uddaraju

New User


Joined: 21 Feb 2007
Posts: 70
Location: bangalore

PostPosted: Thu May 06, 2010 5:30 pm    Post subject:
Reply with quote

this is the orginal query with table names changed
select char(date(update_ts))||char(';') from xyz;

I had treied the query for sysdummy and it worked, but not working for the orginal query.Unfortunately i canot paste the original query

Mu origibla query works til select char(date(update_ts)), but when added the second part ||char(';') it fails with

DSNT408I SQLCODE = -181, ERROR: THE STRING REPRESENTATION OF A DATETIME VALUE
IS NOT A VALID DATETIME VALUE
Back to top
View user's profile Send private message
guptae

Moderator


Joined: 14 Oct 2005
Posts: 1187
Location: Bangalore,India

PostPosted: Thu May 06, 2010 5:32 pm    Post subject:
Reply with quote

Hello Ashok,

Did u compare ur query with the Query provided by me?

UR Query
Code:
select char(date(update_ts))||char(';') from xyz;



My Query
Code:
SELECT CHAR(CHAR(DATE(UPDATE_TS))||';')
FROM  ABC


Hope u get it now
Back to top
View user's profile Send private message
ashok_uddaraju

New User


Joined: 21 Feb 2007
Posts: 70
Location: bangalore

PostPosted: Thu May 06, 2010 5:41 pm    Post subject:
Reply with quote

tried SELECT CHAR(CHAR(DATE(UPDATE_TS))||';')
FROM ABC
as well .... same error
Back to top
View user's profile Send private message
ashok_uddaraju

New User


Joined: 21 Feb 2007
Posts: 70
Location: bangalore

PostPosted: Thu May 06, 2010 6:07 pm    Post subject:
Reply with quote

i got this......... thanks
Back to top
View user's profile Send private message
guptae

Moderator


Joined: 14 Oct 2005
Posts: 1187
Location: Bangalore,India

PostPosted: Thu May 06, 2010 6:08 pm    Post subject:
Reply with quote

Finally :-)
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. DSList (3.4) cannot display Chinese c... jacobdng TSO/ISPF 4 Wed Oct 05, 2016 2:51 pm
No new posts Continuation Character ballaswaroop SYNCSORT 8 Thu Aug 25, 2016 4:46 pm
No new posts Adding a descending character to a file Steve Ironmonger DFSORT/ICETOOL 3 Fri Jul 15, 2016 4:57 pm
No new posts Moving character data to smallint in db2 rikdeb DB2 5 Thu Jul 14, 2016 12:38 am
No new posts character setting configuration into ... sakthikumarT DB2 5 Thu Nov 05, 2015 11:37 am


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