CODEWITHSUNDEEP
sqlreporting-services
user1296762
user1296762

Reputation: 512

Adding Multiple Multi Selects

Basically I already have the code to pull back all orders with same prefix but is there a way I can add in the option of a multi select of ordernames in SSRS so I need to change my code too. i.e. Although the code already can pull back orders that have ABC prefix, I want the ability to also by order name be able to pull many back. With the first version only any policies that have the order prefix would appear but I want all orders with Order name of Amy Jane or Betty Jane

I'm currently trying 

DECLARE @order varchar (100)
set @order = 'Amy Jane' or 'Betty Jane'

SELECT OrderNoref,
OrderDate
FROM Ordertable
WHERE LEFT([OrderNoref],(SELECT MAX(LEN(OrderPrefix)) FROM Orders123 WHERE OrderName = @order)) IN (SELECT OrderPrefixFROM Orders123  WHERE OrderName= @order)

I'm wanting something like the following but I'm having issues 

SELECT  OrderNoref,
OrderDate
FROM Ordertable
WHERE LEFT([OrderNoref],(SELECT MAX(LEN(OrderPrefix)) FROM Orders123 WHERE OrderName = @order)) IN (SELECT OrderPrefixFROM Orders123  WHERE OrderName= @order)
AND (Select OrderName from Orders123) in (@Order)

Upvotes: 0

Views: 32

Answers (1)

Bayeni
Bayeni

Reputation: 1046

See the code below, I've changed 'AND' to OR and corrected the syntax. This will filter data using order name or order prefix

    SELECT 
        OP.OrderName, 
        O.OrderNo,
        O.OrderDate
    FROM Ordertablev AS O
    JOIN (
            SELECT OrderPrefix, OrderName FROM Orders123  WHERE OrderName= 'Amy Jane' OR OrderName = 'Betty Jane'
        ) AS OP ON LEFT(O.OrderNo, LEN(OP.OrderPrefix)) = OP.OrderPrefix

Upvotes: 1

Related Questions