July 10, 2009 at 8:21 am
Hi,
I have the 2 following queries (in IF and ELSE parts). I would like to combine the 2 ones into only one query. In other words, I want to remove IF, ELSE statements. Please give me any idea to do so. Thank you in advance.
declare @OtherAddressDetails nvarchar(1000)
set @OtherAddressDetails ='member address' -- or to whatever string
IF(@OtherAddressDetails='member address')
BEGIN
select
AddressID,
Building=coalesce(Building,0),
Street,
City,
Zip,
StateProvince,
Country,
OtherAddressDetails,
Status
from [Address]
where OtherAddressDetails = 'member address'
END
ELSE
BEGIN
select
AddressID,
Building=coalesce(Building,0),
Street,
City,
Zip,
StateProvince,
Country,
OtherAddressDetails,
Status
from [Address]
where OtherAddressDetails <> 'member address' or OtherAddressDetails is null
END
July 10, 2009 at 8:31 am
select
AddressID,
Building=coalesce(Building,0),
Street,
City,
Zip,
StateProvince,
Country,
OtherAddressDetails,
Status
from [Address]
where (OtherAddressDetails = 'member address' and @OtherAddressDetails='member address')
or ((OtherAddressDetails 'member address' or OtherAddressDetails is null)
and @OtherAddressDetails'member address')
Maybe some explanation: actually you would to show rows that apply to rule1 or rule2, the trick is to add when to use rule1 and rul2, this by adding so you get: where (rule1 and "when rule1) or (rule2 and "when rule2")
July 10, 2009 at 8:33 am
Greetings,
From what I can see, the address is being selected on 2 opposite checks on the same table. So, either way, you still get the address. If that is really the case, then just drop the WHERE clause entirely and use a single SELECT statement. If I am missing a condition somewhere, please let me know.
select
AddressID,
Building=coalesce(Building,0),
Street,
City,
Zip,
StateProvince,
Country,
OtherAddressDetails,
Status
from [Address]
The one thing that I would question is that you have a variable called @OtherAddressDetails and you have a field called OtherAddressDetails in your table. Is the desired result to use this variable to pull the corresponding record based on equality to the like-named field? If so then this may be the code you are looking for:
DECLARE @OtherAddressDetails nvarchar(1000)
SET @OtherAddressDetails ='member address' -- or to whatever string
SELECT
AddressID,
COALESCE(Building, 0) AS Building,
Street,
City,
Zip,
StateProvince
Country,
OtherAddressDetails,
Status
FROM [Address]
WHERE
OtherAddressDetails = @OtherAddressDetails
OR OtherAddressDetails IS NULL
Have a good day.
Terry Steadman
July 10, 2009 at 8:34 am
The two cases look identical, am I missing something?
Depending on what you want to do, you could do something like:
declare @OtherAddressDetails nvarchar(1000)
set @OtherAddressDetails ='member address' -- or to whatever string
select
AddressID,
Building=coalesce(Building,0),
Street,
City,
Zip,
StateProvince,
Country,
OtherAddressDetails,
STATUS,
(
CASE
WHEN OtherAddressDetails = 'member address' THEN PRINT 'SHOW SOMETHING'
ELSE PRINT 'SHOW SOMETHING ELSE'
END
)
from [Address]
July 10, 2009 at 8:35 am
In order to eliminate the IF...ELSE we need to know what you would expect returned and in order to know that it would be helpful if you posted some sample data as explained in the links in my signature line. I can throw a guess out, but it will only be a guess. Also when you are doing an equality or an inequality I don't see how it can be combined into one query because you would always return everything if you used an OR.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
July 10, 2009 at 8:39 am
He want the see the rows with "memberaddress" when the param is member adress, but when it's not he wants to see all the other rows(even null)
litte example:
CREATE TABLE [dbo].[devTable](
[field1] [nchar](1) NULL
) ON [PRIMARY]
Insert into [dbo].[devTable] select'a'
Insert into [dbo].[devTable] select'1'
Insert into [dbo].[devTable] select'2'
Insert into [dbo].[devTable] select'3'
Insert into [dbo].[devTable] select'4'
Insert into [dbo].[devTable] select null
declare @OtherAddressDetails nvarchar(1000)
set @OtherAddressDetails ='b' -- or to whatever string
select *
from dbo.devTable
where (field1 = 'a' and @OtherAddressDetails='a')
or ((field1 'a' or field1 is null)
and @OtherAddressDetails'a')
--gives 1,2,3,4,null
set @OtherAddressDetails ='a' -- or to whatever string
select *
from dbo.devTable
where (field1 = 'a' and @OtherAddressDetails='a')
or ((field1 'a' or field1 is null)
and @OtherAddressDetails'a')
--gives a
July 10, 2009 at 8:44 am
Greetings,
I had missed that the IF statement was on the variable when considering the 2 statements. So, here is a slightly modified statement from the one I had given earlier that does what you had wanted.
DECLARE @OtherAddressDetails nvarchar(1000)
SET @OtherAddressDetails ='member address' -- or to whatever string
SELECT
AddressID,
COALESCE(Building, 0) AS Building,
Street,
City,
Zip,
StateProvince
Country,
OtherAddressDetails,
Status
FROM [Address]
WHERE
(
@OtherAddressDetails = 'member address'
AND OtherAddressDetails = @OtherAddressDetails
)
OR
(
@OtherAddressDetails 'member address'
AND ISNULL(OtherAddressDetails, '') 'member address'
)
Have a good day.
Terry Steadman
July 10, 2009 at 8:46 am
kramaswamy (7/10/2009)
The two cases look identical, am I missing something?Depending on what you want to do, you could do something like:
declare @OtherAddressDetails nvarchar(1000)
set @OtherAddressDetails ='member address' -- or to whatever string
select
AddressID,
Building=coalesce(Building,0),
Street,
City,
Zip,
StateProvince,
Country,
OtherAddressDetails,
STATUS,
(
CASE
WHEN OtherAddressDetails = 'member address' THEN PRINT 'SHOW SOMETHING'
ELSE PRINT 'SHOW SOMETHING ELSE'
END
)
from [Address]
The "Address" table have several rows. It has a nvarchar-types column named "OtherAddressDetails". If an address belongs to "member address", then the column value is 'member address'. Otherwise, the address row is non-member address. Based on given value of @OtherAddressDetails variable, I would like to get rows of member OR non-member, but not both.
example of rows of the "Adddress" table (cut other columns)
----------------------------------------
AddressID OtherAddressDetails
1 member address
2 null
3 the address was input last year
4 member address
5 the address is confirmed
6 null
now, if @OtherAddressDetails='member address', the returned row IDs are 1, 4
if @OtherAddressDetails='xyz derts sdfsd whatever', the returned row IDs are 2, 3, 5, 6
July 10, 2009 at 8:48 am
terrance.steadman (7/10/2009)
Greetings,I had missed that the IF statement was on the variable when considering the 2 statements. So, here is a slightly modified statement from the one I had given earlier that does what you had wanted.
DECLARE @OtherAddressDetails nvarchar(1000)
SET @OtherAddressDetails ='member address' -- or to whatever string
SELECT
AddressID,
COALESCE(Building, 0) AS Building,
Street,
City,
Zip,
StateProvince
Country,
OtherAddressDetails,
Status
FROM [Address]
WHERE
(
@OtherAddressDetails = 'member address'
AND OtherAddressDetails = @OtherAddressDetails
)
OR
(
@OtherAddressDetails 'member address'
AND ISNULL(OtherAddressDetails, '') 'member address'
)
Have a good day.
Terry Steadman
I do not want to get rows of BOTH types of address. The returned rows are either member addresses OR non-member addresses, but NOT both.
Thanks.
July 10, 2009 at 8:52 am
In that case i think your best bet is to just stick with what you have. Any other solution is going to involve a more complex WHERE clause which will just slow your query down. There's no real downsides to having an IF/ELSE statement.
July 10, 2009 at 8:56 am
Greetings,
Please take a look at the code again, especially in the WHERE clause.
WHERE
(
@OtherAddressDetails = 'member address'
AND OtherAddressDetails = @OtherAddressDetails
)
OR
(
@OtherAddressDetails 'member address'
AND ISNULL(OtherAddressDetails, '') 'member address'
)
The first section is checking your variable to be equal to 'member address'. If it is, then your field OtherAddressDetails must also be equal to 'member address'. If not, then the selection fails and the record is not returned.
In the second section. your variable can not be equal to 'member address'. If that is true, then your field also can not be equal to 'member address'. If it is, then the selection fails and the record is not returned.
So, either you get the 'member address' records or you get the records that are not 'member address' all based by your variable: @OtherAddressDetails.
Have a good day.
Terry Steadman
July 10, 2009 at 9:00 am
Jack Corbett (7/10/2009)
In order to eliminate the IF...ELSE we need to know what you would expect returned and in order to know that it would be helpful if you posted some sample data as explained in the links in my signature line. I can throw a guess out, but it will only be a guess. Also when you are doing an equality or an inequality I don't see how it can be combined into one query because you would always return everything if you used an OR.
The "Address" table have several rows. It has a nvarchar-types column named "OtherAddressDetails". If an address belongs to "member address", then the column value is 'member address'. Otherwise, the address row is non-member address. Based on given value of @OtherAddressDetails variable, I would like to get rows of member OR non-member, but not both.
example of rows of the "Adddress" table (cut other columns)
----------------------------------------
AddressID OtherAddressDetails
1 member address
2 null
3 the address was input last year
4 member address
5 the address is confirmed
6 null
now, if @OtherAddressDetails='member address', the returned row IDs are 1, 4
if @OtherAddressDetails='xyz derts sdfsd whatever', the returned row IDs are 2, 3, 5, 6
July 10, 2009 at 9:09 am
terrance.steadman (7/10/2009)
Greetings,Please take a look at the code again, especially in the WHERE clause.
WHERE
(
@OtherAddressDetails = 'member address'
AND OtherAddressDetails = @OtherAddressDetails
)
OR
(
@OtherAddressDetails 'member address'
AND ISNULL(OtherAddressDetails, '') 'member address'
)
The first section is checking your variable to be equal to 'member address'. If it is, then your field OtherAddressDetails must also be equal to 'member address'. If not, then the selection fails and the record is not returned.
In the second section. your variable can not be equal to 'member address'. If that is true, then your field also can not be equal to 'member address'. If it is, then the selection fails and the record is not returned.
So, either you get the 'member address' records or you get the records that are not 'member address' all based by your variable: @OtherAddressDetails.
Have a good day.
Terry Steadman
Thank you very much. Your solution works well when I try it again.
johnsql
July 10, 2009 at 9:18 am
Terry,
I have to agree with kramaswamy on this one. By OR'ing in the WHERE clause there is the likelihood that you will not be able to do an index seek, and then including the ISNULL() function on a column in the WHERE also keeps the optimizer from considering an index seek.
johnsql,
What are the indexes on the table?
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply