Solved

Converting some Sql server syntax to Sybase Syntax.

Posted on 2016-10-03
8
66 Views
Last Modified: 2016-10-05
I need to convert this Sql server sql to Sybase Sql. I am getting an error message : "Incorrect syntax near TOP'. How do I change the code below so that it will run on Sybase?


IF NOT EXISTS (SELECT TOP 1 * FROM sys.columns WHERE NAME = @columnName AND Object_ID = Object_ID (@TableName))
BEGIN
  SELECT @sqlStatement = 'ALTER TABLE ' + @tableName + ' ADD ' + @newAddCountColumn + ' ' + VARCHAR(50) NULL '
  PRINT 'Creating a new column '
  EXEC (@sqlStatement)
END ELSE PRINT
0
Comment
Question by:brgdotnet
  • 3
  • 2
  • 2
  • +1
8 Comments
 
LVL 25

Accepted Solution

by:
chaau earned 250 total points
ID: 41827371
Just get rid of TOP 1 and it should work:
IF NOT EXISTS (SELECT 1 FROM sys.columns WHERE NAME = @columnName AND Object_ID = Object_ID (@TableName))
BEGIN
  SELECT @sqlStatement = 'ALTER TABLE ' + @tableName + ' ADD ' + @newAddCountColumn + ' ' + VARCHAR(50) NULL '
  PRINT 'Creating a new column '
  EXEC (@sqlStatement)
END ELSE PRINT 

Open in new window

0
 
LVL 2

Author Comment

by:brgdotnet
ID: 41827373
I swear I tried that, and it did not work? I will have to try it out at work tomorrow.
0
 
LVL 26

Assisted Solution

by:wilcoxon
wilcoxon earned 125 total points
ID: 41827375
As chaau said, I think it should work if you remove the "top 1".  Chaau's mod of "select 1" rather than "select *" should make it more efficient.
0
Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

 
LVL 2

Author Comment

by:brgdotnet
ID: 41827383
Thanks gentlemen.  I remember, I removed the " top"  and the recieved the error message, incorrect syntax near from? Any ideas?
0
 
LVL 25

Assisted Solution

by:chaau
chaau earned 250 total points
ID: 41827385
When you remove the TOP keyword you also need to remove the number that follows it. The correct way would be to remove TOP 1
0
 
LVL 28

Assisted Solution

by:Pawan Kumar
Pawan Kumar earned 125 total points
ID: 41827392
@Author - Is it done ? If not use below.

Just to summarize what other experts are saying.

IF NOT EXISTS (SELECT * FROM sys.columns WHERE NAME = @columnName AND Object_ID = Object_ID (@TableName))
BEGIN
  SELECT @sqlStatement = 'ALTER TABLE ' + @tableName + ' ADD ' + @newAddCountColumn + ' ' + VARCHAR(50) NULL '
  PRINT 'Creating a new column '
  EXEC (@sqlStatement)
END ELSE PRINT 

Open in new window

0
 
LVL 26

Assisted Solution

by:wilcoxon
wilcoxon earned 125 total points
ID: 41827434
Depending on your product/version of Sybase, doing:
IF NOT EXISTS (SELECT 1 ...

Open in new window


may be more efficient than:
IF NOT EXISTS (SELECT * ...

Open in new window


I'm pretty sure this was true in ASE 11 (really old) but I think it became no longer true in ASE 12-15 (not sure which version exactly).  I don't know the other Sybase engines nearly as well (so can't say for them).
0
 
LVL 2

Author Comment

by:brgdotnet
ID: 41831023
Thank you. Actually one other problem is that you are suppose to query syscolumns not sys.columns, on Sybase.
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Sybase sp_thresholdaction 3 995
Sybase initialize new disk in Solaris 10 failed 2 969
MS SQL 2008 server - system update recommendation 13 118
Need help with SYBASE syntax 5 101
This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
This article discusses how to implement server side field validation and display customized error messages to the client.
I've attached the XLSM Excel spreadsheet I used in the video and also text files containing the macros used below. https://filedb.experts-exchange.com/incoming/2017/03_w12/1151775/Permutations.txt https://filedb.experts-exchange.com/incoming/201…

749 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question