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
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.
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,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,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...
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)...
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...
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....
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' ...
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...
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...
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,...
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...
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]...
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 )...
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' ...
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...
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 ...
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...
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 =...
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...
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...
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...
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 ...
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,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)...
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....
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...
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...
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, '')....
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 ...
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....
You can use: exec ('stored_procedure_namme') at linked_server_name([name of the linked server you used while creating the DB link]) ...
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,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...
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-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) ...
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...
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...
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...
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...
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....
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...
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....
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)...
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...
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...
Perhaps you should try getdate(): DaysTileService as (DATEDIFF(day, getdate(), NextServiceDate)) ...
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...
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) ...
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) ...
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...