Tag Info

Hot answers tagged

8

EAV is not evil; like any other tool, it can be implemented poorly and abused. You can find articles trash talking cursors, dynamic SQL, triggers, even SQL Server itself. That doesn't make them bad things. EAV can be an appropriate solution. Whether it's the right answer in your specific case is probably more opinion-based than anything; I'm answering more ...


7

If the list of payment types and currencies are known, you can do this with CTEs. with paymenttypes as ( select 0 as ptype, Union select 1 as ptype, Union select 2 as ptype ), currencies as ( select 'Aaa' as currency union select 'Bbb' as currency ), sourcedata as ( select currency, ptype as paymenttype, 0 as amount from currencies, ...


5

You want to drop the values part of the query and do an INSERT\SELECT. This is really basic SQL and you can read more about it in BOL. INSERT INTO wtdocversion (col1, col2, col3) SELECT id, 'col2', 'col3' FROM wtdocmaster;


3

I have an EAV for tracking server / database configuration. It's great for getting data in. We can throw any data at it and the loader ensures the "E" and the "A" reflect the data given. Once we got over a few hundred million rows in the Values table, however, getting data out became increasingly problematic. (I think having 400-column PIVOT queries ...


3

Assuming a not very old version of SQL-Server, you can use ROW_NUMBER() to find one row per submitter: ; with r as ( select id, submitter, answer, rnd = row_number() over (partition by submitter order by newid()) from #tmpData ) select id, submitter, answer from r where rnd = 1 ; Test at: SQLfiddle


2

Having a wildcard in front of your search term will prevent SQL Server from being able to use an index and thus become painful very fast. 2 things you could do, besides going for full text search: The obvisous one, do not put a wildcard at the start of the keyword requiring the user to enter a search query matching the start of col1. Splitting col1 from ...


2

Yes you can. Or no you can't. It doesn't really matter. If you think about how DBMS1 is going to have to deal with a commit and/or rollback after it has, via your UDF function in which you have hidden an update to another, external, DBMS2, then you should realize that this is a very bad idea no matter how you toss or turn it, because somwewhere somehow it ...


2

So, I will answer the question "why it is creating problem with whole query": In MySQL's REPEATABLE-READ transaction isolation mode, no phantom rows appear. In order to do that, MySQL locks, not only the rows, but also the gaps between them (also know as next-key locking). As MySQL happens to be executing the read on the first table using an index scan, ...


2

One alternative method which might help you out is to save your stored procedures and UDFs that the job may use (if any) WITH ENCRYPTION. However, given the nature of your question, I wonder whether you really need to start a bottom-up review of your database systems security first, liaising with business owners regarding what permissions currently exist, ...


2

You need to specify whether you want an left, right or full outer join. A left outer join is the same as left join. Assuming this is what you want: [...] FROM (SELECT subfactor.score, dateID, EmployeeID FROM employeescore LEFT JOIN subfactor ON employeescore.SubFactorID = subfactor.SubFactorID LEFT JOIN factor ON ...


2

Just pull the DataTable object(s) and enumerate the DataColumnCollection. Here is some sample code to show how you can enumerate the collection and dump the column names: DataTable queryResults = // get your data foreach (DataColumn column in queryResults.Columns) Console.WriteLine("Column Name :: {0}", column.ColumnName); Of course this is an ...


1

Below is what you need CREATE TABLE customers ([id 1] int, [status] varchar(4), [id 2] int, [name] varchar(4), [category] varchar(6)) ; INSERT INTO customers ([id 1], [status], [id 2], [name], [category]) VALUES (001, 'open', 011, 'john', 'person'), (001, 'open', 011, 'john', 'male') ; select * from dbo.customers ---- You have to use ...


1

I've seen it done both ways. If you want to bulk up your database size just start using BLOBS. Pro's for storing documents in your database you're already storing your data there so why not a little more? (Only it's a lot more) a certain simplicity of access. Any request to read only has to go one place to access the document or thing Cons for storing ...


1

You can try to use replace function: mysql> select replace("A.B C", ".", "") = "AB C"; +------------------------------------+ | replace("A.B C", ".", "") = "AB C" | +------------------------------------+ | 1 | +------------------------------------+ 1 row in set (0.00 sec)


1

If you want even more automation, then download SQLcl, then you can do fun things like ctas current_table new_table and it generates necessary code for you. e.g., SQL> ctas my_table new_table CREATE TABLE OWNER."NEW_TABLE" ( "OWNER", "OBJECT_NAME", "SUBOBJECT_NAME", "OBJECT_ID", "DATA_OBJECT_ID", ...


1

Instead of using simply text based replace, you can do this with the metadata API, which is a more robust solution. Lets say you have a table X.TABLE1: create table x.table1 (id number, name varchar2(100), primary key(id)); You want to recreate it as Y.TABLE2, you could do the following: set serveroutput on declare metadata_handle number; ...


1

You can do it using the Roles in the msdb and using the roles for the SQL Server Agent. Detailed description can be found in the following article: https://msdn.microsoft.com/en-us/library/ms190926.aspx


1

Following sample may be useful to build query which you want to write. USE tempdb GO CREATE TABLE t_Ids (id VARCHAR(10) PRIMARY KEY ) GO INSERT INTO t_Ids VALUES('id1'),('id2'),('id3'),('id4') GO CREATE VIEW v_V1 AS SELECT 'id1' id, 2 value UNION SELECT 'id1' id, 1 UNION SELECT 'id1' id, 3 UNION SELECT 'id2' id, 1 GO CREATE VIEW v_V2 AS SELECT 'id3' ...


1

I would do this: SELECT d.currency, d.paymenttype, SUM(CASE WHEN d.paymenttype = 0 THEN d.amount ELSE 0 END) AS [Type0], SUM(CASE WHEN d.paymenttype = 1 THEN d.amount ELSE 0 END) AS [Type1], SUM(CASE WHEN d.paymenttype = 2 THEN d.amount ELSE 0 END) AS [Type2] FROM dbo.data AS d GROUP BY d.currency, d.paymenttype


1

Aggregate FILTER clause in Postgres 9.4+ Since Postgres 9.4 there is a clean and fast (SQL standard) way: SELECT count(*) FILTER (WHERE score BETWEEN 0 AND 3) AS low , count(*) FILTER (WHERE score BETWEEN 4 AND 7) AS mid , count(*) FILTER (WHERE score BETWEEN 8 AND 10) AS high count(*) AS total -- adds up, unless NULL or other values ...


1

Just use conditional SUM() statements per column for each number range. The total can be summed by just using SUM(1), assuming all of the data in the table is within one of the ranges - if not, just restrict it as with the others. select sum(case when score between 0 and 3 then 1 else 0 end) as minrange, sum(case when score between 4 and 6 then 1 ...


1

Click Add , and put a filename.bak, then retry the backup process.



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