Announcement

**jamief** · July 25, 2004, 02:53 PM

Welcome Willzx

First lets understand what you are doing here.

I think

You are creating a query first.

The query outputs a table....

Then you call the RPG to read the file.

If this is the case then we need to look into logical files and
select/omit logic.....

Please post an example of the query select and I can see where to go from here.

Welcome (Again)

Jamie

**willzx** · July 26, 2004, 02:19 AM

Hi..
Ok, i have these :

example :

OPNQRYF in CLLE (CEMPLOYEE)
-----------------------------------------------------------------------------------
PGM PARM(&USER &EMNO &NAME)

DCL VAR(&USER) TYPE(*CHAR) LEN(2)
DCL VAR(&EMNO) TYPE(*CHAR) LEN(8)
DCL VAR(&NAME) TYPE(*CHAR) LEN(30)

OPNQRYF FILE(EMPLOYEE) +
OPTION(*INP) +
QRYSLT('USER *EQ "' || &USER || '" *AND +
EMNO=%WLDCRD("' *TCAT &EMNO *TCAT '" "?*") *AND +
NAME = %WLDCRD("' *TCAT &NAME *TCAT '" "?*")') +
KEYFLD((USER) (EMNO) (NAME))
-----------------------------------------------------------------------------------

With that CLLE up there i used to set the database in RPGLE, like this :

FEMPLOYEE IF E K DISK
-----------------------------------------

and calling the query :

C EVAL $CMD = 'OVRDBF FILE(EMPLOYEE) ' +
C 'TOFILE(EMPLOYEE) SHARE(*YES)'
C CALL 'QCMDEXC'
C PARM $CMD 134
C PARM 100 $LEN 15 5
C CLOSE EMPLOYEE
C CALL CEMPLOYEE
C PARM $USER
C PARM $EMNO
C PARM $NAME
C OPEN EMPLOYEE

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

What i'd like to ask you is :

when we are querying the datas with join table. Logically if joining 2 or more databases there will be 1 database created.
what i'd like to know is the created database to be phisically exist in PF or only just like a temporary tables without phisical file. ???

-------------------------------------------------------------------------------
PGM PARM(&USER &COP &DEPT)

DCL VAR(&USER) TYPE(*CHAR) LEN(2)
DCL VAR(&COP) TYPE(*CHAR) LEN(2)
DCL VAR(&DEPT) TYPE(*CHAR) LEN(4)

OPNQRYF FILE(D1P9JX011 D1P9JXA11) +
OPTION(*INP) +
FORMAT(W1P9JXW11) +
QRYSLT('USRX011 *EQ "'|| &USER ||'" *AND +
COPX011 = %WLDCRD("' *TCAT &COP *TCAT '" "?*")*AND +
DPTX011 = %WLDCRD("' *TCAT &DEPT *TCAT '" "?*")' ) +
MAPFLD((USRXW11 'USRX011') +
(CIDXW11 'CIDX011') +
(COPXW11 'COPX011') +
(DPTXW11 'DPTX011') +
(ECDXW11 'ECDXA11')) +
JFLD((1/USRX011 2/USRXA11) +
(1/CIDX011 2/CIDXA11)) +
JDFTVAL(*YES) +
OPNID(ID)
RETURN
-------------------------------

question :
How can i set the database in RPGLE :

FW1P9JXW11 ? ? ? ??????????????????

or is it W1P9JXW11, it has to be PF ??

---------------------
I don't if u can catch my question, and i'm sorry if confusing you.
If my theory or knowledge is wrong, please do me a favor,
explain to me.
Thx u

**idscypher** · July 26, 2004, 02:30 AM

welcome willzx

i also think that by explain the logical files you will understand the use of them for what you are doing.

jamief will explain better than me

meanwhile maybe you can find some info here

http://martinri.freeshell.org/as400/logical-files/

Basic AS/400 Tips

http://www.texas400.com/b400tip18.html

**jamief** · July 26, 2004, 06:16 AM

check back later tonight

After work lets out today I will post a "short" (dont laugh idscypher cause I said short) example tonight as close to your data as possible.

Hang in there until then.

Jamie

**idscypher** · July 26, 2004, 07:38 AM

hehe

**jamief** · July 26, 2004, 06:38 PM

Jamie takes the easy way out

Your English is better than mine...........But I am a guy and dont always read the whole message....I hope My answer is what you were looking for.

Yes the file that is created resides only in memory and not as an
actual physical file.

Okay I think I can make this easy enough.

I would either use imbedded SQL instead of OPNQRYF
(IBM will be dropping support for OPNQRYF someday)

Please take a look at some of the examples here

Page not found – Code400

http://www.code400.com/viewsamples.php?lang_id=3

But thats no help to you now.

Here is what you need the command CPYFRMQRYF

This command will create a physical file from the index you created with the OPNQRYF.

You will define the file in an RPGLE program as USROPN and do
an override on the physical created with the CPYFRMQRYF command. Then open your file and KAPOW Data.........

Code:

CPYFRMQRYF FROMOPNID(ID) TOFILE(SOMELIB/SOMEFILE) CRTFILE(*YES)

I hope this helps.

**willzx** · July 26, 2004, 07:01 PM

Thank u before, but i done the :
CPYFRMQRYF before. What i'd like to know is
is there any way that we can access query without being copied it
into PF, we just read from the query file itself ???

When i use VB, it doesn't have to copy it first into another table,
just use it with the recordset to add, update or delete the database.
It's there anyway to do it without using PF? i'd like to this for saving system spaces, Thx u

**jamief** · July 26, 2004, 07:34 PM

sure - You must use SQL or Logical files

Do you have SQL installed?

The easiest way to check this is just type on the command line

STRSQL.

If you do I will show you the SQLRPGLE if not your gonna have to use logicals and RPG logic to subset your data directly from the Iseries table.

Else

Physicals and logical files

Lets say you want all USER = 'AS'
but only want department '0027'

Your keyed physical would look like this
file is keyed by user field.

Code:

     A          R PHYR                      TEXT('Physical')
     A*
     A            USER          02          TEXT('User')
     A            COP           02          TEXT('Cop')
     A            DEPT          04          TEXT('Department')
     A*
     A          K USER

A logical looks like this your physical keyed by department

Code:

A          R PHYR                      PFILE(PHYSICAL) 
A          K DEPT

here is code example

Code:

     fPHYSICAL  UF A E           K DISK
      *
      *  Field Definitions.
      *
     d KeyUser         S             02
     d Dec4            S             04  0
      *
      * read file by user and select only dept '0027'
      *
     c                   exsr      $Read
      *
      * read file by user and select only dept '0027' or dept '0037'
      *
     c                   exsr      $Read2
      *
      * delete all records for user 'AS'  dept '0027'
      *
     c                   exsr      $delete
      *
      * Write three records to the file
      *
     c                   exsr      $write
      *
      * update all records for user 'AS'  dept '0027'
      * set dept = '0037'
      *
     c                   exsr      $update
      *
     c                   eval      *inLR = *On
      *-----------------------------------------------
      * $Read - This is example on how we read
      *         file(Keyed) by user = "AS" and
      *         department "0027"
      *-----------------------------------------------
     c     $Read         begsr
      *
     c                   eval      KeyUser = 'AS'
     c     KeyUser       setll     PHYSICAL
     c     KeyUser       reade     PHYSICAL
     c                   dow       not%Eof(PHYSICAL)
      *
     c                   if        DEPT = '0027'
      *
      *  do something
      *
     c                   endif
      *
     c     KeyUser       reade     PHYSICAL
     c                   enddo
      *
     c                   endsr
      *-----------------------------------------------
      * $Read2- This is example on how we read
      *         file(Keyed) by user = "AS" and
      *         department "0027" or "0037"
      *-----------------------------------------------
     c     $Read2        begsr
      *
     c                   eval      KeyUser = 'AS'
     c     KeyUser       setll     PHYSICAL
     c     KeyUser       reade     PHYSICAL
     c                   dow       not%Eof(PHYSICAL)
      *
     c                   if        DEPT = '0027'
     c                             or DEPT = '0037'
      *
      *  do something
      *
     c                   endif
      *
     c     KeyUser       reade     PHYSICAL
     c                   enddo
      *
     c                   endsr
      *-----------------------------------------------
      * $Delete- This is example on how we delete
      *          file(Keyed) by user = "AS" and
      *          department "0027"
      *-----------------------------------------------
     c     $Read         begsr
      *
     c                   eval      KeyUser = 'AS'
     c     KeyUser       setll     PHYSICAL
     c     KeyUser       reade     PHYSICAL
     c                   dow       not%Eof(PHYSICAL)
      *
     c                   if        DEPT = '0027'
      *
      *  notice we delete by record format.....
      *
     c                   delete    PHYR
      *
     c                   endif
      *
     c     KeyUser       reade     PHYSICAL
     c                   enddo
      *
     c                   endsr
      *-----------------------------------------------
      * $Write - Write 3 records
      *-----------------------------------------------
     c     $Write        begsr
      *
     c                   do        3
     c                   eval      Dec4 = (Dec4 + 1)
     c                   move      Dec4          DEPT
     c                   eval      USER = 'JF'
     c                   eval      COP  = 'XX'
     c                   write     PHYR
     c                   enddo
      *
     c                   endsr
      *-----------------------------------------------
      * $Update -  This is example on how we update
      *            file(Keyed) by user = "AS" and
      *            department "0027" to dept "0037"
      *-----------------------------------------------
     c     $Update       begsr
      *
     c                   eval      KeyUser = 'AS'
     c     KeyUser       setll     PHYSICAL
     c     KeyUser       reade     PHYSICAL
     c                   dow       not%Eof(PHYSICAL)
      *
     c                   if        DEPT = '0027'
      *
     c                   eval      DEPT = '0037'
     c                   update    PHYR
      *
     c                   endif
      *
     c     KeyUser       reade     PHYSICAL
     c                   enddo
      *
     c                   endsr
      *-----------------------------------------------

With your two table OPNQRYF example you would need to define both files and chain to one file by the other. I assume the like field(s) would be the user.
Jamie

**willzx** · July 26, 2004, 08:55 PM

Ok, now maybe i can assume that, in as400. There is no such thing like recordset.
I just want to compare it with with VB, if i would like to join 2 or more databases, i can create one database without creating another database only recordset. I'm sorry for bothering u all and thank u for Jamie and Idschyper that has helping me

**jamief** · July 26, 2004, 09:19 PM

I wrote so much I didnt want to delete

(Thats what SQL is used for)

I think I get it know.

All you want to know is when I do a join over 2 or more files how can I access the data.

You must create a physical file with the fields from both or all of the fields (DIFFERENT NAMES).

physical file #1

Code:

A          R PHYR                      TEXT('Physical')     
A*                                                          
A            USER          02          TEXT('User')         
A            COP           02          TEXT('Cop')          
A            DEPT          04          TEXT('Department')   
A*                                                          
A          K USER

physical file #2

Code:

A          R PHYR2                     TEXT('Physical')  
A*                                                       
A            USER2         02          TEXT('User')      
A            COP2          02          TEXT('Cop')       
A            DEPT2         04          TEXT('Department')
A            PIZZA         10          TEXT('Pizza')     
A*                                                       
A          K USER2

physical file #3 This is just DDS no data will ever be placed into this file.

Code:

A          R JOINEDR                   TEXT('Physical')       
A*                                                            
A            USER3         02          TEXT('User')           
A            COP3          02          TEXT('Cop')            
A            DEPT3         04          TEXT('Department')     
A            PIZZA3        10          TEXT('Pizza')          
A*                                                            
A          K USER3

Then that big-o-ugly OPNQRYF

Code:

        OVRDBF     FILE(JOINEDP) SHARE(*YES)                     
        OPNQRYF    FILE((PHYSICAL) (PHYSICAL2)) +               
                     FORMAT(*LIBL/JOINEDP JOINEDR) +            
                     QRYSLT('USER = "JF"') JFLD((PHYSICAL/USER +
                     PHYSICAL2/USER2)) JDFTVAL(*YES) +          
                     MAPFLD((USER3 USER) (COP3 COP) (DEPT3 +    
                     DEPT2) (PIZZA3 PIZZA)) OPNID(JOINEDP)      
                                                                
          
                                                                
        CLOF JOINEDP

now in RPG just read file JOINEDP

good nite

**willzx** · July 26, 2004, 09:53 PM

Woww i get the picture now

I never thought about using ovrdbf in the CLLE

Thanks u so much, now i know it must be the answer

Oh ya, Jamie.. one more question,
is it the same when in SEU F(Format) area, would be
like this :

fJOINEDP IF E K DISK RENAME(JOINEDR:JOIN)

is it like this ?? but anyway i'll try it..
Thanks u again

**jamief** · July 27, 2004, 04:57 AM

I dont believe you have to rename format

Should work just like this:

Code:

fJOINEDP IF E K DISK 

*start   setll   JOINEDP
             read  JOINEDP
             Dow  Not%Eof(JOINEDP)

            ===>Your Process
 
             read  JOINEDP
             enddo

Its been a while since I have used OPNQRYF.
You'll have to play around to get exactly right.

I think you can update also by playing with this option
OPTION(*INP)

Open options (OPTION) - Help

Specifies the open option used for the query file. The
options chosen on the first full open of a file are not
changed on subsequent shared opens. You can either
specify *ALL or a value that combines *INP, *OUT, *UPD,
and *DLT in a list of up to four values in any order. If
no value is specified, the default is *INP.

You can enter multiple values for this parameter. If you
are on an entry display and you need additional entry
fields to enter these multiple values, type a plus sign
(+) in the entry field opposite the phrase "+ for more"
and press the Enter key.

The possible values are:

*INP
Open the file for input. *INP is the only value
allowed if join processing or group processing is
requested, if UNIQUEKEY processing is specified, or if
all the fields in the open query file record format

specified on the Format specifications prompt (FORMAT
parameter) are for input-only use.

*OUT
Open the file for output. In some high-level
languages, output to certain files (such as files
defined as 'direct access' in the high-level language
program) is done by using a combination of input and
update operations. *UPD and *INP are specified, or
*ALL is specified, in order to use an open query file
with such a program.

*UPD
Open the file for update operations. If an input
operation comes before an update, you must specify
*INP when *UPD is specified.

*DLT
Open the file for delete operations. If a delete
operation is preceded by an input operation, you must
specify *INP when *DLT is specified.

*ALL
Open the file for all operations (*INP, *OUT, *UPD,
*DLT).

good luck

Announcement

How to get Query Value in RPGLE From CLLE without copying it first into PF ?

How to get Query Value in RPGLE From CLLE without copying it first into PF ?

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment