CODEWITHSUNDEEP
sqldb2sql-function
Erik S
Erik S

Reputation: 11

DB2 SQL function returning multiple values when I am expecting only one

I am trying to get the location of the last time an item was moved via sql function with the code below. Pretty basic, I'm just trying to grab the max date and time. If I run the sql as a regular select and hard code an item number in ATPRIM I get only one location. But if I create this function and then try to run it and then pass the function an item number I get every occurrence in the history file instead of just the MAX which would be the most recent. Also I have tried a Select Distinct and that did not do anything for me. 

CREATE FUNCTION ERPLXU/F@QAT1(AATPRIM VARCHAR(10))                
      RETURNS CHAR(50)                                            
   LANGUAGE SQL                                                   
   NOT DETERMINISTIC                                              
BEGIN DECLARE F@QAT1 CHAR(50) ;                                   
SET F@QAT1 = ' ' ;                                                
    SELECT ATOGST                                        
     INTO F@QAT1 FROM ERPLXF/QAT as t1                            
     WHERE ATPRIM = AATPRIM                                       
            AND ATDATE = (SELECT MAX(ATDATE) FROM ERPLXF/QAT AS T2
                 WHERE T2.ATPRIM = AATPRIM)                       
            AND ATTIME = (SELECT MAX(ATTIME) FROM ERPLXF/QAT AS T3
                 WHERE T3.ATPRIM = AATPRIM                        
                        AND T3.ATDATE = T1.ATDATE) ;              
RETURN F@QAT1 ;                                                   
END

EDIT: So what I am trying to do is get that location and I got it to work on my iSeries in strsql but the problem is we use a web application called Web Object Wizard (WoW) which lets us use sql to make reports that are more user friendly. Below is what I was trying to get to work but the subquery in the select does not work in WoW so that is where I was trying create a function which we know works in other applications.

SELECT distinct t1.atprim, atdesc, dbtabl, dbdtin, dblife, dblpdp,  
dbcost, dbbas, dbresv, dbyrdp, dbcurr,                              

(select atogst                                                      
from erplxf.qat as t2                                               
where t1.atprim = t2.atprim and atdate = (select max(atdate) from   
erplxf.qat as t3 where t2.atprim = t3.atprim) and attime = (select  
max(attime) from erplxf.qat as t4 where t1.atprim = t4.atprim and   
t1.atdate = t4.atdate)                                              
) as @113_ToLoc                                                     

FROM erplxf.qat as t1 join erplxf.qdb on atassn = dbassn            
where dbrcid = 'DB'                                                   
and dbcurr != 0

So instead of that subquery at the end of the select it would just be 

, erplxu.f@qat1(atprim) as @113_ToLoc

Upvotes: 1

Views: 658

Answers (1)

Mark Barinstein
Mark Barinstein

Reputation: 12314

Try this:

CREATE FUNCTION ERPLXU/F@QAT1(AATPRIM VARCHAR(10))                
RETURNS CHAR(50)                                            
LANGUAGE SQL                                                                                                 
RETURN                                            
SELECT ATOGST                                        
FROM ERPLXF/QAT                      
WHERE ATPRIM = AATPRIM               
ORDER BY ATDATE DESC, ATTIME DESC
FETCH FIRST 1 ROW ONLY;

Upvotes: 1

Related Questions