John Cobby John Cobby - 3 months ago 8
SQL Question

SQL Subquery more than 1 value

I'm having a problem with a subquery which hopefully someone can help me with.

If I run the following:

select t4.code from OITM T0
INNER JOIN DLN1 T1 ON T1.[ItemCode] = T0.[ItemCode]
INNER JOIN ODLN T2 ON T2.[DocEntry] = T1.[DocEntry]
INNER JOIN ITM10 T3 ON T3.[ItemCode] = T0.[ItemCode]
LEFT JOIN ODCI T4 ON T4.[AbsEntry] = T3.[ISCommCode]
WHERE T2.DocEntry = '7060'


The result is:


Although in some cases there may be multiple rows where the value matches those desired and others that don't at the same time.

I have written a query which has a subquery to check against these results and display a message if the [code] column in any row does not contain something specific:

IF(
(SELECT
T4.Code
FROM OITM T0
INNER JOIN DLN1 T1 ON T1.[ItemCode] = T0.[ItemCode]
INNER JOIN ODLN T2 ON T2.[DocEntry] = T1.[DocEntry]
INNER JOIN ITM10 T3 ON T3.[ItemCode] = T0.[ItemCode]
LEFT JOIN ODCI T4 ON T4.[AbsEntry] = T3.[ISCommCode]
WHERE T2.DocEntry = '7060') NOT IN ('22030010','22030001','22030009')
)
BEGIN
SELECT 'MESSAGE'
END


Obviously this won't compile if it has to return more than 1 row:

Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.


HAVING and COUNT don't work as they compile without displaying the message which is obviously wrong.

Is this possible or am I barking up the wrong tree?

Many thanks.

Answer

For checking if there are such lines, you can use EXISTS:

IF EXISTS (SELECT ...)

In your case I would make the select distinct to return a specific value only once:

select DISTINCT t4.code from OITM T0 ...

UPDATE

As @gordon-linoff pointed out, you could filter out the unwanted values like this:

select distinct t4.code from OITM T0
...
where T2.DocEntry = '7060' and t4.code not in ('22030010','22030001','22030009')
-- using AND instead of ):  ^^^
-- so one ( will be enough between EXISTS and SELECT

Putting it together:

IF EXISTS(SELECT ....  where T2.DocEntry = '7060' and t4.code not in ('22030010','22030001','22030009'))
BEGIN
    ...
END
Comments