April 26, 2012 at 3:18 pm
Is their a slick way that I can check the condition of a field and return multiple discrete fields like a begin end block in a select statment
select wakka, wakka2, wakka3,
CASE WHEN ft.Payment_ID IS NOT NULL THEN p.TransID ELSE r.TransID END AS TransID,
CASE WHEN ft.Payment_ID IS NOT NULL THEN p.Batch_ID ELSE r.Batch_ID END AS Batch_ID,
CASE WHEN ft.Payment_ID IS NOT NULL THEN pm.Method ELSE rm.Method END AS Method,
CASE WHEN ft.Payment_ID IS NOT NULL THEN p.MethodNumber ELSE r.MethodNumber END AS MethodNumber,
CASE WHEN ft.Payment_ID IS NOT NULL THEN p.DepositNumber ELSE r.DepositNumber END AS DepositNumber,
Trying to figure out if there is a faster way to do the five things listed above without having to check the condition 5 times.
April 26, 2012 at 4:49 pm
It's kind of tough to visualize with no DDL, sample data and desired results to infer your requirement and test code against...can you provide that?
Some guesses though...initial thought was two queries and a UNION ALL where one query had WHERE ft.Payment_ID IS NOT NULL and the other had WHERE ft.Payment_ID IS NULL, but that may give you more wakka than you want.
CROSS APPLY can add rows to a resultset with logic to produce the correct column values too...but like I said, without a testbed it's tough to visualize.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
April 26, 2012 at 4:56 pm
What i see from the CASE statemnet is that, based of the payment_id value, he is choosing from 2 different tables.. If thats the case, then i dont think there is any better way in doing it.
April 26, 2012 at 5:45 pm
ColdCoffee (4/26/2012)
What i see from the CASE statemnet is that, based of the payment_id value, he is choosing from 2 different tables.. If thats the case, then i dont think there is any better way in doing it.
from the looks of the case statement i think ColdCoffee is right. unless he wanted to go with dynamic SQL but that opens up a whole other can of worms which may not be needed since the multiple case statements is a way i have done a query in the past. it looked ugly but underneath was the most beautiful execution plan i could get.
For performance Issues see how we like them posted here: How to Post Performance Problems - Gail Shaw[/url]
Need to Split some strings? Jeff Moden's DelimitedSplit8K[/url]
Jeff Moden's Cross tab and Pivots Part 1[/url]
Jeff Moden's Cross tab and Pivots Part 2[/url]
April 26, 2012 at 7:13 pm
jwmott - It is rather annoying but I haven't figured out an easy way to do that either.
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
April 26, 2012 at 11:21 pm
There are a lot of people who can help you here. For that please post DDL, sample data and an example result set of what you are expecting so that they can find a solution for what you are trying to do.
Please help them help you. Its not going to help you if they don't understand what you have posted.
April 27, 2012 at 2:45 am
jwmott (4/26/2012)
Is their a slick way that I can check the condition of a field and return multiple discrete fields like a begin end block in a select statment
select wakka, wakka2, wakka3,
CASE WHEN ft.Payment_ID IS NOT NULL THEN p.TransID ELSE r.TransID END AS TransID,
CASE WHEN ft.Payment_ID IS NOT NULL THEN p.Batch_ID ELSE r.Batch_ID END AS Batch_ID,
CASE WHEN ft.Payment_ID IS NOT NULL THEN pm.Method ELSE rm.Method END AS Method,
CASE WHEN ft.Payment_ID IS NOT NULL THEN p.MethodNumber ELSE r.MethodNumber END AS MethodNumber,
CASE WHEN ft.Payment_ID IS NOT NULL THEN p.DepositNumber ELSE r.DepositNumber END AS DepositNumber,
Trying to figure out if there is a faster way to do the five things listed above without having to check the condition 5 times.
You wanted something like
select t1.col1,t1.col2,t1.col3,
if t1.col4 is null then
t2.col1,
t2.col2
else
t3.col1,
t3.col2,
?
The answer is: No. You cannot do this in t-sql
Using CASE WHEN 5-times is the right way, until you want to use dynamic sql (which is not really looks appropriate for your case)
April 27, 2012 at 9:59 am
These three give the same result:
USE AdventureWorks2008R2
GO
SELECT sp.BusinessEntityID AS BusinessEntityID,
CASE WHEN sp.TerritoryID IS NOT NULL THEN e.OrganizationLevel
ELSE sp.BusinessEntityID
END AS ContactID
FROM Sales.SalesPerson sp
JOIN HumanResources.Employee e ON sp.BusinessEntityID = e.BusinessEntityID
ORDER BY BusinessEntityID,
ContactID
SELECT sp.BusinessEntityID AS BusinessEntityID,
e.OrganizationLevel AS ContactID
FROM Sales.SalesPerson sp
JOIN HumanResources.Employee e ON sp.BusinessEntityID = e.BusinessEntityID
WHERE sp.TerritoryID IS NOT NULL
UNION ALL
SELECT sp.BusinessEntityID AS BusinessEntityID,
sp.BusinessEntityID AS ContactID
FROM Sales.SalesPerson sp
JOIN HumanResources.Employee e ON sp.BusinessEntityID = e.BusinessEntityID
WHERE sp.TerritoryID IS NULL
ORDER BY BusinessEntityID,
ContactID
SELECT sp.BusinessEntityID AS BusinessEntityID,
Contact.ContactID AS ContactID
FROM Sales.SalesPerson sp
JOIN HumanResources.Employee e ON sp.BusinessEntityID = e.BusinessEntityID
CROSS APPLY (
SELECT CASE WHEN sp.TerritoryID IS NOT NULL THEN e.OrganizationLevel
ELSE sp.BusinessEntityID
END
) Contact (ContactID)
ORDER BY BusinessEntityID,
ContactID
GO
The original and CROSS APPLY methods offer the same execution plan on my machine and both use the CASE expression. The plan for the UNION ALL did not look great comparatively speaking, but that doesn't always mean everything. While it appears to give the same result as the other two I did not test performance on large data.
Re: the plans between the original and the CROSS APPLY, I ran this on a lowly 1cpu/2core machine. On a box with more cores, with more data, and more uses of CROSS APPLY the QO may produce a plan with parallelism and you may see some performance gains (nod to Paul White for that tidbit), but the use of a CASE expression may be a limiting factor, not sure.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
April 27, 2012 at 10:28 am
Thanks everyone for taking the time and offering your expertise. I wasn't sure how much to post in the forum since the stored procedure that this comes from is over 3000 lines long. It is something that I have inherited and am trying to simplify, once I understand it all of course.
jwmott
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply