Add lookups to update query

Transact-SQL
1

I have an complex update procedure and now need to add another validation condition
to the process to make sure I'm getting the correct addr for the correct mtl_addr_div and asi_division. I just add a field to the parms table called Division, and on the MTD_INVOICE
there is a field called asi_division these are the key to the new update. The code at the bottom is what I'm trying to add into this procedure, or maybe you have a better way of doing what I'm try to accomplish..

Sorry formatting I tried too but my first post

THanks

;WITH Ranked AS (
SELECT H.ship_cust_nbr, H.Invoice_Nbr, D.Prime_Line_Nbr,d.gl_division, D.Mtl_Type,D.Mtl_Addr_Price,c1.mtl_addr_pricelist,h.asi_division,c1.mtl_addr_division,
COALESCE(C1.mtl_addr_Adder+C1.Mtl_Addr_ExtAddr, C2.mtl_addr_adder+C2.Mtl_Addr_ExtAddr) AS new_price,
COALESCE(C1.mtl_addr_StartDate, C2.mtl_addr_StartDate) AS start_date,
ROW_NUMBER() OVER(PARTITION BY H.Ship_Cust_nbr, D.Mtl_Type,D.Invoice_Nbr,D.Prime_Line_Nbr,cm.cm_curr
ORDER BY CASE WHEN C1.mtl_addr_adder IS NOT NULL
THEN 1
ELSE 2
END, COALESCE(C1.mtl_addr_adder, C2.mtl_addr_adder)) AS rk
FROM dbo.MTD_INVOICE AS H
JOIN dbo.MTD_INV_LINE_ITEM AS D
on h.company = d.company
and h.gl_division = d.gl_division
and h.acct_year = d.acct_year
and h.acct_month = d.acct_month
and h.Invoice_nbr = d.Invoice_nbr
and d.Prod_Line in ('xx6','xx8')
and not h.order_type = 'i'
join cm_mstr as cm
on h.ship_cust_nbr = cm.cm_addr
LEFT JOIN parms.dbo.mtl_addr_price AS C1
ON H.ship_cust_nbr = C1.mtl_addr_custcode
AND D.Mtl_Type = C1.mtl_addr_mtltype
and cm.cm_curr = c1.mtl_addr_currency
AND H.Invoice_Date BETWEEN C1.mtl_addr_StartDate AND COALESCE(C1.mtl_addr_EndDate, CURRENT_TIMESTAMP)
LEFT JOIN parms.dbo.mtl_addr_price AS C2
ON H.ship_cust_nbr = C2.mtl_addr_custcode
AND D.Mtl_Type = C2.mtl_addr_MtlType
AND D.Mtl_Type = C1.mtl_addr_mtltype
and cm.cm_curr = c1.mtl_addr_currency
AND H.Invoice_Date >= C2.mtl_addr_StartDate
AND NOT EXISTS(SELECT *
FROM parms.dbo.mtl_addr_price AS C3
WHERE C3.mtl_addr_custcode = C2.mtl_addr_custcode
AND C3.mtl_addr_MtlType = C2.mtl_addr_MtlType
and c3.mtl_addr_currency = c2.mtl_addr_currency
AND C3.mtl_addr_StartDate > C2.mtl_addr_StartDate
AND C3.mtl_addr_StartDate <= H.Invoice_Date))
UPDATE MTD_INV_LINE_ITEM
SET Mtl_Addr_Price = new_price
output r.ship_cust_nbr,
inserted.invoice_nbr,inserted.prime_line_nbr,
inserted.Prod_line,inserted.Mtl_Type,inserted.mtl_addr_Price,r.mtl_addr_pricelist,r.mtl_addr_division,r.ASI_DIVISION
into dbo.SdtlLOG_Daily(CustNbr,InvoiceNbr,InvLine,Prodline,MtlCode,saddr,PriceList,divused,channel)
FROM MTD_INV_LINE_ITEM AS D
JOIN Ranked AS R
ON D.Invoice_Nbr = R.Invoice_Nbr
AND D.prime_line_nbr = R.prime_line_nbr
-- AND cm.cm_curr = R.cm_curr
AND D.Mtl_Type = R.Mtl_Type
AND R.rk = 1
AND R.start_date IS NOT NULL

-- --AND 1 = CASE
-- WHEN r.ASI_DIVISION IN ('dir','ext') AND r.mtl_addr_division = 'EF' THEN 1
-- WHEN r.ASI_DIVISION IN ('sxb') AND r.mtl_addr_division = 'sxb' THEN 1
-- ELSE 0
-- END;

2

Welcome!

Like most forums when posting code, please use 3 tick marks before and after your code. Click ~ your keyboard it gives you one tick

;WITH Ranked AS 
(
   SELECT
      H.ship_cust_nbr,
      H.Invoice_Nbr,
      D.Prime_Line_Nbr,
      d.gl_division,
      D.Mtl_Type,
      D.Mtl_Addr_Price,
      c1.mtl_addr_pricelist,
      h.asi_division,
      c1.mtl_addr_division,
      COALESCE(C1.mtl_addr_Adder + C1.Mtl_Addr_ExtAddr, C2.mtl_addr_adder + C2.Mtl_Addr_ExtAddr) AS new_price,
      COALESCE(C1.mtl_addr_StartDate, C2.mtl_addr_StartDate) AS start_date,
      ROW_NUMBER() OVER(PARTITION BY H.Ship_Cust_nbr, D.Mtl_Type, D.Invoice_Nbr, D.Prime_Line_Nbr, cm.cm_curr 
   ORDER BY CASE
         WHEN C1.mtl_addr_adder IS NOT NULL 
         THEN 1 
         ELSE 2 
      END
    , COALESCE(C1.mtl_addr_adder, C2.mtl_addr_adder)) AS rk 
   FROM dbo.MTD_INVOICE AS H 
      JOIN dbo.MTD_INV_LINE_ITEM AS D 
        on h.company = d.company 
       and h.gl_division = d.gl_division 
       and h.acct_year = d.acct_year 
       and h.acct_month = d.acct_month 
       and h.Invoice_nbr = d.Invoice_nbr 
       and d.Prod_Line in ('xx6', 'xx8')
         and not h.order_type = 'i' 
      join cm_mstr as cm 
         on h.ship_cust_nbr = cm.cm_addr 
      LEFT JOIN parms.dbo.mtl_addr_price AS C1 
        ON H.ship_cust_nbr = C1.mtl_addr_custcode 
       AND D.Mtl_Type = C1.mtl_addr_mtltype 
       and cm.cm_curr = c1.mtl_addr_currency 
       AND H.Invoice_Date BETWEEN C1.mtl_addr_StartDate AND COALESCE(C1.mtl_addr_EndDate, CURRENT_TIMESTAMP) 
      LEFT JOIN parms.dbo.mtl_addr_price AS C2 
         ON H.ship_cust_nbr = C2.mtl_addr_custcode 
         AND D.Mtl_Type = C2.mtl_addr_MtlType 
         AND D.Mtl_Type = C1.mtl_addr_mtltype 
         and cm.cm_curr = c1.mtl_addr_currency 
         AND H.Invoice_Date >= C2.mtl_addr_StartDate 
         AND NOT EXISTS
         (
            SELECT
               * 
            FROM
               parms.dbo.mtl_addr_price AS C3 
            WHERE
               C3.mtl_addr_custcode = C2.mtl_addr_custcode 
               AND C3.mtl_addr_MtlType = C2.mtl_addr_MtlType 
               and c3.mtl_addr_currency = c2.mtl_addr_currency 
               AND C3.mtl_addr_StartDate > C2.mtl_addr_StartDate 
               AND C3.mtl_addr_StartDate <= H.Invoice_Date
         )
)
UPDATE  MTD_INV_LINE_ITEM 
SET Mtl_Addr_Price = new_price output r.ship_cust_nbr,
    inserted.invoice_nbr,
    inserted.prime_line_nbr,
    inserted.Prod_line,
    inserted.Mtl_Type,
    inserted.mtl_addr_Price,
    r.mtl_addr_pricelist,
    r.mtl_addr_division,
    r.ASI_DIVISION 
	into dbo.SdtlLOG_Daily(CustNbr, InvoiceNbr, InvLine, Prodline, MtlCode, saddr, PriceList, divused, channel) 
FROM MTD_INV_LINE_ITEM AS D 
   JOIN Ranked AS R 
     ON D.Invoice_Nbr = R.Invoice_Nbr 
    AND D.prime_line_nbr = R.prime_line_nbr 		-- AND cm.cm_curr = R.cm_curr
    AND D.Mtl_Type = R.Mtl_Type 
    AND R.rk = 1 
    AND R.start_date IS NOT NULL
3

Thanks ... for tip on submitting code...

Any thoughts on how to make code work trying to use new CASE logic?

4

hi yes it works .. please see if this works for you

DROP TABLE test_data

CREATE TABLE test_data
  (
     asi_division      VARCHAR(10),
     mtl_addr_division VARCHAR(10)
  )

INSERT INTO test_data
SELECT 'dir',
       'EF'

INSERT INTO test_data
SELECT 'ext',
       'EF'

INSERT INTO test_data
SELECT 'sxb',
       'sxb'

INSERT INTO test_data
SELECT 'dir',
       'XY'

INSERT INTO test_data
SELECT 'sxb',
       'def'

SELECT 'Test Data',*
FROM   test_data;

WITH cte
     AS (SELECT r.asi_division,
                mtl_addr_division,
                CASE
                  WHEN r.asi_division IN ( 'dir', 'ext' )
                       AND r.mtl_addr_division = 'EF' THEN 1
                  WHEN r.asi_division IN ( 'sxb' )
                       AND r.mtl_addr_division = 'sxb' THEN 1
                  ELSE 0
                END AS testing
         FROM   test_data r)
SELECT 'SQL Output',*
FROM   cte
WHERE  testing = 1

image

5

I need to update the Stored Procedure to include the CASE logic to make sure I'm getting the
correct Addr applied to DIv\Channel. It's a complicated SP, looking for some help.

Thanks for update

6

complicated things
... are there in lots of things and LOTS of people have to figure it out
..how do they do it ?

( for example 280 million ... different parts are there .. from 280 million you have to get all possible combinations ..which is HUGE ..100 thousand PETA bytes .. then lot of other steps .. this is real life scenario )

you have to break it up into very small parts ... and then join the very small parts together .. to give the
very complicated Whole thing ..

first you would have to identify where in the code you would add the case statement

this is very general advice ..

if it helps great .. unless i need to take a look at your whole SP

or i can remote desktop .. we can both chat same time .. very quickly finish it off

7

in the where clause or in the update join clause?

8

That where I'm unsure were to insert the case clause during WHERE or just in UPDATE

Thanks ALL

9

The whole is SP is attached in prior posts.. If you need to dissect it.

Many Thanks for the help..

10

just an ... idea

how about in the WHERE clause ?

WHERE
(
( r.ASI_DIVISION IN ('dir','ext') AND r.mtl_addr_division = 'EF' )
OR
( r.ASI_DIVISION IN ('sxb') AND r.mtl_addr_division = 'sxb')
)

if you want to use in AND and then compare execution plans
for WHERE and AND .. which is better .. its another thing

11

Okay verify results now...

12

I'm assumed when you said WHERE that was the Ranked section of the code-- Correct?

Thanks.

13

this is what i thought .. dont know the exact logic you are trying to implement ..see my comments below

image
image934×434 8.67 KB

14

Thats where I inserted it, now verify my results..

15

Do you see any way to improve the code?

16

sorry i did not look at the code ..

17

any chance you can review the code to make improvements or maybe re-write how it's working
to make more readable and maintainable

18

i dont know if you have any idea of VIRTUAL Mapping instead of physical mapping ..

no need to change anything in code or the way it is ..

WHAT I mean is ? is understanding what to do ? and doing it
there are the same little thoughts and concepts ..

if you are not interested .. please let me know

I will look at the code

19

just thought maybe an easier way to do what I have defined.

20

Sure take a look an see if any changes needed