Re: Embedded static sql case

[Michael Schutte <mschutte369@xxxxxxxxx>]

My experience with this method has been bad performance with either a range or set to the same value. 

Sent from my iPhone

On Jan 29, 2013, at 7:08 PM, Sam_L <lennon_s_j@xxxxxxxxxxx> wrote:

> If I understand you correctly, you want to select a specific company, or 
> all companies.
>
> What about this approach?
>
> WHERE COMPANY BETWEEN :LO_CMPNY and :HI_CMPNY
>
> Before you open the cursor:
>
> If you want a specific company set both LO_CMPNY and HI_CMPNY to the 
> same value, the company you want.
>
> If you want all companies, set LO_CMPNY to *LOVAL and HI_CMPNY to *HIVAL.
>
> Sam
>
> On 1/29/2013 9:28 AM, Horn, Jim wrote:
> > I know I could do this with dynamic sql but how about static -
> >
> > I want to put options in the select criteria based on something entered
> > into the program.  Is there some kind of case statement that would work?
> > Something like -
> > WHERE
> >  (CASE WHEN 0 = :@COMPANY THEN 1
> >      WHEN 0 < :@COMPANY AND A.ITCOMPANY = @COMPANY  THEN 1
> >        ELSE 0
> >   END) = 1
> >
> > where @company is 0 or a company# entered into the program
> >
> > Jim
> -- 
> This is the RPG programming on the IBM i (AS/400 and iSeries) (RPG400-L) mailing list
> To post a message email: RPG400-L@xxxxxxxxxxxx
> To subscribe, unsubscribe, or change list options,
> visit: http://lists.midrange.com/mailman/listinfo/rpg400-l
> or email: RPG400-L-request@xxxxxxxxxxxx
> Before posting, please take a moment to review the archives
> at http://archive.midrange.com/rpg400-l.