Tag Info

New answers tagged

0

To get all unique pairs of elements from an array of arbitrary length: WITH a(a) AS (SELECT '{A,B,C,D}'::text[]) -- provide array here , i(i) AS (SELECT i FROM a, generate_series(1, array_upper(a.a,1)) i) SELECT ARRAY[a[i1.i], a[i2.i]] AS pair FROM i i1 JOIN i i2 ON i2 > i1 , a; You can then join to the message table. Without knowing any ...


1

Query You UPDATE statement looks good, mostly. I re-formatted and made minor improvements: UPDATE line_items li SET product_id = d.latest_product_id FROM products p JOIN vendors v ON v.id = p.vendor_id JOIN vendorgroups vg ON vg.id = v.vendorgroup_id JOIN duplicate_product_sets d ON d.invtid = ...


0

Wrap the selects into parentheses (SELECT DISTINCT CONCAT (MONTH(`Date`),'-',YEAR(`Date`)) from wp_result_plugin WHERE Bet=2 AND month(`Date`) = EXTRACT(month FROM (NOW())) ORDER BY MONTH(`Date`) ASC ) UNION (SELECT DISTINCT CONCAT (MONTH(`Date`),'-',YEAR(`Date`)) from wp_result_plugin WHERE Bet=2 AND month(`Date`) ORDER BY MONTH(`Date`) ASC)


0

SELECT Id,username,email,usertype,active ,(CASE WHEN usertype='1' THEN 'Admin' WHEN usertype='0' THEN 'Super Admin' WHEN usertype='2' THEN 'User' END) AS UserRoleName ,ngo_name FROM tbl_User u LEFT JOIN tbl_ngoname ngo ON n.UserID = ngo.Userid --or whatever your key fields are ORDER BY Id DESC


0

NoSQL databases have faster writes mostly because they do not support cross-document transactions, ACID, and lack various data integrity constraint usually found in RDBMS. Neither of those things are trivial to support, especially in an environment where the database serves read/write requests from many users concurrently. Think of a typical table in a ...


1

Merge them. Sometimes it's worthwhile to have a separate Addresses table, but generally it's simplest to make those fields of a single Customers dimension. Because addresses are nearly unique, you're not going to save many records by hiving them out to their own table. If your Customers table is really large, and addresses change relatively often then ...


3

The problem is that you are missing a way to tie the outer table to the data inside of the STUFF. You can do this by adding a WHERE clause to the subquery: SELECT x.SurveyQuestionID, PossibleAnswers = STUFF(( SELECT ', ' + Answer FROM SurveyQuestionPossibleAnswer sqpa INNER JOIN SurveyQuestion sq ...


1

If you look at the standard SQL way of constructing a recursive query, you would see something like this: with t (num) as ( select rownum-1 num from dual union all select num+1 from t where num+1 < 10 ) select * from t; SQLFiddle: http://sqlfiddle.com/#!4/9eecb/4371/0 As you can see, it starts with selecting one row from the DUAL table, then ...


0

Looks fairly ominous for you I'm afraid. Likely an issue with data integrity or the validity of your drive. http://www.sqlserverf1.com/tag/restore-database-is-terminating-abnormally/


1

I would suggest NOT to use Designer in creating key constraints or doing any DDL / DML operations. Best is to use T-SQL - much flexible, more options, more powerful and you can automate many things with it. There is a learning curve, but its worth learning TSQL rather than keep using GUI. Also, make sure you are using the latest version of SSMS - sql ...


0

One way would be a check constraint, but this will not give meaningful error messages create table Table1 (PaymentType varchar(5) not null, CCNumber varchar(16), PONumber varchar(30), CheckNumber varchar(10)); GO ALTER Table Table1 add CONSTRAINT CHK_PAYMENTTYPE CHECK (CASE WHEN PaymentType = 'CC' and CCNumber is NOT NULL and PONumber is NULL and ...


0

One way to accomplish this, using Java, would be to prepare the query statement, then use the PreparedStatement.getMetaData() method to obtain the ResultSetMetaData object that has the list of columns that would be returned, as well as their data types. Other database interfaces, such as ODBC and OCI, have similar functionality.


1

You should use AVG function if you need to get the average Using MINUTE function will return the minute part from the date. i.e. 10:05:03 and 11:05:33 will give the same result. (It is fine if the interval is less than 60 minutes, however, it is safer to use the following query:) SELECT left(timestamp, 16) as mtimestamp, avg(vif_2_rx) FROM ...


0

Once you create table use query below yo add first 10 records into table * job_history INSERT INTO job_history VALUES (102, '19930113', '19980724', 'IT_PROG', 60), (101, '19890921', '19931027', 'AC_ACCOUNT', 111), (103, '19931028', '19970315', 'AC_MGR', 110), (201, '19960217', '19991219', 'MK_REP', 20), (114, '19980324', '19991231', 'ST_CLERK', 51), ...


1

I don't think that any of these suggestions are particularly improvements in performance compared to the CASE as that's already very basic. Another possible option (although again, probably not worth the effort as it's just a short-hand way of writing your CASE) for SQL Server 2012 and above. I'd be interested in seeing what the reaction was of the person ...


5

This should do what you need SELECT ISNULL(ta.[tag-value], 'N/A') AS [tag-value] FROM @AgentTableFilt ta Or you could use the following SELECT COALESCE(ta.[tag-value], 'N/A') AS [tag-value] FROM @AgentTableFilt ta


3

Use an UPDATE statement. update student set total = social + math + science; But that column is totally useless. In general you should not store data in a database that can easily be derived from existing values. In your, adding the values of the three columns adds no (noticeable) overhead to a select statement, so there is no point in storing the ...


1

Your file is probably large but empty. In SSMS, right-click on your database and go into Tasks, Shrink Files. DO NOT HIT OK IN THIS DIALOG - but it's the easiest way of seeing how empty your files are. Choose Log in the dropdown, and I imagine you'll see that your log file is actually 99% empty. You probably had a large process that grew the file to ...


3

1. Subquery expression You can fix it with parentheses like @a_horse commented: SELECT * FROM test_function((SELECT customerid FROM tableX where id = 1)); But this form is rather error-prone. Nothing in the code guarantees that the sub-select only returns a single row. We don't know whether id is unique and neither does Postgres (unless it looks up system ...


1

Red Gate's SQL Prompt is what I use and has several dozen different settings. It also has a better auto suggestion and code snippet engine than many others I've tried. Well worth the money in terms of time saved.


4

What is the mathematical equality of two SQL statements? For me two queries are equivalent if, when given both the same of any dataset, they return the same result set. As you pointed out, SQL queries, a superset of relational algebra, can be very complex. We can mix subqueries, use stored procedures and functions (deterministic or not) which will make you ...


2

It is impossible to check semantic equivalence in finite time by definition, see Rice's theorem: for any non-trivial property of partial functions, there is no general and effective method to decide whether an algorithm computes a partial function with that property.


0

One way to do it is to build a parser, or better, use an existing one. I believe C# has a TSQLParser class and has a Parse() method. The parser will break your query to sub-classes which you can then compare.


0

Hint: instead of adding define _END = ""; to each template, it can be added into dialect (netezza.tpl for example). This has fixed compilation of all the templates on the latest tpc-ds 1.3.1.


0

There is the possibility that you are getting lock escalations onto the table level, or page locks on the clustered index page. A deadlock graph will show that, try starting a SQL Server trace grabbing just the Deadlock graphs from the server and check those You can also delete in batches which will improve concurrency and if the delete is handling large ...


1

update mytable set myfield1 = replace(replace(replace(replace(replace(myfield1, '…' ,'-'), '?' ,'|'),'”' , '"'),'‘' , '`'),'?' ,'"');


1

Your question is a bit light on definitions. Assuming: You define the "least amount of information" with how many of the relevant columns are NULL. Primary key is adr_id. Duplicates are marked with a column dupe_id to indicate groups of duplicates. Since it's also vague what to do exactly, I create a separate table with the dupe ranking: CRATE TABLE ...


0

Well, many thanks James, Mark, Aaron and especially David Benoit. I read the Joe Sack article you mentioned and it led me to Benjamin Nevarez's article - http://www.benjaminnevarez.com/2011/05/optimizer-statistics-on-linked-servers/. which then pointed out the BOL - Guidelines for Using Distributed Queries. The stored procedure had 3 remote queries. The ...


0

Many of the plan options in Profiler are estimated plans, so you will have to use the correct one to get the actual plan. Since you are using SQL Sentry (great product by the way :) ), you should be able to get the plan for the statement run from the Top SQL tab if you check shortly after the job was run. Regardless, IF at all possible, I recommend that ...


0

left join customer B on OrderNo = appearance Guessing customer is varchar and OrderNo is int and its having problems converting?


0

Got this answer from StackOverflow : SELECT src.ID, src.TicketNo, src.TicketQuantity, src.TicketRate, src.EnteredDate FROM ( SELECT TicketNo, MAX(EnteredDate) AS MaxEnteredDate FROM Tickets GROUP BY TicketNo ) AS mtn INNER JOIN Tickets AS src ON mtn.TicketNo = src.TicketNo AND mtn.MaxEnteredDate = src.EnteredDate ORDER BY src.EnteredDate DESC


0

For Sql Server , you can try something like this using GROUP BY UPDATE t SET date_to = '3000-12-31' FROM table t INNER JOIN ( SELECT ID ,max( date_to ) date_to FROM table group by ID ) t2 ON t.ID = t2.ID AND t.date_to = t2.date_to WHERE t2.date_to <> '3000-12-31' Or like this using ROW_NUMBER() UPDATE T SET date_to = '3000-12-31' FROM ( ...


3

As for the difference between INNER JOIN ON vs WHERE clause there is a good answer here. There are several answers there and the accepted answer pretty much summarises it all. However, I cannot but comment that you query can be rewritten to significantly improve the performance, like this: select wifi_name ,sum(case when success = 1 then 1 else 0 ...


0

I guess this is what you are looking for : UPDATE table_name SET date_to = "2011-05-29" WHERE date_to IN (SELECT MAX(date_to) FROM table WHERE date_to NOT IN("3000-12-31") GROUP BY ID); But it would be better if you use a unique ID(Primary Key) for each row,so that the update would be easy.


0

Try to use GROUP_CONCAT and then GROUP_BY your id. SELECT t.id1, t.status, t.id2, t.name, GROUP_CONCAT(t.category) category FROM yourtable t WHERE (t.name = 'john' or t.name = 'othername') GROUP BY t.id1


1

you could store your result in a Table Variable DECLARE @TableVar TABLE (col1 varchar,col2 int,...) insert into @TableVar exec (Proc1) select Col1 from @TableVar


0

First, it's hard to ascertain what you need as I don't know what your data means. What is ShareID and what are the groups for? How do ItemIDs relate to ShareID? Is the data one-to-one or one-to-many? etc... With that said, I do know for sure that Option 1 does not follow good practice. You don't store different types of data in the same columns. You have ...


0

This is not possible as the listener just handles the initial connection between the client and the database - so the database doesn't know anything about it. So anyways there at least two ways to get the path Looking at the default locations (i.e. $ORACLE_HOME/network/admin) fire up the OS-Command "lsnrctl status" and grep the path out of the output


1

The listener is an independent entity, can run from a different Oracle Home or even another machine. All the database knows about it is its address.


1

You have conflicting login options in your connection string. There are two main types of authentication: Windows Authentication - using your (or service account's) Windows Active Directory credentials. This option does not require a user ID and password to be specified. This is designated by trust_connection=yes SQL Authentication - a login made on SQL ...


1

The issue is that you are running your GIS programme from a domain that is not trusted by the domain in which SQL Server resides. That are numerous methods you can use to correct this. You should discuss with your network admin/sql server admin as to the best approach. Create a SQL Server Account and use those credentials to connect to the SQL Server ...


0

Following is the SQL Server version in steps to work on IPs Range this might help you to convert in SqL Step 1: Create function [usp_stringpart] in your Database Create By: Aasim Abdullah Description: Function takes a string and section number of string which is required and separator, which separates different sections of given string Create FUNCTION ...


-2

To "proceed" user ID, just enable auto increment. To return friends, have the table include a "friend id" that can be joined to other user IDs. To obtain the total amount of friends, use the "count" function paired with friend IDs associated with their user ID Sql Count Function


1

If I figured out correctly what you're doing, you could also do it using unpivot, something like this with the separate columns in 2 CTEs and join them side by side using number for row_number: ;with D1 as ( select row_number() over (order by (select null)) as RN, p8_14 from ( select prodt_8, prodt_9, prodt_10 from test ) p unpivot ( p8_14 for ...


0

This solved my problem. It doesnt hurt to think a little Select Top 3 [Coluna],[Valor] From( Select Round(Case When Round(Avg(Convert(float,HC08)),1) = 0 Then 0 Else Round(Avg(Convert(float,Prod08)),1) / Round(Avg(Convert(float,HC08)),1) End,1) As Prodt_8, Round(Case When Round(Avg(Convert(float,HC09)),1) = 0 Then 0 ...


1

It really depends. First of all I'm assuming you are using SQL Server although I believe all RDBMSes will work about the same in this respect. There are two major options: If there is no index that the optimizer can use then it will do a table scan. In this particular case it will run through every row (all 8400) and look for cases where name = 'jhon'. ...


-1

A SQL Select statement is not designed to stop executing on first match. If you wish to stop executing your query once it finds the first record, you should add a limit to it. (Depending on SQL server.) In MySQL: SELECT name FROM users WHERE name = 'jhon' LIMIT 1; In MSSQL (Microsoft SQL): SELECT TOP 1 name FROM users WHERE name = 'jhon'; If you are ...


1

Again, I'm a bit unclear. If you run the SELECT in your question, and if the users field is indexed, then it will to go straight to and then stop after it finds jhon. If there are multiple jhons, it will return those and then stop, the index being used to determine the point at which the server will stop searching. If the field isn't indexed, then it will ...


0

This is not an out of the box feature of SQL Server. And here's why: After a fail-over scenario, when the formerly primary server is back online, that doesn't necessarily mean that it should be put back into the primary role. Just because the server is online doesn't mean it's necessarily totally healthy from a hardware/software perspective (after all, it ...


1

How about a rewrite of it? ;with eMK_Mileage AS (SELECT 15755 AS ID, '8E79F' AS FK_CarID, 69646 AS KM , '20140716' AS KM_Date UNION ALL SELECT 15756,'8E79F' , 70393 , '20140726' UNION ALL SELECT 15757,'8E79F' , 58258 , '20140822' UNION ALL SELECT 15758,'8E79F' , 72055 , '20140822') ,FullList AS ( SELECT * FROM eMK_Mileage as Mileage ) -- FINAL SELECT ...



Top 50 recent answers are included