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.
Joined: 06 Dec 2004 Posts: 211 Location: Keane Inc., Minneapolis USA.
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.