select from table function where argument is a result set

I am trying to figure out a single select statement from a table
function f(x) when the function's argument is fed from another result
set, like

SELECT ... FROM TABLE( f(x) ) a

where x is a result set from another select (or subselect) as SELECT x
FROM b

Any idea?

Thanks

Eugene F wrote:

Quote:

I am trying to figure out a single select statement from a table
function f(x) when the function's argument is fed from another result
set, like
>
SELECT ... FROM TABLE( f(x) ) a
>
where x is a result set from another select (or subselect) as SELECT x
FROM b

SELECT ... FROM B, TABLE(f(B.x)) a

--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

IOD Conference
http://www.ibm.com/software/data/ond...ness/conf2006/

Great,

I was trying almost the same but
SELECT ... FROM TABLE(f(B.x)) A, B

and it failed with SQL0206N "B.X" is not valid in the context where it
is used. SQLSTATE=42703, why?

Serge Rielau wrote:

Quote:

Eugene F wrote:

Quote:

I am trying to figure out a single select statement from a table
function f(x) when the function's argument is fed from another result
set, like

SELECT ... FROM TABLE( f(x) ) a

where x is a result set from another select (or subselect) as SELECT x
FROM b

SELECT ... FROM B, TABLE(f(B.x)) a
>
>
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab
>
IOD Conference
http://www.ibm.com/software/data/ond...ness/conf2006/

In answer to a previous posting of mine, Serge made the comment that
"...SQL parses left to right...)." I believe that is why you got the
error you did--the parser comes across the reference to B.x before it
ever sees B.

--Jeff

Eugene F wrote:

Quote:

Great,
>
I was trying almost the same but
SELECT ... FROM TABLE(f(B.x)) A, B
>
and it failed with SQL0206N "B.X" is not valid in the context where it
is used. SQLSTATE=42703, why?
>
>
>
>
Serge Rielau wrote:

Quote:

Eugene F wrote:

Quote:

I am trying to figure out a single select statement from a table
function f(x) when the function's argument is fed from another result
set, like
>
SELECT ... FROM TABLE( f(x) ) a
>
where x is a result set from another select (or subselect) as SELECT x
FROM b

SELECT ... FROM B, TABLE(f(B.x)) a

--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

IOD Conference
http://www.ibm.com/software/data/ond...ness/conf2006/

jefftyzzer wrote:

Quote:

In answer to a previous posting of mine, Serge made the comment that
"...SQL parses left to right...)." I believe that is why you got the
error you did--the parser comes across the reference to B.x before it
ever sees B.
>
--Jeff
>
>
Eugene F wrote:

Quote:

>Great,
>>
>I was trying almost the same but
>SELECT ... FROM TABLE(f(B.x)) A, B
>>
>and it failed with SQL0206N "B.X" is not valid in the context where it
>is used. SQLSTATE=42703, why?
>>
>>
>>
>>
>Serge Rielau wrote:

Quote:

>>Eugene F wrote:
>>>I am trying to figure out a single select statement from a table
>>>function f(x) when the function's argument is fed from another result
>>>set, like
>>>>
>>>SELECT ... FROM TABLE( f(x) ) a
>>>>
>>>where x is a result set from another select (or subselect) as SELECT x
>>>FROM b
>>SELECT ... FROM B, TABLE(f(B.x)) a

Correct.

--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

IOD Conference
http://www.ibm.com/software/data/ond...ness/conf2006/

I didn't know that, my understanding was, the parser goes through the
entire SQL statement and then the references to the objects being
accessed, including correlations, are resolved by the compiler
afterwards.
But thank you anyways, I got the solution now.

-Eugene

Serge Rielau wrote:

Quote:

jefftyzzer wrote:

Quote:

In answer to a previous posting of mine, Serge made the comment that
"...SQL parses left to right...)." I believe that is why you got the
error you did--the parser comes across the reference to B.x before it
ever sees B.

--Jeff

Eugene F wrote:

Quote:

Great,
>
I was trying almost the same but
SELECT ... FROM TABLE(f(B.x)) A, B
>
and it failed with SQL0206N "B.X" is not valid in the context where it
is used. SQLSTATE=42703, why?
>
>
>
>
Serge Rielau wrote:
>Eugene F wrote:
>>I am trying to figure out a single select statement from a table
>>function f(x) when the function's argument is fed from another result
>>set, like
>>>
>>SELECT ... FROM TABLE( f(x) ) a
>>>
>>where x is a result set from another select (or subselect) as SELECT x
>>FROM b
>SELECT ... FROM B, TABLE(f(B.x)) a

Correct.
>
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab
>
IOD Conference
http://www.ibm.com/software/data/ond...ness/conf2006/

Eugene F wrote:

Quote:

I didn't know that, my understanding was, the parser goes through the
entire SQL statement and then the references to the objects being
accessed, including correlations, are resolved by the compiler
afterwards.
But thank you anyways, I got the solution now.

What the parser does is secondary here.
What ANSI SQL demands is what matters.

For general interest DB2 for LUW uses the Starburst SQL Compiler since
DB2 V2
http://www.almaden.ibm.com/cs/starwinds/homepage.html
There is no parse tree for SQL in DB2 for LUW.
The relational algebra graph (called Query Graph Model or QGM) is built
as DB2 parses.

Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

IOD Conference
http://www.ibm.com/software/data/ond...ness/conf2006/

select from table function where argument is a result set

re: select from table function where argument is a result set

re: select from table function where argument is a result set

re: select from table function where argument is a result set

re: select from table function where argument is a result set

re: select from table function where argument is a result set

re: select from table function where argument is a result set

Similar DB2 Database bytes

/bytes/development

/bytes/it

/bytes/about