SQL Question - subquery on a combination of columns
I come from an Oracle background, and I am just getting to grips with
some of the different Informix syntax. I have worked out most things,
but I can't seem to do a subquery on a combination of columns.
As an example, if I have two tables:
table1(col_a, col_b, ...)
table2(col_x, col_y, ...)
I want to select all rows of tabe1 where the combiantion of col_a and
col_b exist as col_x and col_y in table2.
In Oracle I could write a query like:
select * from table1
where (col_a, col_b) in (
select col_x, col_y from table2
);
In Informix, this syntax is rejected. Is it possible to do this sort
of query in Informix?
Note that I am hoping that there is just a different syntax for doing
the same sort of subquery on multiple columns. I know that in the
simple example above I could have rewritten the query just by joining
the tables in the main select, or by having a correlated EXISTS clause
instead of the IN subquery.
Sent via Deja.com http://www.deja.com/
Share what you know. Learn what you don't.
neil_smith@my-deja.com wrote:
>
> I come from an Oracle background, and I am just getting to grips with
> some of the different Informix syntax. I have worked out most things,
> but I can't seem to do a subquery on a combination of columns.
>
> As an example, if I have two tables:
>
> table1(col_a, col_b, ...)
> table2(col_x, col_y, ...)
>
> I want to select all rows of tabe1 where the combiantion of col_a and
> col_b exist as col_x and col_y in table2.
>
> In Oracle I could write a query like:
>
> select * from table1
> where (col_a, col_b) in (
> select col_x, col_y from table2
> );>
> In Informix, this syntax is rejected. Is it possible to do this sort
> of query in Informix?
>
> Note that I am hoping that there is just a different syntax for doing
> the same sort of subquery on multiple columns. I know that in the
> simple example above I could have rewritten the query just by joining
> the tables in the main select, or by having a correlated EXISTS clause
> instead of the IN subquery.
That answers the question doesn't it? ;-) The Oracle syntax you refer
to is not ANSI SQL. Since Informix convened the original ANSI SQL
committee it has always refrained from non-standard extensions with
very few exceptions. Indeed the 7.3x optimizer hints was a major soul
search for the folk at Menlo Park, years in the making, and even then
they implemented them in ANSI standard comments!
Art S. Kagel
Art S. Kagel wrote:
>
> neil_smith@my-deja.com wrote:
> >
> > [snip]
> > In Oracle I could write a query like:
> >
> > select * from table1
> > where (col_a, col_b) in (
> > select col_x, col_y from table2
> > );> >
> > In Informix, this syntax is rejected. Is it possible to do this sort
> > of query in Informix?
> >
> > Note that I am hoping that there is just a different syntax for doing
> > the same sort of subquery on multiple columns. I know that in the
> > simple example above I could have rewritten the query just by joining
> > the tables in the main select, or by having a correlated EXISTS clause
> > instead of the IN subquery.
>
> That answers the question doesn't it? ;-) The Oracle syntax you refer
> to is not ANSI SQL. Since Informix convened the original ANSI SQL
> committee it has always refrained from non-standard extensions with
> very few exceptions. Indeed the 7.3x optimizer hints was a major soul
> search for the folk at Menlo Park, years in the making, and even then
> they implemented them in ANSI standard comments!
>
Not ANSI SQL??? No way. Per ANSI X3.135-1992, section 8.4:
<in predicate> ::=
<row value constructor> [ NOT ] IN <in predicate value>
<in predicate value> ::=
<table subquery> | <left paren> <in value list> <right paren>
True, there is a leveling restriction on <row value constructor>. For
Intermediate SQL, it "shall not contain more than one
<row value constructor element>" (sec. 7.1).