SQL Server Forums
Profile | Register | 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
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Villanuev
Constraint Violating Yak Guru

440 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

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

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

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

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

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

440 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

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

440 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

440 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
52309 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  
 New 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.08 seconds. Powered By: Snitz Forums 2000