Error in tsql - 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 2008 Forums

Transact-SQL (2008)

Error in tsql

Author

Topic

Anand.A
Posting Yak Master

109 Posts

Posted - 2012-01-03 : 06:00:07

hi
i am having procedure in oracle now i want to convert this procedure to tsql by using SwisSQL converter i convert plsql to tsql
but i got a error how to solve this
this is a table

If Exists ( SELECT name
FROM sysobjects
WHERE name = '"DIM_DAY"'
AND type = 'U')
DROP TABLE "DIM_DAY"
GO
CREATE TABLE "DIM_DAY"
(
"DATE_SK" NUMERIC (8, 0) ,
"TIME_DATE" DATETIME ,
"DAY_NUMBER" NUMERIC ,
"DAY_NAME" VARCHAR (25) ,
"DAY_ABBREV" VARCHAR (10) ,
"LAST_DAY_IN_MONTH_FLAG" CHAR (1) ,
"MONTH_NUMBER" NUMERIC ,
"MONTH_NAME" VARCHAR (25) ,
"MONTH_ABBREV" VARCHAR (10) ,
"QUARTER_NUMBER" NUMERIC ,
"QUARTER_NAME" VARCHAR (25) ,
"QUARTER_YEAR" NUMERIC (8, 0) ,
"YEAR_NUMBER" NUMERIC ,
"YEAR_MONTH" NUMERIC (8, 0) ,
"DAY_OF_YEAR" NUMERIC ,
"WEEK_NUMBER" NUMERIC ,
"WEEK_START_DATE" DATETIME ,
"WEEK_END_DATE" DATETIME ,
"DAY_OF_WEEK" NUMERIC ,
"CREATED_DATETIME" DATETIME ,
"UPDATED_DATETIME" DATETIME
)

GO

procedure:

If Exists ( SELECT name
FROM sysobjects
WHERE name = 'LOAD_DIM_DAY'
AND type = 'P')
DROP PROCEDURE LOAD_DIM_DAY
GO
CREATE PROCEDURE LOAD_DIM_DAY
AS
BEGIN
SET NOCOUNT ON

DECLARE @adv_rowcount INT

DECLARE @adv_error INT

DECLARE @time_rec VARCHAR(200) /* SwisSQL (Oracle To SQL Server) : Table not found in Metadata or Metadata not Updated*/
DECLARE @curr_date DATETIME
DECLARE @start_date DATETIME
DECLARE @end_date DATETIME
DECLARE @to_year FLOAT
SET @to_year = CONVERT(NUMERIC(4), 2012)
DECLARE @from_year FLOAT
SET @from_year = CONVERT(NUMERIC(4), 2010)
DECLARE @P_row_count FLOAT
SELECT @P_row_count = 0
WHILE ( @from_year <= @to_year )
BEGIN --(

--p_year := from_year;

SELECT @start_date = CONVERT(DATETIME, @from_year + '0101', 112)
SELECT @end_date = CONVERT(DATETIME, @from_year + '1231', 112)
SELECT @curr_date = @start_date
WHILE ( @curr_date <= @end_date )
BEGIN --(

SELECT @P_row_count = @P_row_count + 1
SELECT @time_rec.date_sk = CONVERT(NUMERIC(8, 2), CONVERT(VARCHAR (23), @curr_date, 112))
SELECT @time_rec.created_datetime = DBO.ADV_getCurrentDate()
SELECT @time_rec.time_date = @curr_date
SELECT @time_rec.day_number = DATEPART(DD, @curr_date)
SELECT @time_rec.updated_datetime = DBO.ADV_getCurrentDate()
SELECT @time_rec.day_name = CONVERT(VARCHAR(23), @curr_date)
SELECT @time_rec.day_abbrev = CONVERT(VARCHAR(23), @curr_date)
IF ( DATEADD(D, -DAY(DATEADD(M, 1, @curr_date)), DATEADD(M, 1, @curr_date))= @curr_date )
BEGIN
SELECT @time_rec.last_day_in_month_flag = '''Y'''
END
ELSE
BEGIN
SELECT @time_rec.last_day_in_month_flag = '''N'''
END

SELECT @time_rec.month_name = LTRIM(RTRIM(CONVERT(VARCHAR (23), @curr_date)))
SELECT @time_rec.month_number = CONVERT(NUMERIC(8, 2), DATEPART(MM, @curr_date))
SELECT @time_rec.quarter_number = CONVERT(NUMERIC(8, 2), CONVERT(VARCHAR (23), @curr_date))
SELECT time_rec.quarter_name =
CASE time_rec.quarter_number
WHEN 1 THEN 'FIRST QUARTER'
WHEN 2 THEN 'SECOND QUARTER'
WHEN 3 THEN 'THIRD QUARTER'
WHEN 4 THEN 'FOURTH QUARTER'
END

SELECT @adv_error = @@ERROR, @adv_rowcount=@@ROWCOUNT
IF @adv_error != 0 OR @adv_rowcount = 0 OR @adv_rowcount > 1
BEGIN
GOTO Exception1
END

SELECT @time_rec.year_month = CONVERT(NUMERIC(8, 2), CONVERT(VARCHAR (23), @curr_date))
SELECT @time_rec.year_number = CONVERT(NUMERIC(8, 2), DATEPART(YYYY, @curr_date))
SELECT @time_rec.day_of_week = CONVERT(NUMERIC(8, 2), CONVERT(VARCHAR (23), @curr_date))
SELECT @time_rec.day_of_year = CONVERT(NUMERIC(8, 2), CONVERT(VARCHAR (23), @curr_date))
SELECT @time_rec.week_number = CONVERT(NUMERIC(8, 2), CONVERT(VARCHAR (23), @curr_date))
IF ( /* SwisSQL (Oracle To SQL Server) : Manual Conversion Required */ time_rec.day_of_week = 1 )
BEGIN
SELECT @time_rec.week_end_date = @curr_date
END
ELSE
BEGIN
SELECT @time_rec.week_end_date = NEXT_DAY(@curr_date, 'SUNDAY')
END

IF ( /* SwisSQL (Oracle To SQL Server) : Manual Conversion Required */ time_rec.day_of_week = 2 )
BEGIN
SELECT @time_rec.week_start_date = @curr_date
END
ELSE
BEGIN
SELECT @time_rec.week_start_date = NEXT_DAY(@curr_date, 'MONDAY')- 7
END

SELECT @time_rec.month_abbrev = CONVERT(VARCHAR(23), @curr_date)
SELECT @time_rec.quarter_year = CONVERT(NUMERIC(8, 2), CONVERT(VARCHAR (23), /* SwisSQL (Oracle To SQL Server) : Manual Conversion Required */ time_rec.year_number) + CONVERT(VARCHAR (23), /* SwisSQL (Oracle To SQL Server) : Manual Conversion Required */ time_rec.quarter_number))
INSERT INTO DIM_DAY
( DATE_SK ,
CREATED_DATETIME ,
TIME_DATE ,
DAY_NAME ,
DAY_NUMBER ,
UPDATED_DATETIME ,
LAST_DAY_IN_MONTH_FLAG ,
MONTH_NAME ,
MONTH_NUMBER ,
QUARTER_NAME ,
QUARTER_NUMBER ,
YEAR_MONTH ,
YEAR_NUMBER ,
WEEK_END_DATE ,
WEEK_START_DATE ,
WEEK_NUMBER ,
DAY_OF_WEEK ,
DAY_OF_YEAR ,
DAY_ABBREV ,
MONTH_ABBREV ,
QUARTER_YEAR )
VALUES ( time_rec.DATE_SK ,
time_rec.CREATED_DATETIME ,
time_rec.TIME_DATE ,
time_rec.DAY_NAME ,
time_rec.DAY_NUMBER ,
time_rec.UPDATED_DATETIME ,
time_rec.LAST_DAY_IN_MONTH_FLAG ,
time_rec.MONTH_NAME ,
time_rec.MONTH_NUMBER ,
time_rec.QUARTER_NAME ,
time_rec.QUARTER_NUMBER ,
time_rec.YEAR_MONTH ,
time_rec.YEAR_NUMBER ,
time_rec.WEEK_END_DATE ,
time_rec.WEEK_START_DATE ,
time_rec.WEEK_NUMBER ,
time_rec.DAY_OF_WEEK ,
time_rec.DAY_OF_YEAR ,
time_rec.DAY_ABBREV ,
time_rec.MONTH_ABBREV ,
time_rec.QUARTER_YEAR )

SELECT @adv_error = @@ERROR
IF @adv_error != 0
GOTO Exception1

SELECT @curr_date = @curr_date + 1

END --)

SELECT @from_year = @from_year + 1

END --)

ERROR :

Msg 102, Level 15, State 1, Procedure LOAD_DIM_DAY, Line 33
Incorrect syntax near '='.
Msg 102, Level 15, State 1, Procedure LOAD_DIM_DAY, Line 34
Incorrect syntax near '='.
Msg 102, Level 15, State 1, Procedure LOAD_DIM_DAY, Line 35
Incorrect syntax near '='.
Msg 102, Level 15, State 1, Procedure LOAD_DIM_DAY, Line 36
Incorrect syntax near '='.
Msg 102, Level 15, State 1, Procedure LOAD_DIM_DAY, Line 37
Incorrect syntax near '='.
Msg 102, Level 15, State 1, Procedure LOAD_DIM_DAY, Line 38
Incorrect syntax near '='.
Msg 102, Level 15, State 1, Procedure LOAD_DIM_DAY, Line 39
Incorrect syntax near '='.
Msg 102, Level 15, State 1, Procedure LOAD_DIM_DAY, Line 42
Incorrect syntax near '='.
Msg 102, Level 15, State 1, Procedure LOAD_DIM_DAY, Line 46
Incorrect syntax near '='.
Msg 102, Level 15, State 1, Procedure LOAD_DIM_DAY, Line 49
Incorrect syntax near '='.
Msg 102, Level 15, State 1, Procedure LOAD_DIM_DAY, Line 50
Incorrect syntax near '='.
Msg 102, Level 15, State 1, Procedure LOAD_DIM_DAY, Line 51
Incorrect syntax near '='.
Msg 102, Level 15, State 1, Procedure LOAD_DIM_DAY, Line 52
Incorrect syntax near '='.
Msg 102, Level 15, State 1, Procedure LOAD_DIM_DAY, Line 67
Incorrect syntax near '='.
Msg 102, Level 15, State 1, Procedure LOAD_DIM_DAY, Line 68
Incorrect syntax near '='.
Msg 102, Level 15, State 1, Procedure LOAD_DIM_DAY, Line 69
Incorrect syntax near '='.
Msg 102, Level 15, State 1, Procedure LOAD_DIM_DAY, Line 70
Incorrect syntax near '='.
Msg 102, Level 15, State 1, Procedure LOAD_DIM_DAY, Line 71
Incorrect syntax near '='.
Msg 102, Level 15, State 1, Procedure LOAD_DIM_DAY, Line 74
Incorrect syntax near '='.
Msg 102, Level 15, State 1, Procedure LOAD_DIM_DAY, Line 78
Incorrect syntax near '='.
Msg 102, Level 15, State 1, Procedure LOAD_DIM_DAY, Line 83
Incorrect syntax near '='.
Msg 102, Level 15, State 1, Procedure LOAD_DIM_DAY, Line 87
Incorrect syntax near '='.
Msg 102, Level 15, State 1, Procedure LOAD_DIM_DAY, Line 90
Incorrect syntax near '='.
Msg 102, Level 15, State 1, Procedure LOAD_DIM_DAY, Line 91
Incorrect syntax near '='.

anand

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2012-01-03 : 06:09:03

None of these are valid T-SQL:

SELECT @time_rec.date_sk = CONVERT(NUMERIC(8, 2), CONVERT(VARCHAR (23), @curr_date, 112))
SELECT @time_rec.created_datetime = DBO.ADV_getCurrentDate()
SELECT @time_rec.time_date = @curr_date
SELECT @time_rec.day_number = DATEPART(DD, @curr_date)
SELECT @time_rec.updated_datetime = DBO.ADV_getCurrentDate()
SELECT @time_rec.day_name = CONVERT(VARCHAR(23), @curr_date)
SELECT @time_rec.day_abbrev = CONVERT(VARCHAR(23), @curr_date)

Variables (of type varchar(200)) don't have properties or methods.

Similarly this will throw errors for the same reason

VALUES ( time_rec.DATE_SK ,
time_rec.CREATED_DATETIME ,
time_rec.TIME_DATE ,
time_rec.DAY_NAME ,
time_rec.DAY_NUMBER ,
time_rec.UPDATED_DATETIME ,
time_rec.LAST_DAY_IN_MONTH_FLAG ,
time_rec.MONTH_NAME ,
time_rec.MONTH_NUMBER ,
time_rec.QUARTER_NAME ,
time_rec.QUARTER_NUMBER ,
time_rec.YEAR_MONTH ,
time_rec.YEAR_NUMBER ,
time_rec.WEEK_END_DATE ,
time_rec.WEEK_START_DATE ,
time_rec.WEEK_NUMBER ,
time_rec.DAY_OF_WEEK ,
time_rec.DAY_OF_YEAR ,
time_rec.DAY_ABBREV ,
time_rec.MONTH_ABBREV ,
time_rec.QUARTER_YEAR )

Rewriting may be easier than converting. What's this supposed to do?

--
Gail Shaw
SQL Server MVP

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources