Re: Query Definitions

[Vernon Hamberg <hambergv@xxxxxxxxxxxxxx>]

Mani

At 02:39 PM 5/5/1998 -0700, you wrote:
>>>> 
> Hello Everyone:
>
> Is there any command/API to retrieve the files used by a QRYDFN?
>
> I need to be able to output the list to a PF.

<<<<

I think there was a recent thread on this topic, so I'll try to remember some of it.

Besides Glenn Gunderman's approach with DMPOBJ, I can think of 2 approaches:

1. You can RUNQRY with PRTDFN(*YES). You'll get a spooled file that has a section called 'Selected Files' or some such. This gives you a pretty nicely formatted listing of the files. CPYSPLF, with RCVF loop in CL can handle the analysis very nicely, or an RPG program. The definition is printed at the front of the report when output is directed to *PRINTER. Typical output as follows:

>>>>
> 5769SS1  V4R1M0  970829            IBM Query/400             MT400     5/05
> Query . . . . . . . . . . . . . . . . . QRYSELTITL                    
>
> Library . . . . . . . . . . . . . . . PGMRVMH                       
>
> Query text  . . . . . . . . . . . . . . query to get unmatched order #'s 
> Query CCSID . . . . . . . . . . . . . . 65535                         
>
> Query language id . . . . . . . . . . . ---                           
>
> Query country id  . . . . . . . . . . . --                            
>
> Collating sequence  . . . . . . . . . . Hexadecimal                   
>
> Processing options                                                    
>
> Use rounding  . . . . . . . . . . . . Yes (default)                 
>
> Ignore decimal data errors  . . . . . No (default)                  
>
> Ignore substitution warnings  . . . . Yes                           
>
> Use collating for all compares  . . . No                            
>
> Special conditions                                                    
>
> *** All records selected by default ***                             
>
> Selected files                                                          
>
> ID     File           Library       Member        Record Format       
>
> T01    OESELRP        PDNDTALIB     OESELRP       SELR                
>
> T02    OETITLP        PDNDTALIB     OETITLP       TITL                
>
> Join tests                                                   
> Type of join  . . . . . . . . . . . . . Matched records    
> Field              Test              Field                 
> T01.SLORDN         EQ                T02.MTORDN            
<<<<


2. Use RTVQMQRY to generate an SQL statement into a source member. Analysis looks even easier. And you don't have leftover spooled files. Typical usage:

>>>>
> RTVQMQRY QMQRY(PGMRVMH/QRYSELTITL) 
> SRCFILE(PGMRVMH/QQMQRYSRC)
> ALWQRYDFN(*ONLY)          
<<<<

Typical layout:

>>>>
> H QM4 05 Q 01 E V W E R 01 03 98/05/05 19:46 
> V 1001 050 query to get unmatched order #'s - OESELRP/OETITLP
> V 5001 004 *HEX                
> SELECT                         
> ALL       T01.SLORDN, T01.SLSELR, T01.SLSEQN
> FROM      PDNDTALIB/OESELRP T01,
> PDNDTALIB/OETITLP T02
> WHERE     T01.SLORDN = T02.MTORDN
> ORDER BY  T01.SLORDN ASC     
<<<<

Join criteria will be included in the WHERE clause, but so will record selection.

HTH


Vernon Hamberg
Systems Software Programmer
Old Republic National Title Insurance Company
400 Second Avenue South
Minneapolis, MN  55401-2499
(612) 371-1111 x480


+--- | This is the Midrange System Mailing List! | To submit a new message, send your mail to MIDRANGE-L@midrange.com. | To unsubscribe from this list send email to MIDRANGE-L-UNSUB@midrange.com. | Questions should be directed to the list owner/operator: david@midrange.com +---