How do I access a specific member in a file using SQL

1946 views
Skip to first unread message

elb...@cmfurniture.com

unread,
Jun 15, 2000, 3:00:00 AM6/15/00
to
I am writing an inquiry program in RPGLE with embedded SQL. I wish to
access data from a specific member in a physical file. I can't figure
out how to code the SELECT FROM statement to point to the file member
I need.

Does SQL support multiple members in a physical file?

We're using an AS400 model 170, V4R4. Any help would be appreciated.

Elbert Cook
AS/400 Programmer
Craftmaster Furniture

Bernard Bandiera

unread,
Jun 15, 2000, 3:00:00 AM6/15/00
to
I'm afraid that SQL does not support multi-member files... For SQL a file is
seen like a table (remember the CODD and DATE specifications).

--
Bernard Bandiera - ADP GSI D.O. Eybens
mailto:Bernard....@fr.adp.com

Joerg Wende

unread,
Jun 15, 2000, 3:00:00 AM6/15/00
to
why don't you use OVRDBF ?

Joerg

<elb...@cmfurniture.com> schrieb in im Newsbeitrag:
3948db6d...@news.abts.net...

Dieter Bender

unread,
Jun 15, 2000, 3:00:00 AM6/15/00
to
Elbert,

the SQL DDL CREATE ALIAS supports Members. Create an alias and use it
like a VIEW.

Dieter

>>>>>>>>>>>>>>>>>> Ursprüngliche Nachricht <<<<<<<<<<<<<<<<<<

Am 15.06.00, 14:43:04, schrieb elb...@cmfurniture.com zum Thema How do
I access a specific member in a file using SQL:

Joost van Stuyvenberg

unread,
Jun 15, 2000, 3:00:00 AM6/15/00
to
Hi Elbert,

>Does SQL support multiple members in a physical file?

As far as I know, no. You'll have to issue an OVRDBF or equivalent to
make SQL open the correct member. Within RPGLE (if you're familiar
with ILE development) you might call a CLLE procedure that overrides
to the member, provided that it has not been opened yet by RPG or SQL.


Regards,

Joost van Stuyvenberg
Utrecht, The Netherlands
jstu...@xs4all.nl

Larry W Loen

unread,
Jun 15, 2000, 3:00:00 AM6/15/00
to
elb...@cmfurniture.com wrote:
>
> I am writing an inquiry program in RPGLE with embedded SQL. I wish to
> access data from a specific member in a physical file. I can't figure
> out how to code the SELECT FROM statement to point to the file member
> I need.
>
> Does SQL support multiple members in a physical file?
>
> We're using an AS400 model 170, V4R4. Any help would be appreciated.
>
> Elbert Cook
> AS/400 Programmer
> Craftmaster Furniture

Use CREATE ALIAS:

CREATE ALIAS yourlib/yourfile FOR
yourlib/yourmultimemberfile (yourmbr)

This came in something like V4R2 or V4R3 or so (forget exactly
when), so those who say that there "is no way" to do this
are correct depending on the release. You, however, are at V4R4,
so it works fine. Try it out via STRSQL if you have that product.

OVRDBF can work also, but for some cases (e.g. Java, remote
data base access) CREATE ALIAS is the only or most practical
way.


N.B. If there is ever a FAQ, this question belongs in such
a document.

Larry Loen speaking on his own

pwmeister

unread,
Jun 15, 2000, 3:00:00 AM6/15/00
to
hi
use OVRDBF on the member.
pwm


elb...@cmfurniture.com wrote in message <3948db6d...@news.abts.net>...

Paul Nicolay

unread,
Jun 16, 2000, 3:00:00 AM6/16/00
to
Hi Bernard,

It does... take a look at the CREATE ALIAS SQL statement.

Regards,
Paul
-----------------------------
Bernard Bandiera wrote in message <3948EAE7...@fr.adp.com>...


I'm afraid that SQL does not support multi-member files... For SQL a file is
seen like a table (remember the CODD and DATE specifications).

elb...@cmfurniture.com wrote:
>
> I am writing an inquiry program in RPGLE with embedded SQL. I wish to
> access data from a specific member in a physical file. I can't figure
> out how to code the SELECT FROM statement to point to the file member
> I need.
>
> Does SQL support multiple members in a physical file?
>
> We're using an AS400 model 170, V4R4. Any help would be appreciated.
>
> Elbert Cook
> AS/400 Programmer
> Craftmaster Furniture

--


Bernard Bandiera - ADP GSI D.O. Eybens
mailto:Bernard....@fr.adp.com


The contents of this message express only the sender's opinion.
This message does not necessarily reflect the policy or views of
my employer, Merck & Co., Inc. All responsibility for the statements
made in this Usenet posting resides solely and completely with the
sender.

Gilad Gordon

unread,
Jun 16, 2000, 3:00:00 AM6/16/00
to
hi Elbert,
suppose you want to access member CPNY02 of file CUSTPF.
first of all, use the following statement:
CREATE ALIAS CUST_C02 FOR CUSTPF(CPNY02)
now that you have an alias on that file use the next statement:
SELECT * FROM CUST_C02 ALIAS
to access the desired member.
hope this helps.

--
Gilad Gordon
Segment IT (Israel)
gi...@segment.co.il
<elb...@cmfurniture.com> wrote in message
news:3948db6d...@news.abts.net...

Sandro L.

unread,
Sep 16, 2020, 11:24:18 AM9/16/20
to
is the alias persistent?
i would need to do spot queries on some files with members but i do not want to create permament aliases is that possible now?

Peter Zich

unread,
Sep 16, 2020, 3:50:34 PM9/16/20
to
The statement
SELECT * FROM CUSTPF MBR(CPNY02)
should work for you :-)

Regards
Peter Zich
Reply all
Reply to author
Forward
0 new messages