Logical equivalent IN operator in a Conditional Split Transformation

I am using a Conditional Split Transformation to find incorrect city listings in a specific list of zipcodes. I have the following condition:

LTRIM([PatientCity]) != 'Wichita Falls' && [PatientZip] IN '76301','76302','76305','76306','76307','76308','76309','76310')

I found the && which is the logical equivalent to AND. I now need a logical operator for IN.

Any ideas or approaches on how I may do this?

No such thing.

You could try:

LTRIM([PatientCity]) != 'Wichita Falls' && ([PatientZip] == '76301' || [PatientZip] == '76302' || [PatientZip] == '76305' || ...etc...)|||

Thanks Phil, that appears to work.

|||

Phil Brammer wrote:

No such thing.

Really? I guess not exactly but FINDSTRING() might work?

|||

Jamie Thomson wrote:

Phil Brammer wrote:

No such thing.

Really? What about FINDSTRING()?

Something like the following?

FINDSTRING("12345,12346,12347",[PatientZip],1)|||

Phil Brammer wrote:

Jamie Thomson wrote:

Phil Brammer wrote:

No such thing.

Really? What about FINDSTRING()?

Something like the following?

FINDSTRING("12345,12346,12347",[PatientZip],1)

Yeah, that's what I had in mind.

|||I guess it should work...... Obviously, I never thought about that...|||

Phil Brammer wrote:

No such thing.

You could try:

LTRIM([PatientCity]) != 'Wichita Falls' && ([PatientZip] == '76301' || [PatientZip] == '76302' || [PatientZip] == '76305' || ...etc...)

Phil - I ran into some issues using the above as some of the PatientZip and PatientCity fields were NULL which Boolean did not like very much. I have tried the following but don't quite have it right, can you suggest a better method to ensure NULL values do not fail the conditional split transformation task?

LTRIM(ISNULL([PatientCity])) != 'Wichita Falls' && (ISNULL([PatientZip]) == '76301' || [PatientZip] == '76302' || [PatientZip] == '76305')

|||I'd use two derived columns. The first to test PatientZip for NULL and if so, replace it with a space, or something.

The second to do the above example, either using my first method, or the FINDSTRING method that Jamie and I talked about.

logic error

Declare @.mx int
Set @.mx = (select max(score)
from ,,,
where ,,,,and
substring(city,1,1)='g')
select score,@.mx
from ,,,,
where ,,,, and
substring(city,1,1)='g')
group by score
go
this seems to work for small tables
but when table is big and substring(city,1,1)='g')
condition is not met it takes aweful time like goes
in an infinit loop.
--
Sent by 3 from yahoo part of com
This is a spam protected message. Please answer with reference header.
Posted via http://www.usenet-replayer.comthat's because substring(city,1,1)='g') forces a table scan even if there is
an index on city. the alternative is to use
city like 'g%'
--
-oj
RAC v2.2 & QALite!
http://www.rac4sql.net
"alexqa2003@.yahoo.com" <u128845214@.spawnkill.ip-mobilphone.net> wrote in
message news:l.1065831439.1981964111@.[63.127.215.130]...
> Declare @.mx int
> Set @.mx = (select max(score)
> from ,,,
> where ,,,,and
> substring(city,1,1)='g')
> select score,@.mx
> from ,,,,
> where ,,,, and
> substring(city,1,1)='g')
> group by score
> go
> this seems to work for small tables
> but when table is big and substring(city,1,1)='g')
> condition is not met it takes aweful time like goes
> in an infinit loop.
>
>
> --
> Sent by 3 from yahoo part of com
> This is a spam protected message. Please answer with reference header.
> Posted via http://www.usenet-replayer.com

logic error

Declare @.mx int
Set @.mx = (select max(score)
from ,,,
where ,,,,and
substring(city,1,1)='g')

select score,@.mx
from ,,,,
where ,,,, and
substring(city,1,1)='g')
group by score
go

this seems to work for small tables
but when table is big and substring(city,1,1)='g')
condition is not met it takes aweful time like goes
in an infinit loop.

--
Sent by 3 from yahoo part of com
This is a spam protected message. Please answer with reference header.
Posted via http://www.usenet-replayer.comthat's because substring(city,1,1)='g') forces a table scan even if there is
an index on city. the alternative is to use

city like 'g%'

--
-oj
RAC v2.2 & QALite!
http://www.rac4sql.net

"alexqa2003@.yahoo.com" <u128845214@.spawnkill.ip-mobilphone.net> wrote in
message news:l.1065831439.1981964111@.[63.127.215.130]...
> Declare @.mx int
> Set @.mx = (select max(score)
> from ,,,
> where ,,,,and
> substring(city,1,1)='g')
> select score,@.mx
> from ,,,,
> where ,,,, and
> substring(city,1,1)='g')
> group by score
> go
> this seems to work for small tables
> but when table is big and substring(city,1,1)='g')
> condition is not met it takes aweful time like goes
> in an infinit loop.
>
>
> --
> Sent by 3 from yahoo part of com
> This is a spam protected message. Please answer with reference header.
> Posted via http://www.usenet-replayer.com