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

select from table function where argument is a result set

P: n/a
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

Sep 14 '06 #1
Share this Question
Share on Google+
6 Replies


P: n/a
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
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

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

P: n/a
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
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

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

P: n/a
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:
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
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

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

P: n/a
jefftyzzer wrote:
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:
>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/
Sep 15 '06 #5

P: n/a
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:
jefftyzzer wrote:
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:
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/
Sep 15 '06 #6

P: n/a
Eugene F wrote:
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/
Sep 15 '06 #7

This discussion thread is closed

Replies have been disabled for this discussion.