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

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Translate iif to Case
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Villanuev
Constraint Violating Yak Guru

478 Posts

Posted - 02/04/2013 :  02:22:09  Show Profile  Reply with Quote
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 - 02/04/2013 :  02:38:48  Show Profile  Reply with Quote
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

India
52326 Posts

Posted - 02/04/2013 :  03:04:21  Show Profile  Reply with Quote
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 - 02/04/2013 :  03:12:10  Show Profile  Reply with Quote
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

India
52326 Posts

Posted - 02/04/2013 :  03:25:56  Show Profile  Reply with Quote
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 - 02/04/2013 :  03:56:25  Show Profile  Reply with Quote
Thank you very much.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52326 Posts

Posted - 02/04/2013 :  04:01:42  Show Profile  Reply with Quote
welcome

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

Go to Top of Page

Villanuev
Constraint Violating Yak Guru

478 Posts

Posted - 02/04/2013 :  04:06:49  Show Profile  Reply with Quote
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

India
52326 Posts

Posted - 02/04/2013 :  04:13:08  Show Profile  Reply with Quote
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 - 02/04/2013 :  04:17:32  Show Profile  Reply with Quote
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

India
52326 Posts

Posted - 02/04/2013 :  04:22:24  Show Profile  Reply with Quote
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 - 02/04/2013 :  04:27:37  Show Profile  Reply with Quote
Thank You.
Go to Top of Page

Villanuev
Constraint Violating Yak Guru

478 Posts

Posted - 02/04/2013 :  21:54:57  Show Profile  Reply with Quote

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

India
52326 Posts

Posted - 02/04/2013 :  22:54:58  Show Profile  Reply with Quote
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 - 02/05/2013 :  01:09:36  Show Profile  Reply with Quote
Hi Visakh.

Thank you for the reply.

I got this error.

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


Edited by - Villanuev on 02/05/2013 01:10:27
Go to Top of Page

Villanuev
Constraint Violating Yak Guru

478 Posts

Posted - 02/05/2013 :  01:13:51  Show Profile  Reply with Quote
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

India
52326 Posts

Posted - 02/05/2013 :  02:29:30  Show Profile  Reply with Quote
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
  Previous Topic Topic Next Topic  
 Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.11 seconds. Powered By: Snitz Forums 2000