Read Distinct records through Openquery

[debz]

I would like to know how can we read records using Openquery subjected to the following condiitons.

1. Record selection based on some condition
2. selection of distinct record in case more than one record exists for the Key filelds comibantion.

I Tried the following but it throws exception.

OPNQRYF FILE((RBLFMTN3)) OPTION(*ALL) QRYSLT('(TFDOS +
*LT ZSDAT) *AND (TFDOS *GT ZEDAT)') +
KEYFLD(*FILE) UNIQUEKEY(2) MAPFLD((ZSDAT +
&SDAT *ZONED 8) (ZEDAT &EDAT *ZONED 8)) +

Please help if some one knows it?.......PLs...

[jamief]

Re: Read Distinct records through Openquery

It has been many moons since I have used QPNQRYF....
I would recoommend you look into SQL for your answer.

Also it seems odd that you would not include duplicate key
records...can you explain this more in detail.

Can you create SQLRPGLE programs?


jf

[arrow483]

Re: Read Distinct records through Openquery

I think, to start with, your quotes are in the wrong places in the QRYSLT statement. You are specifiying a constant (by virtue of the quotes) instead of a compare expression.

You want something more like

DCL &QRYSLT

CHGVAR &QRYSLT VALUE('(' *CAT TFDOS +
' *LT ' *CAT ZSDAT *CAT ') *AND (' *CAT TFDOS *CAT ' *GT ' +
*CAT ZEDAT *CAT')')

Then replace QRYSLT with QRYSLT(&QRYSLT)

The trick is that the constants (such as paren, operators, etc ) must be enclosed in quotes, and the field / varibale names must NOT be enclosed. Using a &QRYSLT variable makes it easier to code, and also allows to to output and/or debug the &QRYSLT value just before the OPNQRYF command to see what its going to look like.

This doesn't address the "distinct", but should help the "select" part.

[jamief]

Re: Read Distinct records through Openquery

@#%@#%@#%@#% just when I thought I was away arrow pulls me back in

Here is an example the other way......
It is important that you start using SQL more often and OPNQRYF less.
I think that ist not even supported anymore by IBM. But what is

anyway

             PGM        PARM(&COMP)

             DCL        VAR(&COMP)   TYPE(*CHAR) LEN(2)
             DCL        VAR(&FRMGL#) TYPE(*CHAR) LEN(10)
             DCL        VAR(&TOGL#)  TYPE(*CHAR) LEN(10)
             DCL        VAR(&FRDATC) TYPE(*CHAR) LEN(8)
             DCL        VAR(&TODATC) TYPE(*CHAR) LEN(8)
             DCL        VAR(&FRDATZ) TYPE(*CHAR) LEN(8)
             DCL        VAR(&TODATZ) TYPE(*CHAR) LEN(8)
             DCL        VAR(&ERRFLG) TYPE(*CHAR) LEN(1)

    /*  RETRIEVE PARMS FROM LOCAL DATA AREA  AS CHARACTER */
             RTVDTAARA  DTAARA(*LDA (1 6)) RTNVAR(&FRDATC)
             RTVDTAARA  DTAARA(*LDA (7 6)) RTNVAR(&TODATC)
             RTVDTAARA  DTAARA(*LDA (13 10)) RTNVAR(&FRMGL#)
             RTVDTAARA  DTAARA(*LDA (23 10)) RTNVAR(&TOGL#)

             IF         COND(&FRDATC *EQ '000000') THEN(DO)
             CHGVAR     VAR(&FRDATZ) VALUE('0000000')
             ENDDO
             ELSE       CMD(DO)
             CALL       PGM(CVTDTC0) PARM(&FRDATC &FRDATZ '*MDY' +
                          '*CYMD' &ERRFLG)
             ENDDO

             IF         COND(&TODATC *EQ '000000' *OR &TODATC *EQ +
                          '999999') THEN(DO)
             CHGVAR     VAR(&TODATZ) VALUE('9999999')
             ENDDO
             ELSE       CMD(DO)
             CALL       PGM(CVTDTC0) PARM(&TODATC &TODATZ '*MDY' +
                          '*CYMD' &ERRFLG)
             ENDDO

             IF         COND(&COMP = '20') THEN(DO)
             OVRPRTF    FILE(QSYSPRT) OUTQ(RPRT2001)
             ENDDO
             IF         COND(&COMP = '30') THEN(DO)
             OVRPRTF    FILE(QSYSPRT) OUTQ(RPRT3006)
             ENDDO
             IF         COND(&COMP = '72') THEN(DO)
             OVRPRTF    FILE(QSYSPRT) OUTQ(RPRT7206)
             ENDDO

             OVRDBF     FILE(APDCDISK) TOFILE(APDCDIS) SHARE(*YES)

             OPNQRYF    FILE((APDCDIS) (APAVEND)) FORMAT(APDCDISK) +
                          QRYSLT('GL# = %RANGE(' *CAT &FRMGL# *BCAT +
                          &TOGL# *CAT ') *AND ADCKDT = %RANGE(' +
                          *CAT &FRDATZ *BCAT &TODATZ *CAT ')') +
                          KEYFLD((ADGL#1) (ADGL#2) (ADGL#3) +
                          (ADGL#4) (AAALPH) (ADVEN#) (ADCKDT) +
                          (ADCHK#)) JFLD((1/ADVEN# 2/AAVEN#)) +
                          MAPFLD((GL# '(ADGL#1 * 10000000) + +
                          (ADGL#2 * 100000) + (ADGL#3 * 100) + +
                          ADGL#4')) ALWCPYDTA(*OPTIMIZE)

             CALL       PGM(APA33) PARM(&COMP)

             CLOF       OPNID(APDCDIS)

             ENDPGM