| Author |
 Topic  |
|
|
egemen_ates
Yak Posting Veteran
Turkey
63 Posts |
 Posted - 03/13/2013 : 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
India
47080 Posts |
Posted - 03/13/2013 : 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
Turkey
63 Posts |
Posted - 03/13/2013 : 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
India
47080 Posts |
Posted - 03/13/2013 : 11:59:37
|
welcome
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
|
|
| |
Topic |
|
|
|
query help
