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
 
select prefix 2 extra characters

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

New User


Joined: 24 Mar 2004
Posts: 29

PostPosted: Thu Sep 02, 2004 12:02 pm    Post subject: select prefix 2 extra characters
Reply with quote

Hi,

I tried unloading a table with DSNTIAUL program and i have following issues.. can one of u help me to solve this.

1) With Select A||'|'||.... query the result dataset set has two special characters prefixed to the value of A. Any techniques to avoid this?

2) Also i have Null values for a field in the table. When i concatenate this field with other field using || an error message that invalid parameter for ||. Is there any methods to concatenate null values too.

thanks and regards
Muthu
Back to top
View user's profile Send private message

ovreddy

Active User


Joined: 06 Dec 2004
Posts: 211
Location: Keane Inc., Minneapolis USA.

PostPosted: Tue Mar 08, 2005 12:43 pm    Post subject:
Reply with quote

Hi Muthu,

1 Ans: You can use RIGHT function to remove left most 2 unwanted characters. The RIGHT function returns the rightmost integer bytes of string-expression.

select RIGHT(A || ',' || B ||.......,LENGTH-2) from <table>;

2 Ans: Yes, we can concatinate null values by using COALESCE. COALESCE returns the first argument that is not NULL. The arguments are evaluated in the order in which they are specified. This function is usually used to replace NULL values with a not NULL value.

SELECT COLUMN A || COALESCE(COLUMN B,'') from <TABLE>;

COLUMN B value will be replaced by '' if it is NULL.

Bye,
Reddy.
Back to top
View user's profile Send private message
muthukumar

New User


Joined: 24 Mar 2004
Posts: 29

PostPosted: Tue Mar 08, 2005 12:50 pm    Post subject: Re: select prefix 2 extra characters
Reply with quote

Hi Reddy,

thanks for the information.

regards
Muthu
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 line 52: Name or string > 250 char... Ashishpanpaliya CLIST & REXX 5 Sat Oct 14, 2017 2:29 am
No new posts Binary to display characters Roy Ware SYNCSORT 3 Tue Sep 19, 2017 8:46 pm
No new posts Can we limit length in concatenation ... balaji81_k DB2 7 Tue Aug 22, 2017 2:50 am
No new posts Select numeric portion from CHAR data... balaji81_k DB2 6 Sat Aug 19, 2017 1:51 am
No new posts Extra character appears in file when ... Balu5491 All Other Mainframe Topics 1 Wed Jul 26, 2017 2:39 pm

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