Re: SQL update a numeric by replacing 2 digits with a constant

[Dan <dan27649@xxxxxxxxx>]

Gentlemen,

Thank you for your assistance!  Just for the record, I have been using the
following expression for the past few days with the expected results:

   CAST( '50' || substr( digits( cmcust), 3, 7) AS DECIMAL(9,0))

Works like a charm!

Merry Christmas, everyone!
- Dan

On Tue, Dec 21, 2010 at 4:36 PM, Luis Rodriguez <luisro58@xxxxxxxxx> wrote:

> Oops!
>
> My bad... I'm afraid I did misunderstood the problem from the beginning
> :-(.
> I really thought he wanted to change the first two (non-zero) digits...
>
> In that case, I think this could work:
>
> *update test1 *
> *  set cmcust = '50' || substr(DIGITS(ZONED(cmcust, 09, 0)), 3, 7)  *
>
> My apologies for the confusion.
>
> Regards,
>
> Luis Rodriguez
> IBM Certified Systems Expert — eServer i5 iSeries
> --
>
>
>
> On Tue, Dec 21, 2010 at 4:10 PM, Charles Wilt <charles.wilt@xxxxxxxxx
> > wrote:
>
> > Luis,
> >
> > It didn't work for you... :)
> >
> > 84,655 --> 000084655 the "right" answer is 500084655...
> >
> > The OP want's to replace the first two digits of a nine digit
> > number...not the first two non-zero digits.
> >
> >
> > Charles
> >
> > On Tue, Dec 21, 2010 at 3:24 PM, Luis Rodriguez <luisro58@xxxxxxxxx>
> > wrote:
> > > Charles,
> > >
> > > In that case, why did it work for me? All my data had a value of less
> > > than 10000000 for the cmcust field...
> > >
> > >
> > > CREATE TABLE QTEMP/TEST1 (CMCUST DECIMAL ( 9, 0) NOT NULL WITH DEFAULT)
> > >
> > > INSERT INTO QTEMP/TEST1 VALUES(rand() * 100000) (repeat as needed)
> > >
> > >
> > >
> > >
> > > select cmcust, '50'||substr(cmcust, 3, 7) as x from test1
> > >
> > > *CMCUST   X    *
> > >
> > > 84,655   50655
> > >
> > > 71,871   50871
> > >
> > > 21,131   50131
> > >
> > > 91,290   50290
> > >
> > >
> > > update test1 set cmcust = '50'||substr(cmcust, 3, 7) (worked ok)
> > >
> > >
> > > Regards,
> > >
> > >
> > >
> > >
> > >
> > > Luis Rodriguez
> > > IBM Certified Systems Expert — eServer i5 iSeries
> > > --
> > >
> > >
> > >
> > > On Tue, Dec 21, 2010 at 3:35 PM, Charles Wilt <charles.wilt@xxxxxxxxx
> > > wrote:
> > >
> > > > The implicit casting done by the system, is equivalent to cast(cmcust
> > > > as char)...
> > > >
> > > > The problem is that such a cast drops lead zeros, which screws up your
> > > > SUBSTR when the value of CMCUST is less than 10000000.
> > > >
> > > > HTH,
> > > > Charles
> > > >
> > > > On Tue, Dec 21, 2010 at 1:20 PM, Dan <dan27649@xxxxxxxxx> wrote:
> > > > > I think there is some default casting going on here, as I'm just
> > reading
> > > > up
> > > > > on that.  I am testing the following:
> > > > >
> > > > > CAST( '50' || substr( digits( cmcust), 3, 7) AS DECIMAL(9,0))
> > > > >
> > > > > and results so far look good.
> > > > >
> > > > > Since doing an UPDATE with the original code didn't provide any
> > > > diagnostic
> > > > > feedback, I constructed the following query:
> > > > > select cmcust as orig_cmcust,
> > > > >       substr( cmcust, 3, 7 ) as substr_cmcust_3_7,
> > > > >       substr( digits( cmcust), 3, 7) as substr_digits_cmcust,
> > > > >       CAST( substr( digits( cmcust), 3, 7) AS DECIMAL(9,0)) as
> > > > CAST_cmcust
> > > > >   from Lib1/Customer
> > > > >
> > > > > Results (best viewed in fixed font):
> > > > > ORIG_CMCUST   SUBSTR_CMCUST_3_7  SUBSTR_DIGITS_CMCUST  CAST_CMCUST
> > > > >  5,086,931        86931               5086931           5,086,931
> > > > >  5,073,765        73765               5073765           5,073,765
> > > > >  5,073,858        73858               5073858           5,073,858
> > > > >  6,062,249        62249               6062249           6,062,249
> > > > >  9,113,707        13707               9113707           9,113,707
> > > > >
> > > > > So, it appears that using a SUBSTR on a packed-decimal without an
> > > > > intervening DIGITS causes havoc.  The second column is a 7-character
> > > > result
> > > > > with two blanks trailing the five digits.  While I haven't
> determined
> > WHY
> > > > it
> > > > > does this, I believe I have determined how to resolve it.
> > > > >
> > > > > Thanks,
> > > > > - Dan
> > > > >
> > > > > On Tue, Dec 21, 2010 at 12:38 PM, Alan Campin <alan0307d@xxxxxxxxx>
> > > > wrote:
> > > > > > I don't see how this would work at all if CMCUST is a packed 9,0. I
> > > > would
> > > > > > have converted CMCUST to a string, then done the substr and then
> cast
> > > > the
> > > > > > result back to a dec 9,0. Based on substringing a packed 9,0 I
> would
> > > > guess
> > > > > > the results could be anything.
> > > > > >
> > > > > > On Tue, Dec 21, 2010 at 10:18 AM, Dan <dan27649@xxxxxxxxx> wrote:
> > > > > >
> > > > > > > The messed-up data I mentioned in my previous thread several
> > minutes
> > > > ago
> > > > > > > was
> > > > > > > caused by:
> > > > > > >
> > > > > > > Update Lib1/Customer  Set cmcust  = '50' || substr( cmcust    ,
> 3,
> > 7 )
> > > > > > > cmcust is a 9.0 packed decimal.  The goal is to REPLACE the first
> > two
> > > > > > > digits
> > > > > > > of cmcust with 50.
> > > > > > >
> > > > > > > In another library I used this in, the update worked as expected.
> > > > > >  However,
> > > > > > > for reasons unknown to me, the update in this library replaced
> the
> > 3rd
> > > > &
> > > > > > > 4th
> > > > > > > digits of cmcust with 50.  Any ideas?
> > > > > > >
> > > > > > > FWIW, we just upgraded to V6R1 with cum PTF TL60215 applied.
>  I've
> > > > been
> > > > > > out
> > > > > > > of the OS admin side of things for too long to know whether that
> is
> > a
> > > > > > > recent
> > > > > > > cum PTF.
> > > > > > >
> > > > > > > TIA,
> > > > > > > - Dan
> > > > > > > --
> > > > > > > This is the Midrange Systems Technical Discussion (MIDRANGE-L)
> > mailing
> > > > > > list
> > > > > > > To post a message email: MIDRANGE-L@xxxxxxxxxxxx
> > > > > > > To subscribe, unsubscribe, or change list options,
> > > > > > > visit: http://lists.midrange.com/mailman/listinfo/midrange-l
> > > > > > > or email: MIDRANGE-L-request@xxxxxxxxxxxx
> > > > > > > Before posting, please take a moment to review the archives
> > > > > > > at http://archive.midrange.com/midrange-l.
> > > > > > --
> > > > > > This is the Midrange Systems Technical Discussion (MIDRANGE-L)
> > mailing
> > > > list
> > > > > > To post a message email: MIDRANGE-L@xxxxxxxxxxxx
> > > > > > To subscribe, unsubscribe, or change list options,
> > > > > > visit: http://lists.midrange.com/mailman/listinfo/midrange-l
> > > > > > or email: MIDRANGE-L-request@xxxxxxxxxxxx
> > > > > > Before posting, please take a moment to review the archives
> > > > > > at http://archive.midrange.com/midrange-l.
> > > > > --
> > > > > This is the Midrange Systems Technical Discussion (MIDRANGE-L)
> mailing
> > > > list
> > > > > To post a message email: MIDRANGE-L@xxxxxxxxxxxx
> > > > > To subscribe, unsubscribe, or change list options,
> > > > > visit: http://lists.midrange.com/mailman/listinfo/midrange-l
> > > > > or email: MIDRANGE-L-request@xxxxxxxxxxxx
> > > > > Before posting, please take a moment to review the archives
> > > > > at http://archive.midrange.com/midrange-l.
> > > > --
> > > > This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing
> > list
> > > > To post a message email: MIDRANGE-L@xxxxxxxxxxxx
> > > > To subscribe, unsubscribe, or change list options,
> > > > visit: http://lists.midrange.com/mailman/listinfo/midrange-l
> > > > or email: MIDRANGE-L-request@xxxxxxxxxxxx
> > > > Before posting, please take a moment to review the archives
> > > > at http://archive.midrange.com/midrange-l.
> > > --
> > > This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing
> > list
> > > To post a message email: MIDRANGE-L@xxxxxxxxxxxx
> > > To subscribe, unsubscribe, or change list options,
> > > visit: http://lists.midrange.com/mailman/listinfo/midrange-l
> > > or email: MIDRANGE-L-request@xxxxxxxxxxxx
> > > Before posting, please take a moment to review the archives
> > > at http://archive.midrange.com/midrange-l.
> > --
> > This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing
> list
> > To post a message email: MIDRANGE-L@xxxxxxxxxxxx
> > To subscribe, unsubscribe, or change list options,
> > visit: http://lists.midrange.com/mailman/listinfo/midrange-l
> > or email: MIDRANGE-L-request@xxxxxxxxxxxx
> > Before posting, please take a moment to review the archives
> > at http://archive.midrange.com/midrange-l.
> --
> This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
> To post a message email: MIDRANGE-L@xxxxxxxxxxxx
> To subscribe, unsubscribe, or change list options,
> visit: http://lists.midrange.com/mailman/listinfo/midrange-l
> or email: MIDRANGE-L-request@xxxxxxxxxxxx
> Before posting, please take a moment to review the archives
> at http://archive.midrange.com/midrange-l.