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@
LVL 15
DonKronosAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Kent OlsenData Warehouse Architect / DBACommented:
Hi DonKronos,

My suspicion is that the coalesce() isn't getting executed.  What's happening is that the result of the select statement is returning a NULL set, which is different from a NULL value.  Since the returned set is NULL, the coalesce() isn't getting executed.

You can wrap the select with another select/coalesce() or test for NULL later.

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


Good Luck!
Kent
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
DonKronosAuthor Commented:
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
0
ocgstylesCommented:
maybe you can use something like this:

-- add this to declarations
DECLARE      TMP_CHAR      CHAR(1);

-- use "select ... into" to select a character (to always return at least 1 row) and the value you want
SELECT 'a' as a, COALESCE(PG_MILES, 0.0) as miles into TMP_CHAR, LD_MILES FROM TMFE_PG_MILES WHERE FROM_PC = IV_FROM_PC AND TO_PC = IV_TO_PC;

i tried that syntax with a stored procedure, it worked...
0
DonKronosAuthor Commented:
Thank you both.  I made a silly mistake in my testing.  The statement I finally used is:

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

0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
DB2

From novice to tech pro — start learning today.