Convert NVARCHAR2 to VARCHAR2 in select statement
734133Apr 6 2010 — edited Apr 6 2010I need to convert NVARCHAR2 values to VARCHAR2 in a select statement. Is this possible? The COUNTY_PARCEL_DATA colums are the NVARCHAR2 columns that need converting. I've tried TRANSLATE(COUNTY_PARCEL_DATA.SITUS_STREET_NUMBER USING CHAR_CS), CAST(COUNTY_PARCEL_DATA.SITUS_STREET_NUMBER AS VARCHAR2(100)), CONVERTCS(COUNTY_PARCEL_DATA.SITUS_STREET_NUMBER, 'CHAR_CS') and others directly in the statement for each column. Anything I'm missing? Thanks.
SELECT BENEFICIARY.NAME_FIRST || ' ' || BENEFICIARY.NAME_LAST AS NAME,
COUNTY_PARCEL_DATA.SITUS_STREET_NUMBER
|| ' '
|| COUNTY_PARCEL_DATA.SITUS_STREET_DIRECTION
|| ' '
|| COUNTY_PARCEL_DATA.SITUS_STREET_NAME
|| ' '
|| COUNTY_PARCEL_DATA.SITUS_STREET_TYPE
AS ADDRESS,
COUNTY_PARCEL_DATA.SITUS_CITY AS CITY,
'AZ' AS STATE,
COUNTY_PARCEL_DATA.SITUS_ZIP AS ZIP,
'T4' AS TRACER
FROM BENEFICIARY
LEFT OUTER JOIN
VARS_PARCEL
ON BENEFICIARY.AVNUM = VARS_PARCEL.AVNUM
LEFT OUTER JOIN
COUNTY_PARCEL_DATA
ON VARS_PARCEL.APN = COUNTY_PARCEL_DATA.APN
LEFT OUTER JOIN
OWNER
ON BENEFICIARY.BENEFICIARY_ID = OWNER.BENEFICIARY_ID
WHERE (SUBSTR (VARS_PARCEL.AVNUM, 0, 4) IN
(SELECT COLUMN_VALUE
FROM TABLE(SPLIT_STRING (
R_VARS_MAILING_NOTICES.NEIGHBORHOODS
)))
OR R_VARS_MAILING_NOTICES.NEIGHBORHOODS IS NULL)
AND BENEFICIARY.BENEFICIARY_TYPE = 'Tenant';
Edited by: Guddie on Apr 6, 2010 12:23 PM