add another check for update

Question

add another check for update

SSCrazy

Points: 2047

May 30, 2021 at 2:21 pm

This SP updates prices and now I need to add an additional check coming from the MTD_INV_LINE_ITEM and the mtl_addr_price tables. In the MTD_INV_LINE_ITEM there is a field called channel, it can have 3 different values z1a,z1b,z1c and in the mtl_addr_price there is a field called division that has 2 values rrb,rrc. If the channel is z1a or z1b that relates to channel rrb, if channel is z1c then that relates to division rrc. I need to make sure I'm picking up the correct price from mtl_addr_price using this additional criteria.

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,
       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 MTD_INVOICE AS H
JOIN 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 ('xx87;)
and not h.order_type = 'i'
join dbo.cm_mstr as cm
on h.ship_cust_nbr = cm.cm_addr
LEFT JOIN 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 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 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
   into SdtlLOG_Daily(CustNbr,InvoiceNbr,InvLine,Prodline,MtlCode,saddr,PriceList)
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 D.Mtl_Type = R.Mtl_Type
 AND R.rk = 1
 AND R.start_date IS NOT NULL;

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004301 More actions · Answer 1

You do realize that you're missing a single-quote in that, right?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Bruin SSCrazy Points: 2047 More actions · Answer 2

Yes sorry typo when I pasted.

and d.Prod_Line in ('xx87;)

Any help on how I and where I add the extra criteria?

Thanks.

Jeff Moden SSC Guru Points: 1004301 More actions · Answer 3

Bruin wrote:

Yes sorry typo when I pasted.
and d.Prod_Line in ('xx87;)
Any help on how I and where I add the extra criteria?
Thanks.

My question is ... is that the only error that you have? For example, you use "mtl_addr_price" both as a column name and a table name. Is that actually correct?

I think that you're trying to solve for too many conditions at once in a single query and that you're going to end up with something that no one will be able to easily maintain in the future. But, we'll give it a shot.

In Left Join for C1, you might try adding this code. Yeah, you could also do it as an "OR". Either way, it's going to end up being non-SARGable and it could cause duplicated returns depending on the underlying data but I'm thinking that you already have that issue or you wouldn't have needed the deduplication caused by you Row_Number partitioning with criteria in the UPDATE to only join if RK=1.

AND 1 = CASE
        WHEN D.channel IN ('z1a','z1b') AND C1.division = 'rrb' THEN 1
        WHEN D.channel IN ('z1c')       AND C1.division = 'rrc' THEN 1
        ELSE 0
        END

I could be wrong but you may also need two of those in the Left Join for C2... one for C1 and 1 for C2.

You might also need one in the WHERE EXISTS for C3.

As an alternative, you might try exposing channel and division in the cte and doing the comparison above just in the UPDATE clause. I don't know for sure... just making a suggestion for you to try.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Bruin SSCrazy Points: 2047 More actions · Answer 4

If there is a better more readable way of doing this update I would appreciate your help.

I'll try and add the code for left join of C1 and see what happens.

Many Thanks!!

Bruin SSCrazy Points: 2047 More actions · Answer 5

I added in the Left join C1 section, but seems like that didn't work. I maybe misunderstanding where to insert new code.

Thanks!!

Jeff Moden SSC Guru Points: 1004301 More actions · Answer 6

I just don't know enough about the data nor what the rules being following for which prices to use are. I also suggested that you'll need to add the extra criteria to 3 spots or possibly to the outer query after exposing the channel and division columns on the queries in the CTE.

I also asked you about a column and table name being identical. Have you actually looked into that? You said your copy'n'paste was the cause of the missing quote error... what else might you have copied incorrectly? Needless to say, your previous response wasn't a great confidence builder. 😉

And, yeah... my suggestion would be to gather up the actual rules and rewrite this code. "Set Based" does NOT necessarily mean "All in one query". A good bit of "Divide'n'Conquer" using more than one UPDATE would likely go a long way here. Adding an update driver table to store your interim results until ready to do the actual UPDATEs would also be very effective, especially when it comes to future maintenance when rules go through a M.A.D. (Modify, Add, Delete... heh... actually, I prefer D.A.M. :D) process

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Bruin SSCrazy Points: 2047 More actions · Answer 7

For example, you use "mtl_addr_price" both as a column name and a table name. Is that actually correct?

That's correct the column is coming from a different table, and there is a table named mtl_addr_price. I tried the approach of exposing the channel and division columns on the queries in the CTE and then adding your piece of code in the update section. I'm checking the results of the update now.

Thanks.

Bruin SSCrazy Points: 2047 More actions · Answer 8

want to tackle the re-write sure could use some help..

Thanks.

Bruin SSCrazy Points: 2047 More actions · Answer 9

I tried exposing the 2 fields in CTE then added your code after:

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 D.Mtl_Type = R.Mtl_Type

AND R.rk = 1

AND R.start_date IS NOT NULL;

I didn't get the results expected, any other suggections?

Thanks.