IF statement error
I have the following columns in TableA
TableA Column1 varchar Column2 int Column3 bit
I am using this statement
IF Column3 = 0
SELECT Column1, Column2 FROM
TableA WHERE
Column2 > 2开发者_如何学编程00
ELSE
SELECT Column1, Column2 FROM
TableA WHERE
Column2 < 200
But the statment does not compile. It says Invalid Column Name 'Column3'
Column3 is not being referenced anywhere outside of the IF
and ELSE
blocks. If you wish to reference this value you will need to declare a new variable and use that;
DECLARE @btColumn3 BIT
SELECT @btColumn3 = Column3 FROM @tblTableA
IF @btColumn3 = 0
SELECT Column1, Column2 FROM
@tblTableA WHERE
Column2 > 200
ELSE
SELECT Column1, Column2 FROM
@tblTableA WHERE
Column2 < 200
Or do the following;
IF (SELECT Column3 FROM @tblTableA) = 0
SELECT Column1, Column2 FROM
@tblTableA WHERE
Column2 > 200
ELSE
SELECT Column1, Column2 FROM
@tblTableA WHERE
Column2 < 200
Either way you will have to ensure that the query used to retrieve Column3 returns a single result either by limiting your query so that it can only return a single value or using MIN()
, MAX()
etc depending on your requirements.
Also, if you need to execute more than one query within the IF
and ELSE
blocks you will need to wrap the contents in BEGIN
and END
as follows:
IF @btColumn3 = 0
BEGIN
// Do a query
// Do another
END
ELSE
BEGIN
// Do a query
// Do another
END
If you want to do this you need to first store the value of Column3 in a variable.
Declare @temp money
Select @Temp = Column3
From TableA
IF @Temp = 0
begin
SELECT Column1, Column2 FROM
TableA WHERE
Column2 > 200
end
ELSE
begin
SELECT Column1, Column2 FROM
TableA WHERE
Column2 < 200
end
Obviously, this assumes that there will only be one value returned for Column3.
EDIT:
This is a different approach which I think should work for you:
declare @CutOffValue money
declare @MaxValue money
Set @CutOffValue = 200
Set @MaxValue = 9999999999
Select Column1, Column2
From TableA
Where Column2 > Case When Column3 = 0 Then @CutOffValue Else 0 End
And Column2 < Case When Column3 = 0 Then @MaxValue Else @CutOffValue End
You are mixing 2 different levels:
IF is at the TSQL (procedure) level and cannot depend on the row values
SELECT is the query itself where the row values can be used to filter the result set
The following would work
IF Condition /* independent of the different values of TableA. can be an aggregate though */
BEGIN
SELECT Column1, Column2 FROM
TableA WHERE
Column2 > 200
END
ELSE
BEGIN
SELECT Column1, Column2 FROM
TableA WHERE
Column2 < 200
END
You will need the syntax as follows
IF <CONDITION>
BEGIN
<Your Statement>
END
ELSE
<Your Statement>
Hope this is helpful!!
Assuming you have posted the complete query, then the problem with your IF
clause is that you are assuming that it can use the columns from the SELECT
statement following it. It cannot and does not. This is why it will not compile.
You need your test condition to be separate from the statements following the IF clause. See on MSDN.
DECLARE @test BIT
SELECT @test = 0
IF @test = 0
BEGIN
SELECT Column1, Column2 FROM
TableA WHERE
Column2 > 200
END
ELSE
BEGIN
SELECT Column1, Column2 FROM
TableA WHERE
Column2 < 200
END
Why not just do
select Column1, Column2 from TableA where
Column2 > 200 and Column3 = 0 or Column2 < 200 and Column3 = 1
or, abusing arithmetics,
select Column1, Column2 from TableA where (Column2 - 200) * (2 * Column3 - 1) < 0
Since the answer to your problem varies largely on exactly what logic has to be implemented, analyzing present scenario all I can give you is a small and compact query which can meet your requirements (I hope so…)
SELECT Column1,column2 FROM TableA WHERE
(CASE WHEN Column3=0 then Column2 else 2)>(CASE WHEN Column3=0 then 200 ELSE 1)
AND (CASE WHEN Column3<>0 then Column2 else 1)<(CASE WHEN Column3<>0 then Column2 else 2)
精彩评论