Rajiv Sahadeo February 2016

inserting data with different value then source in temp table

I need to pull data into a temp table so i can group it into a product type but we currently have different variants of a certain product and I need to insert all the variants as the original variant. I wrote the code as follows but it still isn't working

IF EXISTS (SELECT *
           FROM   sys.tables
           WHERE  name LIKE '#temp%')
  DROP TABLE #temp

CREATE TABLE #temp
  (
     Product      VARCHAR(max) NULL,
     failure_code VARCHAR(max) NULL
  )

IF Product IN ( 18, 19, 20, 23,
                24, 25 )
  SELECT Product = 'DSD 4136'

INSERT INTO #temp
            (Product,
             failure_code)
SELECT product.prod_desc AS Product,
       test_failure_code.failure_code
FROM   repair
       INNER JOIN test_failure_code
               ON repair.id_test_failure_code = test_failure_code.id_test_failure_code
       INNER JOIN production_event_details
               ON repair.id_production_event_details = production_event_details.id_production_event_details
       INNER JOIN product
               ON production_event_details.prod_id = product.prod_id
       INNER JOIN repair_scm_bom_item
               ON repair.id_repair = repair_scm_bom_item.id_repair
       INNER JOIN repair_fault
               ON repair_scm_bom_item.id_repair_fault = repair_fault.id_repair_fault
       INNER JOIN scm_bom_item
               ON repair_scm_bom_item.id_scm_bom_item = scm_bom_item.id_scm_bom_item
GROUP  BY product.prod_desc,
          test_failure_code.failure_code

SELECT *
FROM   #temp

DROP TABLE #temp 

Answers


Jamie Pollard February 2016

The statement

IF Product IN ( 18, 19, 20, 23, 24, 25 )
SELECT Product = 'DSD 4136'

wont actually do anything, you need to use it in your insert statement using a CASE statement, this will select the product DSD 4136 when the product is one of your listed options:

INSERT INTO #temp
            (Product,
             failure_code)
SELECT CASE WHEN product.prod_desc IN ( 18, 19, 20, 23, 24, 25 ) THEN 'DSD 4136'
  ELSE product.prod_desc
END
AS Product,
       test_failure_code.failure_code
FROM   repair
       INNER JOIN test_failure_code
               ON repair.id_test_failure_code = test_failure_code.id_test_failure_code
       INNER JOIN production_event_details
               ON repair.id_production_event_details = production_event_details.id_production_event_details
       INNER JOIN product
               ON production_event_details.prod_id = product.prod_id
       INNER JOIN repair_scm_bom_item
               ON repair.id_repair = repair_scm_bom_item.id_repair
       INNER JOIN repair_fault
               ON repair_scm_bom_item.id_repair_fault = repair_fault.id_repair_fault
       INNER JOIN scm_bom_item
               ON repair_scm_bom_item.id_scm_bom_item = scm_bom_item.id_scm_bom_item
GROUP  BY product.prod_desc,
          test_failure_code.failure_code

Post Status

Asked in February 2016
Viewed 2,825 times
Voted 11
Answered 1 times

Search




Leave an answer