query help - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2012 Forums

Transact-SQL (2012)

query help

Author

Topic

egemen_ates
Yak Posting Veteran

76 Posts

Posted - 2013-03-13 : 11:04:31

How can i write this query using another way ? i dont want using "in" operator for this query performance ,

SELECT
STOCK_ID,
PRODUCT_ID,
SPECT_VAR_ID,
ORDER_ID,
STOCK_STRATEGY_ID,
SUM(RESERVE_STOCK_IN) RESERVE_STOCK_IN,
SUM(RESERVE_STOCK_OUT) RESERVE_STOCK_OUT,
SUM(STOCK_IN) STOCK_IN,
SUM(STOCK_OUT) STOCK_OUT
FROM
TEST_TABLE
WHERE
(
ORDER_WRK_ROW_ID IS NULL
OR
(INVOICE_ID IS NULL AND SHIP_ID IS NULL)
OR
(
(INVOICE_ID IS NOT NULL OR SHIP_ID IS NOT NULL) AND
ORDER_WRK_ROW_ID IS NOT NULL AND
ORDER_WRK_ROW_ID IN(SELECT ORR.ORDER_WRK_ROW_ID FROM TEST_TABLE ORR WHERE ORR.ROW_RESERVED_ID <> TEST_TABLE.ROW_RESERVED_ID)
)
)
GROUP BY
STOCK_ID,
PRODUCT_ID,
SPECT_VAR_ID,
ORDER_ID,
STOCK_STRATEGY_ID

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-03-13 : 11:09:07

use EXISTS


SELECT 
STOCK_ID,
PRODUCT_ID,
SPECT_VAR_ID,
ORDER_ID,
STOCK_STRATEGY_ID,
SUM(RESERVE_STOCK_IN) RESERVE_STOCK_IN,
SUM(RESERVE_STOCK_OUT) RESERVE_STOCK_OUT,
SUM(STOCK_IN) STOCK_IN,
SUM(STOCK_OUT) STOCK_OUT
FROM
TEST_TABLE
WHERE
(
ORDER_WRK_ROW_ID IS NULL
OR
(INVOICE_ID IS NULL AND SHIP_ID IS NULL) 
OR 
(
(INVOICE_ID IS NOT NULL OR SHIP_ID IS NOT NULL) AND
ORDER_WRK_ROW_ID IS NOT NULL AND 
EXISTS (SELECT 1  FROM TEST_TABLE ORR WHERE ORR.ROW_RESERVED_ID <> TEST_TABLE.ROW_RESERVED_ID AND TEST_TABLE.ORDER_WRK_ROW_ID = ORR.ORDER_WRK_ROW_ID)	
)
)
GROUP BY
STOCK_ID,
PRODUCT_ID,
SPECT_VAR_ID,
ORDER_ID,
STOCK_STRATEGY_ID

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

egemen_ates
Yak Posting Veteran

76 Posts

Posted - 2013-03-13 : 11:52:57

You are perfect,thank you visakh16

quote:

Originally posted by visakh16

use EXISTS


SELECT 
STOCK_ID,
PRODUCT_ID,
SPECT_VAR_ID,
ORDER_ID,
STOCK_STRATEGY_ID,
SUM(RESERVE_STOCK_IN) RESERVE_STOCK_IN,
SUM(RESERVE_STOCK_OUT) RESERVE_STOCK_OUT,
SUM(STOCK_IN) STOCK_IN,
SUM(STOCK_OUT) STOCK_OUT
FROM
TEST_TABLE
WHERE
(
ORDER_WRK_ROW_ID IS NULL
OR
(INVOICE_ID IS NULL AND SHIP_ID IS NULL) 
OR 
(
(INVOICE_ID IS NOT NULL OR SHIP_ID IS NOT NULL) AND
ORDER_WRK_ROW_ID IS NOT NULL AND 
EXISTS (SELECT 1  FROM TEST_TABLE ORR WHERE ORR.ROW_RESERVED_ID <> TEST_TABLE.ROW_RESERVED_ID AND TEST_TABLE.ORDER_WRK_ROW_ID = ORR.ORDER_WRK_ROW_ID)	
)
)
GROUP BY
STOCK_ID,
PRODUCT_ID,
SPECT_VAR_ID,
ORDER_ID,
STOCK_STRATEGY_ID

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-03-13 : 11:59:37

welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources