-
Failed using NVL function
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
-
You can use RTRIM(my_string,', ') to remove any stray commas and spaces from the end of my_string.
-
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
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|