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 - 06/12/2013 :  03:52:40  Show Profile  Reply with Quote
How can i write query another way for performance? thank you

SELECT
CAST( I1.INTERNAL_ID AS NVARCHAR) + '*' + I1.INTERNAL_NUMBER INTERNAL_INFO
FROM
INTERNALDEMAND I1,
INTERNALDEMAND_ROW IR1
WHERE
I1.INTERNAL_ID = IR1.I_ID AND
ISNULL(I1.DEMAND_TYPE,0) = 0 AND
IR1.WRK_ROW_ID = I_ROW.WRK_ROW_RELATION_ID

UNION ALL

SELECT
CAST( I2.INTERNAL_ID AS NVARCHAR) + '*' + I2.INTERNAL_NUMBER INTERNAL_INFO
FROM
INTERNALDEMAND I2,
INTERNALDEMAND_ROW IR2
WHERE
I2.INTERNAL_ID = IR2.I_ID AND
ISNULL(I2.DEMAND_TYPE,0) = 0 AND
IR2.WRK_ROW_ID IN ( SELECT
IR1.WRK_ROW_RELATION_ID
FROM
INTERNALDEMAND I1,
INTERNALDEMAND_ROW IR1
WHERE
I1.INTERNAL_ID = IR1.I_ID AND
IR1.WRK_ROW_ID = I_ROW.WRK_ROW_RELATION_ID
)

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 06/12/2013 :  03:56:08  Show Profile  Reply with Quote
The posted query is not even synatctically correct. I_ROW is used as an alias however i cant even see any table referred by that alias.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

egemen_ates
Yak Posting Veteran

Turkey
76 Posts

Posted - 06/12/2013 :  04:07:34  Show Profile  Reply with Quote
SELECT

(
SELECT
CAST( I1.INTERNAL_ID AS NVARCHAR) + '*' + I1.INTERNAL_NUMBER INTERNAL_INFO
FROM
INTERNALDEMAND I1,
INTERNALDEMAND_ROW IR1
WHERE
I1.INTERNAL_ID = IR1.I_ID AND
ISNULL(I1.DEMAND_TYPE,0) = 0 AND
IR1.WRK_ROW_ID = I_ROW.WRK_ROW_RELATION_ID

UNION ALL

SELECT
CAST( I2.INTERNAL_ID AS NVARCHAR) + '*' + I2.INTERNAL_NUMBER INTERNAL_INFO
FROM
INTERNALDEMAND I2,
INTERNALDEMAND_ROW IR2
WHERE
I2.INTERNAL_ID = IR2.I_ID AND
ISNULL(I2.DEMAND_TYPE,0) = 0 AND
IR2.WRK_ROW_ID IN ( SELECT
IR1.WRK_ROW_RELATION_ID
FROM
INTERNALDEMAND I1,
INTERNALDEMAND_ROW IR1
WHERE
I1.INTERNAL_ID = IR1.I_ID AND
IR1.WRK_ROW_ID = I_ROW.WRK_ROW_RELATION_ID
) )

FROM
INTERNALDEMAND I LEFT JOIN
PRO_WORKS PW ON I.WORK_ID = PW.WORK_ID ,
STOCKS S, INTERNALDEMAND_ROW I_ROW
LEFT JOIN workcube_cf.dbo.EMPLOYEES E ON E.EMPLOYEE_ID=I_ROW.BASKET_EMPLOYEE_ID WHERE I.INTERNAL_ID > 0 AND DEMAND_TYPE=1 AND I.INTERNAL_ID = I_ROW.I_ID AND I_ROW.STOCK_ID = S.STOCK_ID AND I.INTERNALDEMAND_STAGE IN (527) AND I.IS_ACTIVE = 1



quote:
Originally posted by visakh16

The posted query is not even synatctically correct. I_ROW is used as an alias however i cant even see any table referred by that alias.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs


Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 06/12/2013 :  04:23:26  Show Profile  Reply with Quote
Sorry cant suggest much without knowing anything about your tables, requirement etc
Please post some sample data from the tables in below format and explain what you want as output from them

http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
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.05 seconds. Powered By: Snitz Forums 2000