Here is the code: As you can see the recursive code gives the result in right order, but the non-recursive code does not. They both use union, why the difference? Answer Basically, your query is incorrect to begin with. Use UNION ALL, not UNION or you would incorrectly remove duplicate entries. (There is noth…
How to check active transactions in SQL Server 2014?
I am using SQL Server 2014 and want to know how to check my active transactions? Answer Query with sys.sysprocesses DBCC OPENTRAN : helps to identify active transactions that may be preventing log truncation. DBCC OPENTRAN displays information about the oldest active transaction and the oldest distributed and…
Create or alter trigger if exists
I’m trying to determine withing if If I should create or alter and trigger. My Code is below. IF OBJECT_ID(N’Sales.bonus_reminder’, N’TR’) IS NOT NULL ALTER TRIGGER Sales.bonus_reminder ON …
SQL: Combine First Name and Last Name columns but if Null change to ‘Name Not Provided’
I’m currently building a simple view and need to combine both the First Name and Last Name columns to create a new Customer column. If a First Name and Last Name are not provided I’d like to change …
How to create Multiple-field foreign key in Access?
I have found this tutorial, which says that question can be solved this way: Multiple-field constraint: For my simplified database: I wrote such sql code, which has an idea to connect 2 columns from 2 tables. In addition, I would like to add unique constraint on 2 field of S2T table: But I get an error, sayin…
sql query “WHERE IN”
I have this query : When I execute it it returns the message: Incorrect syntax near ‘)’. What is the problem? Answer That’s because, your IN clause has no parameter/argument WHERE BeneficaryID IN (). It should be WHERE BeneficaryID IN (id1,id2,id3, …,idn) Your current query is same as …
UPDATE mysql rows
I’m trying to update a lot (close to 500) of rows in mysql database. How can I make it with just 1 query? …and it goes on and on until 500. Answer Use the BETWEEN operator: If not all products with an ID in that range should be updated, you’ll have to use the IN operator and construct the qu…
VBA vs Query: Discrepancy in Result Count
I am implementing a system to convert an existing database to a more efficient layout, as discussed in this question. Applying the union/maketable solution given to that question to a sample database …
PL/SQL: procedure to process a comparison between two tables
This is an example what i need : Table 1 : Table 2: A procedure that feeds a table3 as a result of comparison between the table 1 and table 2 Table 3: Please, i need help if someone had the same challenge 🙂 Answer Data setup: The below query would give you the differences
Dynamically Using Sheetname in Sql-VBA
rs.Open “SELECT [Sheet1$].ID FROM [Sheet1$] WHERE [Sheet1$].ID IS NULL “, cn, adOpenKeyset, adLockReadOnly Sheet1, i.e. the name of the sheet in my case is variable in my case and I want to repeat …