New answers tagged

0

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') ...


0

I believe you cannot do that. But, I came across something similar answered on the forum already. You can achieve a similar effect by following the steps in the link below. Please go through the below link and check if it might be useful to you. http://stackoverflow.com/a/9503789/2818351 Credits to the original author who answered the question.


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','...


0

It's difficult without seeing your query, but you want to aggregate on your fields with null values if you want to remove the redundant rows. This query would give you one row of data in this example, I'm not sure how you mean to get your output where you are removing two rows. SELECT Field1 ,Field2 ,MAX(Field3) Field3 ,MAX(Field4) Field4 ...


1

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....


0

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 ...


0

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,...


0

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


3

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 ...


0

The CASE you wrote is for visualization (SELECT) or for filter data (WHERE). if is the SELECT case, then can be (not sure if you want an SUM(Sales): SELECT (CASE WHEN Parameter = 'RegionWest' THEN 'A,C' WHEN Parameter = 'RegionEast' THEN 'B,D' ELSE Country) AS CountryName, Sales FROM yourTable If is the WHERE case I need more of your SQL to ...


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 ...


5

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: ...


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 (...


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: | ...


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]...


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 ...


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 ...


2

For Postgres 9.4+: UPDATE trajs t SET traj_id = upd.traj_id FROM ( SELECT id, 1 + count(*) FILTER (WHERE t >= t0 + interval '6 min') OVER (PARTITION BY obj_id ORDER BY t) AS traj_id FROM ( SELECT id, obj_id, t, lag(t) OVER (PARTITION BY obj_id ORDER BY t) AS t0 FROM trajs ) sub ) upd WHERE t.id ...


2

See https://github.com/mj1856/SqlServerTimeZoneSupport by Matt Johnson for Sql Server 2008R2+.


4

Two major improvements: SELECT * FROM certificates c WHERE c.expires_on <= current_date + 30 -- sargable! AND NOT EXISTS ( SELECT 1 FROM certificates WHERE common_name = c.common_name AND expires_on > c.expires_on AND state = 'issued' ); Make the first predicate sargable, so that an index can be used. You second ...


14

Is there any negative performance impact for using the same service account and agent account to run SQL Server and SQL Agent respectively for all SQL server instances running at a small company of say 35 servers? No. Service accounts do not affect performance in any way. Its all about security ! From the SQL Server 2012 security best practice whitepaper ...


7

The real problem with using one Service Account for all of your servers is: How secure do you want to be? If someone is able to hack that one account, then all 35 servers are exposed in one swoop. I much prefer at least a Service Account per server. And having several Service Accounts for different uses is also recommended for security. See: Configure ...


4

There is no negative performance impact to using the same account on all your servers. If a change happens to that account, however, it will impact all your services using that account. You may want to consider different accounts for different environments (e.g. DEV, TEST, PROD).


1

Sounds like you're trying to insert rows to a table that already exists, in which case you'll need something like... INSERT INTO new_records SELECT * FROM new_table t JOIN new_record_ids r ON(r.id = t.id) --this assumes the result of the select has the same column layout as new_records... --which seems unlikely. replace (SELECT *...) with (SELECT column1, ...


0

You cannot write your own partition function. What you need to do is create an additional column, assign a value resulting from the function you want to use, and partition on that column. Unfortunately, you cannot partition on a computed column, which would be the easiest solution.


0

why you need use subqueries ? You can use joins for get data SELECT u.name AS user, d.name AS departement, m.year FROM main AS m INNER JOIN department AS d ON m.departement_id = d.id INNER JOIN users AS u ON m.user_id = u.id ; I created a sample fidle http://sqlfiddle.com/#!9/74a4e2/1


0

Different servers, different solutions. You should clarify the database system, version and the error message you got. You may have a look at the merge command depending on the database system you use.


2

Slight variation of @Ankumars answer. Since 'close' is less than 'open': SELECT A1.*, A2.status as current_status FROM AssignedTo A1 JOIN ( SELECT taskID, min(status) as status FROM AssignedTo GROUP BY taskId ) A2 ON A1.taskId = A2.taskId;


2

SELECT A1.*, CASE WHEN A2.taskId IS NULL THEN 'Task Completed' ELSE 'open' END AS CurrentStatus FROM AssignedTo A1 LEFT JOIN ( SELECT taskId FROM AssignedTo WHERE status IN ('open', 'close') GROUP BY taskId HAVING COUNT(DISTINCT status) = 1 ) A2 ON A2.taskId = A1.taskId ORDER BY A1.taskId; Fiddler: http://...


2

The typo in ORDER BY aside, you need descending sort order to get the ingredient with the maximum amount first. SELECT Iname, Amount FROM Composition WHERE Name = 'Pancake' ORDER BY Amount DESC FETCH FIRST 1 ROWS ONLY; Assuming Amount is defined NOT NULL. FETCH FIRST 1 ROWS ONLY is the syntax defined by the SQL standard and supported by several ...


0

Here is your query, you need to join Ingredient i with Composition c on i.iname=c.iname with 2 condition name='Pancake' and where amount >= select max(amount) from Composition where name='Pancake'. SELECT i.Iname, Amount FROM Ingredient i INNER JOIN Composition c ON i.iName=c.iName WHERE Name='Pancake' AND Amount >= (SELECT MAX(Amount) FROM ...


0

The fundamental steps are quite simple in words: perform a restore of the database with the latest backup available. There are tons of documentation which describes the regular restore of a database. Luke gave you some of the primary sources. You might want to get in touch with those at some point. Even the worst DBA should be able to handle this, else he ...


0

OK, after experimenting a bit further with queries and a set statistics time on, here's what I found: On a small IN-list (4-12 items) - ALMOST identical The performance is almost identical, the hardcoded variant uses a little bit more CPU, the overall "elapsed time" is either same or a little bit longer. On a bigger IN-list (150+ items) - subquery wins BY ...


0

Try this : DELIMITER $$ DROP PROCEDURE IF EXISTS `do_thing` $$ CREATE PROCEDURE `do_thing`() BEGIN -- DECLARE STATEMENTS first DECLARE how_many INT; DECLARE `_rollback` BOOL DEFAULT 0; DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET `_rollback` = 1; -- SELECT COUNT(*) INTO how_many FROM things; -- ...


4

ISNULL('','0') Returns the first argument '' (as that is not null) so your question boils down to asking why SELECT REPLACE('','','0') Doesn't return 0. The documentation for REPLACE states string_pattern Is the substring to be found. string_pattern can be of a character or binary data type. string_pattern cannot be an empty string (''), and ...


0

The code below is an (simplified) example of how I met the requirement of having a separate table for each item type (e.g. guns, butters, etc) and that every item, regardless of type, has a unique inventory number assigned to it. Items must also never share the same inv number even if they're in separate tables. This solution is following an inheritance ...


0

Rather than maintaining two separate code bases, the easier way to do this is by aiming for the lowest common denominator. Try to only use features in the database that are supported in Azure SQL DB. That way you'll have a single code base that works for both, and you'll have much easier upgrades, too. For box-product customers that truly need SQL Server ...


0

For a question like this you should get in a routine habit of going to the documentation first. Here is Microsoft's article on SQL Server restores. https://msdn.microsoft.com/en-us/library/ms186858.aspx Make Microsoft Books Online your best friend, and your first go to source for SQL Server questions. You said you are just starting and the documentation ...


7

It is not clear to me what you are asking but I believe that GROUP BY is one of the most misunderstood concepts in SQL, so I'll add this answer anyhow. It may or may not help with the understanding of the concept GROUP BY. Assume we have a table like: CREATE TABLE T ( YEAR INT NOT NULL , PARAMETERNO INT NOT NULL , MARK INT NOT NULL , PRIMARY KEY (YEAR, ...


6

You can not select aggregates across a field if you don't include the field in the group by list. If you want the totals per year you should write SELECT year,sum(mark),sum(maxmark) FROM table1 GROUP BY year If you want the totals per parameterno it should be SELECT parameterno,sum(mark),sum(maxmark) FROM table1 GROUP BY parameterno From the ...


0

"to enforce the fact that each of the lifecycle steps must happen in a particular sequence." If you want to enforce referential integrity of an item's life-cycle, then I would recommend using essential integrity enforcing tools built into the product, namely constraints. Having independent keys is good for quickly getting a record specific to a table, but ...


7

Just add the DBName to your first query: DECLARE @sql varchar(max); SELECT @sql = Coalesce(@sql + ' UNION ALL ', '') + 'SELECT '''+name+''' AS DBName, count(*) as ''Number of Sections'' FROM ' + QuoteName(name) + '.dbo.section' + CHAR(13) FROM sys.databases where database_id > 4; --PRINT @sql EXEC (@sql); Generally you want to avoid ...


1

I like to use a template and then perform a replace string then execute it, for example: SET @SQL_SCRIPT= ' INSERT INTO ##DataTables (DatabaseName, [Other Information]) SELECT ''{DATABASE_NAME}'' AS DatabaseName, ....' Then before execution, do a replace: SET @SQL_SCRIPT = REPLACE(@SQL_SCRIPT, '{DATABASE_NAME}', @DBName) Where @DBName is the ...


1

A way to resolve this would be to put the joining of things with tags into separate tables. You would have your original tables users, posts, meta-tags and perhaps items. In addition, you would have user-tags, post-tags, and perhaps item-tags. The later three tables would only have the keys from the specific type table (e.g. user_id) and the key from the ...


0

Uncheck the box in the follow setting: Check Tools -> Query Execution -> SQL Server -> Advanced -> "Disconnect After The Query Executes"


3

does the index data get switched as well? Yes. It would be bizarre if it didn't because then queries would return the wrong results or we would have to manually rebuild the indexes after switching. I don't know how to verify this One way would be to just try it CREATE TABLE [dbo].[GroupEvent] ( GroupName VARCHAR(100) INDEX ...


4

Simplified version drop table TabA; create table TabA ( a int , b int ); insert into TabA values ( 1, 11 ), ( 2, 22 ), ( 3, 333 ); select * from TabA; --T1. Run T1 first. begin tran t1 insert into TabA values (5, 55 ); waitfor delay '00:00:10'; --Run T2 in the other session within 10 sec. -- After delay expires deadlock is ...


2

A graph database would be a good candidate. They specialise at retrieving interconnected data and navigating the relationships between objects. The ones I'm familiar with allow dynamic schema so different objects can have different values. Some allow classes of objects to be constructed so some consistency can be enforced. The links between objects are ...


1

In order to make a decision for bunch of tables you can run procedure "sp_estimate_data_compression_savings": exec sys.sp_estimate_data_compression_savings @Schema,@Table,NULL,NULL,ROW; exec sys.sp_estimate_data_compression_savings @Schema,@Table,NULL,NULL,PAGE; That will help you determine savings for each particular table. From my experience PAGE ...


0

the solution was to specify the database prefix in the sql query in the @query parameter. After lot of scratch on my head i realized without prefixing DB name+Schema in Exec @query it will not work. Did not work: @query = 'SELECT fields FROM table' Works: @query = 'SELECT fields FROM database..table'



Top 50 recent answers are included