CODEWITHSUNDEEP
sqldb2
Prateek Narendra
Prateek Narendra

Reputation: 1937

DB2 SQL Error in creating a new function with params and returning a table

I wrote a simple query and it works. I want to make it a function. But it gives me an error -

SQL Error [42601]: An unexpected token "DISTINCT" was found following "BEGIN
 RETURN SELECT".  Expected tokens may include:  "(".. SQLCODE=-104, SQLSTATE=42601, DRIVER=4.26.14

The function is - 

CREATE OR REPLACE FUNCTION CSE532.NEIGHBOR_LIST (IN_ZIP VARCHAR(5))
RETURNS TABLE (NEIGHBOR VARCHAR(5))
BEGIN
    RETURN SELECT DISTINCT substr(b.GEOID10,1,5) FROM CSE532.USZIP a, CSE532.USZIP b WHERE db2gse.st_intersects(a.SHAPE,b.SHAPE) = 1 AND substr(a.GEOID10,1,5) = IN_ZIP;
END

All the columns exist

Upvotes: 0

Views: 273

Answers (1)

Paul Vernon
Paul Vernon

Reputation: 3901

Remove the BEGIN and END

CREATE OR REPLACE FUNCTION CSE532.NEIGHBOR_LIST (IN_ZIP VARCHAR(5))
RETURNS TABLE (NEIGHBOR VARCHAR(5))
    RETURN SELECT DISTINCT substr(b.GEOID10,1,5) FROM CSE532.USZIP a, CSE532.USZIP b WHERE db2gse.st_intersects(a.SHAPE,b.SHAPE) = 1 AND substr(a.GEOID10,1,5) = IN_ZIP

or use BEGIN ATOMIC

CREATE OR REPLACE FUNCTION CSE532.NEIGHBOR_LIST (IN_ZIP VARCHAR(5))
RETURNS TABLE (NEIGHBOR VARCHAR(5))
BEGIN ATOMIC
    RETURN SELECT DISTINCT substr(b.GEOID10,1,5) FROM CSE532.USZIP a, CSE532.USZIP b WHERE db2gse.st_intersects(a.SHAPE,b.SHAPE) = 1 AND substr(a.GEOID10,1,5) = IN_ZIP;
END

Upvotes: 1

Related Questions