CODEWITHSUNDEEP
sqlsql-server
FatalError
FatalError

Reputation: 219

Subquery returned more than 1 value. VB6

Hi I'm trying to select the value of movementline.qty but only if movements.movementscode is equal to RW if not just put 0.00

SELECT levelfield1.description,
       products.reportuom,
       products.productcode,
       products.description,
       Isnull(Sum(ReceivingLine.qty), 0.00)       AS [B.Delivery],
       movements.movementcode,
       Isnull((SELECT qty
               FROM   MovementLine
               WHERE  movementcode = 'RW'), 0.00) AS [B. Returned]
FROM   Products
       LEFT JOIN LevelField1
              ON levelfield1.levelfield1code = products.levelfield1code
       LEFT JOIN ReceivingLine
              ON receivingline.PRODUCTCODE = products.productcode
       LEFT JOIN MovementLine
              ON movementline.ProductCode = products.productcode
       LEFT JOIN Movements
              ON movements.MovementID = MovementLine.movementid
GROUP  BY levelfield1.Description,
          products.reportuom,
          products.productcode,
          products.description,
          movementline.qty,
          movements.movementcode

I got error

Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression. Warning: Null value is eliminated by an aggregate or other SET operation.

Upvotes: 0

Views: 183

Answers (1)

Azizur Rahman
Azizur Rahman

Reputation: 68

Your code make to think deeply. Because there maybe join problem and you may not get correct result. Problem will solve using top 1 but correct result will not possible. So here need to use CASE. I hope your problem will solve by using bellow code.

select levelfield1.description, products.reportuom, products.productcode,
        products.description ,ISNULL(SUM(ReceivingLine.qty),0.00) as [B.Delivery], 
  movements.movementcode,
  CASE 
    WHEN movementline.movementcode = 'RW' THEN ISNULL(movementline.qty,0.00)
    ESLE 0.00
  END AS [Returned]

  from Products 
  left join LevelField1 on levelfield1.levelfield1code = products.levelfield1code
  left join ReceivingLine on receivingline.PRODUCTCODE = products.productcode
  left join MovementLine on movementline.ProductCode = products.productcode
  left join Movements on movements.MovementID = MovementLine.movementid 
  group by levelfield1.Description, products.reportuom, products.productcode, products.description, movementline.qty,movements.movementcode

Upvotes: 1

Related Questions