Query - 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 2005 Forums

Transact-SQL (2005)

Query

Author

Topic

cplusplus
Aged Yak Warrior

567 Posts

Posted - 2008-05-13 : 22:45:39

I am getting error message(incorrect syntax near select):

*******************************************************
Declare @COID int
DECLARE @ErrorCode int
DECLARE @TransactionCountOnEntry int
DECLARE @ParGCostHour int
DECLARE @intCount int

SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
SELECT @ErrorCode = @@Error, @TransactionCountOnEntry = @@TranCount
IF @ErrorCode <>0
BEGIN
SET NOCOUNT OFF
RETURN @ErrorCode
END

Create #TempCNID(CNID as int)

SET @ParGCostHour = SELECT parametertext from tab_ccsnetparameters where parameter = 'gCOSTHRS'

Insert into #TempCNID
SELECT ModuleRecordID from TAB_ccsNetModuleLinks
WHERE ModuleName = 'CN' AND LinkModuleRecordID = @COID AND LinkModuleName = 'CO'

Insert into #TempCNID
SELECT LinkModuleRecordID from TAB_ccsNetModuleLinks
WHERE LinkModuleName = 'CN' AND ModuleRecordID = @COID AND ModuleName = 'CO'

Select task.taskcode, cost.payitemdescription, cost.amount, cost.quantity from tab_ccsnetcost cost,
tab_ccsnettasks task where cost.taskid = task.taskid and
cost.MetricType = @ParGCostHour and modulerecordid in (select CNID from #TempCNID) and ModuleName = 'CN'

Drop table #TempCNID

*******************************************************

Thank you for the helpful information.

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2008-05-14 : 01:29:40

When setting a variable, you need to put the select in parenthesis.

SET @ParGCostHour = (SELECT parametertext from tab_ccsnetparameters where parameter = 'gCOSTHRS')

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-05-14 : 02:52:33

No need for temp tables nor intermediate variables.

SELECT		task.taskcode,
		cost.payitemdescription,
		cost.amount,
		cost.quantity
FROM		tab_ccsnetcost AS cost
INNER JOIN	tab_ccsnettasks AS task ON task.taskid = cost.taskid
INNER JOIN	tab_ccsnetparameters AS a ON a.parametertext = cost.metrictype
INNER JOIN	(
			SELECT	ModuleRecordID AS CNID
			FROM	TAB_ccsNetModuleLinks
			WHERE	ModuleName = 'CN'
				AND LinkModuleRecordID = @COID
				AND LinkModuleName = 'CO'

			UNION ALL

			SELECT	LinkModuleRecordID
			FROM	TAB_ccsNetModuleLinks
			WHERE	LinkModuleName = 'CN'
				AND ModuleRecordID = @COID
				AND ModuleName = 'CO'
		) AS b ON b.CNID = modulerecordid
WHERE		a.parameter = 'gCOSTHRS'
		AND ModuleName = 'CN'

E 12°55'05.25"
N 56°04'39.16"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources