Syntax error - 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)

Syntax error

Author

Topic

satya068
Posting Yak Master

233 Posts

Posted - 2010-03-25 : 12:03:09

Hi..

could you pls tell where i have done wrong

SELECT RDB_PAS_APPOINTMENTS AS TABLE_NAME,Sum(case when(getdate() >= CONVERT(datetime,'25/03/2010 00:00:00') then 1 else 0 end) as INSERT_COUNT FROM RDB_PAS_APPOINTMENTS group by RDB_PAS_APPOINTMENTS

error message is
Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'then'.

thanx

Satya

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-25 : 12:07:19

quote:
Originally posted by satya068

Hi..

could you pls tell where i have done wrong

SELECT RDB_PAS_APPOINTMENTS AS TABLE_NAME,Sum(case when(getdate() >= CONVERT(datetime,'25/03/2010 00:00:00') then 1 else 0 end) as INSERT_COUNT FROM RDB_PAS_APPOINTMENTS group by RDB_PAS_APPOINTMENTS

error message is
Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'then'.

thanx

Satya

remove the unnecessary (

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

satya068
Posting Yak Master

233 Posts

Posted - 2010-03-25 : 12:09:52

thanx visakh

it worked.

Satya

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-25 : 12:20:12

welcome

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

satya068
Posting Yak Master

233 Posts

Posted - 2010-03-25 : 12:30:13

Hi..

there is another error whis is invalid column could you plz tell me!

Msg 207, Level 16, State 1, Line 1
Invalid column name 'RDB_PAS_APPOINTMENTS'.
Msg 207, Level 16, State 1, Line 1
Invalid column name 'RDB_PAS_APPOINTMENTS'.

SELECT RDB_PAS_APPOINTMENTS AS TABLE_NAME,Sum(case when getdate() >= CONVERT(datetime,'25/03/2010 00:00:00') then 1 else 0 end) as INSERT_COUNT FROM RDB_PAS_CLINICS group by RDB_PAS_APPOINTMENTS

thanx

Satya

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-25 : 12:32:32

do you have column RDB_PAS_APPOINTMENTS in RDB_PAS_CLINICS table?

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

satya068
Posting Yak Master

233 Posts

Posted - 2010-03-25 : 12:34:08

SORRY VISAHK
THIS THE ACTUAL SCRIPT

SELECT RDB_PAS_APPOINTMENTS AS TABLE_NAME,Sum(case when getdate() >= CONVERT(datetime,'25/03/2010 00:00:00') then 1 else 0 end) as INSERT_COUNT FROM RDB_pas_APPOINTMENTS group by RDB_PAS_APPOINTMENTS

Msg 207, Level 16, State 1, Line 1
Invalid column name 'RDB_PAS_APPOINTMENTS'.
Msg 207, Level 16, State 1, Line 1
Invalid column name 'RDB_PAS_APPOINTMENTS'.

Satya

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-25 : 12:37:17

what you've column and table names same?

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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-25 : 12:37:49

whats your table and whats the column you want to group on?

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

DBA in the making
Aged Yak Warrior

638 Posts

Posted - 2010-03-25 : 12:41:13

Another thing you've done wrong, you've laid your code out so poorly, that relatively simple syntax errors are more difficult to spot. Do yourself a favour, and take the time to lay your code out neatly. Something like this:


SELECT	RDB_PAS_APPOINTMENTS AS TABLE_NAME, 
		Sum(case 
			when getdate() >= CONVERT(datetime,'25/03/2010 00:00:00') then 1 
			else 0 
			end) as INSERT_COUNT 
FROM RDB_PAS_APPOINTMENTS 
GROUP BY RDB_PAS_APPOINTMENTS

It makes your code easier to read, and therefore, syntax errors easier to spot. Also, you don't need to convert the date string to a DATETIME type.

There are 10 types of people in the world, those that understand binary, and those that don't.

satya068
Posting Yak Master

233 Posts

Posted - 2010-03-25 : 12:41:28

COLIMN NAME IS INSERT_DATE
TABLE NAME IS RDB_PAS_APPOINTMENTS

I NEED COUNT FOR THE INSERT_DATE COLUMN FROM THE RDB_PAS_APPOINTMENTS
INSERT_DATE>=SYSDATE()

WANT COUNT FOR INSERT_DATE FOR KNOWING no of records inserted today.

thanks

Satya

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-25 : 12:45:26

then this is enough


SELECT count(*) as INSERT_COUNT FROM RDB_pas_APPOINTMENTS WHERE INSERT_DATE >= DATEADD(dd,DATEDIFF(dd,0,GETDATE()),0)
AND INSERT_DATE < DATEADD(dd,DATEDIFF(dd,0,GETDATE()),1)

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

satya068
Posting Yak Master

233 Posts

Posted - 2010-03-25 : 12:55:29

Visakh,,

it worked.

thanx for ur great help.

Satya

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-25 : 13:00:30

welcome

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

satya068
Posting Yak Master

233 Posts

Posted - 2010-03-25 : 13:01:54

Visakh..

when i used seperately the data is abe to count from RDB table,why its not reading when i used this script in the stored procedure.
its only counting RECORD_COUNT then its stopping its not procedding to INSERT_COUNT and UPDATE_COUNT

THIS IS MY SCRIPT

use PAS_RDB
DECLARE @TableName sysname,
@SQL nvarchar(max),
@INSERT_DATE datetime,
@UPDATE_DATE datetime,
@INSERT_COUNT int,
@UPDATE_COUNT int

DECLARE @TODAY VARCHAR(10)
SET @TODAY = CONVERT(varchar(10), getdate(), 121)

DECLARE @DATABASE varchar(255)
TRUNCATE TABLE RECORD_COUNT
TRUNCATE TABLE INSERT_COUNT
TRUNCATE TABLE UPDATE_COUNT
DECLARE table_cursor CURSOR FAST_FORWARD
FOR
SELECT name FROM sys.tables
OPEN table_cursor

FETCH NEXT FROM table_cursor INTO @TableName
WHILE @@FETCH_STATUS = 0

BEGIN

SELECT @SQL =
'Insert into RECORD_COUNT (TABLE_NAME, ROW_COUNT) SELECT ''' + @TableName + '''as TABLE_NAME, COUNT(*)as ROWS_COUNT ' +

'FROM ['+ @TableName+']'

print @sql

EXEC SP_EXECUTESQL @SQL
SELECT @SQL =

'insert into INSERT_COUNT (TABLE_NAME, NO_OF_INSERTS)
SELECT ''' + @TableName + '''as TABLE_NAME,Sum(case when(' + convert(varchar, @INSERT_DATE )+ ' >= convert(varchar, @TODAY) then 1 else 0 end) as INSERT_COUNT' +
'FROM [' + @TableName + ']''group by ' + @tablename + ''

print @sql
EXEC SP_EXECUTESQL @SQL

SELECT @SQL =

'insert into UPDATE_COUNT (TABLE_NAME, NO_OF_INSERTS)
SELECT ''' + @TableName + '''as TABLE_NAME,Sum(case when(' + convert(varchar,@UPDATE_DATE ) + ' >= convert(varchar, @TODAY ) then 1 else 0 end) as INSERT_COUNT' +
'FROM [' + @TableName + ']''group by ' + @tablename + ''

print @sql
EXEC SP_EXECUTESQL @SQL

FETCH NEXT FROM table_cursor INTO @TableName

END
CLOSE table_cursor
DEALLOCATE table_cursor

IF (Object_ID(N'TOTAL_RECORD_COUNT')IS NOT NULL)
DROP TABLE TOTAL_RECORD_COUNT
go
SELECT RECORD_COUNT.TABLE_NAME,RECORD_COUNT.ROW_COUNT,INSERT_COUNT.NO_OF_INSERTS,UPDATE_COUNT.NO_OF_UPDATES
INTO TOTAL_RECORD_COUNT
from
RECORD_COUNT
left outer join INSERT_COUNT on RECORD_COUNT.TABLE_NAME = INSERT_COUNT.TABLE_NAME
left outer join UPDATE_COUNT on RECORD_COUNT.TABLE_NAME = UPDATE_COUNT.TABLE_NAME

Satya

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-25 : 13:14:01

may be you dont have matching records in them and left join is returning NULL?

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources