SQL UPDATE Numeric Value From Character - IBM AS400

This is a discussion on SQL UPDATE Numeric Value From Character - IBM AS400 ; Hi. I need to update field(CRSTDT) from FILE(CRDLOGL6) to the value from field(MFSETD ) from FILE(CCS9020G) The problem is that: field(CRSTDT) = 8,0 Sined numberic and field(MFSETD) = 8 Alpha This is my SQL Update CRDLOGL6 set CRSTDT =(Select MFSETD ...

+ Reply to Thread
Results 1 to 6 of 6

Thread: SQL UPDATE Numeric Value From Character

  1. SQL UPDATE Numeric Value From Character

    Hi.

    I need to update field(CRSTDT) from FILE(CRDLOGL6) to the value from
    field(MFSETD ) from FILE(CCS9020G)

    The problem is that:
    field(CRSTDT) = 8,0 Sined numberic
    and
    field(MFSETD) = 8 Alpha

    This is my SQL

    Update CRDLOGL6 set CRSTDT =(Select MFSETD from CCS9020G where
    CRTNBR=mfukey and CRSETF = 'CS' and
    CRSTDT = 0)

    This is a Msg that I get
    Value for column or variable CRSTDT not compatible.

    I've tried to use DIGITS - No luck

    Any Ideas.
    Thank you in advance
    Gary

  2. Re: SQL UPDATE Numeric Value From Character

    On Jul 17, 10:22*am, Gary Segal wrote:
    > Hi.
    >
    > I need to update field(CRSTDT) from FILE(CRDLOGL6) to the value from
    > field(MFSETD ) from FILE(CCS9020G)
    >
    > The problem is that:
    > field(CRSTDT) = 8,0 Sined numberic
    > and
    > field(MFSETD) = 8 Alpha
    >
    > This is my SQL
    >
    > Update CRDLOGL6 set CRSTDT =(Select MFSETD from CCS9020G where
    > CRTNBR=mfukey and CRSETF = 'CS' and
    > CRSTDT = 0)
    >
    > This is a Msg that I get
    > Value for column or variable CRSTDT not compatible.
    >
    > I've tried to use DIGITS - No luck
    >
    > Any Ideas.
    > Thank you in advance
    > Gary


    Try this

    Update CRDLOGL6 set CRSTDT = dec(Select MFSETD from CCS9020G where
    CRTNBR=mfukey and CRSETF = 'CS' and
    CRSTDT = 0),8,0)



  3. Re: SQL UPDATE Numeric Value From Character

    On Jul 17, 10:25*am, Graybeard wrote:
    > On Jul 17, 10:22*am, Gary Segal wrote:
    >
    >
    >
    >
    >
    > > Hi.

    >
    > > I need to update field(CRSTDT) from FILE(CRDLOGL6) to the value from
    > > field(MFSETD ) from FILE(CCS9020G)

    >
    > > The problem is that:
    > > field(CRSTDT) = 8,0 Sined numberic
    > > and
    > > field(MFSETD) = 8 Alpha

    >
    > > This is my SQL

    >
    > > Update CRDLOGL6 set CRSTDT =(Select MFSETD from CCS9020G where
    > > CRTNBR=mfukey and CRSETF = 'CS' and
    > > CRSTDT = 0)

    >
    > > This is a Msg that I get
    > > Value for column or variable CRSTDT not compatible.

    >
    > > I've tried to use DIGITS - No luck

    >
    > > Any Ideas.
    > > Thank you in advance
    > > Gary

    >
    > Try this
    >
    > Update CRDLOGL6 set CRSTDT = dec(Select MFSETD from CCS9020G where
    > CRTNBR=mfukey and CRSETF = 'CS' and
    > CRSTDT = 0),8,0)- Hide quoted text -
    >
    > - Show quoted text -


    Thanks but - it did not work

    here is the MSG

    Token MFSETD was not valid. Valid tokens: ) ,.
    Update CRDLOGL6 set CRSTDT = dec(Select MFSETD from CCS9020G where
    CRTNBR=mfukey and CRSETF = 'CS' and CRSTDT = 0)

    Do I need double ")" ?

    Thanks
    Gary

  4. Re: SQL UPDATE Numeric Value From Character

    On Jul 17, 10:22 am, Gary Segal wrote:
    > Hi.
    >
    > I need to update field(CRSTDT) from FILE(CRDLOGL6) to the value from
    > field(MFSETD ) from FILE(CCS9020G)
    >
    > The problem is that:
    > field(CRSTDT) = 8,0 Sined numberic
    > and
    > field(MFSETD) = 8 Alpha
    >
    > This is my SQL
    >
    > Update CRDLOGL6 set CRSTDT =(Select MFSETD from CCS9020G where
    > CRTNBR=mfukey and CRSETF = 'CS' and
    > CRSTDT = 0)
    >
    > This is a Msg that I get
    > Value for column or variable CRSTDT not compatible.
    >
    > I've tried to use DIGITS - No luck
    >
    > Any Ideas.
    > Thank you in advance
    > Gary


    put a parenthesis '(' between the first '(' and Select

  5. Re: SQL UPDATE - Numeric Value From Character


    "Gary Segal" skrev i en meddelelse
    news:018e9838-a0d4-4f50-bcbe-ccf2039e5bef@m3g2000hsc.googlegroups.com...
    > Hi.
    >
    > I need to update field(CRSTDT) from FILE(CRDLOGL6) to the value from
    > field(MFSETD ) from FILE(CCS9020G)
    >
    > The problem is that:
    > field(CRSTDT) = 8,0 Sined numberic
    > and
    > field(MFSETD) = 8 Alpha
    >
    > This is my SQL
    >
    > Update CRDLOGL6 set CRSTDT =(Select MFSETD from CCS9020G where
    > CRTNBR=mfukey and CRSETF = 'CS' and
    > CRSTDT = 0)
    >
    > This is a Msg that I get
    > Value for column or variable CRSTDT not compatible.
    >
    > I've tried to use DIGITS - No luck
    >
    > Any Ideas.
    > Thank you in advance
    > Gary


    Maybe CAST is the answer to your troubles...

    Update CRDLOGL6 set CRSTDT =(
    Select CAST(MFSETD AS NUMERIC(8, 0))
    from CCS9020G
    where CRTNBR=mfukey
    and CRSETF = 'CS'
    and CRSTDT = 0)




  6. Re: SQL UPDATE - Numeric Value From Character

    On Jul 17, 11:52*am, "Kaj Julius"
    wrote:
    > "Gary Segal" skrev i en meddelelsenews:018e9838-a0d4-4f50-bcbe-ccf2039e5bef@m3g2000hsc.googlegroups.com...
    >
    >
    >
    >
    >
    > > Hi.

    >
    > > I need to update field(CRSTDT) from FILE(CRDLOGL6) to the value from
    > > field(MFSETD ) from FILE(CCS9020G)

    >
    > > The problem is that:
    > > field(CRSTDT) = 8,0 Sined numberic
    > > and
    > > field(MFSETD) = 8 Alpha

    >
    > > This is my SQL

    >
    > > Update CRDLOGL6 set CRSTDT =(Select MFSETD from CCS9020G where
    > > CRTNBR=mfukey and CRSETF = 'CS' and
    > > CRSTDT = 0)

    >
    > > This is a Msg that I get
    > > Value for column or variable CRSTDT not compatible.

    >
    > > I've tried to use DIGITS - No luck

    >
    > > Any Ideas.
    > > Thank you in advance
    > > Gary

    >
    > Maybe CAST is the answer to your troubles...
    >
    > Update CRDLOGL6 *set CRSTDT =(
    > * * * * *Select CAST(MFSETD AS NUMERIC(8, 0))
    > * * * * *from CCS9020G
    > * * * * *where CRTNBR=mfukey
    > * * * * * * *and CRSETF = 'CS'
    > * * * * * * *and CRSTDT = 0)- Hide quoted text -
    >
    > - Show quoted text -


    Thank you guys. I did it in RPG. I will try next time.
    Thanks all

+ Reply to Thread