Tag Info

Hot answers tagged

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


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


3

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.


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


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


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


2

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


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


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


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


1

You are getting that error because you have a row in patient_info where either charges or payment (or both) are NULL. To just make the error go away, you could replace ' ' || c.charges - c.payment || ' ' with ' ' || (c.charges - c.payment) || ' ' but I suppose you would want to handle the case where charges or payment are NULL in some meaningful way, ...


1

I'll bet that you are currently I/O bound. This means that nothing can speed it up. (And Rolando's suggestions may be futile.) Let's look deeper. Is this LOAD a recurring task? If so, how often? Is everything blocked waiting for table to be reloaded? Simple solution: Load into a different table, then do a double RENAME TABLE to swap it in. Only ...


1

You may add the WHERE clause in the LEFT JOIN so not joining rows are still in the result set. SELECT `performers`.`hash`, `performers`.`alias`, `performers`.`date_updated`, `performers`.`status`, IF(`performers`.`status` = 'active', 'deleted','active') AS `statususe`, `images`.`image_hash_file` FROM `performers` LEFT JOIN `images` ON ...


1

In your question you are using "SQL" and "relational" to mean the same thing. In the same way you can think of "NoSQL" as meaning "not relational" i.e. there is no primary key / foreign key concept; there is no "normalisation" enforced or implied; there is no real meaning of the words "table" or "column". To a key-value store all it knows about is a big ...


1

According to this MySQL document the LONGTEXT datatype in MySQL is 4 Gigabytes. http://wiki.ispirer.com/sqlways/mysql/data-types/longtext Note that the number of characters depends on the encoding.


1

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


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


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


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.


1

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.


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



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