Is this legal db2 sql ???

[annand00]

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

[Serge Rielau]

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/

[Tonkuma]

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)

[Knut Stolze]

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