Getting fetch returns more than requested number of rows error SQL HANA SAP

Question

Hello can you please help me fix this query :

SELECT T1."Price" into pght FROM ITM1 T1 WHERE T1."PriceList" = '1';

UPDATE "ITM1"
    SET ITM1."Factor" = T2."U_COEFFICIENT" , ITM1."Price" = T2."U_COEFFICIENT" * pght
    FROM ITM1
    INNER JOIN OITM T0 ON T0."ItemCode" = ITM1."ItemCode"
    INNER JOIN OMRC T1 ON T1."FirmCode" = T0."FirmCode"
    INNER JOIN "@PR_MARQUES_ASSOCIE" T2 ON T1."FirmName" = T2."U_MARQUES"
    WHERE T2."U_MARQUES" = 'ROCHAS' AND T2."Code" = '01' 
      AND ITM1."PriceList" = '2' AND T1."FirmName" = 'ROCHAS';

This is the error I get:

Getting fetch returns more than requested number of rows error

Actually my goal is to update the column ITM1."Price" where ITM1."PriceList" = '2' with the price Value where the "PriceList" = '1' * factor

For example:

ITM1."Price" and "PriceList" = '1' = 40 $ 
ITM1."Factor" = T2."U_COEFFICIENT" = 4

Then

ITM1."Price" and "PriceList" = '2' = 160 $ 

I tried the above query (in the body) and I think it's not correct.

UPDATE : The message returned is the one in the title : 'Getting fetch returns more than requested number of rows error' . The lookup need to take account the item from ITM1 . It should take the price list = '1' which represent the 'purchase price' and multiplie with the factor . "U_COEFFICIENT" is used in another table to manipulate and update the column "Factor" from table ITM1

Exemple in Excel Format Exemple in Excel Format 2

Answer 1

The error message is caused by this statement:

SELECT T1."Price" INTO pght FROM ITM1 T1 WHERE T1."PriceList" = '1';

in combination with the data in table ITM1. There are more than a single record in ITM1 that match the condition.

SELECT INTO requires a scalar - that is a single value - to be returned by the SELECT statement. If there are more (or no values at all) an error message is returned.

To avoid this error, there are a few options:

• make the filter conditions so specific that only a single record gets returned.
• use an aggregation, e.g. MAX(T1."Price") to return only a single value.
• make sure that the data in the table does not have multiple records for the filter condition.

Based on the column and table names I am guessing that the purpose for this query is to update the price of items in table ITM1 with "Pricelist"=2 with the value for the same item with "Pricelist=1" multiplied by some factor. If that is indeed the case, a correlated subquery or a join would be a possible solution approaches.

Here is an example of how an outer join could work:

UPDATE "ITM1"
    SET ITM1."Factor" = T2."U_COEFFICIENT" 
      , ITM1."Price" = T2."U_COEFFICIENT" * COALESCE(ITM_P1."Price", 1.0)
    FROM ITM1
    LEFT OUTER JOIN ITM1 ITM_P1
       ON ITM1."ItemCode" = ITM_P1."ItemCode"
       AND ITM1."PriceList" = '2'
       AND ITM_P1."PriceList" = '1'
    INNER JOIN OITM T0 
       ON T0."ItemCode" = ITM1."ItemCode"
    INNER JOIN OMRC T1 
       ON T1."FirmCode" = T0."FirmCode"
    INNER JOIN "@PR_MARQUES_ASSOCIE" T2 
       ON T1."FirmName" = T2."U_MARQUES"
    WHERE 
       T2."U_MARQUES" = 'ROCHAS' 
       AND T2."Code" = '01' 
       AND ITM1."PriceList" = '2' 
       AND T1."FirmName" = 'ROCHAS';

Note how ITM1 is outer joined with itself - an outer join to allow for cases, where an item does not have a record with "PriceList"=1. For those non-matches a NULL will be returned and the COALESCE() function ensures that in those cases, the item price will be multiplied with 1.0.