SQL Scalar Function with SELECT COUNT(*)...?

[Fireloque]

Hi

Can someone please advise how my function should be written to do the following;

I've got a table that I want to count records for that match an input key value and another criteria. If this count is <= 12 then another table value is to be read and returned, otherwise another table value is returned.

This is what I've got;

CREATE FUNCTION XCASHSETT/XGETDTETOL 
   (PSIDIN INT) 
   RETURNS INT 
   LANGUAGE SQL 
   DETERMINISTIC 
   NO EXTERNAL ACTION  
   READS SQL DATA 
   RETURNS NULL ON NULL INPUT
   SET OPTION DBGVIEW=*SOURCE 
  BEGIN
    DECLARE INSTCOUNT SMALLINT;
    DECLARE EXIT HANDLER FOR SQLSTATE VALUE '22007'
      RETURN NULL;
    SET INSTCOUNT = SELECT COUNT(*) FROM UPIZREP
         WHERE IZITTY='INST' AND IZRCFD='' AND IZPSID=PSIDIN;
    IF INSTCOUNT <= 12 THEN
      RETURN SELECT DCVALU FROM UCDTREP WHERE DCNAME='XCSDTETOL1';
    ELSE
      RETURN SELECT DCVALU FROM UCDTREP WHERE DCNAME='XCSDTETOL2';
    ENDIF;
  END

However, when I try to run this it complains about the variable SELECT not being defined and the keyword COUNT.

This is on a v5r4 machine.

Please advise?

Thanks

[vikramx]

Re: SQL Scalar Function with SELECT COUNT(*)...?

Perhaps you could try by enclosing the Select within brackets.

SET INSTCOUNT = (SELECT COUNT(*) FROM UPIZREP WHERE IZITTY='INST' AND IZRCFD=' ' AND IZPSID=PSIDIN); 

This usually works well in many cases and maybe works with yours as well.

Alternative you could also try the following:-

SELECT COUNT(*) INTO INSTCOUNT FROM UPIZREP WHERE IZITTY='INST' AND IZRCFD=' ' AND IZPSID=PSIDIN; 

I know this is supported in PL/SQL or embedded SQL with host variables. So am guessing it should work in your case as well.

[Fireloque]

Re: SQL Scalar Function with SELECT COUNT(*)...?

The first resolved it; putting the SELECT statement in brackets. Thanks a lot for your help!

In your second statement, can you use the INTO keyword to put a returned value into a variable then? I thought it was only for table data.

Thanks

[vikramx]

Re: SQL Scalar Function with SELECT COUNT(*)...?

Yes you can put in the data of a calculated or returned value into a variable. As far as my experience goes, the only restriction would be that the data types must be compatible with each other and your select should not return multiple rows.

It has always worked for me.

[vikramx]

Re: SQL Scalar Function with SELECT COUNT(*)...?

Further addition after thinking a bit

SELECT CASE WHEN COUNT(*) <= 12 THEN
(SELECT DCVALU FROM UCDTREP WHERE DCNAME='XCSDTETOL1')
ELSE
(SELECT DCVALU FROM UCDTREP WHERE DCNAME='XCSDTETOL2')
END
INTO <Variable>
FROM UPIZREP WHERE IZITTY='INST' AND IZRCFD='' AND IZPSID=PSIDIN 

would basically do what your entire function is doing.

Edit: Sorry for the double post.