select prefix 2 extra characters

muthukumar · New User Joined: 24 Mar 2004 Posts: 29

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

ovreddy · Posted: Tue Mar 08, 2005 12:43 pm

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.

muthukumar · New User Joined: 24 Mar 2004 Posts: 29

Hi Reddy,

thanks for the information.

regards
Muthu