Return to Question

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?

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?

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 request:

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

So i asked :

• I want to get the sub query out to make the code sexier.
• I was planning to use a CTE but now i dont see how.
• If you have any idea, please tell me.

It was not a Write me a CTE or do my job as 5 guys for a total of 57k reputation thinks. Buts its ok cause some one readed the post ann answered "Use a Parameters!" Every one must Upvote him for understanding My 1rst Post! There must be a medal for this!

Thanks you for your time. regards,

PIERRE LEBON

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?

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, but now I dont see how. If you have any idea, please tell me.

Is there a better way?

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 request:

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

So i asked :

• I want to get the sub query out to make the code sexier.
• I was planning to use a CTE but now i dont see how.
• If you have any idea, please tell me.

It was not a Write me a CTE or do my job as 5 guys for a total of 57k reputation thinks. Buts its ok cause some one readed the post ann answered "Use a Parameters!" Every one must Upvote him for understanding My 1rst Post! There must be a medal for this!

Thanks you for your time. regards,

PIERRE LEBON