asked on

DB2 function -- testing results of a select

The following function looks up mileage from a table based on postal code. It checks a number of combinations, i.e, {from_pc, to_pc}, {to_pc, from_pc}, etc.

When I call the function if the first select is successful it returns the mileage. If the first select is not successful I get a null returned.

What am I missing????

CREATE FUNCTION TMFE_PG_MI_LOOKUP (IV_FROM_PC VARCHAR(10), IV_TO_PC VARCHAR(10))
RETURNS DOUBLE
LANGUAGE SQL
READS SQL DATA
NO EXTERNAL ACTION
DETERMINISTIC
BEGIN ATOMIC

DECLARE LD_MILES                  DOUBLE ;
DECLARE LI_COUNT INT ;
DECLARE LS_SUB_FROM_PC VARCHAR(3) ;
DECLARE LS_SUB_TO_PC VARCHAR(3) ;

SET LD_MILES = ( SELECT COALESCE(PG_MILES, 0.0) FROM TMFE_PG_MILES WHERE FROM_PC = IV_FROM_PC AND TO_PC = IV_TO_PC ) ;

IF ( LD_MILES = 0.0 ) THEN

SET LD_MILES = ( SELECT COALESCE(PG_MILES, 0.0) FROM TMFE_PG_MILES WHERE FROM_PC = IV_TO_PC AND TO_PC = IV_FROM_PC ) ;

      IF ( LD_MILES = 0.0 ) THEN

            SET LS_SUB_FROM_PC = SUBSTR(IV_FROM_PC,1,3) ;
SET LS_SUB_TO_PC = SUBSTR(IV_TO_PC,1,3) ;

SET LD_MILES = ( SELECT COALESCE(PG_MILES, 0.0) FROM TMFE_PG_MILES WHERE FROM_PC = IV_FROM_PC AND TO_PC = LS_SUB_TO_PC ) ;

            IF ( LD_MILES = 0.0 ) THEN

                  SET LD_MILES = ( SELECT COALESCE(PG_MILES, 0.0) FROM TMFE_PG_MILES WHERE FROM_PC = LS_SUB_FROM_PC AND TO_PC = IV_TO_PC ) ;

                  IF ( LD_MILES = 0.0 ) THEN

                        SET LD_MILES = ( SELECT COALESCE(PG_MILES, 0.0) FROM TMFE_PG_MILES WHERE FROM_PC = LS_SUB_TO_PC AND TO_PC = IV_FROM_PC ) ;

                        IF ( LD_MILES = 0.0 ) THEN

                              SET LD_MILES = ( SELECT COALESCE(PG_MILES, 0.0) FROM TMFE_PG_MILES WHERE FROM_PC = IV_TO_PC AND TO_PC = LS_SUB_FROM_PC ) ;

                              IF ( LD_MILES = 0.0 ) THEN

                                    SET LD_MILES = ( SELECT COALESCE(PG_MILES, 0.0) FROM TMFE_PG_MILES WHERE FROM_PC = LS_SUB_FROM_PC AND TO_PC = LS_SUB_TO_PC ) ;

                                    IF ( LD_MILES = 0.0 ) THEN

                                          SET LD_MILES = ( SELECT COALESCE(PG_MILES, 0.0) FROM TMFE_PG_MILES WHERE FROM_PC = LS_SUB_TO_PC AND TO_PC = LS_SUB_FROM_PC ) ;

                                    END IF;

                              END IF;

                        END IF;

                  END IF;

            END IF;

      END IF;

END IF;

IF ( LD_MILES = 0.0) THEN

      IF ( IV_FROM_PC IS NOT NULL AND IV_FROM_PC <> '' AND SUBSTR(IV_FROM_PC,1,1) <> ' ' ) THEN

            IF ( IV_TO_PC IS NOT NULL AND IV_TO_PC <> '' AND SUBSTR(IV_TO_PC,1,1) <> ' ' ) THEN

                  IF ( LENGTH(IV_FROM_PC) = 7 AND LENGTH(IV_TO_PC) = 7 ) THEN

                        IF ( SUBSTR(IV_FROM_PC,4,1) = ' ' AND SUBSTR(IV_TO_PC,4,1) = ' ' ) THEN

                              SET LI_COUNT = ( SELECT COUNT(*) FROM TMFE_PG_MILES TPM WHERE TPM.FROM_PC = '*' || IV_FROM_PC AND TPM.TO_PC = IV_TO_PC ) ;

                              IF ( LI_COUNT = 0 ) THEN

--                                    INSERT INTO TMFE_PG_MILES VALUES( '*' || IV_FROM_PC , IV_TO_PC , 0.0 ) ;

                              END IF;

                        END IF;

                  END IF;

            END IF;

      END IF;

END IF;

RETURN LD_MILES;

END@

ASKER CERTIFIED SOLUTION

Kent Olsen

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

DonKronos

ASKER

Thanks Kent but that didn't help :( :(

I even changed the return statement to RETURN COALESCE(LD_MILES,-999) and it still returns NULL !!!

Since the first select does not find any rows, is that causing an error condition and causing the function to return with the NULL ????

If that is the case, how do I trap the error and continue????

I even tried this and it still does not go beyond the first test.

SET LD_MILES = 0.0 ;
if exists(SELECT PG_MILES FROM TMFE_PG_MILES WHERE FROM_PC = IV_FROM_PC AND TO_PC = IV_TO_PC) then
SET LD_MILES = ( SELECT PG_MILES FROM TMFE_PG_MILES WHERE FROM_PC = IV_FROM_PC AND TO_PC = IV_TO_PC );
end if;

IF ( LD_MILES = 0.0 ) THEN

Thanks

SOLUTION

ocgstyles