Forum Stats

  • 3,770,165 Users
  • 2,253,079 Discussions
  • 7,875,353 Comments

Discussions

How can I compare two tables in a CASE ?

Edisson Gabriel López
Edisson Gabriel López Member Posts: 34 Green Ribbon

Hello is that I have a problem and the solution is with a SQL statement where I have to perform through SQL the UPDATE statement where to fulfill that with in the table F4311 which is details of purchases and F4101 which is branch of items has an associated supplier, then if that supplier that is in the F4101 is different the conditional is fulfilled to update the status, my SQL statement is as follows:

UPDATE CRPDTA.F4311

SET F4311.PDLTTR = CASE WHEN F4311.PDAN8<>F4102.IBVEND THEN '980',

F4311.PDNXTR = CASE WHEN F4311.PDAN8<>F4102.IBVEND THEN '999'

FROM CRPDTA.F4102

INNER JOIN CRPDTA.F4102 F4102 ON TRIM(PDITM)= TRIM(IBITM) AND TRIM(PDMCU) = TRIM(IBMCU)


hoped you can help me, thank you.

Best Answer

  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 41,233 Red Diamond
    Accepted Answer

    Hi,

    If you have to ask how to use it, then maybe UPDATE isn't the right tool for the job: MERGE may be better.

    In this problem, something like this might work:

    MERGE INTO crpdta.f4311 dst
    USING (
             SELECT  ROWID AS rid
    	 ,	 CASE
    	 	   WHEN y.pdan8 <> x.ibvend
    		   THEN '980'
    	 	 END AS pdlttr
    	 ,	 CASE
    	 	   WHEN y.pdan8 <> x.ibvend
    		   THEN '999'
    	 	 END AS pdnxtr
    	 FROM	 crpdta.f4102 x
    	 JOIN	 crpdta.f4311 y   ON  TRIM (pditm) = TRIM (ibitm) -- Qualify all column names
    	 	 		  AND TRIM (pdmcu) = TRIM (ibmcu) -- Qualify all column names
    
       )			 src
    ON (dst.ROWID = src.rid)
    WHEN MATCHED THEN UPDATE
    SET  dst.pdlttr = src.pdlttr
    ,    dst.pdnxtr = src.pdnxtr
    WHERE DECODE (dst.pdlttr, src.pdlttr, 1, 0) = 0
    OR    DECODE (dst.pdnxtr, src.pdnxtr, 1, 0) = 0
    ;
    

    Of course, I can't test it without some sample data, results and requrements.

    As posted, this assumes f4311 is a real table, not a view.

Answers