By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
454,279 Members | 1,629 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 454,279 IT Pros & Developers. It's quick & easy.

Is this legal db2 sql ???

P: n/a
i have a udf that returns a table i then want to set a variable in
using a select on the returned table :

an example (not the real code)..
..
..
..

declare count_t int;

set count_t = (
with TABLEA(col1) as ( select token from table(udf(arg1,arg2) ) as
sp where token is not null)
select count(*) from TABLEA;
);

but i get this error :
sql21043n : The command processed as an sql statement because it was
not a valid command line processor command. during sql processing it
returned : sql0104N an unexpected token "as" was found "
any ideas of how to do this?

thanks in advance.

--annand

Sep 20 '06 #1
Share this Question
Share on Google+
3 Replies


P: n/a
an******@hotmail.com wrote:
i have a udf that returns a table i then want to set a variable in
using a select on the returned table :

an example (not the real code)..
.
.
.

declare count_t int;

set count_t = (
with TABLEA(col1) as ( select token from table(udf(arg1,arg2) ) as
sp where token is not null)
select count(*) from TABLEA;
);

but i get this error :
sql21043n : The command processed as an sql statement because it was
not a valid command line processor command. during sql processing it
returned : sql0104N an unexpected token "as" was found "
WITH is only allowed in SELECT _STATEMENT_ and the query feeding an
INSERT. What you have there is a scalar subquery.
This has nothing to to with the table function.

SET count = (SELECT COUNT(*) FROM TABLE(udf(arg1, arg2)) AS F)
should work
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

IOD Conference
http://www.ibm.com/software/data/ond...ness/conf2006/
Sep 20 '06 #2

P: n/a
set count_t = (
with TABLEA(col1) as ( select token from table(udf(arg1,arg2) ) as
sp where token is not null)
select count(*) from TABLEA;
Where did token come from? Expressions or renamed column names appeas
only in select list cannot use in where clause.
SET count = (SELECT COUNT(*) FROM TABLE(udf(arg1, arg2)) AS F)
This count(*) will include rows which token is null.
I think it is necessary to add "where token is not null".
Or
SET count = (SELECT COUNT(token) FROM TABLE(udf(arg1, arg2)) AS F)

Sep 20 '06 #3

P: n/a
Tonkuma wrote:
set count_t = (
with TABLEA(col1) as ( select token from table(udf(arg1,arg2) ) as
sp where token is not null)
select count(*) from TABLEA;
Where did token come from? Expressions or renamed column names appeas
only in select list cannot use in where clause.
That's most probably a column returned by the table function. Since those
columns were not explicitly named in the above statement, DB2 picks the
names used in the CREATE FUNCTION statement.

--
Knut Stolze
DB2 Information Integration Development
IBM Germany
Sep 21 '06 #4

This discussion thread is closed

Replies have been disabled for this discussion.