× The internal search function is temporarily non-functional. The current search engine is no longer viable and we are researching alternatives.
As a stop gap measure, we are using Google's custom search engine service.
If you know of an easy to use, open source, search engine ... please contact support@midrange.com.


  1.  Home
  2. RPG400-L
  3. June 2007

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/


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

--
This is the RPG programming on the AS400 / iSeries (RPG400-L) digest
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.



End of RPG400-L Digest, Vol 6, Issue 672
****************************************

--
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.

As an Amazon Associate we earn from qualifying purchases.

This thread ...
Replies:
Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2024 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].

Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.