Re: SQL ignore part of WHERE if parameter is null

[Mónica Anastácio <monicaanastacio@xxxxxxxxx>]

This is what we did and work for us. Tkans u all


*where irseq >= :#crfrnseqp and                 irpgm = case when
:#crfrpgms = ' ' then irpgm else :#crfrpgms end*

Final Version
..........
   select;
     when #crfripos = 'S';
       exec sql                                    // Declara o Cursor
         declare c_tcinrely_S scroll Cursor for
           Select irseq, irdseq, irdlsq, irpgm, iravis, tcirvins, irdatu,
irhoru
           From manastacio/tcinrely


*where irseq >= :#crfrnseqp and                 irpgm = case when
:#crfrpgms = ' ' then irpgm else :#crfrpgms end           Order By irseq,
irdseq;*
       exec sql                                    // Abre o Cursor
         open c_tcinrely_S;
       exec sql                                    // Consulta a Informação
e Guarda na DS
         fetch next from c_tcinrely_S for :wnnreg rows into :wsele;
     when #crfripos = 'N';
       exec sql                                    // Declara o Cursor
         declare c_tcinrely_N scroll Cursor for
           Select irseq, irdseq, irdlsq, irpgm, iravis, tcirvins, irdatu,
irhoru
           From manastacio/tcinrely


*     where irseq > :#crfrnseqp and                 irpgm = case when
:#crfrpgms = ' ' then irpgm else :#crfrpgms end           Order By irseq,
irdseq;*
       exec sql                                    // Abre o Cursor
         open c_tcinrely_N;
       exec sql                                    // Consulta a Informação
e Guarda na DS
         fetch next from c_tcinrely_N for :wnnreg rows into :wsele;
  endsl;
.........

On Mon, Mar 9, 2020 at 2:43 PM Mónica Anastácio <monicaanastacio@xxxxxxxxx>
wrote:

>    exec sql                                    // Declara o Cursor
>
>      declare c_tcinrely scroll Cursor for
>
>      Select irseq, irdseq, irdlsq, irpgm, iravis, tcirvins, irdatu, irhoru
>
>          From manastacio/tcinrely
>
>          where irseq >= :#crfrnseqp and
>
>                irpgm = case when #crfrnseqp = 0 then irpgm else #crfrnseqp
> end
>          Order By irseq, irdseq;
>
>      exec sql                                    // Abre o Cursor
>
>        open c_tcinrely;
>
>      exec sql                                    // Consulta a Informação
> e Guarda na DS
>        fetch next from c_tcinrely for :wnnreg rows into :wsele;
>
>
>
> sqlcode -501 in cursor <http://ibmmainframes.com/about55937.html>
>
>
>
> On Mon, Mar 9, 2020 at 12:56 PM Justin Taylor <JUSTIN@xxxxxxxxxxxxx>
> wrote:
>
> > How about this?
> >          where (ncnlnorn = :wnnorn or :wnnorn = '')  and ncnldtpco =
> > :wndtpco
> >
> >
> >
> > -----Original Message-----
> > From: Mónica Anastácio [mailto:monicaanastacio@xxxxxxxxx]
> > Sent: Saturday, March 07, 2020 2:50 AM
> > To: RPG programming on IBM i <rpg400-l@xxxxxxxxxxxxxxxxxx>
> > Subject: SQL ignore part of WHERE if parameter is null
> >
> > select * from ncnotl
> >          where ncnlnorn = :wnnorn and ncnldtpco = :wndtpco
> >
> > anyone now if its  possible to ignore this -  ncnlnorn = :wnnorn - when
> > the field has no information and pass to the other condition.
> >
> > --
> > This is the RPG programming on IBM i (RPG400-L) mailing list
> > To post a message email: RPG400-L@xxxxxxxxxxxxxxxxxx
> > To subscribe, unsubscribe, or change list options,
> > visit: https://lists.midrange.com/mailman/listinfo/rpg400-l
> > or email: RPG400-L-request@xxxxxxxxxxxxxxxxxx
> > Before posting, please take a moment to review the archives
> > at https://archive.midrange.com/rpg400-l.
> >
> > Please contact support@xxxxxxxxxxxx for any subscription related
> > questions.
> >
> > Help support midrange.com by shopping at amazon.com with our affiliate
> > link: https://amazon.midrange.com