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