Value associated with the case statement condition in SQL

486 views sql
2

I have two tables: Item and Job I'm trying to get the unit weight of the item from the item table. The Job table has two item code columns. Item1, Item 2 I'm using something like this.

  CASE 
      WHEN job.item2 
      LIKE '%cc%'
      THEN item.unitweight * job.qty
      ELSE job.qty
      END AS specific item

Job Table

+-----+-------+-------+-----+
| job | Item1 | Item2 | qty |
+-----+-------+-------+-----+
|   1 | aa    | aaa   |   5 |
|   2 | bb    | cc    |   6 |
|   3 | cc    | bb    |   7 |
|   4 | aa    | ddd   |   8 |
+-----+-------+-------+-----+

Item Table

+------+-------------+
| Item | Unit Weight |
+------+-------------+
| aa   |           5 |
| bb   |           6 |
| cc   |           7 |
| dd   |           8 |
+------+-------------+

I want to get the query to return 42 associated with Item2 but my query returns 36

SELECT DISTINCT  
          job.job
        , job.suffix
        , job.item
        , jobmatl_mst.item
        , jobmatl_mst.matl_qty
        , **CASE WHEN jobmatl_mst.item < 'a' and jobmatl_mst.item like '%RB%'
              THEN ISNULL((item_mst.unit_weight * jobmatl_mst.matl_qty),0) 
              ELSE ISNULL(jobmatl_mst.qty_issued, 0)
         END AS qty_issued**
        , item_mst.unit_weight
        , jobmatl_mst.[sequence]
        , job.qty_complete
        , CASE WHEN bmatl.matl_qty IS NULL 
               THEN 0 
               ELSE bmatl.matl_qty 
          END AS matl_qty
        , CASE WHEN bmatl.item IS NULL 
               THEN '***Not in STD BOM***' 
               ELSE bmatl.item 
          END AS item
        , bitem.unit_cost
        , CASE WHEN jobmatl_mst.item < 'a' and jobmatl_mst.item like '%RB%'
              THEN ISNULL((item_mst.matl_cost/item_mst.unit_weight),0) 
              ELSE ISNULL(item_mst.matl_cost, 0)
          END AS matl_cost
        , wo.line
        , item_mst.ufprofile
        , item_mst.ufcolor
        , wo.run
        , da.sales_acct_unit2
        , wo.[Start Date]
        , wo.[End Date]
        , CAST(DATEDIFF(minute, wo.[Start Date], wo.[End Date])/60.0 AS Decimal(8,3)) AS SPDMchHrs
        , jbr.run_hrs_t_mch AS SytelineMchHrs
        , jrt.run_mch_hrs * job.qty_complete AS StdMchHrs
        , ISNULL(lwt.LotsNetWeightSum, 0) AS TotalLotNetWt
        , item_mst.lot_size * item_mst.unit_weight AS StdRunSize
        , ROUND(CASE WHEN ISNULL(((jrt.run_mch_hrs * sjbr.efficiency * item_mst.lot_size) - (jrt.setup_hrs)), 0) = 0
                     THEN 0
                      ELSE (item_mst.lot_size * item_mst.unit_weight) / ((jrt.run_mch_hrs * (sjbr.efficiency* 0.01) * item_mst.lot_size) - (jrt.setup_hrs))
                END, 0) AS Std_InstantRunRate_lbs_p_hr
        , ROUND(CASE WHEN ISNULL(((jrt.run_mch_hrs * sjbr.efficiency * item_mst.lot_size) - (jrt.setup_hrs)), 0) = 0 OR ISNULL(item_mst.uflength, 0) = 0
                     THEN 0
                     ELSE ((item_mst.lot_size * item_mst.unit_weight) / ((jrt.run_mch_hrs * (sjbr.efficiency* 0.01) * item_mst.lot_size) - (jrt.setup_hrs)))/(item_mst.unit_weight/(item_mst.uflength/12))/60
                END, 2) AS Std_InstantRunRate_ft_p_min
        , ROUND(CASE WHEN ISNULL(sjbr.efficiency, 0) = 0 THEN 0 ELSE (jrt.setup_hrs / (sjbr.efficiency * 0.01)) END, 2) AS Std_SetupHrs
        , ISNULL(CAST(DATEDIFF(minute, wo.[Start Date], two.wofirstpcstime)/60.0 AS Decimal(8,3)), 0) AS Act_SetupHrs
        , two.wofirstpcstime AS FirstPcsTime
    FROM job_mst job
    LEFT JOIN #lwt lwt           ON lwt.job                       = job.job
                                AND lwt.suffix                    = job.suffix
    LEFT JOIN  jobroute_mst jbr  ON jbr.job                       = job.job
                                AND jbr.suffix                    = job.suffix
    INNER JOIN #tmpspdwo two     ON [Admin].[dbo].[TRIM](job.job) = two.woorder 
                                AND job.suffix                    = two.woline
    INNER JOIN jobmatl_mst       ON jobmatl_mst.job               = job.job
                                AND jobmatl_mst.suffix            = job.suffix 
    INNER JOIN item_mst          ON job.item                      = item_mst.item
    INNER JOIN jrt_sch_mst jrt   ON jrt.job                       = item_mst.job
                                AND jrt.suffix                    = item_mst.suffix
                                AND jrt.oper_num                  = jbr.oper_num
    INNER JOIN jobroute_mst sjbr ON sjbr.job                      = item_mst.job
                                AND sjbr.suffix                   = item_mst.suffix
    LEFT JOIN jobmatl_mst bmatl  ON bmatl.job                     = item_mst.job
                                AND bmatl.suffix                  = 1
                                AND jobmatl_mst.item              = bmatl.item
    LEFT JOIN  item_mst bitem    ON jobmatl_mst.item              = bitem.item
    LEF

answered question

I don't really understand the result you want. Can you post a sample of the desired result and the one you are getting?

for job 2 you get 6 which is the value of bb but you'd rather have the value of cc which is 7? What's the join condition? Take Item 2 over item 1?

Can you post the whole query?

aaa and ddd don't have related values in the ITEM table.

why should your query return 42? What is the logic behind that?

@scsimon : can i have the item2 over item1 only when the condition is satisfied?

Do you only want to use column Item2 only? What about Item1? Shouldn't the result be 36 + 42 = 78?

@TabAlleman I'm trying to get the cc value from item table when the item2 is cc

2 Answers

2

I'm trying to get the cc value from item table when the item2 is cc

You can do it by left-joining item2 to your query:

SELECT
  ...
  CASE 
      WHEN job.item2 
      LIKE '%cc%'
      THEN i2.unitweight * job.qty -- Use i2.unweight
      ELSE job.qty
      END AS specific item
FROM job
JOIN item on item.Item=job.Item1
LEFT OUTER JOIN item i2 on i2.Item=job.Item2 -- Add this join

posted this
1

I'm guessing you want something like this?

SELECT j.job, j.Item1, j.Item2, j.qty
    , CASE
        WHEN j.Item2 = 'cc'  -- or do you want it to only have cc in it? then it would be LIKE '%cc%'
            THEN i2.unitweight * j.qty
        ELSE
            j.qty
      END
FROM Job j
    INNER JOIN Item i1 ON j.Item1 = i1.Item
    INNER JOIN Item i2 ON j.Item2 = i2.Item;

posted this

Have an answer?

JD

Please login first before posting an answer.