immediate updating triggers have "incorrect syntax near ')'."

Hello,
I am using SQL 7.0 to replicate to an immediate-updating subscriber and I am
having trouble when updating a table from the subscriber. When I run the
update statement, I get the error "incorrect syntax near ')'. I believe that
this is either in the auto-generated MSsync trigger or the sp on the
publisher. I am sure the original update statement is correct.
It seems almost as though a parameter is missing... I don't know.
One thing that I noticed is that I only receive this error on certain
tables. The main differences are:
1. the tables that don't work are really wide in columns (by all means they
should be three tables each) whereas the tables that do work are relatively
small.
2. the tables that don't work used to have text columns, but I changed them
to enormous varchar columns. The tables that do work have never had text
columns.
My experience with replication is limited, so any help or advice in this
matter would be greatly appreciated.
Thanks in advance,
-b.
Please can you confirm what service pack you are using, as this issue (or a
v.similar one) cropped up prior to sp2 and was fixed there
(http://support.microsoft.com/kb/238658/EN-US/).
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Thank you for your reply. Yes, I read something about that. I had applied sp4
and assumed that it was cumulative... is it not?
-b.
"Paul Ibison" wrote:

> Please can you confirm what service pack you are using, as this issue (or a
> v.similar one) cropped up prior to sp2 and was fixed there
> (http://support.microsoft.com/kb/238658/EN-US/).
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
>
|||Okay, I just read that the service packs are cumulative.
However, I did read something that said that text and image columns will not
replicate normally from immediate updating subscribers, and that special
considerations need to be made
(http://msdn.microsoft.com/library/de...lplan_1l4e.asp)
I no longer have text columns in the tables, but I DID, however, publish to
the subscriber previously with text fields before changing them to varchars
and publishing to the subscriber again. Is it possible that these triggers
remain from the previous publication...?
-b.
"pukeboot" wrote:
[vbcol=seagreen]
> Thank you for your reply. Yes, I read something about that. I had applied sp4
> and assumed that it was cumulative... is it not?
> -b.
> "Paul Ibison" wrote:
|||That shouldn't be the case, unless you did a nosync initialization. Can you
post up the text of some of the triggers so I can take a look?
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||to avoid a massively lengthy post, I have sent the trigger code directly to
you via attachment...
Thanks again,
-b.
"Paul Ibison" wrote:

> That shouldn't be the case, unless you did a nosync initialization. Can you
> post up the text of some of the triggers so I can take a look?
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
>
|||My email address isn't the one I post with (long story). Please send to Paul
.. Ibison@.ReplicationAnswers . Com (no spaces).
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||I read that as "paulibison@.replicationanswers.com" ... I wasn't sure with the
line breaks and the elipsis.
I forwarded the message there. I hope that's it.
-b.
"Paul Ibison" wrote:

> My email address isn't the one I post with (long story). Please send to Paul
> .. Ibison@.ReplicationAnswers . Com (no spaces).
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
>
>
|||Bobby,
there's nothing I can see wrong in the trigger, and no reference to text
columns. As you are on sp4 (publisher, distributor and subscriber?), this is
baffling. I'd run profiler to see where it gets to, and fails, in the
synchronization, and also enable logging to see what that reveals
(http://support.microsoft.com/?id=312292).
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Paul,
I am unfamiliar with the profiler, but I gave it a go and found that the
"internal abort" event was occuring after this line in the trigger (bear with
this... it's a long line):
exec @.retcode = dbo.sp_executesql @.rpc_proc, @.rpc_types,
@.c1
output,@.c2,@.c3,@.c4,@.c5,@.c6,@.c7,@.c8,@.c9,@.c10,@.c11,@. c12,@.c13,@.c14,@.c15,@.c16,@.c17,@.c18,@.c19,@.c20,@.c21,@. c22,@.c23,@.c24,@.c25,@.c26,@.c27,@.c28,@.c29,@.c30,@.c31,@. c32,@.c33,@.c34,@.c35,@.c36,@.c37,@.c38,@.c39,@.c40,@.c41,@. c42,@.c43,@.c44,@.c45,@.c46,@.c47,@.c48,@.c49,@.c50,@.c51,@. c52,@.
c53,@.c54,@.c55,@.c56,@.c57,@.c58,@.c59,@.c60,@.c61,@.c62,@. c63,@.c64,@.c65,@.c66,@.c67,@.c68,@.c69,@.c70,@.c71,@.c72,@. c73,@.c74,@.c75,@.c76,@.c77,@.c78,@.c79,@.c80,@.c81,@.c82,@. c83,@.c84,@.c85,@.c86,@.c87,@.c88,@.c89,@.c90,@.c91,@.c92,@. c93,@.c94,@.c95,@.c96,@.c97,@.c98,@.c99,@.c100,@.c101,@.c10 2,@.c1
03,@.c104,@.c105,@.c106,@.c107,@.c108,@.c109,@.c110,@.c111 ,@.c112,@.c113,@.c114,@.c115,@.c116,@.c117,@.c118,@.c119,@. c120,@.c121,@.c122,@.c123,@.c124,@.c125,@.c126,@.c127,@.c1 28,@.c129,@.c130,@.c131,@.c132,@.c133,@.c134,@.c135,@.c136 ,@.c137,@.c138,@.c139,@.c140,@.c141,@.c142,@.c143,@.c144,@. c145,
@.c146,@.c147,@.c148,@.c149,@.c150,@.c151,@.c152,@.c153,@.c 154,@.c155,@.c156,@.c157,@.c158,@.c159,@.c160,@.c161,@.c16 2,@.c163,@.c164,@.c165,@.c166,@.c167,@.c168,@.c169,@.c170, @.c171,@.c172,@.c173,@.c174,@.c175,@.c176,@.c177,@.c178,@.c 179,@.c180,@.c181,@.c182,@.c183,@.c184,@.c185,@.c186,@.c18 7,@.c1
88,@.c189,@.c190,@.c191,@.c192,@.c193,@.c194,@.c195,@.c196 ,@.c197,@.c198,@.c199,@.c200,@.c201,@.c202,@.c203,@.c204,@. c205,@.c206,@.c207,@.c208,@.c209,@.c210,@.c211,@.c212,@.c2 13,@.c214,@.c215,@.c216,@.c217,@.c218,@.c219,@.c220,@.c221 ,@.c222,@.c223,@.c224,@.c225,@.c226,@.c227,@.c228,@.c229,@. c230,
@.c231,@.c232,@.c233,@.c234,@.c235,@.c236
output,@.c237,@.c238,@.c239,@.c240,@.c241,@.c242,@.c243,@. c244,@.c245,@.c246,@.c247,@.c248
,@.c1_old,@.c2_old,@.c3_old,@.c4_old,@.c5_old,@.c6_old,@. c7_old,@.c8_old,@.c9_old,@.c10_old,@.c11_old,@.c12_old, @.c13_old,@.c14_old,@.c15_old,@.c16_old,@.c17_old,@.c18_ old,@.c19_old,@.c20_old,@.c21_old,@.c22_old,@.c23_old,@. c24_old,@.c25_old,@.c26_old,@.c27_old,@.c28_old,@.c29_o ld,@.c
30_old,@.c31_old,@.c32_old,@.c33_old,@.c34_old,@.c35_ol d,@.c36_old,@.c37_old,@.c38_old,@.c39_old,@.c40_old,@.c4 1_old,@.c42_old,@.c43_old,@.c44_old,@.c45_old,@.c46_old ,@.c47_old,@.c48_old,@.c49_old,@.c50_old,@.c51_old,@.c52 _old,@.c53_old,@.c54_old,@.c55_old,@.c56_old,@.c57_old, @.c58_
old,@.c59_old,@.c60_old,@.c61_old,@.c62_old,@.c63_old,@. c64_old,@.c65_old,@.c66_old,@.c67_old,@.c68_old,@.c69_o ld,@.c70_old,@.c71_old,@.c72_old,@.c73_old,@.c74_old,@.c 75_old,@.c76_old,@.c77_old,@.c78_old,@.c79_old,@.c80_ol d,@.c81_old,@.c82_old,@.c83_old,@.c84_old,@.c85_old,@.c8 6_old
,@.c87_old,@.c88_old,@.c89_old,@.c90_old,@.c91_old,@.c92 _old,@.c93_old,@.c94_old,@.c95_old,@.c96_old,@.c97_old, @.c98_old,@.c99_old,@.c100_old,@.c101_old,@.c102_old,@.c 103_old,@.c104_old,@.c105_old,@.c106_old,@.c107_old,@.c 108_old,@.c109_old,@.c110_old,@.c111_old,@.c112_old,@.c 113_o
ld,@.c114_old,@.c115_old,@.c116_old,@.c117_old,@.c118_o ld,@.c119_old,@.c120_old,@.c121_old,@.c122_old,@.c123_o ld,@.c124_old,@.c125_old,@.c126_old,@.c127_old,@.c128_o ld,@.c129_old,@.c130_old,@.c131_old,@.c132_old,@.c133_o ld,@.c134_old,@.c135_old,@.c136_old,@.c137_old,@.c138_o ld,@.c
139_old,@.c140_old,@.c141_old,@.c142_old,@.c143_old,@.c 144_old,@.c145_old,@.c146_old,@.c147_old,@.c148_old,@.c 149_old,@.c150_old,@.c151_old,@.c152_old,@.c153_old,@.c 154_old,@.c155_old,@.c156_old,@.c157_old,@.c158_old,@.c 159_old,@.c160_old,@.c161_old,@.c162_old,@.c163_old,@.c 164_o
ld,@.c165_old,@.c166_old,@.c167_old,@.c168_old,@.c169_o ld,@.c170_old,@.c171_old,@.c172_old,@.c173_old,@.c174_o ld,@.c175_old,@.c176_old,@.c177_old,@.c178_old,@.c179_o ld,@.c180_old,@.c181_old,@.c182_old,@.c183_old,@.c184_o ld,@.c185_old,@.c186_old,@.c187_old,@.c188_old,@.c189_o ld,@.c
190_old,@.c191_old,@.c192_old,@.c193_old,@.c194_old,@.c 195_old,@.c196_old,@.c197_old,@.c198_old,@.c199_old,@.c 200_old,@.c201_old,@.c202_old,@.c203_old,@.c204_old,@.c 205_old,@.c206_old,@.c207_old,@.c208_old,@.c209_old,@.c 210_old,@.c211_old,@.c212_old,@.c213_old,@.c214_old,@.c 215_o
ld,@.c216_old,@.c217_old,@.c218_old,@.c219_old,@.c220_o ld,@.c221_old,@.c222_old,@.c223_old,@.c224_old,@.c225_o ld,@.c226_old,@.c227_old,@.c228_old,@.c229_old,@.c230_o ld,@.c231_old,@.c232_old,@.c233_old,@.c234_old,@.c235_o ld,@.c236_old,@.c237_old,@.c238_old,@.c239_old,@.c240_o ld,@.c
241_old,@.c242_old,@.c243_old,@.c244_old,@.c245_old,@.c 246_old,@.c247_old,@.c248_old
, @.bitmap
plus, I also get an error that says:
Server: Msg 137, Level 15, State 1, Line 5
Must declare the variable '@.c30o'.
If you have any ideas what might be causing this, or if you have any further
advice on what I can do to resolve the problem, I would love to hear it.
Thanks for all your input.
-b.
"Paul Ibison" wrote:

> Bobby,
> there's nothing I can see wrong in the trigger, and no reference to text
> columns. As you are on sp4 (publisher, distributor and subscriber?), this is
> baffling. I'd run profiler to see where it gets to, and fails, in the
> synchronization, and also enable logging to see what that reveals
> (http://support.microsoft.com/?id=312292).
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
>

Im baffled by the single quotes with STMT

Hi,

Don't worry about the vars, they are defined,
the following line give me an err of "Incorrect syntax near '.'."
Goal: to rename nonstardard column name.

EXEC sp_rename '+@.tbuffer+'.['+@.cbuffer+']','+Replace(+@.cbuffer+','%[^A-Za-z0-9_#$@.]%','')',
'COLUMN';

Thanks.Doug Baroter (qwert12345@.boxfrog.com) writes:
> Don't worry about the vars, they are defined,
> the following line give me an err of "Incorrect syntax near '.'."
> Goal: to rename nonstardard column name.
> EXEC sp_rename '+@.tbuffer+'.['+@.cbuffer+']','+Replace(+@.cbuffer+','%[^A-Za-z0-9_#$@.]%','')',
> 'COLUMN';

You can only pass constants and variables as parameters to stored procedures.
You cannot pass an expression as a parameter, but you must put everything
in variables.

Furthermore, replace() only handles fixed strings, and does not have
any capacbilities to find patterns.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Erland,

Thanks for the quick response. Please my further question below.

Erland Sommarskog <sommar@.algonet.se> wrote in message news:<Xns941BEC43659B9Yazorman@.127.0.0.1>...
> Doug Baroter (qwert12345@.boxfrog.com) writes:
> > Don't worry about the vars, they are defined,
> > the following line give me an err of "Incorrect syntax near '.'."
> > Goal: to rename nonstardard column name.
> > EXEC sp_rename '+@.tbuffer+'.['+@.cbuffer+']','+Replace(+@.cbuffer+','%[^A-Za-z0-9_#$@.]%','')',
> > 'COLUMN';
> You can only pass constants and variables as parameters to stored procedures.
> You cannot pass an expression as a parameter, but you must put everything
> in variables.
Are you saying I should do something like
set @.tbuffer = '''+@.tbuffer+''';
set @.cbuffer = '''+@.cbuffer+''';
and then
EXEC sp_rename @.tbuffer+'.['+@.cbuffer+']' ...

> Furthermore, replace() only handles fixed strings, and does not have
> any capacbilities to find patterns.
Since replace can't do the job for this case, what other option do I
have to remove the non-standard character(s), I've looked at charindex
and stuff function, could they fit in here or a better way to do it?|||Doug Baroter (qwert12345@.boxfrog.com) writes:
> Are you saying I should do something like
> set @.tbuffer = '''+@.tbuffer+''';
> set @.cbuffer = '''+@.cbuffer+''';
> and then
> EXEC sp_rename @.tbuffer+'.['+@.cbuffer+']' ...

Rather:

SELECT @.old_name = 'tbl.' + <whatever>
SELECT @.new_name = <whichever>
EXEC sp_rename @.old_name, @.new_name, 'column'

> Since replace can't do the job for this case, what other option do I
> have to remove the non-standard character(s), I've looked at charindex
> and stuff function, could they fit in here or a better way to do it?

Unfortunately, SQL is poor for this task. You are probably better off
if you write some program in Perl, Visual Basic, VBscript, C or whatever
is your favourite client language.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Thanks, Erland, pls see below.
Erland Sommarskog <sommar@.algonet.se> wrote in message news:<Xns941D8E88A84Yazorman@.127.0.0.1>...
> Doug Baroter (qwert12345@.boxfrog.com) writes:
> > Are you saying I should do something like
> > set @.tbuffer = '''+@.tbuffer+''';
> > set @.cbuffer = '''+@.cbuffer+''';
> > and then
> > EXEC sp_rename @.tbuffer+'.['+@.cbuffer+']' ...
> Rather:

> SELECT @.old_name = 'tbl.' + <whatever>
> SELECT @.new_name = <whichever>
> EXEC sp_rename @.old_name, @.new_name, 'column'
Unfortunately the tbl name is dynamically determined. But as you
recommended below, I just use a non-SQL language to take care of the
whole problem except one minor one, that is, I haven't got the RegExp
part fully completed.

> > Since replace can't do the job for this case, what other option do I
> > have to remove the non-standard character(s), I've looked at charindex
> > and stuff function, could they fit in here or a better way to do it?
> Unfortunately, SQL is poor for this task. You are probably better off
> if you write some program in Perl, Visual Basic, VBscript, C or whatever
> is your favourite client language.|||Doug Baroter (qwert12345@.boxfrog.com) writes:
>> SELECT @.old_name = 'tbl.' + <whatever>
>> SELECT @.new_name = <whichever>
>> EXEC sp_rename @.old_name, @.new_name, 'column'
> Unfortunately the tbl name is dynamically determined.

SELECT @.old_name = @.tbl + '.' + @.column

The point is that when you come to sp_rename you must have a single
value.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||OK. Thanks.
Erland Sommarskog <sommar@.algonet.se> wrote in message news:<Xns941D616A4DA25Yazorman@.127.0.0.1>...
> Doug Baroter (qwert12345@.boxfrog.com) writes:
> >> SELECT @.old_name = 'tbl.' + <whatever>
> >> SELECT @.new_name = <whichever>
> >> EXEC sp_rename @.old_name, @.new_name, 'column'
> > Unfortunately the tbl name is dynamically determined.
> SELECT @.old_name = @.tbl + '.' + @.column
> The point is that when you come to sp_rename you must have a single
> value.

I'm a SQL Beginner

I continue to get syntax errors.
What is the command line for "List any titles that do not have a price
assigned" and list each title with its current cost and with the projected
cost increase of 15%"?No one can really tell you as you didn't provide any
information on the tables, what specific errors you are
getting, what statement you are using, etc. Generally,
something like:
Select titles
from your table
where price is null
and
select title, cost, cost + cost*.15 as IncreasedCost
from your table
-Sue
On Sun, 26 Mar 2006 14:43:59 -0800, Smokey
<Smokey@.discussions.microsoft.com> wrote:

>I continue to get syntax errors.
>What is the command line for "List any titles that do not have a price
>assigned" and list each title with its current cost and with the projected
>cost increase of 15%"?

I'm a SQL Beginner

I continue to get syntax errors.
What is the command line for "List any titles that do not have a price
assigned" and list each title with its current cost and with the projected
cost increase of 15%"?
No one can really tell you as you didn't provide any
information on the tables, what specific errors you are
getting, what statement you are using, etc. Generally,
something like:
Select titles
from your table
where price is null
and
select title, cost, cost + cost*.15 as IncreasedCost
from your table
-Sue
On Sun, 26 Mar 2006 14:43:59 -0800, Smokey
<Smokey@.discussions.microsoft.com> wrote:

>I continue to get syntax errors.
>What is the command line for "List any titles that do not have a price
>assigned" and list each title with its current cost and with the projected
>cost increase of 15%"?

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...

IIF statment in an SQL statment.

I am trying to convert a logical and date fields into number fields. I am
using IIF() but I can't seem to get the syntax correct. Help Please.
SELECT MagazineName, COUNT(Quantity) AS QTY, iif(RenewalFlag = flase, 0,
1) AS REFL, iif(CancelDate is not null,0,1) as CLRFL
FROM Order2
GROUP BY MagazineName
The overall goal is to
1) count then number of records
2) count how many are renewal's
3) count how many are canceled
example:
CHILD 50 5 1
Scott BurkeHey Scott,
Use the CASE statement in SQL, not IIF. Like this:
SELECT MagazineName, COUNT(Quantity) AS QTY,CASE WHEN RenewalFlag =false THEN 0 ELSE 1 END AS REFL, CASE WHEN CancelDate is not null THEN 0 ELSE
1 END as CLRFL
FROM Order2
GROUP BY MagazineName
Michael C
"Scott Burke" wrote:
> I am trying to convert a logical and date fields into number fields. I am
> using IIF() but I can't seem to get the syntax correct. Help Please.
> SELECT MagazineName, COUNT(Quantity) AS QTY, iif(RenewalFlag = flase, 0,
> 1) AS REFL, iif(CancelDate is not null,0,1) as CLRFL
> FROM Order2
> GROUP BY MagazineName
> The overall goal is to
> 1) count then number of records
> 2) count how many are renewal's
> 3) count how many are canceled
> example:
> CHILD 50 5 1
> Scott Burke|||Hi Michael. Thanks for the suggestion. It worked exactly the way I wonted to.
The case statement looks a lot like an IIF() statement to me.
Time to do some research.
Thanks again.
Scott Burke
"Michael C" wrote:
> Hey Scott,
> Use the CASE statement in SQL, not IIF. Like this:
>
> SELECT MagazineName, COUNT(Quantity) AS QTY,CASE WHEN RenewalFlag => false THEN 0 ELSE 1 END AS REFL, CASE WHEN CancelDate is not null THEN 0 ELSE
> 1 END as CLRFL
> FROM Order2
> GROUP BY MagazineName
>
> Michael C
> "Scott Burke" wrote:
> > I am trying to convert a logical and date fields into number fields. I am
> > using IIF() but I can't seem to get the syntax correct. Help Please.
> >
> > SELECT MagazineName, COUNT(Quantity) AS QTY, iif(RenewalFlag = flase, 0,
> > 1) AS REFL, iif(CancelDate is not null,0,1) as CLRFL
> > FROM Order2
> > GROUP BY MagazineName
> >
> > The overall goal is to
> > 1) count then number of records
> > 2) count how many are renewal's
> > 3) count how many are canceled
> >
> > example:
> > CHILD 50 5 1
> >
> > Scott Burke

IIF Statement in Query designer

Hello,

I am trying to use following IIF Statement in Query designer but getting error message saying "Incorrect syntax

near '>')

Can anyone please help, perhaps correct the statement for me?

IIF([Expected Receipt Date] > now(), 1, 0) AS EXPR1

Thank you in advance

-- Praf

the Query Designer uses SQL

Try a Case Statement instead of the IIF statement

example

CASE

WHEN [Expected Receipt Date] > getdate() THEN 1

Else 0

END as EXPR1

|||

Charles is correct; IFF is available in Report Services but is NOT part of the Transact SQL language. If you are going to edit this query from the Query Designer then CASE syntax is a good choice.

( OK, I am confused with this; where am I going wrong with this? )

|||

I tried the case statement,

I error "The Query Designer does not support the CASE SQL construct." but did provide the results.

Also I am building this query in Query designer.

Thanks

|||

Your code seems like a mix between SQL and RS expressions. To use expressions, you must have the = sign in front of the query. This will allow you to build the SQL statement by concatenating strings, using the IIF expression if needed.

Michael

IIF Statement in Query designer

Hello,

I am trying to use following IIF Statement in Query designer but getting error message saying "Incorrect syntax

near '>')

Can anyone please help, perhaps correct the statement for me?

IIF([Expected Receipt Date] > now(), 1, 0) AS EXPR1

Thank you in advance

-- Praf

the Query Designer uses SQL

Try a Case Statement instead of the IIF statement

example

CASE

WHEN [Expected Receipt Date] > getdate() THEN 1

Else 0

END as EXPR1

|||

Charles is correct; IFF is available in Report Services but is NOT part of the Transact SQL language. If you are going to edit this query from the Query Designer then CASE syntax is a good choice.

( OK, I am confused with this; where am I going wrong with this? )

|||

I tried the case statement,

I error "The Query Designer does not support the CASE SQL construct." but did provide the results.

Also I am building this query in Query designer.

Thanks

|||

Your code seems like a mix between SQL and RS expressions. To use expressions, you must have the = sign in front of the query. This will allow you to build the SQL statement by concatenating strings, using the IIF expression if needed.

Michael

IIf in Query question

I am attempting to execute the following query but am getting syntax errors.
I don't have a lot of practice with the IIf function, need help.
Any Ideas??

--QUERY--
UPDATE View_Data SET
Num_07 = Num_01/IIf(Den_01=0,1,Den_01)
where Data_Set_ID = 444

--Error--
Server: Msg 170, Level 15, State 1, Line 2
Line 2: Incorrect syntax near '='.

More Info
----------------
The fields Num_07,Num_01, Den_01 are all of type 'float'I think that you need to replace the Jet/VB Iif() (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/vblr7/html/vafctiif.asp) with the SQL Server CASE (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_ca-co_5t9v.asp) statement.

-PatP