?
Solved

Converting some Sql server syntax to Sybase Syntax.

Posted on 2016-10-03
8
Medium Priority
?
100 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
  • 2
  • +1
8 Comments
 
LVL 25

Accepted Solution

by:
chaau earned 1000 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 500 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
Learn how to optimize MySQL for your business need

With the increasing importance of apps & networks in both business & personal interconnections, perfor. has become one of the key metrics of successful communication. This ebook is a hands-on business-case-driven guide to understanding MySQL query parameter tuning & database perf

 
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 1000 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 30

Assisted Solution

by:Pawan Kumar
Pawan Kumar earned 500 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 500 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

Moving data to the cloud? Find out if you’re ready

Before moving to the cloud, it is important to carefully define your db needs, plan for the migration & understand prod. environment. This wp explains how to define what you need from a cloud provider, plan for the migration & what putting a cloud solution into practice entails.

Question has a verified solution.

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

If you’re involved with your company’s wide area network (WAN), you’ve probably heard about SD-WANs. They’re the “boy wonder” of networking, ostensibly allowing companies to replace expensive MPLS lines with low-cost Internet access. But, are they …
Want to know how to use Exchange Server Eseutil command? Go through this article as it gives you the know-how.
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
We’ve all felt that sense of false security before—locking down external access to a database or component and feeling like we’ve done all we need to do to secure company data. But that feeling is fleeting. Attacks these days can happen in many w…

718 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