Menu
  • HOME
  • TAGS

Sybase: How to string multiple T-SQL commands together

Tag: tsql,sybase,sybase-ase

I want to execute the following commands in a single DataAdapter command:

SET ROWCOUNT 10; SELECT * FROM dbo.SYB_CO_COLOUR ;SET ROWCOUNT 0

but this isn't accepted by my Sybase DB, not even in the Interactive SQL tools that ships with the DB.

I have not changed the command_delimiter from the semi-colon default so I am surprised that I get the error

Incorrect Syntax near ';'

What am I missing?

Thanks

Best How To :

Remove semicolon and seperate them by space like:

SET ROWCOUNT 10 SELECT * FROM dbo.SYB_CO_COLOUR SET ROWCOUNT 0

It outputs 10 rows and resets rowcount back.

How To Get the Sum of a Column within a given Date range in a Table and Update a particular Cell in The Table with The Sum in TSQL?

sql-server,database,tsql,stored-procedures

You are using a cursor, to start with... I would go with an update statement using a join to a derived table. Something like this should do the trick: UPDATE t1 SET TInterest = SumTInterest FROM PersonalLedgerForTFundAndShareClone t1 INNER JOIN ( SELECT EmNo, SUM(TInterest) AS SumTInterest FROM PersonalLedgerForTFundAndShareClone WHERE TDate...

SQL Server query with case in select statement with multiple INNER JOINS

sql-server,tsql,select,case,inner-join

Try this, it should work SELECT *, CASE WHEN p.currencyid=0 THEN 140 ELSE p.currencyid END FROM projects AS p INNER JOIN businesssectors AS bs ON bs.businesssectorid=p.businesssectorid INNER JOIN plants AS pl ON p.plantid=pl.plantid LEFT OUTER JOIN currencies AS c ON c.currencyid=p.currencyid WHERE p.projectid='195' ...

SQL Multiple LIKE Statements

sql,sql-server,tsql,variables,like

WITH CTE AS ( SELECT VALUE FROM ( VALUES ('B79'), ('BB1'), ('BB10'), ('BB11'), ('BB12'), ('BB18'), ('BB2'), ('BB3'), ('BB4'), ('BB5'), ('BB6'), ('BB8'), ('BB9'), ('BB94'), ('BD1'), ('BD10'), ('BD11'), ('BD12'), ('BD13'), ('BD14'), ('BD15'), ('BD16'), ('BD17'), ('BD18'), ('BD19'), ('BD2'), ('BD20'), ('BD21'), ('BD22'), ('BD3'), ('BD4'), ('BD5'), ('BD6') ) V(VALUE) ) SELECT * FROM tbl_ClientFile...

SQL Query using FOR XML PATH that works right

sql,tsql,sql-server-2012

Similar to @MartianCodeHound I'd use STUFF SELECT t1.eventId, STUFF((SELECT ',' + t3.stringValue FROM TABLE2 t2 JOIN TABLE3 t3 ON t2.valueId = t3.valueId WHERE t2.eventId = t1.eventId ORDER BY t3.stringValue FOR XML PATH('')), 1, 1, '') AS stringValue FROM TABLE1 t1 Here's a test for you DECLARE @TABLE1 TABLE (eventId INT)...

TSQL - Error in stored procedure due to conversion failure

sql-server,sql-server-2008,tsql

I'd suggest doing it like that: SET @DATE_RELEASE_START = '2015-01-01'; SET @DATE_RELEASE_END = '2015-05-31' SELECT @statement = ' SELECT * FROM (SELECT AFCDENTE, M.ID_MODIFICATION_CODE, COUNT(*) AS Conteggio--, CAST((COUNT(*) * 100/ 15032) AS decimal(10,7)) AS Percentage FROM CIC_LOG_MODIFICHE AS L INNER JOIN ADM_MODIFICATION_CODE AS M ON L.CD_MODIFICATION_CODE = M.CD_MODIFICATION_CODE INNER JOIN...

Improving work with SQL DataTime

sql,sql-server,database,tsql

You can do it like this: SELECT IIF(DAY(@A) >= 25, DATEADD(d, 25 - DAY(@A), @A), DATEADD(d, 25, EOMONTH(@A, -2))) Here's a sample fiddle as well: sqlfiddle Note: EOMONTH requires SQL Sever 2012 or above - it returns the End-Of-Month date given a start date and a month offset....

Update recently added column in script

sql,tsql

Just put a GO on the line after the first ALTER: ALTER TABLE Certification.ProductEducationCreditTypeTemp ADD NumberOfCredit decimal(18,4) GO UPDATE Certification.ProductEducationCreditTypeTemp SET NumberOfCredit = convert(decimal(18,4), NumberOfCredits) ALTER TABLE Certification.ProductEducationCreditTypeTemp DROP COLUMN NumberOfCredits EXEC sp_RENAME 'Certification.ProductEducationCreditTypeTemp.NumberOfCredit', 'NumberOfCredits', 'Column' ...

Looping distinct values from one table through another without a join

sql,sql-server,tsql,while-loop

So you want all distinct records from table1 paired with all records in table2? That is a cross join: select * from (select distinct * from table1) t1 cross join table2; Or do you want them related by date? Then inner-join: select * from (select distinct * from table1) t1...

Creating a view with a column not in the base table

tsql

Well you have to make use of Count function and GROUP BY clause. Suppose you have student id and assignment id in your table: sId AsnId 1 1 1 2 2 1 2 5 2 8 3 2 3 4 Then following query will give you count of students working...

Combining two SELECT querys

sql,tsql

It looks like you're trying to get seven columns in your output, we you can do like this: SELECT x.a, x.b, x.c, z.d, z.e, z.f, z.g FROM x FULL OUTER JOIN z ON (x.h = z.h) FULL OUTER JOIN y ON (y.h = z.h) UNION ALL SELECT y.a y.b, y.c,...

Compare multi values against multi values, maintaining performance

c#,performance,linq,tsql,database-performance

Your foreach is like a Select. var ProductCodes = db.tbl_ProdCodeValues.Where(x=>x.productID == _Product.productID); var withMatches = ProductCodes .Select(code => new { code, matches = db.InventoryCodeValues.Any(x => x.InventoryValue.ToLower().Contains(code.ProdCodeValue)) }); And now all of this remotes to the database. Look at the query plan to see whether this is acceptable already or whether...

SQL loop through check

sql,sql-server,tsql,case

You have some bad design if you don't have some status field. Here is bruteforce solution: SELECT *, CASE WHEN [Cancelled Date] IS NOT NULL AND ([Cancelled Date] > [Indication Date] OR [Indication Date] IS NULL) AND ([Cancelled Date] > [Taken Date] OR [Taken Date] IS NULL) AND ([Cancelled Date]...

How can I create missing date records for each employee based off a limited calendar?

mysql,sql,tsql

Create a table that contains all the possible dates in the range, @alldates Then insert your missing records with something like this: Query INSERT INTO dbo.timeclock SELECT id ,d.punchtime ,'no time entered' FROM ( SELECT DATE ,id FROM @alldates d CROSS JOIN ( SELECT DISTINCT id FROM dbo.timeclock )...

SQL Subquery column equals operation

sql,sql-server,tsql

Try this query select u.ID, u.Name, case when p.value>0 then 'True' else '' end as IsPermissionEnabled from Users u left join permission p on p.UserID = u.ID and p.key='CanEdit' ...

Number of records per day without timestamp

sql-server,tsql

You are counting the same column that you are using for the GROUP BY. Naturally, there will be only one value on each group. try to use COUNT(*) instead. Also, to ignore the time part the simplest thing to do is cast to date (assuming your database version is 2008...

Using a case statement to set the values of declared variables

sql,sql-server,database,tsql,case

You are on the right track, but syntax you've used is incorrect. It should be select @DeclaredVar1 = case when fieldValue ='stringValue1' then 100 else --another option here-- end ...

Joining tables and LEFT JOIN as new Columns in tsql

sql,sql-server,tsql,table,row

So Step one would be to start off with your base query. This gives you the final report information but in the wrong format. All that is needed now is to pivot it. Select cus_Name, prod_Name, SUM(ord_Qty) from Orders o inner join Customers c on c.cus_ID = o.cus_ID inner join...

Improve my SQL Select statement to select students who have not fully completed a section

sql,sql-server,linq,tsql

Using a subquery with a group by and having statement you can come up with something similar to this: SELECT * FROM Students WHERE StudentID NOT IN ( SELECT s.StudentID FROM Students s JOIN CourseEnrollment ce ON s.StudentID = ce.StudentID JOIN Courses c ON ce.CourseID = c.CourseID WHERE ce.EnrollmentStatus =...

Getting SCOPE_IDENTITY() after insert with select

sql-server,tsql,scope-identity

Since you are trying to retrieve multiple values here you need to use the OUTPUT clause. It will return all the newly inserted values into a table or table variable. https://msdn.microsoft.com/en-us/library/ms177564.aspx...

Fastest way to add a grouping column which divides the result per 4 rows

sql,sql-server,tsql,sql-server-2012

Try this: SELECT col, (ROW_NUMBER() OVER (ORDER BY col) - 1) / 4 + 1 AS grp FROM mytable grp is equal to 1 for the first four rows, equal to 2 for the next four, equal to 3 for the next four, etc. Demo here Alternatively, the following can...

TSQL update value with subquery

sql-server,tsql,sql-update,compare

Could you try this: MERGE TableA AS [Target] USING TableB AS [Source] ON [Target].[ID] = [Source].[ID] AND [Target].[Name ] = [Source].[Name] WHEN NOT MATCHED BY TARGET THEN UPDATE SET NameMod = 1; It is using the MERGE clause. If you do not like the clause, you can use CTE like...

How to display character values for numerical data for many columns

sql,tsql

Select * from Questions left join labels as l1 on questions.Q1 = l1.Labs left join labels as l2 on questions.Q2 = l2.Labs left join labels as l3 on questions.Q3 = l3.Labs ...

How do I convert this tSQL statement to LINQ using group by in a sub query

c#,sql-server,linq,tsql

I think there is opportunity to rewrite your query, but for information purposes I rewrote your sql into linq verbatim. If you explain what you are trying to achieve we can provide alternative sql / linq var eqnums = new[] { "M0435", "Z0843" }; var testdate = "2008-06-01"; var query...

SQL Server : Recursive Advanced Query on two conditional logics Not showing hierarchy relationship

sql,sql-server,tsql,sql-server-2012,common-table-expression

You are on the right track :) Some fixes: You are inserting zero, not null, as ParentId into products 8 and 10 - this is why they are never picked up in your initial #TmpMasterProduct query - you'll need to change these back to NULL e.g. INSERT INTO Product (ProductID,Name,ParentId,IsMasterProdcut)...

Suppress tempdb message when outputting result set

sql-server,tsql,sqlcmd

Change your if condition to the following pattern: IF 0 < OBJECT_ID('tempdb..#TheTable') DROP TABLE #TheTable This should not result in any error messages....

copy table and drop it

sql,sql-server,sql-server-2008,tsql,stored-procedures

IF (EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'tbl2')) BEGIN -- tbl2 exists, so just copy rows INSERT INTO tbl2 SELECT * FROM tbl1; END ELSE BEGIN -- tbl2 doesn't exist, so create new table tbl2 and copy rows SELECT * INTO tbl2 FROM tbl1; DROP tbl1; END This...

Create a new column in my SQL View - Column must be two other columns combined

sql,sql-server,tsql,concatenation

You simply need to add another column to your select: CREATE VIEW KFF_Rep_Comm AS SELECT vw_KLX_RepCommTrx.AccName AS AccountName ,vw_KLX_RepCommTrx.Account AS AccountNum ,vw_KLX_RepCommTrx.RepID ,vw_KLX_RepCommTrx.RepName ,vw_KLX_RepCommTrx.CommPerc AS CommPercent ,vw_KLX_RepCommTrx.Credit ,vw_KLX_RepCommTrx.Debit ,vw_KLX_RepCommTrx.MUPercent AS MarkUpPercent ,vw_KLX_RepCommTrx.ProfitPercent AS GrossProfitPercent ,vw_KLX_RepCommTrx.SgdTrxAmt AS InvValue ,vw_KLX_RepCommTrx.SgdCost AS Cost ,vw_KLX_RepCommTrx.SgdProfit AS GrossProfit...

many rows into a single column with SQL

sql,sql-server,tsql

Here is what you can use: SELECT DISTINCT ParagraphID , STUFF(( SELECT N' | ' + CAST([LoginName] AS VARCHAR(255)) FROM [dbo].[CM_Signature] f2 WHERE f1.ParagraphID = f2.ParagraphID FOR XML PATH ('')), 1, 1, '') AS FileNameString FROM [dbo].[CM_Signature] f1 Note the STUFF("...", 1, 1, '') instead of STUFF("...", 1, 2, '')....

How to join result of a Group by Query to another table

sql,tsql

You have mistake here from tblUsers u,a.Login_Name try to move this piece of code a.Login_Name to select Select u.User_Id, a.Login_Name from tblUsers u inner join (SELECT s.Login_Name Login_Name, COUNT(s.s1CIDNumber)as abc FROM [dbSuppHousing].[dbo].[tblSurvey] s group by s.Login_Name) a on u.Login_Name=a.Login_Name ...

Sybase SQL anywhere 11 Grant/Revoke on column views

sql,views,sybase,grant,sqlanywhere

You can't. From the SQL Anywhere 11 docs: "SELECT permissions on columns cannot be granted for views, only for tables". Disclaimer: I work for SAP in SQL Anywhere engineering....

How can I execute/call a MySQL stored procedure using SQL Server 2008 R2

tsql,sql-server-2008-r2

You can use: exec ('stored_procedure_namme') at linked_server_name([name of the linked server you used while creating the DB link]) ...

SQL Like/Contains on BIGINT Column

sql,sql-server,tsql

Try this instead: DECLARE @search VARCHAR(10) SET @search = '1' SELECT id FROM table WHERE CAST(id AS VARCHAR(10)) LIKE @search + '%' When casting to VARCHAR, you should always specify the length. If you don't define a length, SQL-Server will assign one for you. Sometimes it will be 1 others...

SQL Server Stored procedure declaring variable in different way

sql-server,tsql,stored-procedures

@UPA nvarchar(30) = NULL is the procedure argument/parameter (in this case, an optional parameter though since it's being declared like that having NULL as the default value. So while calling procedure if you don't supply @UPA, it will assume NULL) that you need to supply while calling the procedure like...

Retrieve the Return Result with Chronological Order Based on Parameter

tsql,stored-procedures,sql-server-2012

Without an explicit ORDER BY Statement, SQL Server will determine the order using a variety of means e.g. collation\indexes\order of insert etc. This is arbitrary and will change over time! No Seatbelt - Expecting Order without ORDER BY If you want to guarantee the order of your output, you need...

SQL stored procedure: increment months from a starting date to an end date

sql-server,tsql,date,stored-procedures,cursor

This can easily be done with a recursive CTE: ;WITH cte AS ( SELECT @Start AS [Month] UNION ALL SELECT DATEADD(MONTH, 1, [Month]) FROM cte WHERE [Month] < @End ) SELECT [Month] FROM cte OPTION (MAXRECURSION 0) ...

How to filter a SELECT result with IF condition?

sql,sql-server,tsql

Why not do the filtering in CTE itself like DECLARE @Gender VARCHAR(10) SET @Gender = 'Female' WITH queryResult AS ( SELECT * FROM Employees WHERE Age >= 50 AND Gender = @Gender ) select * from queryResult; EDIT: In that case change your CTE with a CASE expression like below...

What is the execute command to use here

sql,sql-server,sql-server-2008,tsql

Have a look at this and tell me if this works for you - I've added a try catch to check if the command is timing out. Just few tips: I would always use a try catch because it'll tell you exactly what you need to know and why your...

What is difference between join syntax in T-SQL [duplicate]

sql,sql-server,performance,tsql

What is difference between that? AFAIK, both are doing INNER JOIN, First one using a Implicit JOIN syntax whereas the second one using a explicit join syntax I wouldn't expect any performance difference between them but the second style of query using explicit join syntax is much more recommended...

One Table - Two Fields - Same value - T/SQL

sql-server,tsql,sql-server-2012

The problem is that you're comparing both of the lines to themselves. I assume you would like to find just the first row, but also the second line (t1) will match to the first line (t2) and that's the second result you get. Not sure what you're doing, but you'll...

Increment each field in a column by 1 - SQL Server 2008

sql,sql-server,tsql

The general structure for doing this would be: ;WITH Numbered as ( SELECT *,ROW_NUMBER() OVER (ORDER BY <some column>) rn FROM view_kantech_matched WHERE image_id is NULL ) UPDATE Numbered SET image_id = 2865 + rn But I don't know what <some column> would be....

Convert row to columns SQL dynamically [duplicate]

sql,sql-server,sql-server-2008,tsql

First you will need to unpivot your table and after that pivot again. This is the key idea: select * from TableName unpivot(v for xyz in([Atest],[Btest],[Ctest]))u pivot(max(v) for yyyymmdd in([20150525],[20150526],[20150527]))p Fiddle http://sqlfiddle.com/#!3/a675c/1 As for dynamic sql, you can find many example of how you can use STUFF function to concatenate...

T-SQL Ordering a Recursive Query - Parent/Child Structure

sql,tsql,recursion,order,hierarchy

The easiest way would be to pad the keys to a fixed length. e.g. 038,007 will be ordered before 038,012 But the padding length would have to be safe for the largest taskid. Although you could keep your path trimmed for readability and create an extra padded field for sorting....

SQL - what's the best way to look up which table a column belongs to?

sql-server,tsql

Well, you can try both of this. In Management Studio: Mark your query Right click Design Query in Editor You'll get a graphical interface which will make it easier for you to see which column comes from which table. Otherwise you can query (if you want to make it automatically)...

Can someone explain to me how this statement is an exclude?

sql,sql-server,tsql

I can explain... a query that's very close to yours. Let me alter it to: SELECT * FROM [table].[dbo].[one] AS t1 LEFT JOIN [table].[dbo].[one] AS t2 ON (t1.ColumnX = t2.ColumnX AND t2.columnY = 1) WHERE t2.tableID IS NULL This query retrieves all rows from t1, then checks to see if...

Retrieving an XML node value with TSQL?

sql-server,xml,tsql,soap

Here is the solution: DECLARE @xml xml SELECT @xml = '<SOAP-ENV:Envelope xmlns:SOAP-ENV="http://schemas.xmlsoap.org/soap/envelope/" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"> <SOAP-ENV:Body> <webregdataResponse> <result>0</result> <regData /> <errorFlag>99</errorFlag> <errorResult>Not Processed</errorResult> </webregdataResponse> </SOAP-ENV:Body> </SOAP-ENV:Envelope>' declare @table table...

Date Diff in calculated field

sql,sql-server,tsql

Perhaps you should try getdate(): DaysTileService as (DATEDIFF(day, getdate(), NextServiceDate)) ...

How do I resolve the “Enter Parameter value” error in MS-Access

sql,excel,vba,tsql,ms-access

Try this. This will exclude the [DSRT_ERS].[ID] from DSRT_TEMP table and insert only filtered records. INSERT INTO DSRT_ERS SELECT * FROM DSRT_TEMP WHERE [DSRT_TEMP].[ID] NOT IN (Select [DSRT_ERS].[ID] FROM [DSRT_ERS] WHERE [DSRT_ERS].[ID] IS NOT NULL) There are two options, you have NOT IN and NOT EXISTS suggested by @deoeth. Keep...

Split comma separated string table row into separate rows using TSQL

sql,sql-server-2008,tsql

Alternatively, you could use XML like so: DECLARE @yourTable TABLE(ID INT,SomeValue VARCHAR(25)); INSERT INTO @yourTable VALUES (1,'a,b,c,d'), (2,'e,f,g'); WITH CTE AS ( SELECT ID, [xml_val] = CAST('<t>' + REPLACE(SomeValue,',','</t><t>') + '</t>' AS XML) FROM @yourTable ) SELECT ID, [SomeValue] = col.value('.','VARCHAR(100)') FROM CTE CROSS APPLY [xml_val].nodes('/t') CA(col) ...

INSERT INTO fails due to incorrect conversion T-SQL

sql-server,tsql

The problem is that there's no implicit conversion from varchar (your literal) to sql_variant. Just add an explicit conversion and you're done: cast('FooBar' as sql_variant) ...

Getting error “Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.” for comparing sales accounts

sql-server,tsql,sap

I think just querying the tables once should suffice: select T1.CardCode 'BP Code', T1.CardName 'BP Name', COUNT(CASE WHEN T1.SlpCode<>'37' THEN T0.DocNum END) '# of Orders', SUM(CASE WHEN T1.SlpCode<>'37' THEN T0.DocTotal END) 'Total Orders Amt', SUM(CASE WHEN T1.SlpCode<>'37' THEN T0.DOCTOTAL END)/ COUNT(CASE WHEN T1.SlpCode<>'37' THEN T0.DocNum END) 'Avg Order Size', COUNT(CASE...