Results 1 to 2 of 2
  1. #1
    Join Date
    Dec 2010
    Posts
    136

    Unanswered: how to use if then else in UPDATE Statement

    Hi,

    I have table: plan which has fields : PCODE, max_lot, min_lot
    sample data:
    PCODE = P35
    max_lot = 5
    min_lot = 3

    PCODE = P35M
    max_lot = 5
    min_lot = 3

    and table kanban_checker : PCODE, count_wip_chemicalweighing, count_wip_compounding, count_wip_extrusion, count_wip_forming, count_wip_deflashing, virtual, kanban and kanban_status....

    I have this code of update to update kanban_checker table fields except kanban_status.

    here is my code that I want to add update for kanban_status:

    Code:
    UPDATE kanban_checker kc SET count_wip_chemical_weighing = (SELECT COUNT(NULLIF(wip_chemicalweighing, 0)) AS count_wip_chemical_weighing FROM kanban_data kd WHERE kd.PCODE = kc.PCODE GROUP BY kc.PCODE), 
    count_wip_compounding = (SELECT COUNT(NULLIF(wip_compounding, 0)) AS count_wip_compounding FROM kanban_data kd WHERE kd.PCODE = kc.PCODE GROUP BY kc.PCODE),
    count_wip_extrusion = (SELECT COUNT(NULLIF(wip_extrusion, 0)) AS count_wip_extrusion FROM kanban_data kd WHERE kd.PCODE = kc.PCODE GROUP BY kc.PCODE),
    count_wip_forming = (SELECT COUNT(NULLIF(wip_forming, 0)) AS count_wip_forming FROM kanban_data kd WHERE kd.PCODE = kc.PCODE GROUP BY kc.PCODE),
    count_wip_deflashing = (SELECT COUNT(NULLIF(wip_deflashing, 0)) AS count_wip_deflashing FROM kanban_data kd WHERE kd.PCODE = kc.PCODE GROUP BY kc.PCODE),
    virtual = (SELECT ((count(NULLIF(kd.wip_chemicalweighing, 0))) + (count(NULLIF(kd.wip_compounding, 0))) + (count(NULLIF(kd.wip_extrusion, 0))) + (count(NULLIF(kd.wip_forming, 0))) + (count(NULLIF(kd.wip_deflashing, 0)))) AS virtual FROM kanban_data kd WHERE kc.PCODE = kd.PCODE GROUP BY kc.PCODE);
    I need to check the max_lot and min_lot per PCODE in table plan for kanban_status..
    IF virtual is >= max_lot then kanban_status = 'MAX' elseif virtual is <= min_lot then kanban_status = 'MIN'.

    IS it possible?HOw?

    Thank you

  2. #2
    Join Date
    Jan 2012
    Posts
    84
    Maybe something like this ?
    Code:
    REPLACE kanban_checker( 
                    PCODE, 
                    count_wip_chemical_weighing,
                    count_wip_compounding ,
                    count_wip_extrusion,
                    count_wip_forming,
                    count_wip_deflashing,
                    virtual,
                    kanban_status
      )
    SELECT   kc.PCODE, 
             COUNT(NULLIF(wip_chemicalweighing, 0)) AS count_wip_chemical_weighing ,
             COUNT(NULLIF(wip_compounding, 0)) AS count_wip_compounding ,
             COUNT(NULLIF(kd.wip_extrusion, 0)) AS count_wip_extrusion,
             COUNT(NULLIF(kd.wip_forming, 0)) AS count_wip_forming  ,
             COUNT(NULLIF(wip_deflashing, 0)) AS count_wip_deflashing,
             COUNT(NULLIF(wip_chemicalweighing, 0)) +
             COUNT(NULLIF(wip_compounding, 0)) +
             COUNT(NULLIF(kd.wip_extrusion, 0))+
             COUNT(NULLIF(kd.wip_forming, 0)) +
             COUNT(NULLIF(wip_deflashing, 0)) AS virtual,
             CASE 
                WHEN 
                  COUNT(NULLIF(wip_chemicalweighing, 0)) +
                  COUNT(NULLIF(wip_compounding, 0)) +
                  COUNT(NULLIF(kd.wip_extrusion, 0))+
                  COUNT(NULLIF(kd.wip_forming, 0)) +
                  COUNT(NULLIF(wip_deflashing, 0)) >= p.max_lot
                THEN 'MAX'
                ELSE
                CASE
                    WHEN
                       COUNT(NULLIF(wip_chemicalweighing, 0)) +
                       COUNT(NULLIF(wip_compounding, 0)) +
                       COUNT(NULLIF(kd.wip_extrusion, 0))+
                       COUNT(NULLIF(kd.wip_forming, 0)) +
                       COUNT(NULLIF(wip_deflashing, 0)) <= p.min_lot
                    THEN 'MIN'
                    ELSE '???'
                END
             END status
    FROM kanban_data kd 
    JOIN kanban_checker kc ON kd.PCODE = kc.PCODE
    JOIN plan p ON kc.PCODE = p.PCODE
    GROUP by kc.pcode
    ;

Posting Permissions

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