December 15, 2014 at 2:42 pm
I need to perform some operations that seem to require multiple nested tables.
Is the following code the way I should be attempting this or is there a better way?
Here is the sample code:
SELECT tt.tdl_ID, tt.tx_ID, adj.Prof_Chgs, rc.Rvu_Comp
FROM Table1 as tt
LEFT JOIN (
SELECT tt1.Tdl_Id,
CASE
WHEN tt1.tdl_ID = '1000'
THEN tt1.Rvu_Work
ELSE '0'
END as Rvu_Comp
FROM Table1 as TT1)
as RC ON tt.Tdl_Id = rc.Tdl_Id
LEFT JOIN (
SELECT tt2.Tdl_Id,
CASE
WHEN tt2.Dept_Id = '334'
THEN ((rc1.Rvu_Comp)*100)
ELSE '0'
END as Prof_Chgs
FROM Table1 as TT2
LEFT JOIN (
SELECT tt3.Tdl_Id,
CASE
WHEN tt3.tdl_ID = '1000'
THEN tt3.Rvu_Work
ELSE '0'
END as Rvu_Comp
FROM Table1 as TT3)
as RC1 ON tt2.Tdl_Id = rc1.Tdl_Id)
as ADJ ON tt1.Tdl_Id = adj.Tdl_Id
Thank you for any feedback you have.
December 15, 2014 at 4:18 pm
You can try using common table expressions.
However, your example shows that you're using the same table over and over again joined by the same key. That's only a recipe for disaster, unless it's part of code obfuscation.
December 15, 2014 at 5:01 pm
Thank you for your response.
I was able to create a CTE for the first part of my code.
You indicated that I should not use Table1.TDL_ID as a key for everything else.
I am simply trying to tie all records back to that primary key. From what you say, I am obviously doing it wrong.
Can you point me in the direction I need to go to do it correctly?
Thank you.
December 15, 2014 at 5:28 pm
Based on the logic that I can understand from your query, you wouldn't even need derived tables. Look at how I'm posting DDL and sample data, you're supposed to post it that way when you ask for help. It's mostly to give us an idea on what kind of problems we can face and you should post the expected results based on the sample data so we can be able to ensure that the output is correct.
CREATE TABLE Table1( Tdl_Id int,
Tx_Id int,
Rvu_Work int,
Dept_Id int );
INSERT INTO Table1
VALUES
( 1, 2, 3, 4 ),
( 1, 2, 3, 334 ),
( 1000, 2, 3, 334 ),
( 1000, 2, 3, 5 );
SELECT Tt.Tdl_Id,
Tt.Tx_Id,
CASE
WHEN Tt.Dept_Id = 334 AND Tt.Tdl_Id = 1000
THEN Tt.Rvu_Work * 100
ELSE 0
END AS Prof_Chgs,
CASE
WHEN Tt.Tdl_Id = 1000 THEN Tt.Rvu_Work
ELSE 0
END AS Rvu_Comp
FROM Table1 AS Tt;
DROP TABLE Table1;
December 15, 2014 at 5:31 pm
Note how the duplicates in the column Tdl_Id generate more rows with your code. You can also check the difference between execution plans (which correlates directly to the code).
December 15, 2014 at 5:44 pm
Edited out
December 16, 2014 at 9:56 am
It seems that you edited your reply. I wonder if it was due to security/privacy concerns.
I'm not sure what the issue was as it seems that it should work as you posted it. You could also read about APPLY and how you can use it to use correlated derived tables.
December 16, 2014 at 11:00 am
Thank you very much for your assistance.
Implementing CTEs enabled me to complete the solution.
I am always trying to find the "correct" way to write SQL code and I thank you for your input.
December 16, 2014 at 11:18 am
It's good to know that you found a solution to your problem.
Remember that there's not a "one-code-to-rule-them-all" and you need to test possible approaches to find out which one is better for each occasion.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply