New SKIP, LIMIT of SELECT are afe
Jean Sagi asked whether the new SKIP/LIMIT projection clauses in IDS 10.00.xC3 could be used when writing results INTO TEMP, since SELECT FIRST n ... INTO TEMP had previously failed with error -944 ("Cannot use 'first' in this context"). IBM staff (Nita Dembla, Vinayak Shenoi) confirmed that SELECT SKIP m LIMIT n ... [ORDER BY] INTO TEMP is allowed, and that INSERT INTO ... SELECT is an alternative; the capability was added in 10.00.xC3, so it fails on earlier builds like 10.00.TC1. Art Kagel also noted the related new ROWNUM pseudo-column for filtering row ranges.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
I noticed this from "New Features in Dynamic Server, Version 10.00.xC3" of http://publib.boulder.ibm.com/infocenter/ids9help/index.jsp?topic=/com.ibm.sqls. doc/sqls02.htm -------------------------------------------------------------------- New Projection Clause syntax of the SELECT statement can control the number of qualifying rows in the result set of a query: * SKIP offset * LIMIT max The SKIP offset clause excludes the first offset qualifying rows from the result set, for offset an integer. If you also include the LIMIT max clause (where LIMIT is a keyword synonym for FIRST), no more than max rows are returned. The offset and max values can be specified as literal integers in the SERIAL8 range, or as host variables, or as local SPL variables. You can save the result set as a collection-derived table (CDT). If you also include the ORDER BY clause, the qualifying rows are first arranged by the ORDER BY specification before the SKIP offset and LIMIT max clauses are applied. -------------------------------------------------------------------- Are these type of querys impossible if inserted into a temporary table? J. PD: I don't have an IDS 10.00.xc3 to test myself.
Well limit is a synonym for first as it is stated below and querys
like this never worked:
select first 10 tabid, tabname[1,20]
from systables
into temp tx_first10_sys
It gives:
-944 Cannot use "first" in this context.
This statement uses FIRST N inside a subquery. This action is not supported.
Review the use of FIRST N and check that it is applied only to the outer main
query SELECT clause.
Which personally consider is very silly from the engine point of view.
J.
-----Original Message-----
From: "Gilker, Charles" <CGilker@milbank.com>
To: "Jean Sagi " <jeansagi@myrealbox.com>
Date: Fri, 5 Aug 2005 09:15:04 -0400
Subject: RE: New SKIP, LIMIT of SELECT are afe [5557]
Why would you think that it wouldn't apply to temp tables?
-----Original Message-----
From: forum.subscriber@iiug.org [mailto:forum.subscriber@iiug.org] On
Behalf Of Jean Sagi
Sent: Friday, August 05, 2005 7:57 AM
To: ids@iiug.org
Subject: New SKIP, LIMIT of SELECT are afe [5557]
I noticed this from "New Features in Dynamic Server, Version 10.00.xC3"
of
http://publib.boulder.ibm.com/infocenter/ids9help/index.jsp?topic=/com.i
bm.sqls.doc/sqls02.htm
--------------------------------------------------------------------
New Projection Clause syntax of the SELECT statement can control the
number of qualifying rows in the result set of a query:
* SKIP offset
* LIMIT max
The SKIP offset clause excludes the first offset qualifying rows from
the result set, for offset an integer. If you also include the LIMIT max
clause (where LIMIT is a keyword synonym for FIRST), no more than max
rows are returned. The offset and max values can be specified as literal
integers in the SERIAL8 range, or as host variables, or as local SPL
variables. You can save the result set as a collection-derived table
(CDT).
If you also include the ORDER BY clause, the qualifying rows are first
arranged by the ORDER BY specification before the SKIP offset and LIMIT
max clauses are applied.
--------------------------------------------------------------------
Are these type of querys impossible if inserted into a temporary table?
J.
PD: I don't have an IDS 10.00.xc3 to test myself.
=======================================================================
IRS Circular 230 Disclosure: U.S. federal tax advice in the foregoing message
from Milbank, Tweed, Hadley & McCloy LLP is not intended or written to be, and
cannot be used, by any person for the purpose of avoiding tax penalties that
may be imposed regarding the transactions or matters addressed. If any U.S.
federal tax advice contained in this message is used or referred to in
promoting, marketing or recommending of the transactions or matters addressed
(which any person who is not our client with respect to the transactions or
matters addressed should assume to be the case), then (i) such tax advice
should be construed as written in connection with the promotion or marketing
(within the meaning of IRS Circular 230) of the transactions or matters
addressed and (ii) such person should seek advice based on their particular
circumstances from an independent tax advisor.
=======================================================================
This e-mail message may contain legally privileged and/or confidential
information. If you are not the intended recipient(s), or the employee or
agent responsible for delivery of this message to the intended recipient(s),
you are hereby notified that any dissemination, distribution or copying of
this e-mail message is strictly prohibited. If you have received this message
in error, please immediately notify the sender and delete this e-mail message
from your computer.
Jean Sagi
jeansagi@myrealbox.com
jeansagi@gmail.com
--0__=0ABBFAC7DFC7C9118f9e8a93df938690918c0ABBFAC7DFC7C911 Content-type: multipart/alternative; Boundary="1__=0ABBFAC7DFC7C9118f9e8a93df938690918c0ABBFAC7DFC7C911" --1__=0ABBFAC7DFC7C9118f9e8a93df938690918c0ABBFAC7DFC7C911 Content-type: text/plain; charset=US-ASCII Hi, Are these type of queries impossible if inserted into a temporary table? If you intended to ask whether the following type of query allows SKIP and LIMIT clauses.. "SELECT SKIP m LIMIT n * FROM tabname [ORDER BY colname] INTO TEMP temptabname;" Then yes, they are allowed. Alternatively, you can use the INSERT INTO.. SELECT statement to insert into temp table. Thanks, Nita Dembla Software Engineer IBM - Information Management Solutions "Jean Sagi " <jeansagi@myrealb To: ids@iiug.org ox.com> cc: Sent by: Subject: New SKIP, LIMIT of SELECT are afe [5557] forum.subscriber@ iiug.org 08/05/2005 07:56 AM I noticed this from "New Features in Dynamic Server, Version 10.00.xC3" of http://publib.boulder.ibm.com/infocenter/ids9help/index.jsp?topic=/com.ibm.sqls. doc/sqls02.htm -------------------------------------------------------------------- New Projection Clause syntax of the SELECT statement can control the number of qualifying rows in the result set of a query: * SKIP offset * LIMIT max The SKIP offset clause excludes the first offset qualifying rows from the result set, for offset an integer. If you also include the LIMIT max clause (where LIMIT is a keyword synonym for FIRST), no more than max rows are returned. The offset and max values can be specified as literal integers in the SERIAL8 range, or as host variables, or as local SPL variables. You can save the result set as a collection-derived table (CDT). If you also include the ORDER BY clause, the qualifying rows are first arranged by the ORDER BY specification before the SKIP offset and LIMIT max clauses are applied. -------------------------------------------------------------------- Are these type of querys impossible if inserted into a temporary table? J. PD: I don't have an IDS 10.00.xc3 to test myself. --1__=0ABBFAC7DFC7C9118f9e8a93df938690918c0ABBFAC7DFC7C911 Content-type: text/html; charset=US-ASCII Content-Disposition: inline <html><body> <p>Hi,<br> <br> <tt>Are these type of queries impossible if inserted into a temporary table?<br> <br> </tt>If you intended to ask whether the following type of query allows SKIP and LIMIT clauses..<br> <br> <font face="Arial">"SELECT SKIP m LIMIT n * FROM tabname</font><br> <font face="Arial">[ORDER BY colname]</font><br> <font face="Arial"> INTO TEMP temptabname;"</font><br> <br> Then yes, they are allowed.<br> <br> Alternatively, you can use the INSERT INTO.. SELECT statement to insert into temp table.<br> <br> Thanks,<br> Nita Dembla<br> Software Engineer<br> IBM - Information Management Solutions<br> <br> <br> <img src="cid:10__=0ABBFAC7DFC7C9118f9e8a93df938@us.ibm.com" width="16" height="16" alt="Inactive hide details for "Jean Sagi " <jeansagi@myrealbox.com>">"Jean Sagi " <jeansagi@myrealbox.com><br> <br> <br> <table V5DOTBL=true width="100%" border="0" cellspacing="0" cellpadding="0"> <tr valign="top"><td width="1%"><img src="cid:20__=0ABBFAC7DFC7C9118f9e8a93df938@us.ibm.com" border="0" height="1" width="72" alt=""><br> </td><td style="background-image:url(cid:30__=0ABBFAC7DFC7C9118f9e8a93df938@us.ibm.com); background-repeat: no-repeat; " width="1%"><img src="cid:20__=0ABBFAC7DFC7C9118f9e8a93df938@us.ibm.com" border="0" height="1" width="225" alt=""><br> <ul> <ul> <ul> <ul><b><font size="2">"Jean Sagi " <jeansagi@myrealbox.com></font></b><br> <font size="2">Sent by: forum.subscriber@iiug.org</font> <p><font size="2">08/05/2005 07:56 AM</font></ul> </ul> </ul> </ul> </td><td width="100%"><img src="cid:20__=0ABBFAC7DFC7C9118f9e8a93df938@us.ibm.com" border="0" height="1" width="1" alt=""><br> <font size="1" face="Arial"> </font><br> <font size="2"> To: </font><font size="2">ids@iiug.org</font><br> <font size="2"> cc: </font><br> <font size="2"> Subject: </font><font size="2">New SKIP, LIMIT of SELECT are afe [5557]</font></td></tr> </table> <br> <br> <tt>I noticed this from "New Features in Dynamic Server, Version 10.00.xC3" <br> of <br> </tt><tt><a href="http://publib.boulder.ibm.com/infocenter/ids9help/index.jsp?topic=/com.ibm .sqls.doc/sqls02.htm">http://publib.boulder.ibm.com/infocenter/ids9help/index.js p?topic=/com.ibm.sqls.doc/sqls02.htm</a></tt><tt><br> <br> --------------------------------------------------------------------<br> New Projection Clause syntax of the SELECT statement can control the <br> number of qualifying rows in the result set of a query:<br> <br> * SKIP offset<br> * LIMIT max<br> <br> The SKIP offset clause excludes the first offset qualifying rows from <br> the result set, for offset an integer. If you also include the LIMIT max <br> clause (where LIMIT is a keyword synonym for FIRST), no more than max <br> rows are returned. The offset and max values can be specified as literal <br> integers in the SERIAL8 range, or as host variables, or as local SPL <br> variables. You can save the result set as a collection-derived table (CDT).<br> <br> If you also include the ORDER BY clause, the qualifying rows are first <br> arranged by the ORDER BY specification before the SKIP offset and LIMIT <br> max clauses are applied.<br> --------------------------------------------------------------------<br> <br> Are these type of querys impossible if inserted into a temporary table?<br> <br> <br> J.<br> <br> <br> PD: I don't have an IDS 10.00.xc3 to test myself.<br> <br> <br> </tt> </body></html> --1__=0ABBFAC7DFC7C9118f9e8a93df938690918c0ABBFAC7DFC7C911-- --0__=0ABBFAC7DFC7C9118f9e8a93df938690918c0ABBFAC7DFC7C911 Content-type: image/gif; name="graycol.gif" Content-Disposition: inline; filename="graycol.gif" Content-ID: <10__=0ABBFAC7DFC7C9118f9e8a93df938@us.ibm.com> Content-transfer-encoding: base64 R0lGODlhEAAQAKECAMzMzAAAAP///wAAACH5BAEAAAIALAAAAAAQABAAAAIXlI+py+0PopwxUbpu ZRfKZ2zgSJbmSRYAIf4fT3B0aW1pemVkIGJ5IFVsZWFkIFNtYXJ0U2F2ZXIhAAA7 --0__=0ABBFAC7DFC7C9118f9e8a93df938690918c0ABBFAC7DFC7C911 Content-type: image/gif; name="ecblank.gif" Content-Disposition: inline; filename="ecblank.gif" Content-ID: <20__=0ABBFAC7DFC7C9118f9e8a93df938@us.ibm.com> Content-transfer-encoding: base64 R0lGODlhEAABAIAAAAAAAP///yH5BAEAA
Excelent ! J. -----Original Message----- From: Nita Dembla <nita@us.ibm.com> To: "Jean Sagi " <jeansagi@myrealbox.com> Date: Fri, 5 Aug 2005 11:40:29 -0400 Subject: Re: New SKIP, LIMIT of SELECT are afe [5557] Hi, Are these type of queries impossible if inserted into a temporary table? If you intended to ask whether the following type of query allows SKIP and LIMIT clauses.. "SELECT SKIP m LIMIT n * FROM tabname [ORDER BY colname] INTO TEMP temptabname;" Then yes, they are allowed. Alternatively, you can use the INSERT INTO.. SELECT statement to insert into temp table. Thanks, Nita Dembla Software Engineer IBM - Information Management Solutions "Jean Sagi " <jeansagi@myrealb To: ids@iiug.org ox.com> cc: Sent by: Subject: New SKIP, LIMIT of SELECT are afe [5557] forum.subscriber@ iiug.org 08/05/2005 07:56 AM I noticed this from "New Features in Dynamic Server, Version 10.00.xC3" of http://publib.boulder.ibm.com/infocenter/ids9help/index.jsp?topic=/com.ibm.sqls. doc/sqls02.htm -------------------------------------------------------------------- New Projection Clause syntax of the SELECT statement can control the number of qualifying rows in the result set of a query: * SKIP offset * LIMIT max The SKIP offset clause excludes the first offset qualifying rows from the result set, for offset an integer. If you also include the LIMIT max clause (where LIMIT is a keyword synonym for FIRST), no more than max rows are returned. The offset and max values can be specified as literal integers in the SERIAL8 range, or as host variables, or as local SPL variables. You can save the result set as a collection-derived table (CDT). If you also include the ORDER BY clause, the qualifying rows are first arranged by the ORDER BY specification before the SKIP offset and LIMIT max clauses are applied. -------------------------------------------------------------------- Are these type of querys impossible if inserted into a temporary table? J. PD: I don't have an IDS 10.00.xc3 to test myself. Jean Sagi jeansagi@myrealbox.com jeansagi@gmail.com
Wait a minute:
select first 10 tabid, tabname[1,20]
from systables
into temp tx_first10_sys;
Does not work in IDS 10.00.TC1TL (Windows XP-Pro SP2).
I'm almost sure it doesn't work in Linux too (UC1).
Maybe it works in "10.00.xC3"
...
Hey there is an IDS 10 express edition trial !! (Downloading right know) it is
iif.10.00.TC3ET.W2K.zip, so I supose this kind of query works with this
version.
http://www14.software.ibm.com/webapp/download/search.jsp?go=y&rs=ifxsee
J.
PD: This flash is also interesting about IDS:
http://www-306.ibm.com/software/data/informix/ids/mobility/everyplace4ids/
-----Original Message-----
From: Vinayak Shenoi <vshenoi@us.ibm.com>
To: "Jean Sagi" <jeansagi@myrealbox.com>
Date: Fri, 5 Aug 2005 09:21:52 -0700
Subject: Re: RE: New SKIP, LIMIT of SELECT are afe [5558]
The query you have mentioned below works with IDS 10x server.
dbaccess sysmaster -
Database selected.
> select first 10 tabid, tabname[1,20] from systables into temptx_first10_sys;
10 row(s) retrieved into temp table.
dbaccess sysmaster -
Database selected.
> select skip 5 limit 20 tabid, tabname[1,20] from systables intotemp tx_first10_sys;
20 row(s) retrieved into temp table.
Thanks,
Vinayak Shenoi
======================================================
Vinayak Shenoi
Advisory Software Engineer
IBM Informix Server Engineering
Information Management
4100 Bohannon Drive, Menlo Park, CA 94025
Ph:650-926-6341; T/L 630-6341
vshenoi@us.ibm.com
======================================================
"Jean Sagi"
<jeansagi@myrealb
ox.com> To
Sent by: ids@iiug.org
forum.subscriber@ cc
iiug.org
Subject
Re: RE: New SKIP, LIMIT of SELECT
08/05/2005 06:35 are afe [5558]
AM
Well limit is a synonym for first as it is stated below and querys like
this never worked:
select first 10 tabid, tabname[1,20]
from systables
into temp tx_first10_sys
It gives:
-944 Cannot use "first" in this context.
This statement uses FIRST N inside a subquery. This action is not
supported.
Review the use of FIRST N and check that it is applied only to the outer
main
query SELECT clause.
Which personally consider is very silly from the engine point of view.
J.
-----Original Message-----
From: "Gilker, Charles" <CGilker@milbank.com>
To: "Jean Sagi " <jeansagi@myrealbox.com>
Date: Fri, 5 Aug 2005 09:15:04 -0400
Subject: RE: New SKIP, LIMIT of SELECT are afe [5557]
Why would you think that it wouldn't apply to temp tables?
-----Original Message-----
From: forum.subscriber@iiug.org [mailto:forum.subscriber@iiug.org] On
Behalf Of Jean Sagi
Sent: Friday, August 05, 2005 7:57 AM
To: ids@iiug.org
Subject: New SKIP, LIMIT of SELECT are afe [5557]
I noticed this from "New Features in Dynamic Server, Version 10.00.xC3"
of
http://publib.boulder.ibm.com/infocenter/ids9help/index.jsp?topic=/com.i
bm.sqls.doc/sqls02.htm
--------------------------------------------------------------------
New Projection Clause syntax of the SELECT statement can control the
number of qualifying rows in the result set of a query:
* SKIP offset
* LIMIT max
The SKIP offset clause excludes the first offset qualifying rows from
the result set, for offset an integer. If you also include the LIMIT max
clause (where LIMIT is a keyword synonym for FIRST), no more than max
rows are returned. The offset and max values can be specified as literal
integers in the SERIAL8 range, or as host variables, or as local SPL
variables. You can save the result set as a collection-derived table
(CDT).
If you also include the ORDER BY clause, the qualifying rows are first
arranged by the ORDER BY specification before the SKIP offset and LIMIT
max clauses are applied.
--------------------------------------------------------------------
Are these type of querys impossible if inserted into a temporary table?
J.
PD: I don't have an IDS 10.00.xc3 to test myself.
=======================================================================
IRS Circular 230 Disclosure: U.S. federal tax advice in the foregoing
message from Milbank, Tweed, Hadley & McCloy LLP is not intended or written
to be, and cannot be used, by any person for the purpose of avoiding tax
penalties that may be imposed regarding the transactions or matters
addressed. If any U.S. federal tax advice contained in this message is used
or referred to in promoting, marketing or recommending of the transactions
or matters addressed (which any person who is not our client with respect
to the transactions or matters addressed should assume to be the case),
then (i) such tax advice should be construed as written in connection with
the promotion or marketing (within the meaning of IRS Circular 230) of the
transactions or matters addressed and (ii) such person should seek advice
based on their particular circumstances from an independent tax advisor.
=======================================================================
This e-mail message may contain legally privileged and/or confidential
information. If you are not the intended recipient(s), or the employee or
agent responsible for delivery of this message to the intended
recipient(s), you are hereby notified that any dissemination, distribution
or copying of this e-mail message is strictly prohibited. If you have
received this message in error, please immediately notify the sender and
delete this e-mail message from your computer.
Jean Sagi
jeansagi@myrealbox.com
jeansagi@gmail.com
Jean Sagi
jeansagi@myrealbox.com
jeansagi@gmail.com
Jean, FYI, I don't see it documented on that page, but there is also the new
related pseudo-column ROWNUM in 10.00.xC3+ which can be used to either return
the ordinal number of the row within the select set or even to filter rows, so:
SELECT *, ROWNUM
FROM atable
WHERE ROWNUM BETWEEN 11 AND 20
ORDER BY 1;
is equivalent to:
SELECT LIMIT 10 SKIP 10 *, ROWNUM
FROM atable
ORDER BY 1;
Opens up the possibility of random sampling etc.!
Art
----- Original Message -----
From: Jean Sagi <jeansagi@myrealbox.com>
At: 8/ 5 13:47
Excelent !
J.
-----Original Message-----
From: Nita Dembla <nita@us.ibm.com>
To: "Jean Sagi " <jeansagi@myrealbox.com>
Date: Fri, 5 Aug 2005 11:40:29 -0400
Subject: Re: New SKIP, LIMIT of SELECT are afe [5557]
Hi,
Are these type of queries impossible if inserted into a temporary table?
If you intended to ask whether the following type of query allows SKIP and
LIMIT clauses..
"SELECT SKIP m LIMIT n * FROM tabname
[ORDER BY colname]
INTO TEMP temptabname;"
Then yes, they are allowed.
Alternatively, you can use the INSERT INTO.. SELECT statement to insert
into temp table.
Thanks,
Nita Dembla
Software Engineer
IBM - Information Management Solutions
"Jean Sagi "
<jeansagi@myrealb To: ids@iiug.org
ox.com> cc:
Sent by: Subject: New SKIP, LIMIT of
SELECT are afe [5557]
forum.subscriber@
iiug.org
08/05/2005 07:56
AM
I noticed this from "New Features in Dynamic Server, Version 10.00.xC3"
of
http://publib.boulder.ibm.com/infocenter/ids9help/index.jsp?topic=/com.ibm.sqls.
doc/sqls02.htm
--------------------------------------------------------------------
New Projection Clause syntax of the SELECT statement can control the
number of qualifying rows in the result set of a query:
* SKIP offset
* LIMIT max
The SKIP offset clause excludes the first offset qualifying rows from
the result set, for offset an integer. If you also include the LIMIT max
clause (where LIMIT is a keyword synonym for FIRST), no more than max
rows are returned. The offset and max values can be specified as literal
integers in the SERIAL8 range, or as host variables, or as local SPL
variables. You can save the result set as a collection-derived table (CDT).
If you also include the ORDER BY clause, the qualifying rows are first
arranged by the ORDER BY specification before the SKIP offset and LIMIT
max clauses are applied.
--------------------------------------------------------------------
Are these type of querys impossible if inserted into a temporary table?
J.
PD: I don't have an IDS 10.00.xc3 to test myself.
Jean Sagi
jeansagi@myrealbox.com
jeansagi@gmail.com
Wait a
minute:
select first 10 tabid, tabname[1,20]
from systables
into temp tx_first10_sys;
Does not work in IDS 10.00.TC1TL (Windows XP-Pro SP2).
I'm almost sure it doesn't work in Linux too (UC1).
Maybe it works in "10.00.xC3"
...
Hey there is an IDS 10 express edition trial !! (Downloading right know) it is
iif.10.00.TC3ET.W2K.zip, so I supose this kind of query works with this
version.
http://www14.software.ibm.com/webapp/download/search.jsp?go=y&rs=ifxsee
J.
PD: This flash is also interesting about IDS:
http://www-306.ibm.com/software/data/informix/ids/mobility/everyplace4ids/
-----Original Message-----
From: Vinayak Shenoi <vshenoi@us.ibm.com>
To: "Jean Sagi" <jeansagi@myrealbox.com>
Date: Fri, 5 Aug 2005 09:21:52 -0700
Subject: Re: RE: New SKIP, LIMIT of SELECT are afe [5558]
The query you have mentioned below works with IDS 10x server.
dbaccess sysmaster -
Database selected.
> select first 10 tabid, tabname[1,20] from systables into temptx_first10_sys;
10 row(s) retrieved into temp table.
dbaccess sysmaster -
Database selected.
> select skip 5 limit 20 tabid, tabname[1,20] from systables intotemp tx_first10_sys;
20 row(s) retrieved into temp table.
Thanks,
Vinayak Shenoi
======================================================
Vinayak Shenoi
Advisory Software Engineer
IBM Informix Server Engineering
Information Management
4100 Bohannon Drive, Menlo Park, CA 94025
Ph:650-926-6341; T/L 630-6341
vshenoi@us.ibm.com
======================================================
"Jean Sagi"
<jeansagi@myrealb
ox.com> To
Sent by: ids@iiug.org
forum.subscriber@ cc
iiug.org
Subject
Re: RE: New SKIP, LIMIT of SELECT
08/05/2005 06:35 are afe [5558]
AM
Well limit is a synonym for first as it is stated below and querys like
this never worked:
select first 10 tabid, tabname[1,20]
from systables
into temp tx_first10_sys
It gives:
-944 Cannot use "first" in this context.
This statement uses FIRST N inside a subquery. This action is not
supported.
Review the use of FIRST N and check that it is applied only to the outer
main
query SELECT clause.
Which personally consider is very silly from the engine point of view.
J.
-----Original Message-----
From: "Gilker, Charles" <CGilker@milbank.com>
To: "Jean Sagi " <jeansagi@myrealbox.com>
Date: Fri, 5 Aug 2005 09:15:04 -0400
Subject: RE: New SKIP, LIMIT of SELECT are afe [5557]
Why would you think that it wouldn't apply to temp tables?
-----Original Message-----
From: forum.subscriber@iiug.org [mailto:forum.subscriber@iiug.org] On
Behalf Of Jean Sagi
Sent: Friday, August 05, 2005 7:57 AM
To: ids@iiug.org
Subject: New SKIP, LIMIT of SELECT are afe [5557]
I noticed this from "New Features in Dynamic Server, Version 10.00.xC3"
of
http://publib.boulder.ibm.com/infocenter/ids9help/index.jsp?topic=/com.i
bm.sqls.doc/sqls02.htm
--------------------------------------------------------------------
New Projection Clause syntax of the SELECT statement can control the
number of qualifying rows in the result set of a query:
* SKIP offset
* LIMIT max
The SKIP offset clause excludes the first offset qualifying rows from
the result set, for offset an integer. If you also include the LIMIT max
clause (where LIMIT is a keyword synonym for FIRST), no more than max
rows are returned. The offset and max values can be specified as literal
integers in the SERIAL8 range, or as host variables, or as local SPL
variables. You can save the result set as a collection-derived table
(CDT).
If you also include the ORDER BY clause, the qualifying rows are first
arranged by the ORDER BY specification before the SKIP offset and LIMIT
max clauses are applied.
--------------------------------------------------------------------
Are these type of querys impossible if inserted into a temporary table?
J.
PD: I don't have an IDS 10.00.xc3 to test myself.
=======================================================================
IRS Circular 230 Disclosure: U.S. federal tax advice in the foregoing
message from Milbank, Tweed, Hadley & McCloy LLP is not intended or written
to be, and cannot be used, by any person for the purpose of avoiding tax
penalties that may be imposed regarding the transactions or matters
addressed. If any U.S. federal tax advice contained in this message is used
or referred to in promoting, marketing or recommending of the transactions
or matters addressed (which any person who is not our client with respect
to the transactions or matters addressed should assume to be the case),
then (i) such tax advice should be construed as written in connection with
the promotion or marketing (within the meaning of IRS Circular 230) of the
transactions or matters addressed and (ii) such person should seek advice
based on their particular circumstances from an independent tax advisor.
=======================================================================
This e-mail message may contain legally privileged and/or confidential
information. If you are not the intended recipient(s), or the employee or
agent responsible for delivery of this message to the intended
recipient(s), you are hereby notified that any dissemination, distribution
or copying of this e-mail message is strictly prohibited. If you have
received this message in error, please immediately notify the sender and
delete this e-mail message from your computer.
Jean Sagi
jeansagi@myrealbox.com
jeansagi@gmail.com
Jean Sagi
jeansagi@myrealbox.com
jeansagi@gmail.com
--0__=0ABBFAC7DFFD04588f9e8a93df938690918c0ABBFAC7DFFD0458
Content-type: multipart/alternative;
Boundary="1__=0ABBFAC7DFFD04588f9e8a93df938690918c0ABBFAC7DFFD0458"
--1__=0ABBFAC7DFFD04588f9e8a93df938690918c0ABBFAC7DFFD0458
Content-type: text/plain; charset=US-ASCII
Content-transfer-encoding: quoted-printable
Yes, this feature has gone in 10.00xC3 release.
Thanks,
Nita Dembla
Software Engineer
IBM - Information Management Solutions
=
"Jean Sagi" =
<jeansagi@myrealb To: ids@iiug.org =
ox.com> cc: =
Sent by: Subject: Re: Re: RE: Ne=
w SKIP, LIMIT of SELECT are afe [5562]
forum.subscriber@ =
iiug.org =
=
=
08/05/2005 02:09 =
PM =
=
Wait a minute:
select first 10 tabid, tabname[1,20]
from systables
into temp tx_first10_sys;
Does not work in IDS 10.00.TC1TL (Windows XP-Pro SP2).
I'm almost sure it doesn't work in Linux too (UC1).
Maybe it works in "10.00.xC3"
..
Hey there is an IDS 10 express edition trial !! (Downloading right know=
) it
is iif.10.00.TC3ET.W2K.zip, so I supose this kind of query works with t=
his
version.
http://www14.software.ibm.com/webapp/download/search.jsp?go=3Dy&rs=3Dif=
xsee
J.
PD: This flash is also interesting about IDS:
http://www-306.ibm.com/software/data/informix/ids/mobility/everyplace4i=
ds/
-----Original Message-----
From: Vinayak Shenoi <vshenoi@us.ibm.com>
To: "Jean Sagi" <jeansagi@myrealbox.com>
Date: Fri, 5 Aug 2005 09:21:52 -0700
Subject: Re: RE: New SKIP, LIMIT of SELECT are afe [5558]
The query you have mentioned below works with IDS 10x server.
dbaccess sysmaster -
Database selected.
> select first 10 tabid, tabname[1,20] from systables into temptx_first10_sys;
10 row(s) retrieved into temp table.
dbaccess sysmaster -
Database selected.
> select skip 5 limit 20 tabid, tabname[1,20] from systables intotemp tx_first10_sys;
20 row(s) retrieved into temp table.
Thanks,
Vinayak Shenoi
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D
Vinayak Shenoi
Advisory Software Engineer
IBM Informix Server Engineering
Information Management
4100 Bohannon Drive, Menlo Park, CA 94025
Ph:650-926-6341; T/L 630-6341
vshenoi@us.ibm.com
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D
"Jean Sagi"
<jeansagi@myrealb
ox.com> =
To
Sent by: ids@iiug.org
forum.subscriber@ =
cc
iiug.org
Subj=
ect
Re: RE: New SKIP, LIMIT of SELEC=
T
08/05/2005 06:35 are afe [5558]
AM
Well limit is a synonym for first as it is stated below and querys like=
this never worked:
select first 10 tabid, tabname[1,20]
from systables
into temp tx_first10_sys
It gives:
-944 Cannot use "first" in this context.
This statement uses FIRST N inside a subquery. This action is not
supported.
Review the use of FIRST N and check that it is applied only to the oute=
r
main
query SELECT clause.
Which personally consider is very silly from the engine point of view.
J.
-----Original Message-----
From: "Gilker, Charles" <CGilker@milbank.com>
To: "Jean Sagi " <jeansagi@myrealbox.com>
Date: Fri, 5 Aug 2005 09:15:04 -0400
Subject: RE: New SKIP, LIMIT of SELECT are afe [5557]
Why would you think that it wouldn't apply to temp tables?
-----Original Message-----
From: forum.subscriber@iiug.org [mailto:forum.subscriber@iiug.org] On
Behalf Of Jean Sagi
Sent: Friday, August 05, 2005 7:57 AM
To: ids@iiug.org
Subject: New SKIP, LIMIT of SELECT are afe [5557]
I noticed this from "New Features in Dynamic Server, Version 10.00.xC3"=
of
http://publib.boulder.ibm.com/infocenter/ids9help/index.jsp?topic=3D/co=
m.i
bm.sqls.doc/sqls02.htm
--------------------------------------------------------------------
New Projection Clause syntax of the SELECT statement can control the
number of qualifying rows in the result set of a query:
* SKIP offset
* LIMIT max
The SKIP offset clause excludes the first offset qualifying rows from
the result set, for offset an integer. If you also include the LIMIT ma=
x
clause (where LIMIT is a keyword synonym for FIRST), no more than max
rows are returned. The offset and max values can be specified as litera=
l
integers in the SERIAL8 range, or as host variables, or as local SPL
variables. You can save the result set as a collection-derived table
(CDT).
If you also include the ORDER BY clause, the qualifying rows are first
arranged by the ORDER BY specification before the SKIP offset and LIMIT=
max clauses are applied.
--------------------------------------------------------------------
Are these type of querys impossible if inserted into a temporary table?=
J.
PD: I don't have an IDS 10.00.xc3 to test myself.
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D
IRS Circular 230 Disclosure: U.S. federal tax advice in the foregoing
message from Milbank, Tweed, Hadley & McCloy LLP is not intended or wri=
tten
to be, and cannot be used, by any person for the purpose of avoiding ta=
x
penalties that may be imposed regarding the transactions or matters
addressed. If any U.S. federal tax advice contained in this message is =
used
or referred to in promoting, marketing or recommending of the transacti=
ons
or matters addressed (which any person who is not our client with respe=
ct
to the transactions or matters addressed should assume to be the case),=
then (i) such tax advice should be construed as written in connection w=
ith
the promotion or marketing (within the meaning of IRS Circular 230) of =
the
transactions or matters addressed and (ii) such person should seek advi=
ce
based on their particular circumstances from an independent tax advisor=
.
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D
This e-mail message may contain legally privileged and/or confidential
information. If you are not the intended recipient(s), or the employee =
or
agent responsible for delivery of this message to the intended
recipient(s), you are hereby notified that any dissemination, distribut=
ion
or copying of this e-mail message is strictly prohibited. If you have
received this message in error, please immediately notify the sender an=
d
delete this e-mail message from your computer.
Jean Sagi
jea