IIF X AND Y , why does Y get evaluated?

I have some logic in a report that is not working as I thought it should and
it puzzles me.
Lets says I have this line, (ignoring syntax errors, I forget what is
supposed to be there for days)
IIF(Fields!ProductType.Value = 5,"Hello there", DateAdd(days, -8, SomeDate))
Now because in this case ProductType is 5, SomeDate isn't populated, its
null if this language has that concept. But the error I get is telling me
that adding -8 to SomeDate produces something thats not a date. Well of
course thats true because when ProductType is 5 and SomeDate is nothing so
why is it even looking at it.
Is there a way around this?
thanksOn Apr 12, 4:22 pm, "Coaster" <Coas...@.Coaster.net> wrote:
> I have some logic in a report that is not working as I thought it should and
> it puzzles me.
>
That's weird, I would not have expected IIF to evaluate both branches,
but maybe if you first check SomeDate to see if it is null, then you
can control when DateAdd is run, like so:
IIF(Fields!ProductType.Value = 5,"Hello there", IIF
IsNull(SomeDate)=False, DateAdd(days, -8, SomeDate), ''))
HTH
> Lets says I have this line, (ignoring syntax errors, I forget what is
> supposed to be there for days)
> IIF(Fields!ProductType.Value = 5,"Hello there", DateAdd(days, -8, SomeDate))
> Now because in this case ProductType is 5, SomeDate isn't populated, its
> null if this language has that concept. But the error I get is telling me
> that adding -8 to SomeDate produces something thats not a date. Well of
> course thats true because when ProductType is 5 and SomeDate is nothing so
> why is it even looking at it.
> Is there a way around this?
> thanks|||On Apr 14, 4:59 am, "Jerry H." <boilersr...@.gmail.com> wrote:
> On Apr 12, 4:22 pm, "Coaster" <Coas...@.Coaster.net> wrote:> I have some logic in a report that is not working as I thought it should and
> > it puzzles me.
> That's weird, I would not have expected IIF to evaluate both branches,
> but maybe if you first check SomeDate to see if it is null, then you
> can control when DateAdd is run, like so:
> IIF(Fields!ProductType.Value = 5,"Hello there", IIF
> IsNull(SomeDate)=False, DateAdd(days, -8, SomeDate), ''))
>
I dont know if it resolves your issue or not . as per my experience,in
iif statement, then clause and else clause should have the same
datatype values,in the following case.one is date type and another is
string type .please correct this also
Thanks
Raj deep.A
>
> > Lets says I have this line, (ignoring syntax errors, I forget what is
> > supposed to be there for days)
> > IIF(Fields!ProductType.Value = 5,"Hello there", DateAdd(days, -8, SomeDate))
> > Now because in this case ProductType is 5, SomeDate isn't populated, its
> > null if this language has that concept. But the error I get is telling me
> > that adding -8 to SomeDate produces something thats not a date. Well of
> > course thats true because when ProductType is 5 and SomeDate is nothing so
> > why is it even looking at it.
> > Is there a way around this?
> > thanks|||I've seen today a coworker using IIF to avoid a division by zero error and
it seemed to works fine..
What is the error you get ? I suspect a problem with DateAdd (are you sure
"day" shouldn't be within quotes ?)
--
Patrice
"Coaster" <Coaster@.Coaster.net> a écrit dans le message de news:
ObHp0uNnIHA.1052@.TK2MSFTNGP05.phx.gbl...
>I have some logic in a report that is not working as I thought it should
>and it puzzles me.
> Lets says I have this line, (ignoring syntax errors, I forget what is
> supposed to be there for days)
> IIF(Fields!ProductType.Value = 5,"Hello there", DateAdd(days, -8,
> SomeDate))
> Now because in this case ProductType is 5, SomeDate isn't populated, its
> null if this language has that concept. But the error I get is telling me
> that adding -8 to SomeDate produces something thats not a date. Well of
> course thats true because when ProductType is 5 and SomeDate is nothing so
> why is it even looking at it.
> Is there a way around this?
> thanks
>|||On Apr 12, 4:22=A0pm, "Coaster" <Coas...@.Coaster.net> wrote:
> I have some logic in a report that is not working as I thought it should a=nd
> it puzzles me.
> Lets says I have this line, (ignoring syntax errors, I forget what is
> supposed to be there for days)
> IIF(Fields!ProductType.Value =3D 5,"Hello there", DateAdd(days, -8, SomeDa=te))
> Now because in this case ProductType is 5, SomeDate isn't populated, its
> null if this language has that concept. But the error I get is telling me
> that adding -8 to SomeDate produces something thats not a date. Well of
> course thats true because when ProductType is 5 and SomeDate is nothing so=
> why is it even looking at it.
> Is there a way around this?
> thanks
This is a quote from Chris Hayes from Microsoft:
"The problem is this: The IIF function evaluates all of its
arguments."
JerryH's solution (I adjusted his SQL to SSRS/VB syntax),
IIF(Fields!ProductType.Value =3D 5,"Hello there", IIF(SomeDate =3D
Nothing, Nothing, DateAdd("d", -8, SomeDate)))
may work because the nested IIf is evaluated first.
In the Code tab/window of Report Properties, enter the following:
Public Function DateMinus8(ByVal Exp1)
If Exp1 =3D 5 Then
DateMinus8 =3D "Hello There"
Else DateMinus8 =3D DateAdd("d", -8, SomeDate)
End If
End Function
Then use =3Dcode.DateMinus8(Fields!ProductType.Value )
instead of =3D IIF(Fields!ProductType.Value =3D 5,"Hello there",
DateAdd(days, -8, SomeDate))
To Patrice: I think your co-worker just got lucky and had no zeros
show up in the denominator because IIF will not resolve divide by zero
issues without some tweaking.
To truly avoid divide by zero use either:
Public Function DivideBy(ByVal Exp1, ByVal Exp2)
If Exp2 =3D 0 Then
DivideBy =3D 0
Else DivideBy =3D Exp1 / Exp2
End If
End Function
Then use =3Dcode.DivideBy(Numerator,Denominator)
instead of =3DIIF(Denominator =3D 0, 0, Numerator/Denominator)
OR if you don't want to use custom code try
=3DIIf(Denominator =3D 0, "N/A", Numerator / IIf(Denominator =3D 0, 1,
Denominator))|||On Apr 14, 12:46=A0pm, "Patrice" <http://www.chez.com/scribe/> wrote:
> I've seen today a coworker using IIF to avoid a division by zero error and=
> it seemed to works fine..
> What is the error you get ? I suspect a problem with DateAdd (are you sure=
> "day" shouldn't be within quotes ?)
> --
> Patrice
> "Coaster" <Coas...@.Coaster.net> a =E9crit dans le message de news:
> ObHp0uNnIHA.1...@.TK2MSFTNGP05.phx.gbl...
>
> >I have some logic in a report that is not working as I thought it should
> >and it puzzles me.
> > Lets says I have this line, (ignoring syntax errors, I forget what is
> > supposed to be there for days)
> > IIF(Fields!ProductType.Value =3D 5,"Hello there", DateAdd(days, -8,
> > SomeDate))
> > Now because in this case ProductType is 5, SomeDate isn't populated, its=
> > null if this language has that concept. But the error I get is telling m=e
> > that adding -8 to SomeDate produces something thats not a date. Well of
> > course thats true because when ProductType is 5 and SomeDate is nothing =so
> > why is it even looking at it.
> > Is there a way around this?
> > thanks- Hide quoted text -
> - Show quoted text -
This is a quote from Chris Hayes from Microsoft:
"The problem is this: The IIF function evaluates all of its
arguments."
JerryH's solution (I adjusted his SQL to SSRS/VB syntax),
IIF(Fields!ProductType.Value =3D 5,"Hello there", IIF(SomeDate =3D
Nothing, Nothing, DateAdd("d", -8, SomeDate)))
may work because the nested IIf is evaluated first.
I usually use custom code to get around the IIF issue. You could try
something like the following.
In the Code tab/window of Report Properties, enter the following:
Public Function DateMinus8(ByVal Exp1)
If Exp1 =3D 5 Then
DateMinus8 =3D "Hello There"
Else DateMinus8 =3D DateAdd("d", -8, SomeDate)
End If
End Function
Then use =3Dcode.DateMinus8(Fields!ProductType.Value )
instead of =3D IIF(Fields!ProductType.Value =3D 5,"Hello there",
DateAdd(days, -8, SomeDate))
To Patrice: I think your co-worker just got lucky and had no zeros
show up in the denominator because IIF will not resolve divide by
zero
issues without some tweaking.
To truly avoid divide by zero use either:
Public Function DivideBy(ByVal Exp1, ByVal Exp2)
If Exp2 =3D 0 Then
DivideBy =3D 0
Else DivideBy =3D Exp1 / Exp2
End If
End Function
Then use =3Dcode.DivideBy(Numerator,Denominator)
instead of =3DIIF(Denominator =3D 0, 0, Numerator/Denominator)
OR if you don't want to use custom code try
=3DIIf(Denominator =3D 0, "N/A", Numerator / IIf(Denominator =3D 0, 1,
Denominator))|||"toolman" <timd@.infocision.com> wrote in message
news:f3db3d38-59d5-402d-9b4b-e2b59c64f563@.u69g2000hse.googlegroups.com...
On Apr 12, 4:22 pm, "Coaster" <Coas...@.Coaster.net> wrote:
> I have some logic in a report that is not working as I thought it should
> and
> it puzzles me.
> Lets says I have this line, (ignoring syntax errors, I forget what is
> supposed to be there for days)
> IIF(Fields!ProductType.Value = 5,"Hello there", DateAdd(days, -8,
> SomeDate))
> Now because in this case ProductType is 5, SomeDate isn't populated, its
> null if this language has that concept. But the error I get is telling me
> that adding -8 to SomeDate produces something thats not a date. Well of
> course thats true because when ProductType is 5 and SomeDate is nothing so
> why is it even looking at it.
> Is there a way around this?
> thanks
This is a quote from Chris Hayes from Microsoft:
"The problem is this: The IIF function evaluates all of its
arguments."
JerryH's solution (I adjusted his SQL to SSRS/VB syntax),
IIF(Fields!ProductType.Value = 5,"Hello there", IIF(SomeDate =Nothing, Nothing, DateAdd("d", -8, SomeDate)))
may work because the nested IIf is evaluated first.
In the Code tab/window of Report Properties, enter the following:
Public Function DateMinus8(ByVal Exp1)
If Exp1 = 5 Then
DateMinus8 = "Hello There"
Else DateMinus8 = DateAdd("d", -8, SomeDate)
End If
End Function
Then use =code.DateMinus8(Fields!ProductType.Value )
instead of = IIF(Fields!ProductType.Value = 5,"Hello there",
DateAdd(days, -8, SomeDate))
To Patrice: I think your co-worker just got lucky and had no zeros
show up in the denominator because IIF will not resolve divide by zero
issues without some tweaking.
To truly avoid divide by zero use either:
Public Function DivideBy(ByVal Exp1, ByVal Exp2)
If Exp2 = 0 Then
DivideBy = 0
Else DivideBy = Exp1 / Exp2
End If
End Function
Then use =code.DivideBy(Numerator,Denominator)
instead of =IIF(Denominator = 0, 0, Numerator/Denominator)
OR if you don't want to use custom code try
=IIf(Denominator = 0, "N/A", Numerator / IIf(Denominator = 0, 1,
Denominator))
Thanks alot !!! I 'll check it out tomorrow at work. I didn't even know you
could have functions like that in the report. JerryH's solution didn't work
for me because it still evaluated the date even though it was nested,
hopefully this won't happen using a function.|||"Jerry H." <boilersrock@.gmail.com> wrote in message
news:8bd61d4f-5c11-434e-931b-0615c09fd011@.59g2000hsb.googlegroups.com...
> On Apr 12, 4:22 pm, "Coaster" <Coas...@.Coaster.net> wrote:
>> I have some logic in a report that is not working as I thought it should
>> and
>> it puzzles me.
> That's weird, I would not have expected IIF to evaluate both branches,
> but maybe if you first check SomeDate to see if it is null, then you
> can control when DateAdd is run, like so:
> IIF(Fields!ProductType.Value = 5,"Hello there", IIF
> IsNull(SomeDate)=False, DateAdd(days, -8, SomeDate), ''))
> HTH
>
>
>> Lets says I have this line, (ignoring syntax errors, I forget what is
>> supposed to be there for days)
>> IIF(Fields!ProductType.Value = 5,"Hello there", DateAdd(days, -8,
>> SomeDate))
>> Now because in this case ProductType is 5, SomeDate isn't populated, its
>> null if this language has that concept. But the error I get is telling me
>> that adding -8 to SomeDate produces something thats not a date. Well of
>> course thats true because when ProductType is 5 and SomeDate is nothing
>> so
>> why is it even looking at it.
>> Is there a way around this?
>> thanks
>
yeah it is weird and it even evaluated it in your solution. Perhaps the
toolmans solution will work. Find out tomorrow.|||Humm... I gave this a try on another report i'm working on :
=IIf(True,1,0/0)
and it worked fine. If I change True to False I then have a "non numerical
value" string shown in the field...
I'm using RS 2005...

Ignoring time stamp in my date parameter

Hi,

I'm pretty new at this, writing SQL and reporting services. I created a report with a date parameter. I need the report to ignore the timestamp. My @.Startdate is fine because the timestamps is at 12:00:00AM but my @.EndDate also has this timestamp. I need to pull all the data up to the end date the user enters without taking the timestamp into consideration.

If someone can help me out with, I would greatly appreciate it.

Thanks,

Hello,

If I understand correctly, you want to include the date that your user selects in your results. The problem is that when a parameter is used, it assumes midnight, so any values that are on that day but have a time other than midnight will not be included. In order to fix this, just add one day to your EndDate parameter.

In your SQL query, add this:

... where DateField >= @.StartDate and DateField < dateadd(d, 1, @.EndDate)

Hope this helps.

Jarret

|||

You got it! I've been adding one day when I enter the dates when I test the report. I don't know why I did not think of just adding one day in the code. I guess I was thinking too hard trying to completely ignore the timestamp.

Anyways, I think this will work with the users. THey will definitely be happy that they do not have to type in the timestamp when they run their reports.

Thank you so much!!

|||

Glad I could help!

Jarret

Ignoring spaces!

Hi All,
Wondering if i can tap into your knowlege...
I have 2 lists of ID Codes (users and potential users of a service)
which i need to match together
1 list is of existing users, 1 list of potential users.
I want to find, from the list of potential users, ID codes which are
not in the list of users.
This is simple enough and i'm using a lef join to establish matching
ID codes in the 2 lists, those not matched have not used the service.
My problem is, that ID codes from both lists sometimes have a single
space at random points within the code and these are not constent
between the 2 lists.
What i ideally would like is a piece of code which says to match list
1 with list 2 but ignore anything which is not a-zA-Z0-9, which would
then ignore the ' ' [space].
Any advice?
PS i know i could use the replace ' ', with '' code in both lists to
uniform them, but i don't really want to have to go down that line
everytime i want to do the match.
Thanks!
> PS i know i could use the replace ' ', with '' code in both lists to
> uniform them, but i don't really want to have to go down that line
> everytime i want to do the match.
Then fix the problem instead of searching for some magical better
alternative to using replace.
By "fix the problem" I mean:
(a) correct the existing data that shouldn't have spaces; and, more
importantly,
(b) correct the code/app(s) that is putting the spaces into the data in the
first place.
Aaron Bertrand
SQL Server MVP
http://www.sqlblog.com/
http://www.aspfaq.com/5006
|||On Fri, 15 Jun 2007 06:00:13 -0700, chriselias271@.gmail.com wrote:

>What i ideally would like is a piece of code which says to match list
>1 with list 2 but ignore anything which is not a-zA-Z0-9, which would
>then ignore the ' ' [space].
ON REPLACE(A.ID, ' ', '') = REPLACE(B.ID, ' ', '')
However, performance will be poor as that can not use indexes. If the
tables are not too large and the match is not run too often
performance might be acceptable, or not.

>PS i know i could use the replace ' ', with '' code in both lists to
>uniform them, but i don't really want to have to go down that line
>everytime i want to do the match.
If you don't fix the data - which would seem to be the ideal solution
- then whatver "piece of code" you use will have to be used every time
the match is performed. I don't know what sort of code could be
simpler than using REPLACE as in the example above.
If it is not practical to remove the spaces, and the match must be run
regularly, then I would consider adding another column to each table
to hold the column without the blank, or adding such a column to a
pair of views on the two tables and indexing them to create indexed
views and match on the views.
Roy Harvey
Beacon Falls, CT
|||I agree with Aaron on it's better to fix the data source, but assuming
you cannot...
A user-defined scalar-value function can do the string cleaning...if
you are on 2005 and can use CLR, just a simple wrapper of
Regex.Replace will do the trick in one line...if you are 2000 or no
CLR, then you would have to do t-sql string manipulation to clean it
up...did this last week for an ETL project...not a very good idea
performance wise, as it will scan all your base tables if you are
using it in the join:
-- Returns only the digits contained in @.input
CREATE FUNCTION dbo.VarcharDigits
(
@.input varchar(255)
)
RETURNS varchar(255)
AS
BEGIN
DECLARE @.i int
DECLARE @.cur char
DECLARE @.output varchar(255)
SET @.output = ''
SET @.i = 1
WHILE (@.i <= LEN(@.input))
BEGIN
SET @.cur = SUBSTRING(@.input,@.i,1)
IF (ASCII(@.cur) BETWEEN 48 AND 57) -- Digits only
SET @.output = @.output + @.cur
SET @.i = @.i + 1
END
RETURN @.output
END
On Jun 15, 9:00 am, chriselias...@.gmail.com wrote:
> Hi All,
> Wondering if i can tap into your knowlege...
> I have 2 lists of ID Codes (users and potential users of a service)
> which i need to match together
> 1 list is of existing users, 1 list of potential users.
> I want to find, from the list of potential users, ID codes which are
> not in the list of users.
> This is simple enough and i'm using a lef join to establish matching
> ID codes in the 2 lists, those not matched have not used the service.
> My problem is, that ID codes from both lists sometimes have a single
> space at random points within the code and these are not constent
> between the 2 lists.
> What i ideally would like is a piece of code which says to match list
> 1 with list 2 but ignore anything which is not a-zA-Z0-9, which would
> then ignore the ' ' [space].
> Any advice?
> PS i know i could use the replace ' ', with '' code in both lists to
> uniform them, but i don't really want to have to go down that line
> everytime i want to do the match.
> Thanks!
|||On 15 Jun, 15:18, Roy Harvey <roy_har...@.snet.net> wrote:
> On Fri, 15 Jun 2007 06:00:13 -0700, chriselias...@.gmail.com wrote:
> ON REPLACE(A.ID, ' ', '') = REPLACE(B.ID, ' ', '')
> However, performance will be poor as that can not use indexes. If the
> tables are not too large and the match is not run too often
> performance might be acceptable, or not.
>
> If you don't fix the data - which would seem to be the ideal solution
> - then whatver "piece of code" you use will have to be used every time
> the match is performed. I don't know what sort of code could be
> simpler than using REPLACE as in the example above.
> If it is not practical to remove thespaces, and the match must be run
> regularly, then I would consider adding another column to each table
> to hold the column without the blank, or adding such a column to a
> pair of views on the two tables and indexing them to create indexed
> views and match on the views.
> Roy Harvey
> Beacon Falls, CT
Absolutely spot on exactly what i wanted.
Thanks for understanding the problem so well!!

Ignoring spaces!

Hi All,
Wondering if i can tap into your knowlege...
I have 2 lists of ID Codes (users and potential users of a service)
which i need to match together
1 list is of existing users, 1 list of potential users.
I want to find, from the list of potential users, ID codes which are
not in the list of users.
This is simple enough and i'm using a lef join to establish matching
ID codes in the 2 lists, those not matched have not used the service.
My problem is, that ID codes from both lists sometimes have a single
space at random points within the code and these are not constent
between the 2 lists.
What i ideally would like is a piece of code which says to match list
1 with list 2 but ignore anything which is not a-zA-Z0-9, which would
then ignore the ' ' [space].
Any advice'
PS i know i could use the replace ' ', with '' code in both lists to
uniform them, but i don't really want to have to go down that line
everytime i want to do the match.
Thanks!> PS i know i could use the replace ' ', with '' code in both lists to
> uniform them, but i don't really want to have to go down that line
> everytime i want to do the match.
Then fix the problem instead of searching for some magical better
alternative to using replace.
By "fix the problem" I mean:
(a) correct the existing data that shouldn't have spaces; and, more
importantly,
(b) correct the code/app(s) that is putting the spaces into the data in the
first place.
--
Aaron Bertrand
SQL Server MVP
http://www.sqlblog.com/
http://www.aspfaq.com/5006|||On Fri, 15 Jun 2007 06:00:13 -0700, chriselias271@.gmail.com wrote:
>What i ideally would like is a piece of code which says to match list
>1 with list 2 but ignore anything which is not a-zA-Z0-9, which would
>then ignore the ' ' [space].
ON REPLACE(A.ID, ' ', '') = REPLACE(B.ID, ' ', '')
However, performance will be poor as that can not use indexes. If the
tables are not too large and the match is not run too often
performance might be acceptable, or not.
>PS i know i could use the replace ' ', with '' code in both lists to
>uniform them, but i don't really want to have to go down that line
>everytime i want to do the match.
If you don't fix the data - which would seem to be the ideal solution
- then whatver "piece of code" you use will have to be used every time
the match is performed. I don't know what sort of code could be
simpler than using REPLACE as in the example above.
If it is not practical to remove the spaces, and the match must be run
regularly, then I would consider adding another column to each table
to hold the column without the blank, or adding such a column to a
pair of views on the two tables and indexing them to create indexed
views and match on the views.
Roy Harvey
Beacon Falls, CT|||I agree with Aaron on it's better to fix the data source, but assuming
you cannot...
A user-defined scalar-value function can do the string cleaning...if
you are on 2005 and can use CLR, just a simple wrapper of
Regex.Replace will do the trick in one line...if you are 2000 or no
CLR, then you would have to do t-sql string manipulation to clean it
up...did this last week for an ETL project...not a very good idea
performance wise, as it will scan all your base tables if you are
using it in the join:
-- Returns only the digits contained in @.input
CREATE FUNCTION dbo.VarcharDigits
(
@.input varchar(255)
)
RETURNS varchar(255)
AS
BEGIN
DECLARE @.i int
DECLARE @.cur char
DECLARE @.output varchar(255)
SET @.output = ''
SET @.i = 1
WHILE (@.i <= LEN(@.input))
BEGIN
SET @.cur = SUBSTRING(@.input,@.i,1)
IF (ASCII(@.cur) BETWEEN 48 AND 57) -- Digits only
SET @.output = @.output + @.cur
SET @.i = @.i + 1
END
RETURN @.output
END
On Jun 15, 9:00 am, chriselias...@.gmail.com wrote:
> Hi All,
> Wondering if i can tap into your knowlege...
> I have 2 lists of ID Codes (users and potential users of a service)
> which i need to match together
> 1 list is of existing users, 1 list of potential users.
> I want to find, from the list of potential users, ID codes which are
> not in the list of users.
> This is simple enough and i'm using a lef join to establish matching
> ID codes in the 2 lists, those not matched have not used the service.
> My problem is, that ID codes from both lists sometimes have a single
> space at random points within the code and these are not constent
> between the 2 lists.
> What i ideally would like is a piece of code which says to match list
> 1 with list 2 but ignore anything which is not a-zA-Z0-9, which would
> then ignore the ' ' [space].
> Any advice'
> PS i know i could use the replace ' ', with '' code in both lists to
> uniform them, but i don't really want to have to go down that line
> everytime i want to do the match.
> Thanks!|||On 15 Jun, 15:18, Roy Harvey <roy_har...@.snet.net> wrote:
> On Fri, 15 Jun 2007 06:00:13 -0700, chriselias...@.gmail.com wrote:
> >What i ideally would like is a piece of code which says to match list
> >1 with list 2 butignoreanything which is not a-zA-Z0-9, which would
> >thenignorethe ' ' [space].
> ON REPLACE(A.ID, ' ', '') = REPLACE(B.ID, ' ', '')
> However, performance will be poor as that can not use indexes. If the
> tables are not too large and the match is not run too often
> performance might be acceptable, or not.
> >PS i know i could use the replace ' ', with '' code in both lists to
> >uniform them, but i don't really want to have to go down that line
> >everytime i want to do the match.
> If you don't fix the data - which would seem to be the ideal solution
> - then whatver "piece of code" you use will have to be used every time
> the match is performed. I don't know what sort of code could be
> simpler than using REPLACE as in the example above.
> If it is not practical to remove thespaces, and the match must be run
> regularly, then I would consider adding another column to each table
> to hold the column without the blank, or adding such a column to a
> pair of views on the two tables and indexing them to create indexed
> views and match on the views.
> Roy Harvey
> Beacon Falls, CT
Absolutely spot on exactly what i wanted.
Thanks for understanding the problem so well!!

Ignoring spaces!

Hi All,
Wondering if i can tap into your knowlege...
I have 2 lists of ID Codes (users and potential users of a service)
which i need to match together
1 list is of existing users, 1 list of potential users.
I want to find, from the list of potential users, ID codes which are
not in the list of users.
This is simple enough and i'm using a lef join to establish matching
ID codes in the 2 lists, those not matched have not used the service.
My problem is, that ID codes from both lists sometimes have a single
space at random points within the code and these are not constent
between the 2 lists.
What i ideally would like is a piece of code which says to match list
1 with list 2 but ignore anything which is not a-zA-Z0-9, which would
then ignore the ' ' [space].
Any advice'
PS i know i could use the replace ' ', with '' code in both lists to
uniform them, but i don't really want to have to go down that line
everytime i want to do the match.
Thanks!> PS i know i could use the replace ' ', with '' code in both lists to
> uniform them, but i don't really want to have to go down that line
> everytime i want to do the match.
Then fix the problem instead of searching for some magical better
alternative to using replace.
By "fix the problem" I mean:
(a) correct the existing data that shouldn't have spaces; and, more
importantly,
(b) correct the code/app(s) that is putting the spaces into the data in the
first place.
Aaron Bertrand
SQL Server MVP
http://www.sqlblog.com/
http://www.aspfaq.com/5006|||On Fri, 15 Jun 2007 06:00:13 -0700, chriselias271@.gmail.com wrote:

>What i ideally would like is a piece of code which says to match list
>1 with list 2 but ignore anything which is not a-zA-Z0-9, which would
>then ignore the ' ' [space].
ON REPLACE(A.ID, ' ', '') = REPLACE(B.ID, ' ', '')
However, performance will be poor as that can not use indexes. If the
tables are not too large and the match is not run too often
performance might be acceptable, or not.

>PS i know i could use the replace ' ', with '' code in both lists to
>uniform them, but i don't really want to have to go down that line
>everytime i want to do the match.
If you don't fix the data - which would seem to be the ideal solution
- then whatver "piece of code" you use will have to be used every time
the match is performed. I don't know what sort of code could be
simpler than using REPLACE as in the example above.
If it is not practical to remove the spaces, and the match must be run
regularly, then I would consider adding another column to each table
to hold the column without the blank, or adding such a column to a
pair of views on the two tables and indexing them to create indexed
views and match on the views.
Roy Harvey
Beacon Falls, CT|||I agree with Aaron on it's better to fix the data source, but assuming
you cannot...
A user-defined scalar-value function can do the string cleaning...if
you are on 2005 and can use CLR, just a simple wrapper of
Regex.Replace will do the trick in one line...if you are 2000 or no
CLR, then you would have to do t-sql string manipulation to clean it
up...did this last week for an ETL project...not a very good idea
performance wise, as it will scan all your base tables if you are
using it in the join:
-- Returns only the digits contained in @.input
CREATE FUNCTION dbo.VarcharDigits
(
@.input varchar(255)
)
RETURNS varchar(255)
AS
BEGIN
DECLARE @.i int
DECLARE @.cur char
DECLARE @.output varchar(255)
SET @.output = ''
SET @.i = 1
WHILE (@.i <= LEN(@.input))
BEGIN
SET @.cur = SUBSTRING(@.input,@.i,1)
IF (ASCII(@.cur) BETWEEN 48 AND 57) -- Digits only
SET @.output = @.output + @.cur
SET @.i = @.i + 1
END
RETURN @.output
END
On Jun 15, 9:00 am, chriselias...@.gmail.com wrote:
> Hi All,
> Wondering if i can tap into your knowlege...
> I have 2 lists of ID Codes (users and potential users of a service)
> which i need to match together
> 1 list is of existing users, 1 list of potential users.
> I want to find, from the list of potential users, ID codes which are
> not in the list of users.
> This is simple enough and i'm using a lef join to establish matching
> ID codes in the 2 lists, those not matched have not used the service.
> My problem is, that ID codes from both lists sometimes have a single
> space at random points within the code and these are not constent
> between the 2 lists.
> What i ideally would like is a piece of code which says to match list
> 1 with list 2 but ignore anything which is not a-zA-Z0-9, which would
> then ignore the ' ' [space].
> Any advice'
> PS i know i could use the replace ' ', with '' code in both lists to
> uniform them, but i don't really want to have to go down that line
> everytime i want to do the match.
> Thanks!|||On 15 Jun, 15:18, Roy Harvey <roy_har...@.snet.net> wrote:
> On Fri, 15 Jun 2007 06:00:13 -0700, chriselias...@.gmail.com wrote:
> ON REPLACE(A.ID, ' ', '') = REPLACE(B.ID, ' ', '')
> However, performance will be poor as that can not use indexes. If the
> tables are not too large and the match is not run too often
> performance might be acceptable, or not.
>
> If you don't fix the data - which would seem to be the ideal solution
> - then whatver "piece of code" you use will have to be used every time
> the match is performed. I don't know what sort of code could be
> simpler than using REPLACE as in the example above.
> If it is not practical to remove thespaces, and the match must be run
> regularly, then I would consider adding another column to each table
> to hold the column without the blank, or adding such a column to a
> pair of views on the two tables and indexing them to create indexed
> views and match on the views.
> Roy Harvey
> Beacon Falls, CT
Absolutely spot on exactly what i wanted.
Thanks for understanding the problem so well!!

Ignoring Source Field

Hi,

I am implementing a Transactional Replication btwn two SQL 2000 servers, which is using Data Transform services (DTS package) to manipulate the data. I need to ignore a source column from replication but that column field value needs to be appended with other field column.

For ex. Let two source fields be named FirstName and LastName. In the target database i need to merge those field values into a Single field called CustomerName. Also i don't want the two source fields to be replicated in the target.

I tried to ignore the field by unchecking the Column Transformation and Mapping screen, but then unable to append the field value to the other field.

Is there any other way to do the above?

Thanks in advance.

urs,
T. Jayakumar

I don't think there is a way to use a field in a transformation but not include it in the package data.

Phil Garding

Ignoring NULL values in LOOKUP transformation.

Hi,

Can you please tell me the way to configure the LOOK UP transformation so that it will ignore all the null values ? I want to configure a Look up component for the column "Col1" as follows

All the NULL values of Col1 should not be considered for look-up process. They should be passed to the downstream component as valid rows.

All NOT NULL values of Col1 should be processed by the Look up component.

If there is no matching value present for any NOT NULL value of Col1 then it should be directed to error output.

Regards,

Gopi

Use a conditional split to direct the not nulls to the lookup(!ISNULL(Col1)). use a union to combine the results from the lookup with the records where Col1=NULL from the other conditional split path. set your error on the lookup to redirect rows to handle the records that have no match.

Frank

|||

Frank,

Thank you for your reply.

But, Is there any way to avoid conditional split ? I would like to configure Look up component itself.

Cheers,

Gopi

|||

If you pass the NULL Col1 records to the lookup, you would need an entry in the lookup table to handle the NULL. otherwise, it will fail the lookup and end up in the error table along with the not NULL Col1 records that failed the lookup.

Frank

Ignoring Noise words in SQL 2005 Full Text Search

To ignore the noise words in the query microsoft remommends to execute the following statements, by which we can take advantage of the new transformation of noise words in CONTAINS queries:

EXEC sp_configure 'show advanced options', 1
GO
RECONFIGURE
GO
EXEC sp_configure 'transform noise words', 1
GO
RECONFIGURE
GO

But this never works. Can any one please suggest how to make this work.(without modifying the full text text file)

Did you restart the service, this is a non-runtime value, so you will have to restart the service to apply the change.

Jens K. Suessmeyer

http://www.sqlserver2005.de

|||

I had restarted the Full Text Index Service, but still it did not work

Also tried re-starting SQL Service, still it did not work.

Ignoring Noise words in SQL 2005 Full Text Search

To ignore the noise words in the query microsoft remommends to execute the following statements, by which we can take advantage of the new transformation of noise words in CONTAINS queries:

EXEC sp_configure 'show advanced options', 1
GO
RECONFIGURE
GO
EXEC sp_configure 'transform noise words', 1
GO
RECONFIGURE
GO

But this never works. Can any one please suggest how to make this work.(without modifying the full text text file)

Did you restart the service, this is a non-runtime value, so you will have to restart the service to apply the change.

Jens K. Suessmeyer

http://www.sqlserver2005.de

|||

I had restarted the Full Text Index Service, but still it did not work

Also tried re-starting SQL Service, still it did not work.

Ignoring Noise words in SQL 2005 Full Text Search

To ignore the noise words in the query microsoft remommends to execute the following statements, by which we can take advantage of the new transformation of noise words in CONTAINS queries:

EXEC sp_configure 'show advanced options', 1
GO
RECONFIGURE
GO
EXEC sp_configure 'transform noise words', 1
GO
RECONFIGURE
GO

But this never works. Can any one please suggest how to make this work.(without modifying the full text text file)

Did you restart the service, this is a non-runtime value, so you will have to restart the service to apply the change.

Jens K. Suessmeyer

http://www.sqlserver2005.de

|||

I had restarted the Full Text Index Service, but still it did not work

Also tried re-starting SQL Service, still it did not work.

ignoring keys while Truncating tables

Hello everyone.

I'm working with a customers business application which is developed in MSSQL. The system has over 170 tables and there is no documentation by those who created it. Now the problem is that I have to write script that deletes all the data in all the tables but since there are foreign keys defined in the tables I can't delete the data. Ofcourse I can figure it out eventually by testing back and forth in which order I have to delete the data in the tables but since there are over 170 tables that could take a very long time.

Does anyone now how I can solve this?? is there for examaple a way to make SQL server to ignore the foreign key lookup? What can I do?? is there any way which I can see in what order I should delete the data in the tables?

appritiate any help or comments.

Thanks.

\Homan1. script out your deletes and run the script 170 times, eventualy you will clear out all the tables.

2. use Enterprise Manager to generate a diagram of all the tables in the database. This would tell you the exact order you would need to follow to get everything deleted.

3. in Enterprise Manager and Query Analyzer you can look up the dpendencies of any object. In EM right click on any object, select all tasks, dependencies, in QA press "F8" to show the object browser, select and object drill down till you see dependencies.

4. use sp_depends on all 170 tables/view to figure out the parents and or child relationships.|||If you are going to have to reload the database you are going to want to know your foreign keys.|||For reference of cascade delete refer to SQL TEam (http://www.sqlteam.com/item.asp?ItemID=8595) link.

Refer to this Code (http://www.planet-source-code.com/vb/scripts/ShowCode.asp?txtCodeId=641&lngWId=5) and modify to accomplish the task.

HTH|||disable your fk checking before deleting data, and re-enable them after.

Ignoring expressions

I have an write an expression which let me select a value if the condition is
met, but if not then the expression should be ignored.
i.e. =Iif(Parameter!Industry.value <> '_ALL_', Parameter!Industry.value, 0)
The 0 here is supposed to ignore the expression, but it does not. What can I
use to ignore the expression.
Any help will be appriciated.Where is this expression, in a filter?
"Sumi" wrote:
> I have an write an expression which let me select a value if the condition is
> met, but if not then the expression should be ignored.
> i.e. =Iif(Parameter!Industry.value <> '_ALL_', Parameter!Industry.value, 0)
> The 0 here is supposed to ignore the expression, but it does not. What can I
> use to ignore the expression.
> Any help will be appriciated.|||Yes. This expression is in a filter.
"Antoon" wrote:
> Where is this expression, in a filter?
> "Sumi" wrote:
> > I have an write an expression which let me select a value if the condition is
> > met, but if not then the expression should be ignored.
> > i.e. =Iif(Parameter!Industry.value <> '_ALL_', Parameter!Industry.value, 0)
> >
> > The 0 here is supposed to ignore the expression, but it does not. What can I
> > use to ignore the expression.
> >
> > Any help will be appriciated.|||Did you try 1 instead of 0
--
"Everyone knows something you don't know"
"Sumi" wrote:
> Yes. This expression is in a filter.
> "Antoon" wrote:
> > Where is this expression, in a filter?
> >
> > "Sumi" wrote:
> >
> > > I have an write an expression which let me select a value if the condition is
> > > met, but if not then the expression should be ignored.
> > > i.e. =Iif(Parameter!Industry.value <> '_ALL_', Parameter!Industry.value, 0)
> > >
> > > The 0 here is supposed to ignore the expression, but it does not. What can I
> > > use to ignore the expression.
> > >
> > > Any help will be appriciated.|||Filter expression
switch(Parameter!Industry.value <> '_ALL_' and Parameter!Industry.value =Industry.value, 1, true, 0)
Filter value
=1
in this expression "Industry.value" is your query value that carresponds to
you parameter
"Sumi" wrote:
> I have an write an expression which let me select a value if the condition is
> met, but if not then the expression should be ignored.
> i.e. =Iif(Parameter!Industry.value <> '_ALL_', Parameter!Industry.value, 0)
> The 0 here is supposed to ignore the expression, but it does not. What can I
> use to ignore the expression.
> Any help will be appriciated.

Ignoring Errors

I have some "best effort" jobs that try at various times to walk over
missed records from one system to another about 5 times. After that,
the data becomes stale and is no longer usable, so I have another
process that comes after that to investigate the problem.
The problem I'm having is that when my best effort jobs ran in MS SQL
Server, they would run and then bomb out immediately. I want it instead
to just continue on error.
Meanwhile, I then switched the jobs to osql.exe and Task Scheduler this
weekend. The results were exactly the same -- the job dies on error.
Can you help me figure out a way either for osql.exe to continue on
error, or for me to ignore all stored procedure errors and just keep on
continuing?
I'm used to VB's "on error resume next" and unfortunately I don't see
one in MS SQL Server. This is absurd!
Check out the error handling sections of Erland's web site:
http://www.sommarskog.se/
Andrew J. Kelly SQL MVP
<googlemike@.hotpop.com> wrote in message
news:1113236973.134465.132330@.l41g2000cwc.googlegr oups.com...
>I have some "best effort" jobs that try at various times to walk over
> missed records from one system to another about 5 times. After that,
> the data becomes stale and is no longer usable, so I have another
> process that comes after that to investigate the problem.
> The problem I'm having is that when my best effort jobs ran in MS SQL
> Server, they would run and then bomb out immediately. I want it instead
> to just continue on error.
> Meanwhile, I then switched the jobs to osql.exe and Task Scheduler this
> weekend. The results were exactly the same -- the job dies on error.
> Can you help me figure out a way either for osql.exe to continue on
> error, or for me to ignore all stored procedure errors and just keep on
> continuing?
> I'm used to VB's "on error resume next" and unfortunately I don't see
> one in MS SQL Server. This is absurd!
>
|||OSQL is better than Agent TSQL as OSQL doesn't terminate on errors. However, for some errors, *SQL
Server* terminates the batch. I suggest you check out the articles on error handling at
www.sommarskog.se.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
<googlemike@.hotpop.com> wrote in message
news:1113236973.134465.132330@.l41g2000cwc.googlegr oups.com...
>I have some "best effort" jobs that try at various times to walk over
> missed records from one system to another about 5 times. After that,
> the data becomes stale and is no longer usable, so I have another
> process that comes after that to investigate the problem.
> The problem I'm having is that when my best effort jobs ran in MS SQL
> Server, they would run and then bomb out immediately. I want it instead
> to just continue on error.
> Meanwhile, I then switched the jobs to osql.exe and Task Scheduler this
> weekend. The results were exactly the same -- the job dies on error.
> Can you help me figure out a way either for osql.exe to continue on
> error, or for me to ignore all stored procedure errors and just keep on
> continuing?
> I'm used to VB's "on error resume next" and unfortunately I don't see
> one in MS SQL Server. This is absurd!
>