SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2012 Forums
 Transact-SQL (2012)
 query help
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

egemen_ates
Yak Posting Veteran

Turkey
76 Posts

Posted - 03/13/2013 :  11:04:31  Show Profile  Reply with Quote
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
52325 Posts

Posted - 03/13/2013 :  11:09:07  Show Profile  Reply with Quote
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

Turkey
76 Posts

Posted - 03/13/2013 :  11:52:57  Show Profile  Reply with Quote
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

India
52325 Posts

Posted - 03/13/2013 :  11:59:37  Show Profile  Reply with Quote
welcome

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

Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000