Subquery Result
-
Hi ,
I need to use the same subquery result more then once in a select statement.
I also need to use it in the "where" section.
I know I can use it in the "order by" section (using alias) ....
Any idea ?
Yaniv
-
A little more detail would help. Perhaps some sample SQL of what you want to do.
If the phone doesn't ring...It's me.
-
Hm... since you didn't provide any info about your query, it is rather complicated to find a solution. What about this (sorry for the pseudo-SQL, but you didn't supply anything to go on):
SELECT Q.col_a, Q.col_b, Q.col_c
FROM
(SELECT col_a, col_b, (select ... your subquery) as col_c
FROM sometable JOIN someothertable...) AS Q
WHERE Q.col_c = yourcondition
ORDER BY col_c
HTH, Vladan
-
Here is an example of what I mean :
SELECT
(
SELECT RIGHT(CM_SADE,5) AS TACHAZIT FROM CRM_CFM
WHERE CM_KOVETZ = 2 AND CM_SUG = 1 AND CM_MAF_MAKOR = MT_NUMERATOR AND CM_SHURA = 5
 
AS AMOUNT1 ,(
SELECT RIGHT(CM_SADE,5) FROM CRM_CFM
WHERE CM_KOVETZ = 2 AND CM_SUG = 1 AND CM_MAF_MAKOR = MT_NUMERATOR AND CM_SHURA = 1
 
AS AMOUNT2FROM CRM_MTL
I need 3 things :
1.The difference between AMOUNT1 and AMOUNT2.
2.The difference between AMOUNT1 and AMOUNT2 in presentage.
3,.Use the result (of 1 and 2) in the "where" section.
Yaniv
-
I don't know how you wish to calculate percentage, but you can easily change the formula so that it suits your needs. And I suppose there are some columns from the CRM_MTL table in the actual query which you skipped to simplify the query, since otherwise it wouldn't make sense to build this select over CRM_MTL. OK, this should work, though I didn't have time to create test data and verify it.
SELECT Q.amount1 - Q.amount2 as diff, (Q.amount1 - Q.amount2)/Q.amount1 as percentage
FROM
(
SELECT
(SELECT RIGHT(CM_SADE,5) AS TACHAZIT FROM CRM_CFM
WHERE CM_KOVETZ = 2 AND CM_SUG = 1 AND CM_MAF_MAKOR = MT_NUMERATOR AND CM_SHURA = 5) AS AMOUNT1 ,
(SELECT RIGHT(CM_SADE,5) FROM CRM_CFM
WHERE CM_KOVETZ = 2 AND CM_SUG = 1 AND CM_MAF_MAKOR = MT_NUMERATOR AND CM_SHURA = 1) AS AMOUNT2
FROM CRM_MTL
) AS Q
WHERE Q.amount1 - Q.amount2 > 10 AND (Q.amount1 - Q.amount2)/Q.amount1 < 0.6
-
I'm not sure I can use it.....
I also need columns from CRM_MTL .....
-
That's not a problem, just add them to the inner query (aliased Q) and then select all you need from Q. If you have any problems, please post the whole query and DDL (CREATE TABLE for all the tables involved) if possible - and specify what precisely is your problem. So far, it seems to me that you should be able to get your result this way, but maybe I've overlooked something.
-
The following partial example only has one pass through your CRM_CFM table, which should perform faster...
SELECT
CASE
WHEN CM_SHURA = 5 THEN RIGHT(CM_SADE,5)
else 0 end as amount1,
CASE
WHEN CM_SHURA = 1 THEN RIGHT(CM_SADE,5)
else 0 end as amount2
FROM CRM_CFM
WHERE CM_KOVETZ = 2 AND CM_SUG = 1 AND CM_MAF_MAKOR = MT_NUMERATOR AND CM_SHURA in( 1,5)
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
-
It seems to me that this would work best as a join.
SELECT CRM_MTL.Whatever
, RIGHT(Amount1.CM_SADE,5) - RIGHT(Amount2.CM_SADE,5) AS Difference
, ( RIGHT(Amount1.CM_SADE,5) - RIGHT(Amount2.CM_SADE,5)) / RIGHT(Amount1.CM_SADE,5) AS Percentage
FROM CRM_MTL
LEFT JOIN CRM_CFM Amount1
ON Amount1.CM_KOVETZ = 2
AND Amount1.CM_SUG = 1
AND Amount1.CM_MAF_MAKOR = MT_NUMERATOR
AND Amount1.CM_SHURA = 5
LEFT JOIN CRM_CFM Amount2
ON Amount2.CM_KOVETZ = 2
AND Amount2.CM_SUG = 1
AND Amount2.CM_MAF_MAKOR = MT_NUMERATOR
AND Amount2.CM_SHURA = 5
WHERE ( RIGHT(Amount1.CM_SADE,5) - RIGHT(Amount2.CM_SADE,5)) / RIGHT(Amount1.CM_SADE,5) > 0.5
Of course, the join conditions above were just a guess. You may need to filter on NULL CM_SADE in the where clause or handle NULL values within the math depending on whether or not you can guarentee matching values. You may want or need to do explicite type conversions from varchar (the result of RIGHT) to whatever numeric type is appropriate.
-
Hi Vladan ,
I tryed what you said.
It works.
Thanks
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply