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