Re: a small query -- MIDRANGE-L

Murali

I think you'd take the PFIRM test out of the 2 places it is now and put it first, with the *IF. And change the *IF you have now to a *OR

CPYF       FROMFILE(QS36G/GPOSF1) +
              TOFILE(QS36F/YPOSF1) +
              MBROPT(*REPLACE) CRTFILE(*YES) +
              INCREL((*IF PFIRM *EQ 'J') +
              (*OR POFFIC *EQ '980') (*AND PEXCH *EQ '22') +
              (*OR POFFIC *EQ '980') (*AND PEXCH *EQ '24'))+
               FMTOPT(*NOCHK)

SQL really shines when you have an AND condition of 2 lists (essentially multiple ORs). You had a single value ANDed with a list of 2 items. A total of 1x2 POFIC/PEXCH combination. But if you had 5 POFIC values and 6 PEXCH values, you'd need 5X6 pairs of POFIC/PEXCH. With SQL, you could use the first set with ORs, in parentheses, ANDed with the second set of ORs (in parentheses). Or even simpler for us developers, IN predicates.

BTW, IN predicate lists are converted to multiple ORs by the optimizer, IIRC, in various associative, commutative, and distributive ways.

HTH
Vern

At 07:00 PM 11/20/2003 -0800, you wrote:

Vern! you are really great! it worked out for
me.Thanks somuch!
The sql  query you suggested is wokring fine,bt i
dunno how to convert this sql query exactly into cpyf
selection (INCREL),anyway no problems!
Thank you,
Murali.
--- Vern Hamberg <vhamberg@xxxxxxxxxxxxxxxxxxxxxxxxx>
wrote:
> This ;-) is why SQL is so nice - this is just
> painful, although possible.
> You cannot use parentheses here, so things get
> messy.
>
> I believe that *AND has precedence over *OR - any
> *AND conditions are
> evaluated before the *OR's. So, in SQL, you have
>
> PFIRM = 'J' or
>
> (POFFIC = '980' and (PEXCH = '22' or PEXCH = '24'))
>
> By associative properties you can figure out the 2
> *OR clauses for the
> second part.
>
> Personally, I'd put an SQL select into a QMQRY, the
> STRQMQRY with
> OUTPUT(*OUTFILE) OUTMBR(*FIRST *REPLACE)
>
> HTH
> Vern
>
> At 01:27 AM 11/19/2003 -0800, you wrote:
> >Hi
> >please help me here
> >CPYF       FROMFILE(QS36G/GPOSF1) +
> >              TOFILE(QS36F/YPOSF1) +
> >              MBROPT(*REPLACE) CRTFILE(*YES) +
> >
> >              INCREL((*IF POFFIC *EQ '980') (*AND
> PFIRM
> >+
> >              *EQ 'J') (*AND PEXCH *EQ '22') (*OR +
> >
> >              POFFIC *EQ '980') (*AND PFIRM *EQ
> 'J') +
> >
> >              (*AND PEXCH *EQ '24')) FMTOPT(*NOCHK)
> >
> >this is to select office=980 firm=J and Exch=22,24.
> >-----------------------------------------
> >Now I ve to include all records with firm=J...
> >How do i change my query,Iam bit confused,some one
> >confirm  me please !
> >Thanks for your help in adv,
> >Murali.