Hot answers tagged

14

They are different. In the first option you get 2 times Table2 into your query. Once as t2 and once as t3. Both have a different content and you must put them somehow back together. To me this is more an OR instead of an AND. In the second option you get only the Table2 rows that meet both criteria. Suppose you have Table2 with the following content: | ...


6

I am not sure what you mean by Bindings will be done in another function. You have an EXECUTE statement that is going to invoke your query right there and then, and that statement is not providing parameter values for your :name parameters. That is what the error message is about. Your EXECUTE statement must have a USING clause, as per the manual: ...


4

Will the optimizer recognize the change and consider them before using cached query plans for re-run transactions? Put another way, do I need to consider removing any query plans? Yes, the optimizer should recompile the plans since changes to an underlying table are one of the triggers that result in a plan recompilation. Since checking a constraint ...


4

This question has been cross-posted on Stack Overflow at: http://stackoverflow.com/questions/38080741/in-sql-server-is-there-a-way-to-determine-the-values-of-the-parameters-passed-t As I said there: This information -- run-time parameter values passed into a Stored Procedure (i.e. RPC call) or parameterized query -- is only available via a SQL Trace (...


4

You can turn on the actual execution plan and then look at the execution plan XML. Or you can use sql sentry's plan explorer tool and see the parameters tab that will list the compiled value and run time value for actual execution plan. If you cannot turn on the actual plan then you can look into plan cache as described below. -- borrowed from Erland ...


4

In the ANSI SQL Standard, there is no TEXT type. There are various string types defined, like CHAR and VARCHAR and many more but no mention of a TEXT type. There is a CHARACTER LARGE OBJECT and a BINARY LARGE OBJECT. The various TEXT types found in various DBMS (Postgres, SQL Server, MySQL) are additions and have small differences between them. Their ...


3

In short, you want to prove that: bitand(x,y) + bitor(x,y) = x + y Split the x and y numbers into the respective bits. Now, lets prove first that the same stands for bits a and b (a and b can only have the values 0and 1): bitand(a,b) + bitor(a,b) = a + b But for bits these are trivial: bitand(a,b) = least(a,b) bitor(a,b) = greatest(a,b) Therefore (...


3

There is no contradiction in that documentation. The confusion here seems to be that there is an implied assumption regarding the reader's expectations of how variable scope works. In many other languages, variables declared in an outer scope are visible to subroutines / functions. For example (and this is not how it works in T-SQL; I am just illustrating ...


2

Logic is very simple. We are taking two instance of same table; second in a subquery. We pick first salary of main table and compare it against all salaries in subquery table to get a count of salaries greater than the salary in main table under consideration. If count is N-1; then it implies that salary in main table is Nth max salary because there are N-1 ...


2

Another option is to handle this via SQLCLR. There is even a method already available in .NET that does this: TextInfo.ToTitleCase (in System.Globalization). This method will Upper-Case the first letter of each word, and Lower-Case the remaining letters. Unlike the other proposals here, it also skips words that are in all upper-case, assuming them to be ...


2

Another possible solution: (thanks John Eisbrener for the sample data) DECLARE @Assignment TABLE ( StartDate DATE, EndDate DATE, Equipment VARCHAR(50) ); INSERT INTO @Assignment VALUES ('4/1/2016', '4/23/2016', '1001') ,('4/3/2016', '4/10/2016', '1002') ,('3/30/2016', '4/20/2016', '1003'); DECLARE @Event TABLE ( EventID INT NOT ...


2

Your subquery is uncorrelated and returns all rows from the join between native_name_aliases and dpoint, where only a single row would be allowed. I guess this is what you are after: UPDATE dpoint d SET native_name = nna.native_name FROM native_name_aliases nna WHERE d.building_id = 42 AND d.dpoint_id = nna.dpoint_id AND d.native_name IS ...


2

You are trying to insert 4 values in 5 columns: :Id => Id :uId => uId :name => name :address => address ??? => phone You can either add or remove Phone Add Phone: INSERT INTO tableX(Id, uId, name, address, phone) VALUES (:Id, :uId, :name, :address, :phone) If Phone allows NULL and you don't know its value (...


2

You need 2 anti-joins. Written with either two LEFT JOIN / IS NULL: SELECT user.id FROM users AS user LEFT JOIN user_follows AS follower ON follower.users_id = user.id AND follower.shown_users_id = 2 LEFT JOIN user_follows AS followed ON followed.shown_users_id = user.id AND followed....


2

Must be something like: select s1.id, s2.id from surveys s1, surveys s2 where (s2.start_date between s1.start_date and s1.end_date or s2.end_date between s1.start_date and s1.end_date) and s1.id!=s2.id; This fetches you the IDs where the START_DATE or END_DATE (of s2) is in the period of another START_DATE and END_DATE (s1).


2

this would be exact select min(id), max(id) from table group by start_da, end_date having count(*) > 1 start in select t1.*, t2* from table t1 join table t2 on t1.startdate between t2.startdate and t2.end_date


1

ALTER TRIGGER [dbo].[t_upd_insert] ON [dbo].[Player] FOR INSERT, UPDATE AS SET XACT_ABORT, NOCOUNT ON; BEGIN IF (update(name)) BEGIN INSERT INTO dbo.Changes ( [name] [created_at] ) SELECT ins.[name], ins.[created_at] FROM INSERTED ins END


1

Try this ( replace tbl with your table name): WITH Unpivoted AS ( SELECT customerID ,ProductLineSlot ,ProductLine FROM ( SELECT customerID ,productLine1 ,productLine2 ,productLine3 FROM tbl ) p unpivot(ProductLine FOR ProductLineSlot IN ( productLine1 ...


1

To expand a bit, it is what is referred to in the previously cited doc as a 'net service name'. Let's say you have a database instance named 'orcl'. On the client server you'd have a tnsnames.ora file with an entry (net service name) something like this: ORCL = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = mydbservername)(...


1

To start with, JOIN and GROUP BY select b.BudgetDate, sum(b.BudgetAmount) - coalesce(sum(ex.ExMount),0) as BudgetLessExpenses from dbo.Budget b left join dbo.Expense ex on b.BudgetDate=ex.Date group by b.BudgetDate BTW. Shouldn't there be a UNIQUE constraint on [Budget].[BudgetDate] ?


1

Even if not applicable here, please be wary of runaway queries initiated from an OPENQUERY command. These run on remote servers, and continue to do so no matter how many times the user tries to cancel from their SSMS console....these pass-thru queries will continue to run on the target server. And if, God forbid, there's a recursive loop in there, it'll ...


1

You have to be very careful. Assuming your query is not restarting and you have a rollback for that SPID. If you restart SQL Server it won't help, because transaction still would have to be rolled back. The problem with that is following: When you run a transaction in multi-CPU environment with not restricted degree of parallelism it most probably will ...


1

Application would be much better place to deal with such cases as Aaron Bertrand said but you can do it with SQL as well . select case when TypeId = 1 then 1 when TypeId = 0 then 2 end from Type where mail = 'name@test.com' UNION ALL SELECT 3 FROM DUAL WHERE NOT EXISTS (SELECT NULL FROM Type WHERE mail = 'name@test.com') ...


1

You just need to add a case statement to your select list: declare @bid varchar(4) = '1007' select sr.release_date, sr.customer_id, sr.customer_name, sr.item_id, sr.item_desc, sr.order_no, sr.qty_on_hand, sr.release_qty, sr.qty_after_released, sr.extended_sell, sr.enough, case when sr.route_code in ('pm 01','...


1

Use SQL Server Import and Export Wizard. It is the best for one time transfers.


1

The problem is a simpler version of: How to have a one-to-many relationship with a privileged child? Either option is good. If you choose storing the InUse flag in the Versions table, you just need to add a filtered unique index: ADD UNIQUE INDEX Versions_InUse_FUX ON dbo.Versions (InUse) WHERE (InUse = 'Y') ; If you choose the separate table option,...


1

With one table you would need to limit it to a single row. I think a trigger on InUse would be a better solution.


1

Would it be better to use a NOT IN or NOT EXISTS query? Yes, you would most likely benefit from an IN() (or EXISTS()) instead of the join and IS NULL because you're effectively only using the tableREPORTS data as a filter. This should work for what you need to do: SELECT [Call_Num], [Call_Cust_Num], [Call_Cust Name], [Call_Status]...


1

This is a bit kludgy as I was unable to get a windowing function (i.e. DENSE_RANK, NTILE, RANK, or ROW_NUMBER) to work like I had hoped, so I had to "design" one via COALESCE and some customizations within the table structure. The basic premise here is you first need to join these tables to an intermediary table that contains a list of all days (e.g. an ...


1

As Nickolay explained in his answer, WITH is not allowed in a view definition in Teradata. You can work around the specific problem by using a derived table instead. You can either specify it twice, keeping the union of your query: create view derived_table (derived_column) as select (a||'-'||b) as derived_column from (select a,b,c from table_a ...



Only top voted, non community-wiki answers of a minimum length are eligible