0
    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.
Bilal Shaikh
  • 47
  • 5
  • Hi, interesting, perhaps print some debugging output with `raise notice` or use the built-in debugger https://stackoverflow.com/questions/20190406/how-to-debug-postgresql-stored-procedures – IronMan Dec 21 '20 at 01:04
  • `limit` is a reserved keyword. You should at least add the error message or the problem you have with the query. – Laurenz Albe Dec 21 '20 at 02:11

0 Answers0