OPNQRYF

[T.Holt]

<font color="blue">On Wednesday, April 29, 1998, 11:20 PM, Sarah Cooper wrote: I need to join two record formats of a logical file with a physical file. When you specify the FORMAT you have to specify one of the record formats of the logical file therefore any fields that do not exist on that particular record format will not be included in the query. Does anyone know a way to get around this??</font> Let me see if I understand. You have a physical file and a logical file. The logical file has at least 2 record formats. You want to join two of the logical file record formats (I'll call them A and B) to the physical file. So you would wind up with a join that has two different record types -- the physical joined to A and the physical joined to B. Am I right? If so, you can't do this with one join. You could run two different OPNQRYF's -- one for each join -- and your RPG/COBOL program would have to process each one as a separate file. In such a case, it may be better to process the physical file and logical files without going thru OPNQRYF. Maybe you should describe the problem. It sounds like OPNQRYF may not be the best approach. <font color="blue"> Additionally does anyone know how to specify the record format on the MAPFLD parm for the field definition expression?? </font> Use numbers, indicating the position of the file in the FILE parameter. E.g. OPNQRYF FILE((ABC) (DEF) (GHI)) + MAPFLD(( .... '2/SOMEFIELD') SOMEFIELD gets its value from DEF, the 2nd file listed in the FILE parameter. Since the record format parameter is specified in the FILE parameter, this technique does what you're asking.

[Guest.Visitor]

On Wednesday, April 29, 1998, 11:20 PM, Sarah Cooper wrote: I need to join two record formats of a logical file with a physical file. When you specify the FORMAT you have to specify one of the record formats of the logical file therefore any fields that do not exist on that particular record format will not be included in the query. Does anyone know a way to get around this?? Additionally does anyone know how to specify the record format on the MAPFLD parm for the field definition expression?? Just create a physical file which has the desired resultant format <ie. format to describe the combined/joined results>. I find the easiest way is to use Query/400 WRKQRY interface to define effectively the same join the OPNQRYF will do, then run the query with select that will return just a couple of records. Since you do not need the member <like a FRF it is used only for the format> you should RMVM of the file just created. <pre> OPNQRYF FILE((DATA512)) FORMAT(DATA512X) MAPFLD( (DATA512# '%len(%strip(data512 '' '' *trail))' *CALC) (DATA512A '%sst(data512 (%len(%strip(data512 '' '' *trail))) 1)') ) /* File DATA512X has fields: DATA512# P( 9,0) DATA512A A( 1) DATA512 A( 512) and File DATA512 has field: DATA512 A( 512) */ </pre> If the data512 had required per like-named fields in a join scenario, it is modified to be 1/data512 where the '1' indicates the first file in the join specifications. Regards, Chuck Comments provided "as is" with no warranties of any kind whatsoever.