February 24, 2013 at 6:31 am
Hi,
I have the following query
IF(SELECT 1 FROM VERSION
WHERE PATINDEX ('7.0%', VERSION)> 0
OR PATINDEX ('7.1%', VERSION)> 0
OR PATINDEX ('7.2%', VERSION)> 0
OR PATINDEX ('7.3%', VERSION)> 0
OR PATINDEX ('7.4%', VERSION)> 0
OR PATINDEX ('7.5%', VERSION)> 0
OR PATINDEX ('7.6%', VERSION)> 0
OR PATINDEX ('7.7%', VERSION)> 0)>0
BEGIN
if statement is giving error
"Msg 512, Level 16, State 1, Line 1
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression."
because its trying to give the following output
1
1
1
1
1
1
any solution pls
Regards,
antony
February 24, 2013 at 7:04 am
Hi Anthony,
the question is, what do you want to do?
Do you want to check whether at least one row is returned? Then take:
IF exists(SELECT 1 FROM VERSION
WHERE PATINDEX ('7.0%', VERSION)> 0
OR PATINDEX ('7.1%', VERSION)> 0
OR PATINDEX ('7.2%', VERSION)> 0
OR PATINDEX ('7.3%', VERSION)> 0
OR PATINDEX ('7.4%', VERSION)> 0
OR PATINDEX ('7.5%', VERSION)> 0
OR PATINDEX ('7.6%', VERSION)> 0
OR PATINDEX ('7.7%', VERSION)> 0)
BEGIN
Greets
Patrick
[font="Arial"]Kind regards,
Patrick Fiedler
Consultant
February 24, 2013 at 7:05 am
It's returning a 1 for each row that satisfies the predicates.
Maybe you could use IF EXISTS?
February 24, 2013 at 7:13 am
yes, it works.. thanks a lot
February 25, 2013 at 10:39 am
IF EXISTS(SELECT 1 FROM VERSION
WHERE VERSION LIKE '7.[01234567]%')
BEGIN
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply