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-06-12 : 03:52:40

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

52326 Posts

Posted - 2013-06-12 : 03:56:08

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

egemen_ates
Yak Posting Veteran

76 Posts

Posted - 2013-06-12 : 04:07:34

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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-06-12 : 04:23:26

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources