|
midrange.com
If you are doing this programmatically, then you could create the
execute string on the fly and use an array for the month field names:
<psuedo-code>
d pMonthFields s * inz( %addr( monthFields ) )
d fieldList s 10a based( pMonthFields ) dim(12)
d monthFields ds
d 10a inz('SAL01')
d 10a inz('SAL02')
d 10a inz('SAL03')
d 10a inz('SAL04')
d 10a inz('SAL05')
d 10a inz('SAL06')
d 10a inz('SAL07')
d 10a inz('SAL08')
d 10a inz('SAL09')
d 10a inz('SAL10')
d 10a inz('SAL11')
d 10a inz('SAL12')
d sqlStatement s 32767a varying
/free
sqlStatment = 'Update %%FILENAME%% set ' +
%trim( monthFields( %%monthVariable ) ) +
' = 0 ' ;
/end-free
c/exec sql
c+ Execute Immediate :sqlStatement
c/end-exec
</psuedo-code>
Joel
http://www.rpgnext.com
On Thu, 2004-03-04 at 18:55, THarteau@xxxxxxxxxxxxxxxxxx wrote:
> Hi,
>
> UPDATE ROB/FELKER
> SET
> (case when current month= 1 then SAL01 = :MYVAR
> when current month= 2 then SAL02 = :MYVAR
> when current month= 3 then SAL03 = :MYVAR
> END)
>
> That would work, I would only need one SQL statement. But I was thinking
> more along the lines of:
> I know what the month number is
> Use an If to put the field name in :MONTH
> Then SET :MONTH = 0
>
> Typing this in, I realize I still need an IF or SELECT to get the field
> name. Your way might be clearer.
>
> <===================================================>
>
> Terri Harteau
> Felker Brothers Corporation
> ****************
> "There's no point in being grown up if you can't be childish sometimes."
> - Dr. Who
> ****************
>
>
>
>
>
>
>
>
> rob@xxxxxxxxx
>
> Sent by: To: RPG programming on
> the AS400 / iSeries
> rpg400-l-bounces@m <rpg400-l@xxxxxxxxxxxx>
>
> idrange.com cc:
>
> Subject: Re: SQL Update
> using Variable Field
>
>
> 03/04/2004 12:32
>
> PM
>
> Please respond to
>
> RPG programming on
>
> the AS400 /
>
> iSeries
>
>
>
>
>
>
>
>
> I don't have this worked out yet, but is this kind of what you are
> thinking:
> UPDATE ROB/FELKER
> SET
> (case when current month= 1 then SAL01 = :MYVAR
> when current month= 2 then SAL02 = :MYVAR
> when current month= 3 then SAL03 = :MYVAR
> END)
>
> Rob Berendt
> --
> Group Dekko Services, LLC
> Dept 01.073
> PO Box 2000
> Dock 108
> 6928N 400E
> Kendallville, IN 46755
> http://www.dekko.com
>
>
>
>
>
> THarteau@xxxxxxxxxxxxxxxxxx
> Sent by: rpg400-l-bounces@xxxxxxxxxxxx
> 03/04/2004 12:52 PM
> Please respond to
> RPG programming on the AS400 / iSeries <rpg400-l@xxxxxxxxxxxx>
>
>
> To
> RPG programming on the AS400 / iSeries <rpg400-l@xxxxxxxxxxxx>
> cc
>
> Subject
> SQL Update using Variable Field
>
>
>
>
>
>
>
> Hi,
> I can't find anywhere where it says I can't do this, but I'm not
> sure
> how to achieve it. I have a file with a sales amount for each month
> (SAL01,SAL02...SAL12). I want to use an SQL statement to zero the month in
> all records before updating it. So, instead of UPDATE SALES SET SAL01 = 0,
> I can replace SAL01 with the field for the appropriate month. Is this kind
> of thing possible? I found where you can use variables for the order by
> clause, but nothing like this.
>
> <===================================================>
>
> Terri Harteau
> Felker Brothers Corporation
> ****************
> "There's no point in being grown up if you can't be childish sometimes."
> - Dr. Who
> ****************
>
>
>
>
>
>
> _______________________________________________
> This is the RPG programming on the AS400 / 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.
>
>
> _______________________________________________
> This is the RPG programming on the AS400 / 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.
>
>
>
>
>
>
> _______________________________________________
> This is the RPG programming on the AS400 / 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.
As an Amazon Associate we earn from qualifying purchases.
This mailing list archive is Copyright 1997-2024 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].
Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.
