Comparing multiple values in an iif clause

Hello,

Just want to ask..

Does anyone have an idea how i can compare multiple values in the iif() clause like

IDType IN (1, 2, 3, 4)

This one doesnt work

iif(IDType IN (1, 2, 3, 4), true, false)

Please help..

Thanks..

One option is to use an IIF function call like this:

=iif(Fields!IDType.Value = 1 OR Fields!IDType.Value = 2 OR Fields!IDType.Value = 3 OR ..., true, false)

In some cases (e.g. numeric values), you could use alternatives to the IIF function:
* =Choose(...)
http://msdn.microsoft.com/library/en-us/vblr7/html/vafctchoose.asp
* =Switch(...)
http://msdn.microsoft.com/library/en-us/vblr7/html/vafctswitch.asp

-- Robert

Comparing columns contents between 2 table...

any idea on the syntax for such a querie? all my attempts to compare an item # column in 2 seperate tables keep coming up an incorrect syntax...(I want to compare one column in one table with a column in another table).
Thanks in advance:rolleyes:I'm not sure on what you are asking?

SELECT a.*
FROM TBL a, TBL b
WHERE a.col = b.col

or

SELECT a.*
FROM TBL a
WHERE NOT EXISTS
(
SELECT *
FROM TBL b
WHERE a.col = b.col
)

??|||sorry I did not respond to your reply:

Thanks for the info - also the first one works for me...

SELECT *
FROM tableA, tableB
WHERE tableA.a_fieldname = tableB.b_fieldname

simple really :)

Again thanks... Now if I can only get it to ignore NULL and empty cells...|||SELECT *
FROM tableA, tableB
WHERE tableA.a_fieldname = tableB.b_fieldname
AND tableB.b_fieldname is not null
and LEN (tableB.b_fieldname) <> 0

--you can also put in tableA.a_fieldname as well if you so desire
-- tell me if that sorts out your problem|||thanks works fine :)

got to get it onto my production server and run now... Thanks,

Compare Tables with compound PK

Compare Tables with compound PK

Would like to know the correct way to compare two tables that has a Compound Primary Key.
The idea is, I have tables with the same structure, i need to delete from one the rows that already have
in the anotherone, but have primary keys with more than one column.
I'm doing this way, but not sure if there are any other best. '

Delete Table1
Where Ltrim(Rtrim(Convert(Char(20),code))) +
Ltrim(Rtrim(Convert(Char(20),period))) +
Ltrim(Rtrim(Convert(Char(20),named)))

In ( Select Ltrim(Rtrim(Convert(Char(20),code))) +
Ltrim(Rtrim(Convert(Char(20),period))) +
Ltrim(Rtrim(Convert(Char(20),named)))
From ##Table2)

Thanks in Advance!

: )

Code Snippet

Delete tb1

from Table1 tb1

inner join ##Table2 tb2

on tb1.code = tb2.code

and tb1.period = tb2.period

and tb1.named = tb2.named

|||

Try using EXISTS operator.

delete dbo.t1

where exists (

select *

from dbo.t2

where dbo.t2.c1 = dbo.t1.c1

and dbo.t2.c2 = dbo.t1.c2

dbo.t2.c3 = dbo.t1.c3

)

go

AMB

|||

Thank you so much,

Let me ask you something more just to understand clearly the convenience of not to do it the first way.. is it in terms of performance or precision a wrong way to use concatenation? My question is because I have to do it for many tables and I would like to do it correctly.

|||

String catenation incurs a substantial performance hit -and will most likely also be unable to utilize any indexing. And to require if for every row in the table JUST in order to accomplish this JOIN is totally unnecessary.

Alejandro's suggestion of using the [ IF EXISTS ] structure is the most efficient, since the DELETE will occur upon finding the first row that meets the criteria, whereas Dale's suggestion will have to traverse through the indexes to see if there are more than one row that meets the criteria.

|||ok, Thank you, so much : )

Communication between two service brokers

I am trying to setup an alert system for our new application. Idea is to create triggers on the tables that we need updates about. When a table changes trigger will be fired and that will send a change data message to a service on a

different sql server. That service will process the message and create an event in the notification services database. Notification service will later send an email or sms etc depeding on what is required.

Currently i got the message exchange working between two service in the same database. Now I am working on exchanging message between two SQL Servers with no luck. Can anyone please post an article on how to setup communications between two services on different servers.

I did try to expose service broker as an endpoint and create a route on the other server to call it? (This don't work)

Thanks,

Fahad

Here is what I have been doing and it works well once you figure it out:

http://blogs.msdn.com/remusrusanu/archive/2006/04/07/571066.aspx
|||Thanks, that worked for me.