# Calculating price with a sub query

So I have this sub query:

*(select p.value1 From Params as p where p.PAR_Field = 'Taxe' and p.PAR_KEY = 'TVA')*


And I was using it 3 times to get the same value.

In this SELECT statement:

Select a, b, c, d,
price *(select p.value1 From Params as p where p.PAR_Field = 'Taxe' and p.PAR_KEY = 'TVA')/100 as RealTVA,
price+price*(select p.value1 From Params as p where p.PAR_Field = 'Taxe' and p.PAR_KEY = 'TVA')/100 as RealPuTTc,
Qte*(price+price*(select p.value1 From Params as p where p.PAR_Field = 'Taxe' and p.PAR_KEY = 'TVA')/100 As RealPtTTC,
price*(select p.value1 From Params as p where p.PAR_Field = 'Taxe' and p.PAR_KEY = 'TVA')/100 as RealTVA

From  myTable
left outer join myTable2
on mt_Key = Mt2_mt_key


I would like to take the sub query out, to make the code sexier. I was planning to use a CTE(because im tired), but now I dont see how. If you have any idea, please tell me.

Is there a better way? in a select context // in a Create view context?

• perhaps with the edited sql, you can see where your sub-queries are. Do you understand what a sub query and a CTE are? It doesn't look to me like you need a CTE in your case. – Gareth Jul 11 '16 at 15:10
• Please don't edit a "solution" into your original question (see what you can and cannot do when someone answers) - having multiple revisions of the code makes the Q&A hard to follow, and becomes messy when future answers address a newer revision. – Mathieu Guindon Jul 12 '16 at 15:11
• Done. Solution is now in a solution post not in the main post. I get messy because the question and the problem is not clear when the question is asked even for the one asking it. More time you spend explaining your question, more time you spend understanding your problem. And i have a really bad formating problem. As i know my code i have no issue reading it. But i understand why ppl was to lazy to read it now. I will make better post in the future. And i ll try to not get mad about been edited every 2 hours. – Drag and Drop Jul 13 '16 at 9:05

You have the same column twice, I think I would start there in your quest to make your SQL "sexier" (I probably wouldn't use that term in a professional environment [i.e. interviews]).

 price*(select p.value1 From Params as p where p.PAR_Field = 'Taxe' and p.PAR_KEY = 'TVA')/100 as RealTVA


Instead of using a CTE you probably want to create a variable and assign the single value to the variable and then use the variable in the query

I would also suggest using more parentheses in order to assure that you are performing the mathematical operations in the correct order.

I made some assumptions in placing these Parentheses

DECLARE @Tax AS INTEGER
SET @Tax = SELECT p.value1 FROM Params AS p WHERE p.PAR_Field = 'Taxe' AND p.PAR_KEY = 'TVA'

SELECT a
, b
, c
, d
, price * (@Tax / 100) AS RealTVA
, price + (price * (@Tax / 100)) AS RealPuTTc
, Qte * (price + (price * (@Tax / 100)) AS RealPtTTC
FROM  myTable
LEFT OUTER JOIN myTable2
ON mt_Key = Mt2_mt_key


I also used capital letters for the SQL Keywords, a lot of developers use Capital Case, but a lot don't, this is preference.

I also gave your operators some space so that I could see what was going on there.

Your naming makes it seem as though this is example code or that you are changing the names to protect the innocent. the only names that look real are one that are not obvious what they mean, that is not good for reading your code later, long names are not always bad.

• Thx Malachi. You deserve a medal! I will use you way to format SQL every time its so mutch clearer than mine. <3 – Drag and Drop Jul 12 '16 at 14:54

I'm creating a View with this query so I can't use a variable. Because in a view the select can't be a reference to a temporary table or a table variable.

So this is my solution :

SELECT a
, b
, c
, d
, price * (value1/ 100) AS RealTVA
, price + (price * (value1/ 100)) AS RealPuTTc
, Qte * (price + (price * (value1/ 100)) AS RealPtTTC
FROM  myTable
LEFT OUTER JOIN myTable2
ON mt_Key = Mt2_mt_key
FULL OUTER JOIN Params
ON PAR_Field = 'Taxe' and PAR_KEY = 'TVA'

• You should have mentioned this in your question. – Phrancis Jul 14 '16 at 11:18
• So many edit on the main post that went from -12 to -2 in a single night with a roll back to the 1rst version after every one of my edit. But you are right I should have. Do i have to edit my Op, to change te rules and make Malachi answer invalid ? Should i Edit my answer to to combine both context n make this basicaly his post + mine ? If some one make a merged answer do i have to give him the answered token ? – Drag and Drop Jul 15 '16 at 6:48
• Generally, you would not edit your question any more after you have received answers. You may ask a new question instead, if you wish. As for accepting an answer, that is up to you which one you choose to accept. – Phrancis Jul 15 '16 at 15:14