IIF()

What is wrong with this: Datediff(dd, open_date_key, iif(Closed_date_key =
'1/1/1900', Current_Date, Closed_date_key )) as OpenTime?
it is in a SELECT statement and I keep getting an error:
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near '='.
TIA
Andrew MadsenDon't believe IIF is supported in TSQL (although I think it is available to
Analysis Services...)
Kevin Hill
President
3NF Consulting
www.3nf-inc.com/NewsGroups.htm
"Andrew Madsen" <andrew.madsen@.harley-davidson.com> wrote in message
news:uU5u73B7DHA.632@.TK2MSFTNGP12.phx.gbl...

quote:

---

> What is wrong with this: Datediff(dd, open_date_key, iif(Closed_date_key =
> '1/1/1900', Current_Date, Closed_date_key )) as OpenTime?
> it is in a SELECT statement and I keep getting an error:
> Server: Msg 170, Level 15, State 1, Line 1
> Line 1: Incorrect syntax near '='.
> TIA
> Andrew Madsen
>

---

|||No IIF in SQL Server, look up CASE in Books Online.
SELECT OpenTime = DATEDIFF(DAY, open_date_key,
CASE WHEN Closed_date_key = '19000101' THEN Current_date ELSE
Closed_date_key END)
If you used NULL instead of a token date when the closed_date_key is
"unknown", you could say
SELECT OpenTime = DATEDIFF(DAY, open_date_key,
COALESCE(Closed_date_key, Current_date))
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"Andrew Madsen" <andrew.madsen@.harley-davidson.com> wrote in message
news:uU5u73B7DHA.632@.TK2MSFTNGP12.phx.gbl...

quote:

---

> What is wrong with this: Datediff(dd, open_date_key, iif(Closed_date_key =
> '1/1/1900', Current_Date, Closed_date_key )) as OpenTime?
> it is in a SELECT statement and I keep getting an error:
> Server: Msg 170, Level 15, State 1, Line 1
> Line 1: Incorrect syntax near '='.
> TIA
> Andrew Madsen
>

---

|||IIF is not supported in T-SQL. Use CASE:
Datediff(dd, open_date_key
, case when Closed_date_key = '1/1/1900'
then Current_Date -- I think you mean to use CURRENT_TIMESTAMP
else Closed_date_key end) as OpenTime
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Andrew Madsen" <andrew.madsen@.harley-davidson.com> wrote in message
news:uU5u73B7DHA.632@.TK2MSFTNGP12.phx.gbl...
What is wrong with this: Datediff(dd, open_date_key, iif(Closed_date_key =
'1/1/1900', Current_Date, Closed_date_key )) as OpenTime?
it is in a SELECT statement and I keep getting an error:
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near '='.
TIA
Andrew Madsen|||That would throw an error. It is just interesting it did not throw at the
IIF( instead of waiting for the '=' sign. Thanks for the help.
Andrew Madsn
Harley-Davidson Motor Company
"Kevin3NF" <KHill@.NopeIDontNeedNoSPAM3NF-inc.com> wrote in message
news:uf86F9B7DHA.1052@.TK2MSFTNGP12.phx.gbl...

quote:

---

> Don't believe IIF is supported in TSQL (although I think it is available

---

to

quote:

---

> Analysis Services...)
> --
> Kevin Hill
> President
> 3NF Consulting
> www.3nf-inc.com/NewsGroups.htm
> "Andrew Madsen" <andrew.madsen@.harley-davidson.com> wrote in message
> news:uU5u73B7DHA.632@.TK2MSFTNGP12.phx.gbl...
=[QUOTE]
>

---

|||hi andrew,
Use CASE, there is no iif in SQL Server.
Try:
select datediff(dd, open_date_key, case when Closed_date_key = '19000101' th
en getdate() else Closed_date_key end)
from <table>
-Vishal|||Tom, Aaron and Vishal,
Case was it. Thank you very much. and Tom Current_Timestamp was what I
wanted even though Current_Date is a T-SQL value. Weird. Well, Thank you all
Andrew C. Madsen
Information Architect
Harley-Davidson Motor Company
"Vishal Parkar" <remove_this_vgparkar@.yahoo.co.in> wrote in message
news:673ECD20-76F2-418F-9B73-110BE31A9F8D@.microsoft.com...

quote:

---

> hi andrew,
> Use CASE, there is no iif in SQL Server.
> Try:
> select datediff(dd, open_date_key, case when Closed_date_key = '19000101'

---

then getdate() else Closed_date_key end)

quote:

---

> from <table>
> -Vishal

---

IIF()

What is wrong with this: Datediff(dd, open_date_key, iif(Closed_date_key = '1/1/1900', Current_Date, Closed_date_key )) as OpenTime?
it is in a SELECT statement and I keep getting an error:
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near '='.
TIA
Andrew MadsenDon't believe IIF is supported in TSQL (although I think it is available to
Analysis Services...)
--
Kevin Hill
President
3NF Consulting
www.3nf-inc.com/NewsGroups.htm
"Andrew Madsen" <andrew.madsen@.harley-davidson.com> wrote in message
news:uU5u73B7DHA.632@.TK2MSFTNGP12.phx.gbl...
> What is wrong with this: Datediff(dd, open_date_key, iif(Closed_date_key => '1/1/1900', Current_Date, Closed_date_key )) as OpenTime?
> it is in a SELECT statement and I keep getting an error:
> Server: Msg 170, Level 15, State 1, Line 1
> Line 1: Incorrect syntax near '='.
> TIA
> Andrew Madsen
>|||No IIF in SQL Server, look up CASE in Books Online.
SELECT OpenTime = DATEDIFF(DAY, open_date_key,
CASE WHEN Closed_date_key = '19000101' THEN Current_date ELSE
Closed_date_key END)
If you used NULL instead of a token date when the closed_date_key is
"unknown", you could say
SELECT OpenTime = DATEDIFF(DAY, open_date_key,
COALESCE(Closed_date_key, Current_date))
--
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"Andrew Madsen" <andrew.madsen@.harley-davidson.com> wrote in message
news:uU5u73B7DHA.632@.TK2MSFTNGP12.phx.gbl...
> What is wrong with this: Datediff(dd, open_date_key, iif(Closed_date_key => '1/1/1900', Current_Date, Closed_date_key )) as OpenTime?
> it is in a SELECT statement and I keep getting an error:
> Server: Msg 170, Level 15, State 1, Line 1
> Line 1: Incorrect syntax near '='.
> TIA
> Andrew Madsen
>|||This is a multi-part message in MIME format.
--=_NextPart_000_0289_01C3EBF6.C5A01230
Content-Type: text/plain;
charset="Windows-1252"
Content-Transfer-Encoding: 7bit
IIF is not supported in T-SQL. Use CASE:
Datediff(dd, open_date_key
, case when Closed_date_key = '1/1/1900'
then Current_Date -- I think you mean to use CURRENT_TIMESTAMP
else Closed_date_key end) as OpenTime
--
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Andrew Madsen" <andrew.madsen@.harley-davidson.com> wrote in message
news:uU5u73B7DHA.632@.TK2MSFTNGP12.phx.gbl...
What is wrong with this: Datediff(dd, open_date_key, iif(Closed_date_key ='1/1/1900', Current_Date, Closed_date_key )) as OpenTime?
it is in a SELECT statement and I keep getting an error:
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near '='.
TIA
Andrew Madsen
--=_NextPart_000_0289_01C3EBF6.C5A01230
Content-Type: text/html;
charset="Windows-1252"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&

---

IIF is not supported in T-SQL. =Use CASE:
Datediff(dd, =open_date_key
, case when Closed_date_key =3D ='1/1/1900'
then Current_Date -- I think you mean to =use CURRENT_TIMESTAMP
else Closed_date_key end) as OpenTime
-- Tom
---T=homas A. Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL =Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql
"Andrew Madsen" wrote in message news:uU5u73B7DHA.632@.T=K2MSFTNGP12.phx.gbl...What is wrong with this: Datediff(dd, open_date_key, iif(Closed_date_key =3D'1/1/1900', Current_Date, Closed_date_key )) as =OpenTime?it is in a SELECT statement and I keep getting an error:Server: Msg 170, =Level 15, State 1, Line 1Line 1: Incorrect syntax near ='=3D'.TIAAndrew Madsen

---

--=_NextPart_000_0289_01C3EBF6.C5A01230--|||That would throw an error. It is just interesting it did not throw at the
IIF( instead of waiting for the '=' sign. Thanks for the help.
Andrew Madsn
Harley-Davidson Motor Company
"Kevin3NF" <KHill@.NopeIDontNeedNoSPAM3NF-inc.com> wrote in message
news:uf86F9B7DHA.1052@.TK2MSFTNGP12.phx.gbl...
> Don't believe IIF is supported in TSQL (although I think it is available
to
> Analysis Services...)
> --
> Kevin Hill
> President
> 3NF Consulting
> www.3nf-inc.com/NewsGroups.htm
> "Andrew Madsen" <andrew.madsen@.harley-davidson.com> wrote in message
> news:uU5u73B7DHA.632@.TK2MSFTNGP12.phx.gbl...
> > What is wrong with this: Datediff(dd, open_date_key, iif(Closed_date_key
=> > '1/1/1900', Current_Date, Closed_date_key )) as OpenTime?
> >
> > it is in a SELECT statement and I keep getting an error:
> >
> > Server: Msg 170, Level 15, State 1, Line 1
> > Line 1: Incorrect syntax near '='.
> >
> > TIA
> >
> > Andrew Madsen
> >
> >
>|||hi andrew
Use CASE, there is no iif in SQL Server
Try
select datediff(dd, open_date_key, case when Closed_date_key = '19000101' then getdate() else Closed_date_key end
from <table
-Vishal|||Tom, Aaron and Vishal,
Case was it. Thank you very much. and Tom Current_Timestamp was what I
wanted even though Current_Date is a T-SQL value. Weird. Well, Thank you all
--
Andrew C. Madsen
Information Architect
Harley-Davidson Motor Company
"Vishal Parkar" <remove_this_vgparkar@.yahoo.co.in> wrote in message
news:673ECD20-76F2-418F-9B73-110BE31A9F8D@.microsoft.com...
> hi andrew,
> Use CASE, there is no iif in SQL Server.
> Try:
> select datediff(dd, open_date_key, case when Closed_date_key = '19000101'
then getdate() else Closed_date_key end)
> from <table>
> -Vishal

IIF Statement to Case but getting error

I tried converting the statement below, which is just one of many statements
in a view. This one poplulates one column in the view:
IF(DATEDIFF(dd, MAX(INVOICE_DA), GETDATE())<=30 AND
COUNT([CUSTOMER__])>=5,YES,NO)
to:
CASE WHEN DATEDIFF(dd, MAX(INVOICE_DA), GETDATE())<=30 AND
COUNT([CUSTOMER__])>=5 THEN 'YES' ELSE 'NO'
I'm getting an error that says the query designer does not support the CASE
sql construct. Any thoughts on how I can rewrite the IIF statement so that i
t
can work in a sql view? THANKS!!Mike,
Where are you creating the view?. Use Query analyzer.
AMB
"Mike C" wrote:

> I tried converting the statement below, which is just one of many statemen
ts
> in a view. This one poplulates one column in the view:
> IF(DATEDIFF(dd, MAX(INVOICE_DA), GETDATE())<=30 AND
> COUNT([CUSTOMER__])>=5,YES,NO)
> to:
> CASE WHEN DATEDIFF(dd, MAX(INVOICE_DA), GETDATE())<=30 AND
> COUNT([CUSTOMER__])>=5 THEN 'YES' ELSE 'NO'
> I'm getting an error that says the query designer does not support the CAS
E
> sql construct. Any thoughts on how I can rewrite the IIF statement so that
it
> can work in a sql view? THANKS!!|||Mike C a écrit :
> I tried converting the statement below, which is just one of many statemen
ts
> in a view. This one poplulates one column in the view:
> IF(DATEDIFF(dd, MAX(INVOICE_DA), GETDATE())<=30 AND
> COUNT([CUSTOMER__])>=5,YES,NO)
> to:
> CASE WHEN DATEDIFF(dd, MAX(INVOICE_DA), GETDATE())<=30 AND
> COUNT([CUSTOMER__])>=5 THEN 'YES' ELSE 'NO'
END missing in CAS structure :
CASE
WHEN DATEDIFF(dd, MAX(INVOICE_DA), GETDATE()) <=30
AND COUNT([CUSTOMER__]) >= 5 THEN 'YES'
ELSE 'NO'
END as YesNoCol

> I'm getting an error that says the query designer does not support the CAS
E
> sql construct. Any thoughts on how I can rewrite the IIF statement so that
it
> can work in a sql view? THANKS!!
A +
Frédéric BROUARD, MVP SQL Server, expert bases de données et langage SQL
Le site sur le langage SQL et les SGBDR : http://sqlpro.developpez.com
Audit, conseil, expertise, formation, modélisation, tuning, optimisation
********************* http://www.datasapiens.com ***********************|||Alejandro,
Thank you. That worked. The problem I'm left with is how to run this report
automatically. I've been using DTS to export a view to an Excel sheet but it
looks like that won't work in this case. I guess I could try to put this in
an sp (which I haven't done much of and should probably start mastering) and
either DTS the sp result or I could just throw the results in a web-based
datagrid and export the datagrid to Excel on demand. Do you have any
recommendations on how to make the query results available to users? Thanks
again for the earlier suggestion.
MC
"Alejandro Mesa" wrote:
> Mike,
> Where are you creating the view?. Use Query analyzer.
>
> AMB
> "Mike C" wrote:
>|||I actually had END in the view but I forgot to type it into my question.
"SQLpro [MVP]" wrote:

> Mike C a écrit :
> END missing in CAS structure :
>
> CASE
> WHEN DATEDIFF(dd, MAX(INVOICE_DA), GETDATE()) <=30
> AND COUNT([CUSTOMER__]) >= 5 THEN 'YES'
> ELSE 'NO'
> END as YesNoCol
>
> A +
> --
> Frédéric BROUARD, MVP SQL Server, expert bases de données et langage SQ
L
> Le site sur le langage SQL et les SGBDR : http://sqlpro.developpez.com
> Audit, conseil, expertise, formation, modélisation, tuning, optimisation
> ********************* http://www.datasapiens.com ***********************
>

IIF Datediff in SQL SERVER

Hi,
I am trying to build a view in SQL server. I have a function in Access
which looks like this:
Breach:
IIf(DateDiff("n",[PP_ARRIVAL_DATE],[PP_DISCHARGE_DATE])>240,"Breach","Non
Breach")

>From reading it is clear that the IIF statement is not available in
SQLServer what do i need to use to produce the same results in
SQLServer?
ThanksSELECT CASE WHEN DATEDIFF(MINUTE, PP_ARRIVAL_DATE, PP_DISCHARGE_DATE) > 240
THEN 'Breach' ELSE 'Non Breach' END
http://www.aspfaq.com/2214
"yariso" <john.campbell600@.ntlworld.com> wrote in message
news:1124112204.794604.128840@.z14g2000cwz.googlegroups.com...
> Hi,
> I am trying to build a view in SQL server. I have a function in Access
> which looks like this:
> Breach:
> IIf(DateDiff("n",[PP_ARRIVAL_DATE],[PP_DISCHARGE_DATE])>240,"Breach","Non
> Breach")
>
> SQLServer what do i need to use to produce the same results in
> SQLServer?
> Thanks
>|||Great stuff thanks