|
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-2025 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.