developers

    Forum Stats

  • 3,873,729 Users
  • 2,266,635 Discussions
  • 7,911,624 Comments

Discussions

OPEN INTERFACE FOR ITEM CATEGORY

3247206
3247206 Member Posts: 62
edited Aug 21, 2017 10:20AM in MySQL Community Space

Hi All,

     I have tried to update the existing category for an item trough open interface but category is not updating So Please help and give suggestions on this . I am sending my code So please see and give any suggestions need in the query.

INSERT INTO mtl_item_categories_interface

( category_set_id

,category_id

,last_update_date

,ORGANIZATION_CODE

,process_flag

,inventory_item_id

,old_category_id

,transaction_type

,set_process_id

)

VALUES

( 1000000015     -- category_set_id

,1513            -- new category_id

,SYSDATE

,'V1'           -- ORGANIZATION_CODE (should be master_orgaization_id if category set is controlled at master level)

,1               -- always 1

,380989         -- Item name  Note: for performance consideration use inventory_item_id in place of item_number

,2257           -- old category_id

,'UPDATE'

,555            -- set_process_id can be any positive number

);

commit;

Best Answer

  • Gaz in Oz
    Gaz in Oz Member Posts: 3,785 Bronze Crown
    edited Aug 20, 2017 5:04AM Answer ✓
    mysql> INSERT INTO mtl_item_categories_interface    -> ( category_set_id    ->  ,category_id    ->  ,last_update_date    ->  ,ORGANIZATION_CODE    ->  ,process_flag    ->  ,inventory_item_id    ->  ,old_category_id    ->  ,transaction_type    ->  ,set_process_id    -> )    -> VALUES    -> ( 1000000015  -- category_set_id    ->  ,1513        -- new category_id    ->  ,SYSDATE    ->  ,'V1'        -- ORGANIZATION_CODE (should be master_orgaization_id if category set is controlled at master level)    ->  ,1           -- always 1    ->  ,380989      -- Item name  Note: for performance consideration use inventory_item_id in place of item_number    ->  ,2257        -- old category_id    ->  ,'UPDATE'    ->  ,555         -- set_process_id can be any positive number    -> );ERROR 1054 (42S22): Unknown column 'SYSDATE' in 'field list'

    You need to use "SYSDATE()" in mysql. (Or NOW() or CURRENT_DATE(), depending on what you actually want. See the documentation link for the differences).

    mysql> INSERT INTO mtl_item_categories_interface    -> ( category_set_id    ->  ,category_id    ->  ,last_update_date    ->  ,ORGANIZATION_CODE    ->  ,process_flag    ->  ,inventory_item_id    ->  ,old_category_id    ->  ,transaction_type    ->  ,set_process_id    -> )    -> VALUES    -> ( 1000000015   -- category_set_id    ->  ,1513         -- new category_id    ->  ,SYSDATE()    -- last_udate_date datatype DATETIME    ->  ,'V1'         -- ORGANIZATION_CODE (should be master_orgaization_id if category set is controlled at master level)    ->  ,1            -- always 1    ->  ,380989       -- Item name  Note: for performance consideration use inventory_item_id in place of item_number    ->  ,2257         -- old category_id    ->  ,'UPDATE'    ->  ,555          -- set_process_id can be any positive number    -> );Query OK, 1 row affected (0.00 sec)

    https://dev.mysql.com/doc/refman/5.7/en/date-and-time-functions.html

    mysql> select now(), sysdate(), current_date();+---------------------+---------------------+----------------+| now()               | sysdate()           | current_date() |+---------------------+---------------------+----------------+| 2017-08-20 03:25:06 | 2017-08-20 03:25:06 | 2017-08-20     |+---------------------+---------------------+----------------+1 row in set (0.00 sec)

Answers

  • Dave Stokes-MySQL Community Team-Oracle
    Dave Stokes-MySQL Community Team-Oracle MySQL Community Manager TexasMember Posts: 374 Employee
    edited Aug 16, 2017 10:00AM

    What, if any, errors are you seeing after entering the query. Also include 'SHOW CREATE TABLE  mtl_item_categories_interface' too.

    Dave Stokes

    MySQL Community Manager

  • Gaz in Oz
    Gaz in Oz Member Posts: 3,785 Bronze Crown
    edited Aug 20, 2017 5:04AM Answer ✓
    mysql> INSERT INTO mtl_item_categories_interface    -> ( category_set_id    ->  ,category_id    ->  ,last_update_date    ->  ,ORGANIZATION_CODE    ->  ,process_flag    ->  ,inventory_item_id    ->  ,old_category_id    ->  ,transaction_type    ->  ,set_process_id    -> )    -> VALUES    -> ( 1000000015  -- category_set_id    ->  ,1513        -- new category_id    ->  ,SYSDATE    ->  ,'V1'        -- ORGANIZATION_CODE (should be master_orgaization_id if category set is controlled at master level)    ->  ,1           -- always 1    ->  ,380989      -- Item name  Note: for performance consideration use inventory_item_id in place of item_number    ->  ,2257        -- old category_id    ->  ,'UPDATE'    ->  ,555         -- set_process_id can be any positive number    -> );ERROR 1054 (42S22): Unknown column 'SYSDATE' in 'field list'

    You need to use "SYSDATE()" in mysql. (Or NOW() or CURRENT_DATE(), depending on what you actually want. See the documentation link for the differences).

    mysql> INSERT INTO mtl_item_categories_interface    -> ( category_set_id    ->  ,category_id    ->  ,last_update_date    ->  ,ORGANIZATION_CODE    ->  ,process_flag    ->  ,inventory_item_id    ->  ,old_category_id    ->  ,transaction_type    ->  ,set_process_id    -> )    -> VALUES    -> ( 1000000015   -- category_set_id    ->  ,1513         -- new category_id    ->  ,SYSDATE()    -- last_udate_date datatype DATETIME    ->  ,'V1'         -- ORGANIZATION_CODE (should be master_orgaization_id if category set is controlled at master level)    ->  ,1            -- always 1    ->  ,380989       -- Item name  Note: for performance consideration use inventory_item_id in place of item_number    ->  ,2257         -- old category_id    ->  ,'UPDATE'    ->  ,555          -- set_process_id can be any positive number    -> );Query OK, 1 row affected (0.00 sec)

    https://dev.mysql.com/doc/refman/5.7/en/date-and-time-functions.html

    mysql> select now(), sysdate(), current_date();+---------------------+---------------------+----------------+| now()               | sysdate()           | current_date() |+---------------------+---------------------+----------------+| 2017-08-20 03:25:06 | 2017-08-20 03:25:06 | 2017-08-20     |+---------------------+---------------------+----------------+1 row in set (0.00 sec)
developers