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

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