RE: creating a view -- RPG400-L

Thanks for your reply Rick
It seems to be a moot point now, as it looks like you CANNOT create a view
against any other member EXCEPT to the *FIRST.
However, I am still open to ideas/thoughts/suggestions.

Alan Shore

NBTY, Inc
(631) 244-2000 ext. 5019
AShore@xxxxxxxx
"If you're going through Hell, keep going" - Winston Churchill

rpg400-l-bounces@xxxxxxxxxxxx wrote on 06/27/2007 09:13:56 AM:

Alan,

I'm coming into this thread late so I apologize if I'm being
redundant or am way off the mark.

<snip>
...I'm concerned about what would happen to the view once data is
added, deleted and/or changed to other members of the original file.
</snip>
I don't understand why you would be concerned about data being
added, deleted, and/or changed to members of a file you are not
interested in.

<snip>
My thinking is that the same thing would happen to the view as the
OVRDBF is not in play at the time that data is added, deleted and/or
changed to other members of the original file.
</snip>
What do you mean by "the same thing would happen to the view"?

A view is just a select statement that gets executed each time the
view is opened. Any data changes will be reflected at the next open
of the view presuming the OVRDBF is in effect. Are you saying you
don't want to see the data changes?

Rick

-----Original Message-----
From: rpg400-l-bounces@xxxxxxxxxxxx [mailto:rpg400-l-
bounces@xxxxxxxxxxxx] On Behalf Of Alan Shore
Sent: Wednesday, June 27, 2007 7:27 AM
To: RPG programming on the AS400 / iSeries
Subject: RE: creating a view

Thanks for your reply Wayne, however I'm concerned about what would
happen to the view once data is added, deleted and/or changed to
other members of the original file. My thinking is that the same
thing would happen to the view as the OVRDBF is not in play at the
time that data is added, deleted and/or changed to other members of
the original file.

Alan Shore

NBTY, Inc
(631) 244-2000 ext. 5019
AShore@xxxxxxxx
"If you're going through Hell, keep going" - Winston Churchill

rpg400-l-bounces@xxxxxxxxxxxx wrote on 06/26/2007 05:45:30 PM:

I know this is probably not what you are looking for, but you can do
ovrdbf to the member then create the view.

Wayne Farmer
Intex Recreation Corp
wfarmer@xxxxxxxxxxxxx

-----Original Message-----
From: rpg400-l-bounces@xxxxxxxxxxxx
[mailto:rpg400-l-bounces@xxxxxxxxxxxx] On Behalf Of
rpg400-l-request@xxxxxxxxxxxx
Sent: Tuesday, June 26, 2007 1:24 PM
To: rpg400-l@xxxxxxxxxxxx
Subject: RPG400-L Digest, Vol 6, Issue 672

Send RPG400-L mailing list submissions to
rpg400-l@xxxxxxxxxxxx

To subscribe or unsubscribe via the World Wide Web, visit
http://lists.midrange.com/mailman/listinfo/rpg400-l
or, via email, send a message with subject or body 'help' to
rpg400-l-request@xxxxxxxxxxxx

You can reach the person managing the list at
rpg400-l-owner@xxxxxxxxxxxx

When replying, please edit your Subject line so it is more specific
than "Re: Contents of RPG400-L digest..."

*** NOTE: When replying to this digest message, PLEASE remove all text
unrelated to your reply and change the subject line so it is

meaningful.

Today's Topics:

1. Re: Re: subfile page/size issue (James.Hawkins@xxxxxxxxxx)
2. Re: Creating a view (Raul A. Jager W.)
3. Re: AW: Creating a view (Alan Shore)
4. Re: Creating a view (Alan Shore)
5. RE: Exception SQL Question (Rick.Chevalier@xxxxxxxxxxxxxxx)
6. Exception SQL Question (Ala, Michael A)
7. Defining DS to retrieve variable length data from API
(johnking@xxxxxxx)

----------------------------------------------------------------------

message: 1
date: Tue, 26 Jun 2007 13:11:42 -0400
from: James.Hawkins@xxxxxxxxxx
subject: Re: Re: subfile page/size issue

Turns out one of fields in the subfile somehow had the line # changed
making each record 3 lines instead of two. Fixed that and now it
works wonderfully. Thanks for your input

Thank you,
- Jim

------------------------------

message: 2
date: Tue, 26 Jun 2007 13:49:28 -0400
from: "Raul A. Jager W." <raul@xxxxxxxxxx>
subject: Re: Creating a view

Multiple member are a "DDS" stuff, not SQL. You can handle it
defining a "Logical File" in DDS and specifying the member (s) in the
CRTLF command.
______________________________________________________________________
__
_______________
Alan Shore wrote:

Hi guys
I have been approached with the following problem that has me

scratching my

head

We have a file that contains multiple members. A view is required

against

ONE of those members.
Is this possible, and if so how?

I contemplated creating an ALIAS within qtemp but (unless I'm doing
something wrong) I am stopped from creating a view over this ALIAS I
also thought of creating another temporary file within QTEMP that

would

have the data that would be needed and then regenerating the data
each

and

every time that the "VIEW" would be inquired upon, but this takes
quite

a

while.
Anybody any ideas?

Any help would be MUCH appreciated.
Thanks in advance

Alan Shore

NBTY, Inc
(631) 244-2000 ext. 5019
AShore@xxxxxxxx
"If you're going through Hell, keep going" - Winston Churchill

------------------------------

message: 3
date: Tue, 26 Jun 2007 13:55:32 -0400
from: Alan Shore <AlanShore@xxxxxxxx>
subject: Re: AW: Creating a view

Thanks for your reply Birgitta
and yep, that was the same message that I received when I attempted to
create a view over an alias.

Alan Shore

NBTY, Inc
(631) 244-2000 ext. 5019
AShore@xxxxxxxx
"If you're going through Hell, keep going" - Winston Churchill

"BirgittaHauser"

<Hauser@sss-softw

are.de>
To
Sent by: "'RPG programming on the AS400 /

rpg400-l-bounces@ iSeries'"

<rpg400-l@xxxxxxxxxxxx>

midrange.com
cc

Subject
06/26/2007 01:04 AW: Creating a view

PM

Please respond to

RPG programming

on the AS400 /

iSeries

<rpg400-l@midrang

e.com>

Hi,

An alias that is created over a physical file member cannot be
specified in an CREATE VIEW statement.
You'll get SQL7030:
Message . . . . : Alias X for table QRPGLESRC in HAUSER not valid for

statement.

Cause . . . . . : The SQL statement cannot be performed on alias X
because

the alias refers to a member of table QRPGLESRC in schema HAUSER.

Recovery . . . : Specify a valid table or an alias that does not
refer
to
a
member. Try the request again.

Mit freundlichen Gr??en / Best regards

Birgitta Hauser

"Shoot for the moon, even if you miss, you'll land among the stars."
(Les
Brown)
"If you think education is expensive, try ignorance." (Derek Bok)
"What is worse than training your staff and losing them? Not training
them and keeping them!"

-----Urspr?ngliche Nachricht-----
Von: rpg400-l-bounces+hauser=sss-software.de@xxxxxxxxxxxx
[mailto:rpg400-l-bounces+hauser=sss-software.de@xxxxxxxxxxxx] Im
Auftrag von Alan Shore
Gesendet: Tuesday, June 26, 2007 18:28
An: RPG programming on the AS400 / iSeries
Betreff: Creating a view

Hi guys
I have been approached with the following problem that has me
scratching my head

We have a file that contains multiple members. A view is required
against ONE of those members. Is this possible, and if so how?

I contemplated creating an ALIAS within qtemp but (unless I'm doing
something wrong) I am stopped from creating a view over this ALIAS I
also thought of creating another temporary file within QTEMP that
would have the data that would be needed and then regenerating the
data each and every time that the "VIEW" would be inquired upon, but
this takes quite a while.
Anybody any ideas?

Any help would be MUCH appreciated.
Thanks in advance

Alan Shore

NBTY, Inc
(631) 244-2000 ext. 5019
AShore@xxxxxxxx
"If you're going through Hell, keep going" - Winston Churchill
--
This is the RPG programming on the AS400 / iSeries (RPG400-L) mailing
list To post a message email: RPG400-L@xxxxxxxxxxxx To subscribe,
unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxx Before posting, please take a
moment to review the archives at http://archive.midrange.com/rpg400-l.

--
This is the RPG programming on the AS400 / iSeries (RPG400-L) mailing
list To post a message email: RPG400-L@xxxxxxxxxxxx To subscribe,
unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxx Before posting, please take a
moment to review the archives at http://archive.midrange.com/rpg400-l.

------------------------------

message: 4
date: Tue, 26 Jun 2007 13:57:59 -0400
from: Alan Shore <AlanShore@xxxxxxxx>
subject: Re: Creating a view

Thanks for your reply Raul.
However, there's more to the view that the logical can give. The view
has to be the sum of a difference of 2 keys.

Alan Shore

NBTY, Inc
(631) 244-2000 ext. 5019
AShore@xxxxxxxx
"If you're going through Hell, keep going" - Winston Churchill

rpg400-l-bounces@xxxxxxxxxxxx wrote on 06/26/2007 01:49:28 PM:

Multiple member are a "DDS" stuff, not SQL. You can handle it

defining

a "Logical File" in DDS and specifying the member (s) in the CRTLF

command.

______________________________________________________________________
__
_______________

Alan Shore wrote:

Hi guys
I have been approached with the following problem that has me

scratching
my

head

We have a file that contains multiple members. A view is required

against

ONE of those members.
Is this possible, and if so how?

I contemplated creating an ALIAS within qtemp but (unless I'm doing
something wrong) I am stopped from creating a view over this ALIAS
I also thought of creating another temporary file within QTEMP that

would

have the data that would be needed and then regenerating the data

each
and

every time that the "VIEW" would be inquired upon, but this takes

quite
a

while.
Anybody any ideas?

Any help would be MUCH appreciated.
Thanks in advance

Alan Shore

NBTY, Inc
(631) 244-2000 ext. 5019
AShore@xxxxxxxx
"If you're going through Hell, keep going" - Winston Churchill

--
This is the RPG programming on the AS400 / iSeries (RPG400-L)
mailing

list

To post a message email: RPG400-L@xxxxxxxxxxxx To subscribe,
unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxx Before posting, please take
a moment to review the archives at
http://archive.midrange.com/rpg400-l.

------------------------------

message: 5
date: Tue, 26 Jun 2007 13:16:32 -0500
from: <Rick.Chevalier@xxxxxxxxxxxxxxx>
subject: RE: Exception SQL Question

Michael,

Sorry, I just read my post and realized I misread the file names you
were using for the join and where clause. Ignore my previous post.

Rick

-----Original Message-----
From: rpg400-l-bounces@xxxxxxxxxxxx
[mailto:rpg400-l-bounces@xxxxxxxxxxxx] On Behalf Of Chevalier, Rick
Sent: Tuesday, June 26, 2007 10:39 AM
To: rpg400-l@xxxxxxxxxxxx
Subject: RE: Exception SQL Question

Michael,

I think what you have is a situation where you are returning records
from F4211la that are not in F59912. Then you are asking to only see
the records that are also in F59911. Because you are asking for
exceptions, any records returned fail the test in your where clause.

Try removing the where clause and see what you get. If that gets you
closer (returns some records) you might try moving the select from the
where clause to the top and create a temp table. It would look
something like this: (not tested)

With temptable as (select DISTINCT * from ajdevmdta.F59911 where
CCTYPE = 'AB')

SELECT DISTINCT SDKCOO,SDDOCO,SDDCTO,SDLNID,SDMCU FROM
ajdevjdta.F4211la EXCEPTION JOIN temptable ON
SDKCOO = CBKCOO
and SDDOCO = CBDOCO
and SDDCTO = CBDCTO
and SDLNID = CBLNID
and SDMCU = CBMCU
WHERE SDLNTY = 'S' and SDNXTR <= '560'
for READ ONLY

HTH,

Rick

-----Original Message-----
From: rpg400-l-bounces@xxxxxxxxxxxx
[mailto:rpg400-l-bounces@xxxxxxxxxxxx] On Behalf Of Ala, Michael A
Sent: Tuesday, June 26, 2007 9:28 AM
To: rpg400-l@xxxxxxxxxxxx
Subject: Exception SQL Question

I am trying to determine Changes to an Order

A baseline is Taken (Detail-F59912)

And then the JDE Order Detail File Is compared to that

SELECT DISTINCT SDKCOO,SDDOCO,SDDCTO,SDLNID,SDMCU FROM
ajdevjdta.F4211la EXCEPTION JOIN ajdevmdta.F59912 ON
SDKCOO = CBKCOO
and SDDOCO = CBDOCO
and SDDCTO = CBDCTO
and SDLNID = CBLNID
and SDMCU = CBMCU
WHERE SDDOCO in
(select DISTINCT CCDOCO from ajdevmdta.F59911 where CCTYPE = 'AB')
and SDLNTY = 'S' and SDNXTR <= '560'
for READ ONLY

This is not returning any results

Yet there is differences in the Data (SDMCU<>CBMCU on 2 Lines of the
Order)

One difference That I have identified is the Line number types are
different Packed and Zoned could that be creating an issue

I changed and SDLNID = CBLNID to and char(SDLNID) = char(CBLNID) and
got the same result

Any help with this Sql Statement would be appreciated

Thanks

Mike

--
This is the RPG programming on the AS400 / iSeries (RPG400-L) mailing
list To post a message email: RPG400-L@xxxxxxxxxxxx To subscribe,
unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxx Before posting, please take a
moment to review the archives at http://archive.midrange.com/rpg400-l.

Privileged and Confidential. This e-mail, and any attachments there
to, is intended only for use by the addressee(s) named herein and may
contain privileged or confidential information. If you have received
this e-mail in error, please notify me immediately by a return e-mail
and delete this e-mail. You are hereby notified that any
dissemination, distribution or copying of this e-mail and/or any
attachments thereto, is strictly prohibited.

--
This is the RPG programming on the AS400 / iSeries (RPG400-L) mailing
list To post a message email: RPG400-L@xxxxxxxxxxxx To subscribe,
unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxx Before posting, please take a
moment to review the archives at http://archive.midrange.com/rpg400-l.

------------------------------

message: 6
date: Tue, 26 Jun 2007 19:53:54 +0100
from: "Ala, Michael A" <michael.ala@xxxxxx>
subject: Exception SQL Question

It was the data in the outer file it had records that matched and did
not

Changed the SQL Exception to include trading Partner and it worked

SELECT DISTINCT SDKCOO,SDDOCO,SDDCTO,SDLNID,SDMCU, CCTPID FROM
AJdevjdta/F4211la A, AJDEVMDTA/F59911 B EXCEPTION JOIN
ajdevmdta/F59912 ON
SDKCOO = CBKCOO
and SDDOCO = CBDOCO
and SDDCTO = CBDCTO
and SDLNID = CBLNID
and SDMCU = CBMCU
and CBTPID = B.CCTPID
WHERE SDDOCO in
(select DISTINCT CCDOCO from ajdevmdta/F59911 where CCTYPE = 'AB')
and SDLNTY = 'S' and SDNXTR <= '560'
for READ ONLY

Inner File
Order Order Or Line Business
Co Number Ty Number Unit
----- -------- -- ------ ------------
00001 2533516 SO 1000 935
00001 2533516 SO 2000 935
00001 2533516 SO 3000 935
00001 2533516 SO 4000 935
******** End of data ********

Outer

CBTPID CBKCOO CBDOCO CBDCTO CBLNID CBMCU
00029689 00001 2,533,516 SO 1,000 935
00029689 00001 2,533,516 SO 2,000 935
00029689 00001 2,533,516 SO 3,000 935
00029689 00001 2,533,516 SO 4,000 935
00003411 00001 2,533,516 SO 1,000 920
00003411 00001 2,533,516 SO 2,000 920
00003411 00001 2,533,516 SO 3,000 935
00003411 00001 2,533,516 SO 4,000 935

-----Original Message-----
From: rpg400-l-bounces@xxxxxxxxxxxx
[mailto:rpg400-l-bounces@xxxxxxxxxxxx] On Behalf Of
rpg400-l-request@xxxxxxxxxxxx
Sent: Tuesday, June 26, 2007 1:00 PM
To: rpg400-l@xxxxxxxxxxxx
Subject: RPG400-L Digest, Vol 6, Issue 671

Send RPG400-L mailing list submissions to
rpg400-l@xxxxxxxxxxxx

To subscribe or unsubscribe via the World Wide Web, visit
http://lists.midrange.com/mailman/listinfo/rpg400-l
or, via email, send a message with subject or body 'help' to
rpg400-l-request@xxxxxxxxxxxx

You can reach the person managing the list at
rpg400-l-owner@xxxxxxxxxxxx

When replying, please edit your Subject line so it is more specific
than
"Re: Contents of RPG400-L digest..."

------------------------------

message: 7
date: Tue, 26 Jun 2007 15:29:52 -0500
from: <johnking@xxxxxxx>
subject: Defining DS to retrieve variable length data from API

All,

Q) How to define a DS to retrieve variable-length data from an API?
The goal is to define an /include member that can be used in V5R3
RPGLE.

For example, QMHRCVM uses format RCVM0100 to return the text of a
message.
Although the length of the data available to "MsgRplData" is actually
defined by "RplDAvail", for most purposes just defining "MsgRplData"
as a fixed length is sufficient. Referencing this DS in a calling
program with
"LikeDS(RCVM0100_t)
Inz(*likeDS)" makes using this API convenient and consistent.

D RCVM0100_t DS Qualified
D RCVM0100 32767A
D ByteReturn 10I 0 INZ Overlay(RCVM0100)
D ByteAvail 10I 0 INZ Overlay(RCVM0100:*next)
D MsgSever 10I 0 INZ Overlay(RCVM0100:*next)
D MsgId 7 INZ Overlay(RCVM0100:*next)
D MsgTyp 2 INZ Overlay(RCVM0100:*next)
D MsgKey 4 INZ Overlay(RCVM0100:*next)
D Resserve_1 7 INZ Overlay(RCVM0100:*next)
D CCSID_CnvSt 10I 0 INZ Overlay(RCVM0100:*next)
D CCSID_RplD 10I 0 INZ Overlay(RCVM0100:*next)
D RplDReturn 10I 0 INZ Overlay(RCVM0100:*next)
D RplDAvail 10I 0 INZ Overlay(RCVM0100:*next)
D MsgRplData 127 INZ Overlay(RCVM0100:*next)

So how should RCVM0200 be defined if it became necessary to retrieve
the "MsgText" or "MsgHText" fields? Their start positions depend of
the length of the previous fields.

D RCVM0200_t DS Qualified
D RCVM0200 32767A
D ByteReturn 10I 0 INZ Overlay(RCVM0200)

D RplDReturn 10I 0 INZ Overlay(RCVM0200:*next)
D RplDAvail 10I 0 INZ Overlay(RCVM0200:*next)
D MsgReturn 10I 0 INZ Overlay(RCVM0200:*next)
D MsgAvail 10I 0 INZ Overlay(RCVM0200:*next)
D MsgHReturn 10I 0 INZ Overlay(RCVM0200:*next)
D MsgHAvail 10I 0 INZ Overlay(RCVM0200:*next)
D RplDText (*)
D MsgText (*)
D MsgHText (*)

I'm sure there is some combination of "%addr" and "%len" and "overlay"
and
"presto" that will accomplish this but I'm sure having trouble getting
clear on the concept. Would anyone be kind enough to point me to a
previous thread or article that discusses this?

Many thanks, JK

---- Msg sent via Internet America Webmail -
http://www.internetamerica.com/

Privileged and Confidential. This e-mail, and any attachments there
to, is intended only for use by the addressee(s) named herein and
may contain privileged or confidential information. If you have
received this e-mail in error, please notify me immediately by a
return e-mail and delete this e-mail. You are hereby notified that
any dissemination, distribution or copying of this e-mail and/or any
attachments thereto, is strictly prohibited.

--
This is the RPG programming on the AS400 / iSeries (RPG400-L) mailing

list

To post a message email: RPG400-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/rpg400-l.