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

Question

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

What tool do you use to run this statement? Change the statement separator in your tool to, let’s say, @, place it at the end of the statement and try again. — Mark Barinstein
– Mark Barinstein, Commented Apr 5, 2020 at 6:07
@mark-barinstein DBeaver is the gui I use. Let me try with your suggestion. — Prateek Narendra
– Prateek Narendra, Commented Apr 5, 2020 at 8:44

Paul Vernon · Accepted Answer · 2020-04-07 20:43:13Z

1

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

answered Apr 7, 2020 at 20:43

Paul Vernon

3,9212 gold badges12 silver badges23 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

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

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related