Results 1 to 7 of 7
  1. #1
    Join Date
    Feb 2012
    Posts
    12

    Arrow Unanswered: update using decode

    please I need your help !!

    I am trying to update a table using a decode
    I have a table with column represents the amount
    i want to update the commission column according to the following criteria
    if the amount between 1 to 999 the commission column will be updated to 1
    if the amount between 1000 to 1999 the commission column will be updated to 2
    if the amount >= 2000 the commission column will be updated to 5
    i wrote the following code

    Dim dbs As Database
    Dim qdf As QueryDef
    Set dbs = CurrentDb
    dbs.Execute "update table_temp set commission =
    decode(trunc(amount/1000),0,1,1,2,5);"
    dbs.Close

    but i got an error message on the run time (undefined expression 'decode' in expression)


    any suggestion please ...
    appreciate any help ...

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    So what is the sql you are actually sending?
    Either set a watch / breakpoint or assign the sql to a variable and display the variable before executing the sql
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    I havent got a scooby what the function decode is supposed to do
    can you provide the function declaration



    If it were me Id want to put the commission rates into a table so any changes in commission are handled byusers not by db changes.

    If you must stick with putting your commission rates as hard coded values then in the absence of your user written function decode you have two options
    either a complicated compound IIF statement in SQL or a simple nested IF or better yet select case statement in VBA

    you may be able to use the switch statement within the SQL.
    Last edited by healdem; 01-14-13 at 06:42.
    I'd rather be riding on the Tiger 800 or the Norton

  4. #4
    Join Date
    Feb 2012
    Posts
    12

    Arrow helppp please

    Let’s say that we have the following table

    Amount Commission Total



    The user inserts the amount

    Amount Commission Total
    800
    1200
    3000

    The criteria is as follows:

    if the amount between 1 to 999 $ the commission column will be updated to 1 $
    if the amount between 1000 to 1999 $ the commission column will be updated to 2$
    if the amount >= 2000 $ the commission column will be updated to 5$

    Amount Commission Total
    800 1 801
    1200 2 1202
    3000 5 3005

    Am not so good in access I used to use SQL in oracle previously, where I used to use the decode as follows:
    example :
    Desc = Decode( x , 1 , ’first’ , 2 , ’second’ , ’third’ )
    this means:
    If x = 1 then desc= ‘first’
    If x = 2 then desc= ‘second’
    Else desc= ‘third’


    Any suggestions to be able to fill the commission column (the values are hard coded as described in the criteria above)

  5. #5
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    so you have restated the problem
    you haven't identified wtf the decode function is. It is not a native function of Access VBA or JET as a consequence unless you have declared it somewhere it doesn't exist. referrign to decode as a function on this foirum is meaningless as unless soemone happens to know what that function is when used elsewhere itt isn't going to advance your problem any further

    as said above In my books there are several solutions
    1) replicate the decode function by writing a user written function
    2) use a compound IIF in your SQL. compound IIF's can be a pig to test and maintain over time
    3) use a nested IF/ELSEIF or a select case to determine thge commission and then ionsert that value into your SQL
    4) possibly use a switch statement inside your SQL (to be honest I don't know if that will work)
    5) install the commissions logic as a separate rules table

    personally I'd prefer 5 as it means changes in commission rates are controlled by the user and require no further development resource to change.
    failing that I'd probably opt for either a user written function OR logic inside some VBA which

    Code:
    public function CalcCommission(GrossAmount as double) as double 'not you may want to select different datatypes to make certain that you have the correct precision
    select case GrossAmount
    case <=0 : CalcCommission=0 'stop silly results for negative sales
    case <1000: CalcCommission=1
    ....
    default: CalcCommission=0: 'backstop just in case there is a problem
    end select
    end function
    place the function in a code module and that function is now available to any form, report, (JET) query or other code module
    Last edited by healdem; 01-14-13 at 08:42.
    I'd rather be riding on the Tiger 800 or the Norton

  6. #6
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    of course if you are used to Oracle there is nothing stopping you from writing a version of the DECODE function from Oracle as a user written function
    I'd rather be riding on the Tiger 800 or the Norton

  7. #7
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    995
    Provided Answers: 2
    DECODE is an Oracle/PL-SQL function. Investigate the SELECT CASE statement instead - they're more or less the equivalents of each other.
    10% of magic is knowing something that no-one else does. The rest is misdirection.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •