Conditional Split

I am using a conditional split to evaluate the condition below. It should only send records to my SQL Server database if the PatientZip matches one of the eight below and the PatientCity is not Wichita Falls (you wouldn't believe how bad this is mispelled sometimes). I checked the output table and it has all records for the zipcodes below both matching and non-matching the cityname of Wichita Falls. The table should not have entries for records with the cityname of Wichita Falls. Do I have the code correct or could I have missed something?

LTRIM(PatientCity) != "Wichita Falls" && (PatientZip == "76301" || PatientZip == "76302" || PatientZip == "76305" || PatientZip == "76306" || PatientZip == "76307" || PatientZip == "76308" || PatientZip == "76309" || PatientZip == "76310")

One thing to look at is if you're sending the correct output from the Conditional Split to your destination.

Another thing is that you may want to RTRIM to catch trailing spaces instead of just LTRIMming to catch leading spaces.

|||

Thanks for the replay Matthew. I added the RTRIM as you suggested. My output name for my condition is "Bad City Name" and the default output name is "Correct City Name". I connected each output to different SQL Server tables that are exactly the same except for the table names. The Bad City Name output table is still being populated with data that is actually correct (city = "Wichita Falls" and is in the zipcodes listed above). The Correct City Name output table is being populated with any and all entries except (city = "Wichita Falls" and is in the zipcodes listed above).

As a check; I just ran the following query against the source database after replacing the logical operators with their SQL equivalents and the double quotes (") with single quotes (') and the query returned exactly what I am attempting to achieve with Integration Services.

select PatientName, PatientCity, PatientState, PatientZip

from ampfm.rpt_PatientDemographics

where LTRIM(RTRIM(PatientCity)) != 'Wichita Falls'

and (PatientZip = '76301' or PatientZip = '76302'

or PatientZip = '76305' or PatientZip = '76306'

or PatientZip = '76307' or PatientZip = '76308'

or PatientZip = '76309' or PatientZip = '76310')

I am at a loss as to why the Integration Services routine is not returning the correct row data. I must have something designed incorrectly. This is the first of several similar packages I am creating as the cornerstone to our audit process, but I need the correct data in the output (reporting) tables first. Please advise anything you feel may be in error that I can check.

Thanks!

|||

Have you tried putting a data viewer on the path going into and out of the conditional split? It might help to see what data you are getting in, and what data is on which path going out... (perhaps you have your tables flipped on your destinations, etc)

|||

I appreciate your post. Yes, I had earlier added data viewers and they showed the same data that querying the output tables were showing. I have everything set correctly as far as I can tell, it just isn't working as expected.

I finally deleted the conditional split and went with a Lookup object using the query below and it is pulling the correct information and putting it in the correct output tables. I guess I'll try to tackle conditional split issues at another time.

select PatientName, PatientCity, PatientState, PatientZip

from ampfm.rpt_PatientDemographics3

where LTRIM(RTRIM(PatientCity)) <> 'Wichita Falls'

and LTRIM(RTRIM(PatientZip)) IN ('76301','76302',

'76305','76306','76307','76308','76309','76310')

Thanks to all who have responded.

concatenating fields

Hi,
Have a simple question.
i have a value field and a criteria field would need to find all records for which the value matches the criteria
Eg:
table
name value criteria target
a 4 <=10
In the 'table' i need to update target based on whether
value meets the criteria

SELECT name FROM table
WHERE value + criteria

does not work

what am i doing wrong?
thanks
manjuI'm not clear on this but if you are saying that

SELECT name FROM table
WHERE value + criteria

does not work, that is because you have left of the condition, or the test. As in "value + criteria" is what?

Is "value + criteria" > 10
Is "value + criteria" = 568
Is "value + criteria" between 26 and 55

You need to add the test condition, the right hand side of the argument.|||im sorry if i wasnt clear.
actually the test condition is in the criteria field
so the table has 3 fields
name:a
value:4
criteria:<=10
thats why i need to concatenate
value and criteria and evaluate that expr
So if 4<=10 i would need to do something
thanks
manju|||You'll need to use dynamic SQL with the EXEC command.

DECLARE @.sqlcmd varchar(100)

SELECT @.sqlcmd = 'SELECT * FROM ' + name + ' WHERE ' + value + criteria

EXEC (@.sqlcmd)

Something like that.|||Thx for the reply but this will not do it
name, value and criteria are fields in sql
i need to evaluate the expr by combining 2 fields in that
table and perform an action based on that value
Eg:
if the table has
a|4|<=5
b|6|<=4
i need to select records for which
the value and criteria match, in this case
only 'a'
thanks
manju|||-- ------------------------
-- Run this
-- ------------------------
create table #tmp (tblname varchar(10), value varchar(10), critiria varchar(10), target varchar(10))
insert into #tmp values ('a','4','<=10',Null)
insert into #tmp values ('b','11','<=10',Null)
select * from #tmp
declare @.tblname varchar(10), @.sqlcmd varchar(100)
select @.tblname = min(tblname) from #tmp
while @.tblname is not null begin
select @.sqlcmd = 'update #tmp set target = ''~'' where value = ''' + value + ''' and critiria = ''' + critiria + ''' and ' + value + critiria From #tmp where tblname = @.tblname
raiserror(@.sqlcmd,0,1) with nowait
exec (@.sqlcmd)
select @.tblname = min(tblname) from #tmp where tblname > @.tblname
end
select * from #tmp

-- ------------------------
-- Should produce this
-- ------------------------
tblname value critiria target
---- ---- ---- ----
a 4 <=10 NULL
b 11 <=10 NULL

(2 row(s) affected)

update #tmp set target = '~' where value = '4' and critiria = '<=10' and 4<=10

(1 row(s) affected)

update #tmp set target = '~' where value = '11' and critiria = '<=10' and 11<=10

(0 row(s) affected)

tblname value critiria target
---- ---- ---- ----
a 4 <=10 ~
b 11 <=10 NULL

(2 row(s) affected)

That's about as good as I can do with the info provided... Hope it helps!