On Tuesday, May 4, 2021 at 12:44:12 p.m. UTC-4, Prashant wrote:

Hello,

Can someone help me how I can get top n (say 10 or 20) rows from SQL table using SELECT query without using SET LIST_COUNT?

SELECT [FIRST 10] * FROM ...

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)

Hello,

Can someone help me how I can get top n (say 10 or 20) rows from SQL table using SELECT query without using SET LIST_COUNT?

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)

In article <835712fd-fc46-4551-af21-b1d2bc3fa025n@googlegroups.com>, bhamre.prashant@gmail.com says...

Hello,

Can someone help me how I can get top n (say 10 or 20) rows from SQL table using
SELECT query without using SET LIST_COUNT?

What do you mean by 'TOP' ?

The first 20 rows, like the top of a report
or 20 rows with the highest key value ?

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)

On Tue, 4 May 2021 09:54:47 -0700 (PDT), Randall <rsbecker@nexbridge.com> wrote:

On Tuesday, May 4, 2021 at 12:44:12 p.m. UTC-4, Prashant wrote:

Hello,

Can someone help me how I can get top n (say 10 or 20) rows from SQL table using SELECT query without using SET LIST_COUNT?

SELECT [FIRST 10] * FROM ...

I believe that works for SQL/MX, but not for SQL/MP. To use it against MP tables you'll need to use MPALIAS and the MX executor.

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)

On Tuesday, May 4, 2021 at 3:19:09 p.m. UTC-4, Bill Honaker wrote:

On Tue, 4 May 2021 09:54:47 -0700 (PDT), Randall <> wrote:

On Tuesday, May 4, 2021 at 12:44:12 p.m. UTC-4, Prashant wrote:

Hello,

Can someone help me how I can get top n (say 10 or 20) rows from SQL table using SELECT query without using SET LIST_COUNT?

SELECT [FIRST 10] * FROM ...

I believe that works for SQL/MX, but not for SQL/MP. To use it against MP tables you'll need to use MPALIAS and the MX executor.

That's a point. SQL/MP and SQL/MX have very different query languages. Using MPALIAS would be correct since MX has the capability. We don't know what the OP intended, though, so all advice is purely guesswork.

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)

In article <rf739ghe83v9iu9vojrgb179un0peb7nc0@4ax.com>, no_spam_bhonaker__@x_i_d.com
says...

On Tue, 4 May 2021 09:54:47 -0700 (PDT), Randall <rsbecker@nexbridge.com> wrote:

On Tuesday, May 4, 2021 at 12:44:12 p.m. UTC-4, Prashant wrote:

Hello,

Can someone help me how I can get top n (say 10 or 20) rows from SQL table us

ing SELECT query without using SET LIST_COUNT?

SELECT [FIRST 10] * FROM ...

I believe that works for SQL/MX, but not for SQL/MP. To use it against MP table
s you'll need to use MPALIAS and the MX executor.

mxci can directly access SQL/MP tables


Hewlett Packard Enterprise NonStop(TM) SQL/MX Conversational Interface 3.3
(c) Copyright 2003-2015 Hewlett Packard Enterprise Development LP.

select [first 10] * from $data01.sql.table;

PK_TEXT PK_TS COMMAND C1
-------- ---------- ---------------- -----------------
0 0 pathcom status pathway

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)

On Tue, 4 May 2021 20:51:21 -0000 (UTC), invalid@nowhere.com (JShepherd) wrote:

In article <rf739ghe83v9iu9vojrgb179un0peb7nc0@4ax.com>, no_spam_bhonaker__@x_i_d.com
says...

On Tue, 4 May 2021 09:54:47 -0700 (PDT), Randall <rsbecker@nexbridge.com> wrote:

On Tuesday, May 4, 2021 at 12:44:12 p.m. UTC-4, Prashant wrote:

Hello,

Can someone help me how I can get top n (say 10 or 20) rows from SQL table us

ing SELECT query without using SET LIST_COUNT?

SELECT [FIRST 10] * FROM ...

I believe that works for SQL/MX, but not for SQL/MP. To use it against MP table
s you'll need to use MPALIAS and the MX executor.

mxci can directly access SQL/MP tables

Hewlett Packard Enterprise NonStop(TM) SQL/MX Conversational Interface 3.3 >(c) Copyright 2003-2015 Hewlett Packard Enterprise Development LP.

select [first 10] * from $data01.sql.table;

PK_TEXT PK_TS COMMAND C1
-------- ---------- ---------------- -----------------
0 0 pathcom status pathway

You're correct. I'd forgotten, as our standards (and all customer's standards) have always requierd either an ANSI name or DEFINE name.

MPALIAS is not required.

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)

On Wednesday, 5 May 2021 at 02:47:05 UTC+5:30, Bill Honaker wrote:

On Tue, 4 May 2021 20:51:21 -0000 (UTC), inv...@nowhere.com (JShepherd) wrote:

In article <rf739ghe83v9iu9vo...@4ax.com>, no_spam_bhonaker__@x_i_d.com >says...

On Tue, 4 May 2021 09:54:47 -0700 (PDT), Randall <rsbe...@nexbridge.com> wrote:

On Tuesday, May 4, 2021 at 12:44:12 p.m. UTC-4, Prashant wrote:

Hello,

Can someone help me how I can get top n (say 10 or 20) rows from SQL table us

ing SELECT query without using SET LIST_COUNT?

SELECT [FIRST 10] * FROM ...

I believe that works for SQL/MX, but not for SQL/MP. To use it against MP table
s you'll need to use MPALIAS and the MX executor.

mxci can directly access SQL/MP tables

Hewlett Packard Enterprise NonStop(TM) SQL/MX Conversational Interface 3.3 >(c) Copyright 2003-2015 Hewlett Packard Enterprise Development LP.

select [first 10] * from $data01.sql.table;

PK_TEXT PK_TS COMMAND C1
-------- ---------- ---------------- -----------------
0 0 pathcom status pathway

You're correct. I'd forgotten, as our standards (and all customer's standards) have always requierd either an ANSI name or DEFINE name.

MPALIAS is not required.

Hello All,
Thanks a lot for valuable help. SELECT [FIRST 10] helped me to solve what I am looking for.
I have SQLMP table and I created it's Alias in SQLMX.

I do have one more query relates to same, I can see in SQLMX manual, we can use ROWNUM in SELECT query for 3.6 version and above but it doesn't support SQLMP table or Alias created in SQLMX. Is there any way
I can get ROWNUM from SQLMP table or Alias? Please suggest.

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)

On Wednesday, May 5, 2021 at 12:38:16 p.m. UTC-4, Prashant wrote:

On Wednesday, 5 May 2021 at 02:47:05 UTC+5:30, Bill Honaker wrote:

On Tue, 4 May 2021 20:51:21 -0000 (UTC), inv...@nowhere.com (JShepherd) wrote:

In article <rf739ghe83v9iu9vo...@4ax.com>, no_spam_bhonaker__@x_i_d.com >says...

On Tue, 4 May 2021 09:54:47 -0700 (PDT), Randall <rsbe...@nexbridge.com> wrote:

On Tuesday, May 4, 2021 at 12:44:12 p.m. UTC-4, Prashant wrote:

Hello,

Can someone help me how I can get top n (say 10 or 20) rows from SQL table us

ing SELECT query without using SET LIST_COUNT?

SELECT [FIRST 10] * FROM ...

I believe that works for SQL/MX, but not for SQL/MP. To use it against MP table
s you'll need to use MPALIAS and the MX executor.

mxci can directly access SQL/MP tables

Hewlett Packard Enterprise NonStop(TM) SQL/MX Conversational Interface 3.3
(c) Copyright 2003-2015 Hewlett Packard Enterprise Development LP.

select [first 10] * from $data01.sql.table;

PK_TEXT PK_TS COMMAND C1
-------- ---------- ---------------- -----------------
0 0 pathcom status pathway

You're correct. I'd forgotten, as our standards (and all customer's standards) have always requierd either an ANSI name or DEFINE name.

MPALIAS is not required.

Hello All,
Thanks a lot for valuable help. SELECT [FIRST 10] helped me to solve what I am looking for.
I have SQLMP table and I created it's Alias in SQLMX.

I do have one more query relates to same, I can see in SQLMX manual, we can use ROWNUM in SELECT query for 3.6 version and above but it doesn't support SQLMP table or Alias created in SQLMX. Is there any way
I can get ROWNUM from SQLMP table or Alias? Please suggest.

I don't think you will get a happy answer to this one. SQL/MP is based on top of ENSCRIBE files. The notion of a record number does not actually exist across ENSCRIBE file types so it is unlikely that there is a general solution. Of course, there are
inefficient ones, where you could build populate an index of primary keys by record number. The difficulty is that you could create an unkeyed table based on a RELATIVE file, delete a row in the middle of the table, and your row numbers would be off
because of a gap. In addition, a FUP RELOAD could invalidate any location information, including relative byte offsets (RBAs) that are used to locate records (the hard way) in ENSCRIBE. I might be wrong - my SQL/MP is a bit rusty.

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)

Randall schrieb am Mittwoch, 5. Mai 2021 um 19:31:51 UTC+2:

On Wednesday, May 5, 2021 at 12:38:16 p.m. UTC-4, Prashant wrote:

On Wednesday, 5 May 2021 at 02:47:05 UTC+5:30, Bill Honaker wrote:

On Tue, 4 May 2021 20:51:21 -0000 (UTC), inv...@nowhere.com (JShepherd) wrote:

In article <rf739ghe83v9iu9vo...@4ax.com>, no_spam_bhonaker__@x_i_d.com >says...

On Tue, 4 May 2021 09:54:47 -0700 (PDT), Randall <rsbe...@nexbridge.com> wrote:

On Tuesday, May 4, 2021 at 12:44:12 p.m. UTC-4, Prashant wrote:

Hello,

Can someone help me how I can get top n (say 10 or 20) rows from SQL table us

ing SELECT query without using SET LIST_COUNT?

SELECT [FIRST 10] * FROM ...

I believe that works for SQL/MX, but not for SQL/MP. To use it against MP table
s you'll need to use MPALIAS and the MX executor.

mxci can directly access SQL/MP tables

Hewlett Packard Enterprise NonStop(TM) SQL/MX Conversational Interface 3.3
(c) Copyright 2003-2015 Hewlett Packard Enterprise Development LP.

select [first 10] * from $data01.sql.table;

PK_TEXT PK_TS COMMAND C1
-------- ---------- ---------------- -----------------
0 0 pathcom status pathway

You're correct. I'd forgotten, as our standards (and all customer's standards) have always requierd either an ANSI name or DEFINE name.

MPALIAS is not required.

Hello All,
Thanks a lot for valuable help. SELECT [FIRST 10] helped me to solve what I am looking for.
I have SQLMP table and I created it's Alias in SQLMX.

I do have one more query relates to same, I can see in SQLMX manual, we can use ROWNUM in SELECT query for 3.6 version and above but it doesn't support SQLMP table or Alias created in SQLMX. Is there any way
I can get ROWNUM from SQLMP table or Alias? Please suggest.

I don't think you will get a happy answer to this one. SQL/MP is based on top of ENSCRIBE files. The notion of a record number does not actually exist across ENSCRIBE file types so it is unlikely that there is a general solution. Of course, there are

inefficient ones, where you could build populate an index of primary keys by record number. The difficulty is that you could create an unkeyed table based on a RELATIVE file, delete a row in the middle of the table, and your row numbers would be off
because of a gap. In addition, a FUP RELOAD could invalidate any location information, including relative byte offsets (RBAs) that are used to locate records (the hard way) in ENSCRIBE. I might be wrong - my SQL/MP is a bit rusty.
If you want to see the first N rows you can use the Report Writer even with SQL/MP. There is a manual for that.
You can do it like this:
SET LIST_COUNT 0;
Select.....
LIST 10;

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)