Need to return more than one record from sub-query -- why doesn't this work?

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Alex - 21 Jul 2008 20:25 GMT

Hi Everyone,

I have a question, more of why MS SQL is doing this than anything I
guess. I'm writing a Case statement within my Where clause to create
a conditional where, and though in theory it would work, MS SQL is
complaining when I don't think it should.

Below is my SQL:

Select *
from BillingTable
Where customerID in (
Case @inReturnType
When 1 Then @customerID
Else
(Select customerID from CustomerTable
Where AltCustomerID = @customerID)
End
)

Basically if @inReturnType is 1 then it just searches the BillingTable
for the record where customerID equals @customerID, but if anything
else it returns however many records have CustomerID in the
AltCustomerID field. Since I'm using an 'in' in the Where this should
work and removing the Case statement and leaving just the aprt in Else
works great ... but MS SQL is giving me this error:
Msg 512, Level 16, State 1, Line 7
Subquery returned more than 1 value. This is not permitted when the
subquery follows =, !=, <, <= , >, >= or when the subquery is used as
an expression.

Why??? I can think of cases when returning more than one record
wouldn't be a good idea, especially if I'm using an '=' instead of
'in', but in the way this statement is setup logically it should work.

Just curious...

Alex

Reply to this Message

Alex Kuznetsov - 21 Jul 2008 20:30 GMT

> Hi Everyone,
>
[quoted text clipped - 34 lines]
>
> Alex

syntax demand a single value in that place. Try this:

Select *
from BillingTable
Where ( @inReturnType = 1 AND customerID = @customerID)
OR (@inReturnType <> 1 AND
customerID in (
Select customerID from CustomerTable
Where AltCustomerID = @customerID)
)

Reply to this Message

--CELKO-- - 21 Jul 2008 20:57 GMT

>> Below is my SQL: <<

Nope, that is not SQL at all. Do not tell us that a table is a table;
follow the ISO-11179 rules and name it for what it is and not how it
is stored. That means a table name is a collective or plural noun to
show that it is a set. Never use * in production code, it just makes a
mess to maintain.

Next, CASE is an **expression** and not a control flow construct. SQL
is a declarative language. That is the whole point of SQL. You are
still writing procedural code and thinking with a file system mindset
(hence the confusion of rows/records, columns/fields)

>> Basically if @in_return_type is 1 then it just searches the Billing for the record [sic: rows are not records] where customer_id equals @customer_id, but if anything else it returns however many records [sic] have customer_id in the alt_customer_id field [sic: columns are not fields]. Since I'm using an 'IN' in the WHERE clause this should work and removing the CASE statement [sic: expression] and leaving just the part in ELSE works great... <<

Finally, you need to post DDL if you expect to get a useful reply. It
is very hard to guess about data when you never show it to us. For
example, how do you join Customers and Billings? I guess there is an
account number in the system, so customers can have more than one
account. Is @in_return_type NULL-able or not? etc. We have no idea
what the SELECT list should look like. Here is a wild guess:

SELECT B.*, C.*
FROM Billing AS B, Customers AS C
WHERE B.acct_nbr = C.acct_nbr
AND ((@in_customer_id = B.customer_id AND @in_return_type = 1)
OR (@in_customer_id = C.alt_customer_id AND @in_return_type <>
1));

Want to try again with specs?

Reply to this Message