SELECT statement in FROM Clause
A user moving from Oracle/MySQL asked whether Informix supports a SELECT in the FROM clause (inline/derived table), since his query using a "virtual table" alias failed. Replies: that syntax wasn't supported at the time (derived tables were said to be coming in IDS 11); workarounds offered were TABLE(MULTISET(SELECT ...)) with SERIAL columns cast to INT, stored procedures, or (Art Kagel's advice) rewriting as a plain multi-table join or a temp table, which is usually faster. The poster rewrote the query with a correlated subquery in the select list and considered it solved; the rest of the thread drifted into a debate about "thinking like a programmer" when writing SQL.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
Hi,
I am new to Informix (have migrated from MySQL, Oracle world), and was
wondering if SELECT statements are allowed in FROM clause (the select
statement results in the from acts as sort of virtual table) as they are in
Oracle & MySQL.
An example query is:
SELECT u.id AS user_id, u.user_name AS user_name,
count((select call_status from call_log cl where cl.id = vt.call_id and
cl.call_status = 0)) AS received_desk,
FROM usr u,
(select cl.id call_id, rcpt.recipient_id recipient_id
from call_log cl, rcpt_ac_req rcpt
where cl.ac_req_id = rcpt.id
and rcpt.create_time >= 111111111) vt
WHERE u.id = vt.recipient_id
GROUP BY u.id, u.user_name
The query in the example bombs first on the count statement in select (i think
i can overcome that by doing select count(call_status)) but the main problem
is the virtual table (vt) in the from clause.
In reading the SQL guide, i ran into something like a MULTISET data type that
one can potentially use but the limitation there are that you can't have
SERIAL columns which i do have in there.
If this form of SQL is not allowed, let me know if there are alternatives to
achieve the same result.
Maybe could you to use store procedures for to replace "sub selects".
Saludos!
Norbeto.
----- Original Message -----
From: "ANKUR SHAH" <ankurdotshah@gmail.com>
To: <ids@iiug.org>
Sent: Tuesday, January 16, 2007 2:41 PM
Subject: SELECT statement in FROM Clause [8214]
>
> Hi,
>
> I am new to Informix (have migrated from MySQL, Oracle world), and was
> wondering if SELECT statements are allowed in FROM clause (the select
> statement results in the from acts as sort of virtual table) as they are
> in
> Oracle & MySQL.
>
> An example query is:
> SELECT u.id AS user_id, u.user_name AS user_name,>
> count((select call_status from call_log cl where cl.id = vt.call_id and
> cl.call_status = 0)) AS received_desk,
> FROM usr u,
>
> (select cl.id call_id, rcpt.recipient_id recipient_id
>
> from call_log cl, rcpt_ac_req rcpt
>
> where cl.ac_req_id = rcpt.id
>
> and rcpt.create_time >= 111111111) vt
> WHERE u.id = vt.recipient_id
> GROUP BY u.id, u.user_name
>
> The query in the example bombs first on the count statement in select (i
> think
> i can overcome that by doing select count(call_status)) but the main
> problem
> is the virtual table (vt) in the from clause.
>
> In reading the SQL guide, i ran into something like a MULTISET data type
> that
> one can potentially use but the limitation there are that you can't have
>
> SERIAL columns which i do have in there.
>
> If this form of SQL is not allowed, let me know if there are
> alternatives to
> achieve the same result.
>
>
> ************************************************************************
> *******
> Forum Note: Use "Reply" to post a response in the discussion forum.
> > I am new to Informix (have migrated from MySQL, Oracle world), and was
> > wondering if SELECT statements are allowed in FROM clause (the select
> > statement results in the from acts as sort of virtual table) as they are
> > in Oracle & MySQL.
> > The query in the example bombs first on the count statement in select (i
> > think i can overcome that by doing select count(call_status)) but the main
> > problem is the virtual table (vt) in the from clause.
> > In reading the SQL guide, i ran into something like a MULTISET data type
> > that one can potentially use but the limitation there are that you can't
have
> > SERIAL columns which i do have in there.
Just cast the serial keys to INTs.
SELECT a
FROM TABLE(MULTISET(SELECT xr_serial_no::INT AS a
FROM xrefr
WHERE xr_serial_no > 3000000))
> > If this form of SQL is not allowed, let me know if there are
> > alternatives to achieve the same result.
Virtual tables are a crutch. Most such queries can be rewritten as simple joins
and the rest as select into a temp table and join to the temp table. Always
the results are more efficient and complete more quickly. I think this catches
the gist of yours:
SELECT u.id AS user_id, u.user_name AS user_name, count(*)
FROM usr u, call_log cl, rcpt_ac_req rcpt
WHERE u.id = rcpt.recipient_id
AND cl.ac_req_id = rcpt.id
AND rcpt.create_time >= 111111111
GROUP BY user_id, user_name;
Advice: stop thinking like a programmer. SQL is a user's language, not a
programming language. Software logic thinking formulates queries that are
overly complex and inefficient. If you can state the query in simple natural
language (like English) you can usually formulate a simple SQL query from that
statement that works better.
Art S. Kagel
----- Original Message -----
From: Ankur Shah <ids@iiug.org>
At: 1/16 14:50:56
Hi,
I am new to Informix (have migrated from MySQL, Oracle world), and was
wondering if SELECT statements are allowed in FROM clause (the select
statement results in the from acts as sort of virtual table) as they are in
Oracle & MySQL.
An example query is:
SELECT u.id AS user_id, u.user_name AS user_name,
count((select call_status from call_log cl where cl.id = vt.call_id and
cl.call_status = 0)) AS received_desk,
FROM usr u,
(select cl.id call_id, rcpt.recipient_id recipient_id
from call_log cl, rcpt_ac_req rcpt
where cl.ac_req_id = rcpt.id
and rcpt.create_time >= 111111111) vt
WHERE u.id = vt.recipient_id
GROUP BY u.id, u.user_name
The query in the example bombs first on the count statement in select (i think
i can overcome that by doing select count(call_status)) but the main problem
is the virtual table (vt) in the from clause.
In reading the SQL guide, i ran into something like a MULTISET data type that
one can potentially use but the limitation there are that you can't have
SERIAL columns which i do have in there.
If this form of SQL is not allowed, let me know if there are alternatives to
achieve the same result.
*******************************************************************************
Forum Note: Use "Reply" to post a response in the discussion forum.
For a price ..... no .. I don't have it ... just wanted to give you a hard
time ...
"ART KAGEL, BLOOMBERG/ 731 LEXIN" <kagel@bloomberg.net>
Sent by: ids-bounces@iiug.org
01/16/2007 04:13 PM
Please respond to
ids@iiug.org
To
ids@iiug.org
cc
Subject
Re: SELECT statement in FROM Clause [8217]
Virtual tables are a crutch. Most such queries can be rewritten as simple
joins
and the rest as select into a temp table and join to the temp table.
Always
the results are more efficient and complete more quickly. I think this
catches
the gist of yours:
SELECT u.id AS user_id, u.user_name AS user_name, count(*)
FROM usr u, call_log cl, rcpt_ac_req rcpt
WHERE u.id = rcpt.recipient_id
AND cl.ac_req_id = rcpt.id
AND rcpt.create_time >= 111111111
GROUP BY user_id, user_name;
Advice: stop thinking like a programmer. SQL is a user's language, not a
programming language. Software logic thinking formulates queries that are
overly complex and inefficient. If you can state the query in simple
natural
language (like English) you can usually formulate a simple SQL query from
that
statement that works better.
Art S. Kagel
----- Original Message -----
From: Ankur Shah <ids@iiug.org>
At: 1/16 14:50:56
Hi,
I am new to Informix (have migrated from MySQL, Oracle world), and was
wondering if SELECT statements are allowed in FROM clause (the select
statement results in the from acts as sort of virtual table) as they are
in
Oracle & MySQL.
An example query is:
SELECT u.id AS user_id, u.user_name AS user_name,
count((select call_status from call_log cl where cl.id = vt.call_id and
cl.call_status = 0)) AS received_desk,
FROM usr u,
(select cl.id call_id, rcpt.recipient_id recipient_id
from call_log cl, rcpt_ac_req rcpt
where cl.ac_req_id = rcpt.id
and rcpt.create_time >= 111111111) vt
WHERE u.id = vt.recipient_id
GROUP BY u.id, u.user_name
The query in the example bombs first on the count statement in select (i
think
i can overcome that by doing select count(call_status)) but the main
problem
is the virtual table (vt) in the from clause.
In reading the SQL guide, i ran into something like a MULTISET data type
that
one can potentially use but the limitation there are that you can't have
SERIAL columns which i do have in there.
If this form of SQL is not allowed, let me know if there are alternatives
to
achieve the same result.
*******************************************************************************
Forum Note: Use "Reply" to post a response in the discussion forum.
*******************************************************************************
Forum Note: Use "Reply" to post a response in the discussion forum.
sorry about that ... responded to the wrong note ..
"Peter_Logan@spartanstores.com" <Peter_Logan@spartanstores.com>
Sent by: ids-bounces@iiug.org
01/16/2007 04:23 PM
Please respond to
ids@iiug.org
To
ids@iiug.org
cc
Subject
Re: SELECT statement in FROM Clause [8218]
For a price ..... no .. I don't have it ... just wanted to give you a hard
time ...
"ART KAGEL, BLOOMBERG/ 731 LEXIN" <kagel@bloomberg.net>
Sent by: ids-bounces@iiug.org
01/16/2007 04:13 PM
Please respond to
ids@iiug.org
To
ids@iiug.org
cc
Subject
Re: SELECT statement in FROM Clause [8217]
Virtual tables are a crutch. Most such queries can be rewritten as simple
joins
and the rest as select into a temp table and join to the temp table.
Always
the results are more efficient and complete more quickly. I think this
catches
the gist of yours:
SELECT u.id AS user_id, u.user_name AS user_name, count(*)
FROM usr u, call_log cl, rcpt_ac_req rcpt
WHERE u.id = rcpt.recipient_id
AND cl.ac_req_id = rcpt.id
AND rcpt.create_time >= 111111111
GROUP BY user_id, user_name;
Advice: stop thinking like a programmer. SQL is a user's language, not a
programming language. Software logic thinking formulates queries that are
overly complex and inefficient. If you can state the query in simple
natural
language (like English) you can usually formulate a simple SQL query from
that
statement that works better.
Art S. Kagel
----- Original Message -----
From: Ankur Shah <ids@iiug.org>
At: 1/16 14:50:56
Hi,
I am new to Informix (have migrated from MySQL, Oracle world), and was
wondering if SELECT statements are allowed in FROM clause (the select
statement results in the from acts as sort of virtual table) as they are
in
Oracle & MySQL.
An example query is:
SELECT u.id AS user_id, u.user_name AS user_name,
count((select call_status from call_log cl where cl.id = vt.call_id and
cl.call_status = 0)) AS received_desk,
FROM usr u,
(select cl.id call_id, rcpt.recipient_id recipient_id
from call_log cl, rcpt_ac_req rcpt
where cl.ac_req_id = rcpt.id
and rcpt.create_time >= 111111111) vt
WHERE u.id = vt.recipient_id
GROUP BY u.id, u.user_name
The query in the example bombs first on the count statement in select (i
think
i can overcome that by doing select count(call_status)) but the main
problem
is the virtual table (vt) in the from clause.
In reading the SQL guide, i ran into something like a MULTISET data type
that
one can potentially use but the limitation there are that you can't have
SERIAL columns which i do have in there.
If this form of SQL is not allowed, let me know if there are alternatives
to
achieve the same result.
*******************************************************************************
Forum Note: Use "Reply" to post a response in the discussion forum.
*******************************************************************************
Forum Note: Use "Reply" to post a response in the discussion forum.
*******************************************************************************
Forum Note: Use "Reply" to post a response in the discussion forum.
Thx for all the response. Seems like what i wanted is not possible and hence
had to write the query with a simple enough work around.
SELECT u.id AS user_id, u.user_name AS user_name,
((select count(call_status)
from call_log cl, rcpt_ac_req rcpt
where cl.ac_req_id = rcpt.id
and rcpt.recipient_id = u.id
and cl.call_status = 0
and rcpt.create_time >= 1165458808779
and rcpt.create_time <= 1166143708407)) AS received_desk,
FROM USR As u
GROUP BY u.id, u.user_name
HI,
What you are asking for will be available in Informix IDS 11 which will be
called derived tables; the ability to use a select in the FROM clause for
example.
Until then, you will have to wrtite your queries differently.
Khaled BENTEBAL
ConsultiX
Selon ANKUR SHAH <ankurdotshah@gmail.com>:
>
> Thx for all the response. Seems like what i wanted is not possible and hence
> had to write the query with a simple enough work around.
>
> SELECT u.id AS user_id, u.user_name AS user_name,
> ((select count(call_status)>
> from call_log cl, rcpt_ac_req rcpt
>
> where cl.ac_req_id = rcpt.id
>
> and rcpt.recipient_id = u.id
>
> and cl.call_status = 0
>
> and rcpt.create_time >= 1165458808779
>
> and rcpt.create_time <= 1166143708407)) AS received_desk,
> FROM USR As u
> GROUP BY u.id, u.user_name
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
I disagree - I would say he needs to START thinking like a programmer.
I consider myself a programmer and I would never think of such a twisted way
of querying data. That's the kind of thing I would expect from an accountant
(or some other non-programmer type) who took a week-long class in SQL and
started calling themselves a programmer.
I do agree with your point about stating the query in a more natural language
first, but that is what good programmers do, IMO.
"ART KAGEL, BLOOMBERG/ 731 LEXIN" <kagel@bloomberg.net> wrote:
Virtual tables are a crutch. Most such queries can be rewritten as simple
joins
and the rest as select into a temp table and join to the temp table. Always
the results are more efficient and complete more quickly. I think this catches
the gist of yours:
SELECT u.id AS user_id, u.user_name AS user_name, count(*)
FROM usr u, call_log cl, rcpt_ac_req rcpt
WHERE u.id = rcpt.recipient_id
AND cl.ac_req_id = rcpt.id
AND rcpt.create_time >= 111111111
GROUP BY user_id, user_name;
Advice: stop thinking like a programmer. SQL is a user's language, not a
programming language. Software logic thinking formulates queries that are
overly complex and inefficient. If you can state the query in simple natural
language (like English) you can usually formulate a simple SQL query from that
statement that works better.
Art S. Kagel
----- Original Message -----
From: Ankur Shah
At: 1/16 14:50:56
Hi,
I am new to Informix (have migrated from MySQL, Oracle world), and was
wondering if SELECT statements are allowed in FROM clause (the select
statement results in the from acts as sort of virtual table) as they are in
Oracle & MySQL.
An example query is:
SELECT u.id AS user_id, u.user_name AS user_name,
count((select call_status from call_log cl where cl.id = vt.call_id and
cl.call_status = 0)) AS received_desk,
FROM usr u,
(select cl.id call_id, rcpt.recipient_id recipient_id
from call_log cl, rcpt_ac_req rcpt
where cl.ac_req_id = rcpt.id
and rcpt.create_time >= 111111111) vt
WHERE u.id = vt.recipient_id
GROUP BY u.id, u.user_name
The query in the example bombs first on the count statement in select (i think
i can overcome that by doing select count(call_status)) but the main problem
is the virtual table (vt) in the from clause.
In reading the SQL guide, i ran into something like a MULTISET data type that
one can potentially use but the limitation there are that you can't have
SERIAL columns which i do have in there.
If this form of SQL is not allowed, let me know if there are alternatives to
achieve the same result.
*******************************************************************************
Forum Note: Use "Reply" to post a response in the discussion forum.
*******************************************************************************
Forum Note: Use "Reply" to post a response in the discussion forum.
---------------------------------
TV dinner still cooling?
Check out "Tonight's Picks" on Yahoo! TV.
OK, I didn't want to get into my whole philosophy of how one properly switches
from one programming language to another. I'll try a brief one here, but I
continue hold that SQL is not a 'programmer's language' it is a 'user's
language'. Perhaps that's better, as you are correct it is used to 'program' an
RDBMS query so formally it is a programming language, but my point really was
that it's not one designed for programmers.
I believe that to properly use mulitple programming languages one must immerse
oneself in the gestalt of the language's usage. Not only must one write the
syntax of the language correctly, but one must format the code as in such a way
that is usual and expected by a practitioner of that particular language. One
must think 'C' to properly write in 'C', similarly for FORTRAN, COBOL, C++,
Java, or SQL. I call this 'putting on my <insert language here> head' and
sometimes, especially when I'm having difficulty making the switch, go through
the mental exercise of imagining removing one head and attaching another.
For example, how one writes a hash table memory cache in C is very different
from how one would do that in C++ (ignoring the STL tools already available for
now) or in FORTRAN.
The same goes for writing SQL. As a programmer, when I'm thinking like a
programmer, to write an outstanding orders detail report, I might say: "I need
to select each row from the customer table. For each of those rows I need to
find all of that customer's orders that have not yet shipped. For each order I
need to fetch the total value of the order. I also need a percent complete in
order to account for orders partially filled and shipped." This logic will
either result in a program with several nested loops and several independent
SQL
statements glued together in code, or, if I'm experienced enough to write the
SQL before the code, in a complex SQL with nested queries or even virtual
tables.
On the other hand, if I put on my user/SQL head, I can say instead: "I want to
select the customer information, open orders header information, and the sum of
the value of the total order and the unshipped portion of the order from the
customer, orders, and order detail tables." This results and flows into a nice
neat flat three way join SQL statement that may be an order of magnitude or
more
faster than the first statement(s).
Now what I call "Kagel's First Law of SQL" (GOOGLE it if you want) always
applies (or it wouldn't be much of a law ;-( ) so I'm going to try several
versions of the SQL including the ugly and horribly complex one and perhaps
even
the nested independent queries (if I'm embedding this in C anyway) to see what
gives me better and more consistent performance.
Hope that clears it up.
Art S. Kagel
----- Original Message -----
From: Danny Wright <ids@iiug.org>
At: 1/30 1:53:35
I disagree - I would say he needs to START thinking like a programmer.
I consider myself a programmer and I would never think of such a twisted way
of querying data. That's the kind of thing I would expect from an accountant
(or some other non-programmer type) who took a week-long class in SQL and
started calling themselves a programmer.
I do agree with your point about stating the query in a more natural language
first, but that is what good programmers do, IMO.
"ART KAGEL, BLOOMBERG/ 731 LEXIN" <kagel@bloomberg.net> wrote:
Virtual tables are a crutch. Most such queries can be rewritten as simple
joins
and the rest as select into a temp table and join to the temp table. Always
the results are more efficient and complete more quickly. I think this catches
the gist of yours:
SELECT u.id AS user_id, u.user_name AS user_name, count(*)
FROM usr u, call_log cl, rcpt_ac_req rcpt
WHERE u.id = rcpt.recipient_id
AND cl.ac_req_id = rcpt.id
AND rcpt.create_time >= 111111111
GROUP BY user_id, user_name;
Advice: stop thinking like a programmer. SQL is a user's language, not a
programming language. Software logic thinking formulates queries that are
overly complex and inefficient. If you can state the query in simple natural
language (like English) you can usually formulate a simple SQL query from that
statement that works better.
Art S. Kagel
----- Original Message -----
From: Ankur Shah
At: 1/16 14:50:56
Hi,
I am new to Informix (have migrated from MySQL, Oracle world), and was
wondering if SELECT statements are allowed in FROM clause (the select
statement results in the from acts as sort of virtual table) as they are in
Oracle & MySQL.
An example query is:
SELECT u.id AS user_id, u.user_name AS user_name,
count((select call_status from call_log cl where cl.id = vt.call_id and
cl.call_status = 0)) AS received_desk,
FROM usr u,
(select cl.id call_id, rcpt.recipient_id recipient_id
from call_log cl, rcpt_ac_req rcpt
where cl.ac_req_id = rcpt.id
and rcpt.create_time >= 111111111) vt
WHERE u.id = vt.recipient_id
GROUP BY u.id, u.user_name
The query in the example bombs first on the count statement in select (i think
i can overcome that by doing select count(call_status)) but the main problem
is the virtual table (vt) in the from clause.
In reading the SQL guide, i ran into something like a MULTISET data type that
one can potentially use but the limitation there are that you can't have
SERIAL columns which i do have in there.
If this form of SQL is not allowed, let me know if there are alternatives to
achieve the same result.
*******************************************************************************
Forum Note: Use "Reply" to post a response in the discussion forum.
*******************************************************************************
Forum Note: Use "Reply" to post a response in the discussion forum.
---------------------------------
TV dinner still cooling?
Check out "Tonight's Picks" on Yahoo! TV.
*******************************************************************************
Forum Note: Use "Reply" to post a response in the discussion forum.