Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Translate iif to Case

Author  Topic 

Villanuev
Constraint Violating Yak Guru

478 Posts

Posted - 2013-02-04 : 02:22:09
Hi Guys,

I have an SSRS report. i have a fields calculation using iif, intead of doing this in field calculation i will do it in the query.
how could i translate this in case statement.

=IIf(Fields!TOTAL.Value = 0, FormatPercent(0),
IIF(Fields!CoresReclaim.Value=0, FormatPercent(0),
FormatPercent(Fields!CoresReclaim.Value/Fields!TOTAL.Value,0)))

something like this..

SUM(Case When ASUPRODLINEID='Line1' and d.total=0 Then 0 ????


Thank you in Advance .

Villanuev
Constraint Violating Yak Guru

478 Posts

Posted - 2013-02-04 : 02:38:48
Just come up with this solution. not yet tested.

SUM(Case When ASUPRODLINEID='Line1' and d.total=0 or d.CORESQTY=0 Then 0 else d.CORESQTY/d.total End) as CoresUsage1
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-02-04 : 03:04:21
should be

SUM(Case When ASUPRODLINEID='Line1' and (d.total=0 or d.CORESQTY=0) Then 0 else d.CORESQTY/NULLIF(d.total,0) End) as CoresUsage1

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

Villanuev
Constraint Violating Yak Guru

478 Posts

Posted - 2013-02-04 : 03:12:10
Thank you for your prompt reply Visakh.

what if i did not put a clise and open patenthesis for this (d.total=0 or d.CORESQTY=0)
what is the issue?

Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-02-04 : 03:25:56
If you dont put parenthesis it will ignore the other conditions (eg. ASUPRODLINEID='Line1' ) when d.CORESQTY=0 is satisfied because of the or
I hope what you were looking for was either of total= 0 or d.CORESQTY=0 being true and ASUPRODLINEID='Line1' being always true

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

Villanuev
Constraint Violating Yak Guru

478 Posts

Posted - 2013-02-04 : 03:56:25
Thank you very much.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-02-04 : 04:01:42
welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

Villanuev
Constraint Violating Yak Guru

478 Posts

Posted - 2013-02-04 : 04:06:49
Hi Visakh, additional query.

there are two condition.

=IIf(Fields!requiredQty.Value = 0, FormatPercent(0),
IIf(Fields!requiredQty.Value <= Fields!TOTAL.Value, FormatPercent(0),
FormatPercent((Fields!requiredQty.Value-Fields!TOTAL.Value)/IIf(Fields!requiredQty.Value=0,1,Fields!requiredQty.Value))))
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-02-04 : 04:13:08
so what was the question?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

Villanuev
Constraint Violating Yak Guru

478 Posts

Posted - 2013-02-04 : 04:17:32
could not fgiure out on how to translate this in case statement with multiple condition. same as my previous sample..

SUM(Case When ASUPRODLINEID='Line1' and requiredqty=0 then 0, ??
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-02-04 : 04:22:24
SUM(Case When requiredQty > 0 AND requiredQty > TOTAL THEN (requiredQty - TOTAL)/requiredQty ELSE 0 END) as ...


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

Villanuev
Constraint Violating Yak Guru

478 Posts

Posted - 2013-02-04 : 04:27:37
Thank You.
Go to Top of Page

Villanuev
Constraint Violating Yak Guru

478 Posts

Posted - 2013-02-04 : 21:54:57

Tried to convert this into Case but i got an error.

=IIf(Fields!requiredQty.Value = 0, FormatPercent(0),IIf(Fields!requiredQty.Value <= Fields!TOTAL.Value,
FormatPercent(0), FormatPercent((Fields!requiredQty.Value-Fields!TOTAL.Value)/IIf(Fields!requiredQty.Value=0,1,Fields!requiredQty.Value))))

--Translate to SUM(Case)
SUM(Case When ASUPRODLINEID='Line1' and d.RequiredQty=0 and d.RequiredQty <= d.total Then 0
else (d.RequiredQty-d.total)/SUM(Case When ASUPRODLINEID='Line1' and d.REQUIREDQTY=0 Then 1
else d.RequiredQty End)) as CoresUsage1

Incorrect syntax near ")"

Thank you in Advance.

Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-02-04 : 22:54:58
SUM(Case When ASUPRODLINEID='Line1' and d.RequiredQty=0 and d.RequiredQty <= d.total Then 0
else (d.RequiredQty-d.total))/NULLIF(SUM(Case When ASUPRODLINEID='Line1' and d.REQUIREDQTY=0 Then 1
else d.RequiredQty End),0) as CoresUsage1

I dont think this is equivalent to IIF though as applying sum will mean aggregating the 1 conditions which might give you a different result when you've multiple records within each group

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

Villanuev
Constraint Violating Yak Guru

478 Posts

Posted - 2013-02-05 : 01:09:36
Hi Visakh.

Thank you for the reply.

I got this error.

Msg 102, Level 15, State 1, Line 135
Incorrect syntax near ')'.

Go to Top of Page

Villanuev
Constraint Violating Yak Guru

478 Posts

Posted - 2013-02-05 : 01:13:51
just place an End of the open and close parenthesis..

SUM(Case When ASUPRODLINEID='Line1' and d.RequiredQty=0 and d.RequiredQty <= d.total Then 0
else (d.RequiredQty-d.total) end)/NULLIF(SUM(Case When ASUPRODLINEID='Line1' and d.REQUIREDQTY=0 Then 1
else d.RequiredQty End),0) as CoresUsage1
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-02-05 : 02:29:30
quote:
Originally posted by Villanuev

just place an End of the open and close parenthesis..

SUM(Case When ASUPRODLINEID='Line1' and d.RequiredQty=0 and d.RequiredQty <= d.total Then 0
else (d.RequiredQty-d.total) end)/NULLIF(SUM(Case When ASUPRODLINEID='Line1' and d.REQUIREDQTY=0 Then 1
else d.RequiredQty End),0) as CoresUsage1



cool
hope you saw what i told reg. the equivalence of above against IIF. make sure your group doesnt have mutiple records other wise above will give different results than the IIF

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page
   

- Advertisement -