ls_sql := (CONCAT_WS('', '
SELECT
INITCAP(
CASE
WHEN (TRIM(STREET_NUMBER) IS NOT NULL) THEN
TRIM(STREET_NUMBER)||'' ''
END)||
CASE
WHEN (STREET_DIRECTION IS NOT NULL) AND (STREET_DIRECTION <> ''', aws_oracle_ext.getglobalvariable(proutinename => 'PROD.PROPERTY_FUNCTIONS', pvariable => 'c_blank_street_direction', ptp => NULL::CHARACTER(2)), ''') THEN
TRIM(STREET_DIRECTION)||'' ''
END||
INITCAP(
TRIM(STREET_NAME)
),
CASE WHEN (UNIT_NUMBER IS NOT NULL) THEN
LTRIM(INITCAP(
CASE
WHEN (UNIT_TYPE IS NULL) THEN ''Unit''
WHEN (UNIT_TYPE = ''', aws_oracle_ext.getglobalvariable(proutinename => 'PROD.PROPERTY_FUNCTIONS', pvariable => 'c_blank_unit_type_code', ptp => NULL::CHARACTER VARYING(10)), ''') THEN ''''
ELSE INITCAP(UNIT_TYPE)
END
)||'' '')||UNIT_NUMBER
END,
INITCAP(CITIES.CITY_NAME)||'', ''||STATE||'' ''||ZIP_CODE||
CASE
WHEN (ZIP_PLUS_4 IS NOT NULL) THEN ''-''||ZIP_PLUS_4
END
,
'));
IF (ls_address_type = aws_oracle_ext.getglobalvariable(proutinename => 'PROD.PROPERTY_FUNCTIONS', pvariable => 'c_fa_addrtype_property', ptp => NULL::CHARACTER VARYING(50))) THEN
/* Property */
ls_sql := CONCAT_WS('', ls_sql, '
PROPERTY.STATE
FROM
prod.PROPERTY,
prod.CITIES
WHERE
( PROPERTY.CITY_CODE = CITIES.CITY_CODE ) AND
( PROPERTY.PROPERTY_ID = :n_property_id_in ) AND
(limit < 2)
');
ELSE
/* Commercial Property */
ls_sql := CONCAT_WS('', ls_sql, '
COMMERCIAL_PROPERTY.STATE
FROM
prod.COMMERCIAL_PROPERTY,
prod.CITIES
WHERE
( COMMERCIAL_PROPERTY.CITY_CODE = CITIES.CITY_CODE ) AND
( COMMERCIAL_PROPERTY.COMMERCIAL_PROPERTY_ID = :n_property_id_in ) AND
(limit < 2)
');
END IF;
/* Execute the SQL */
raise notice 'ls_sql %',ls_sql;
This is a small piece of code where I printed ls_sql to check what value is it giving out! but when I call the function it returns an exception value! Can someone help me to identify where should I make changes to it? Below is the RESULT I am getting when I run this function. I could not identify which part of the code is not working.
NOTICE: lb_statesingleline f
NOTICE: lb_street_unit_line f
NOTICE: lb_citystatezip_line f
NOTICE: ls_spacer ,
NOTICE: ls_sql
SELECT
INITCAP(
CASE
WHEN (TRIM(STREET_NUMBER) IS NOT NULL) THEN
TRIM(STREET_NUMBER)||' '
END)||
CASE
WHEN (STREET_DIRECTION IS NOT NULL) AND (STREET_DIRECTION <> 'ZZ') THEN
TRIM(STREET_DIRECTION)||' '
END||
INITCAP(
TRIM(STREET_NAME)
),
CASE WHEN (UNIT_NUMBER IS NOT NULL) THEN
LTRIM(INITCAP(
CASE
WHEN (UNIT_TYPE IS NULL) THEN 'Unit'
WHEN (UNIT_TYPE = 'ZZ') THEN ''
ELSE INITCAP(UNIT_TYPE)
END
)||' ')||UNIT_NUMBER
END,
INITCAP(CITIES.CITY_NAME)||', '||STATE||' '||ZIP_CODE||
CASE
WHEN (ZIP_PLUS_4 IS NOT NULL) THEN '-'||ZIP_PLUS_4
END
,
PROPERTY.STATE
FROM
prod.PROPERTY,
prod.CITIES
WHERE
( PROPERTY.CITY_CODE = CITIES.CITY_CODE ) AND
( PROPERTY.PROPERTY_ID = :n_property_id_in ) AND
(limit < 2)
NOTICE: EXCEPTION P0001
NOTICE: RESULT1 <NULL>
DO.
raise noticeor use the built-in debugger stackoverflow.com/questions/20190406/…limitis a reserved keyword. You should at least add the error message or the problem you have with the query.