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/

Go to Top of Page

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/



Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

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

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

Go to Top of Page
   

- Advertisement -