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
 

 

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 Remove junk values in a file and rite... KP1125 DFSORT/ICETOOL 2 Wed Jan 25, 2017 9:58 pm
No new posts Two Selects in a single select query Rohit Umarjikar DB2 1 Fri Nov 04, 2016 8:46 pm
No new posts Removing Extra Spaces in a comma sepa... Puspojit DFSORT/ICETOOL 3 Tue Sep 13, 2016 7:46 am
No new posts Remove Special Characters from Mainfr... Rodger Zhang All Other Mainframe Topics 6 Wed Jul 06, 2016 1:12 am
No new posts Select first 2 group data vice_versa DFSORT/ICETOOL 10 Mon Jun 13, 2016 2:21 pm


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