Failed using NVL function

[nir_s]

Hi All,

I have the following query (from Business Objects):
SELECT
T_HOUSEHOLD.HOUSEHOLD_ID,
T_HOUSEHOLD.ST_NAME || ' ' || T_HOUSEHOLD.HOUSE_NUM || '/' || T_HOUSEHOLD.APART_NUM || ' ' || T_HOUSEHOLD.CITY_NAME || ', ' || T_HOUSEHOLD.POST_CODE "Address",
count(T_ORDERS.order_id)
FROM
T_ORDERS,
T_SUBS,
T_HOUSEHOLD
WHERE
( T_HOUSEHOLD.HOUSEHOLD_ID=T_SUBS.HOUSEHOLD_ID )
AND ( T_SUBS.SUB_ID=T_ORDERS.SUB_ID )
AND (
to_date(to_char(T_ORDERS.ORDER_DATE,'dd/mm/rrrr'),'dd/mm/rrrr') >= '01-jan-00'
AND to_date(to_char(T_ORDERS.ORDER_DATE,'dd/mm/rrrr'),'dd/mm/rrrr') <= '01-jan-09'
AND ( T_ORDERS.ORIGINAL_ORDER_ID is null )
)
GROUP BY
T_HOUSEHOLD.HOUSEHOLD_ID,
T_HOUSEHOLD.ST_NAME || ' ' || T_HOUSEHOLD.HOUSE_NUM || '/' || T_HOUSEHOLD.APART_NUM || ' ' || T_HOUSEHOLD.CITY_NAME || ', ' || T_HOUSEHOLD.POST_CODE

The result is:
HOUSEHOLD_ID Address
------------ ----------------------------------------------------------
10000 Sumsum 21/ NY, 1
10043 rty 77/ NY,

The problem is with rows that the POST_CODE is null and the comma sign is concatenated to the address.
I've tried to use NVL to hide the comma sign but i failed:

SQL> select nvl(', ' || T_HOUSEHOLD.POST_CODE,'') from T_HOUSEHOLD
2 where HOUSEHOLD_ID=10043;

NVL(','||T_HOUSEHOLD.POST_CODE,'')
-----------------------------------------------
,

What did i do wrong?

Thanks in advance,
Nir

[slimdave]

You can use RTRIM(my_string,', ') to remove any stray commas and spaces from the end of my_string.

[nir_s]

Hi,

Eventually i replaced the NVL with DECODE and it worked:
SELECT
T_HOUSEHOLD.HOUSEHOLD_ID,
T_HOUSEHOLD.ST_NAME || ' ' || T_HOUSEHOLD.HOUSE_NUM || '/' || T_HOUSEHOLD.APART_NUM || ' ' ||
T_HOUSEHOLD.CITY_NAME || decode(T_HOUSEHOLD.POST_CODE, null,null, ', '||T_HOUSEHOLD.POST_CODE) AS ADRESS
,count(T_ORDERS.order_id)
FROM
T_ORDERS,
T_SUBS,
T_HOUSEHOLD
WHERE
( T_HOUSEHOLD.HOUSEHOLD_ID=T_SUBS.HOUSEHOLD_ID )
AND ( T_SUBS.SUB_ID=T_ORDERS.SUB_ID )
AND (
to_date(to_char(T_ORDERS.ORDER_DATE,'dd/mm/rrrr'),'dd/mm/rrrr') >= '01-jan-00'
AND to_date(to_char(T_ORDERS.ORDER_DATE,'dd/mm/rrrr'),'dd/mm/rrrr') <= '01-jan-09'
AND ( T_ORDERS.ORIGINAL_ORDER_ID is null )
)
GROUP BY
T_HOUSEHOLD.HOUSEHOLD_ID,
T_HOUSEHOLD.ST_NAME || ' ' || T_HOUSEHOLD.HOUSE_NUM || '/' || T_HOUSEHOLD.APART_NUM || ' ' ||
T_HOUSEHOLD.CITY_NAME || decode(T_HOUSEHOLD.POST_CODE, null,null, ', '||T_HOUSEHOLD.POST_CODE)
/

HOUSEHOLD_ID ADRESS
------------ ----------------------------------------------------------------------------
10000 Sumsum 21/ NY, 1
10043 rty 77/ NY

Thanks any way,
Nir