Tag Info

New answers tagged

1

Lennart's answer explains why your query fails and how to fix it so it is syntactically correct. About the additional problem that you "want to display the output regardless if one of the joined sub queries doesn't have any rows", you could rewrite the query and simplify it: SELECT AVG(CASE WHEN es.DateID = 'Jan2015' THEN sf.score ELSE NULL END) AS ...


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


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


-4

Remove the keyword OUTER. MySQL uses the ON/WHERE clause to decide between INNER and OUTER functionality. There is another problem... If Employee#1 was not there one of the months, the result will be the empty. I suggest that you use a 4th subquery (first) to find all employes, then LEFT JOIN to each of the existing subqueries. But then you may have ...


0

I could achieve your query in three steps. Assuming your table values like below Step1 : In SQL View paste this query and save it as part1, This is for package_id transpose TRANSFORM First([packing].package_id) SELECT [packing].[product_id] FROM packing GROUP BY [packing].[product_id] PIVOT [packing].package_id; Step1 : In SQL View paste this query and ...


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


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


0

You are missing the single quote closure after motherboard in both of your queries AND value != 'Motherboard AND AND a.value != 'Motherboard The first query can be more organized as SELECT id_product FROM Atr_basic WHERE attribute = 'Product Type' AND category = 140 AND value NOT IN ( 'Motherboard', 'Intel Motherboard')


0

The error is being raised because it doesn't know how you want to define the dummy_driver table. What fields should be part of the table? From the code you show I am assuming dummy_driver has the same fields as Person, given the code you've shared. If so, the code would look like this to create the table: CREATE TABLE dummy_driver ( first_name <insert ...


0

There's a few things wrong with your post. But cutting to the point: Judging by your code samples, the fastest/easiest way to create a table as a copy of another is to "select * into tableA from tableB" In your case, assuming you only have (or want) the 2 columns you have listed you could use the following: SELECT f_name, l_name INTO dummy_driver ...


0

You have to define the elements (columns) of a table. For example. This code creates a table named people with fields for first name and last name (defined as firstname and lastname respectively) which holds up to 30 characters for each name: create table people( firstname varchar(30), lastname varchar(30) ); Next, we can insert the person John ...


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

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

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


0

Ok, After further analysis and following the recommendation of Aaron Bertrand and google "parameter sniffing" i found what is causing this its the SQL Server. This is the "normal" behavior of the SQL Server, and the queries must be analysed and changed individually to resolve its problems. Here is the link for the best description of what is happening ...


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


0

IF((select REPLACE(name,'.','') from #tmpDots) = (select name from #tmpNoDots)) PRINT 'TRUE' ELSE PRINT 'FALSE' Replace the dots with the space of the characters that are worrying you using the Replace() function in SQL and this should do it. You can also embed this logic in some SQL function or in an SP. Let me know if more help needed.


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

use a View and UNION ALL SELECT col1,col2 FROM tableX UNION ALL SELECT col1,col2 FROM tableX_Archive


0

After some processing this boiled down to: While your predicate d."SettlementPointName" = 'John' is filtering a single value for "SettlementPointName" anyway, simplify to: SELECT count( d."SettlementPointPrice" < 10.5 OR NULL) AS da_00_10 , count(d."SettlementPointPrice" >= 10.5 AND d."SettlementPointPrice" ...


0

Assuming the system handles only a few actions per second and only millions of ids, either flavor of schema design should work well enough. I can think of no strong argument for, or against, splitting the confirmations off into a separate table -- performance, sparseness (or lack of) in the Confirmations table, 1:1 relationship, coding convenience, purging, ...


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


0

I used this script in a custom report once. ;WITH AG_Stats AS ( SELECT AGS.name AS AGGroupName, AR.replica_server_name AS InstanceName, HARS.role_desc, Db_name(DRS.database_id) AS DBName, DRS.database_id, ...


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


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

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

SELECT TOP 1 * FROM #tmpData ORDER BY NEWID() This should work. Let me know if there are further questions!


-1

Try this: SELECT TOP 1 field FROM table ORDER BY NEWID() You can find more on this subject here


0

Using SQL Server, following should work select 'all' as stateid,'all' as statename, 'all' as stateshortname union all select cast(stateid as varchar(16)), statename, stateshortname from state order by stateshortname A minor performance optimization in this cas is to use UNION ALL instead of UNION. Using a union essentially does a distinct, forcing the ...


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;


0

I've added last used date and code for dropping to Raj's query. SELECT OBJECT_NAME(S.[OBJECT_ID]) AS [OBJECT NAME], I.[NAME] AS [INDEX NAME], type_desc, coalesce(last_user_seek,last_user_scan,last_user_lookup,last_system_scan,last_system_seek,last_system_lookup) as LastUsed, USER_SEEKS, USER_SCANS, ...


0

We changed the SQL SERVICE user to one that is "Domain Admin". I've made some researches to know why this happens. It says that when you shutdown the service, you need an account with privileges do create a new SPN 9 when it turns on again ). If you start a service without it, it will show in CANNOT GENERATE SSPI CONTEXT. we are changing the privileges of ...


0

From what you post, seems ok . My guess is that you have some joins that alter your result set. (7 rows x 3 tables = 21 records) Can you post the entire code ? One more remark: are you sure that you pass all the values available in that fields ? If you escape one , then 'ALL' <> ('a','b','c','d','e','f','g')


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


0

Maybe you could try with: WHERE c.tid = :id AND c.pid IN(0,1) or WHERE c.tid = :id AND c.pid <=1 but I am not sure what you want to get. Maybe an UNION is more appropriate. -- only 20 with c.pid = 0 (SELECT *, c.id AS cid, u.id AS userid FROM comments c INNER JOIN users u ON u.id = c.uid WHERE c.tid = :id AND c.pid = 0 LIMIT 20) UNION (SELECT ...


0

Can you please confirm the Compatibility level of the database you upgraded? (Right-click the database -> Properties -> Options -> Compatibility level). If this is set to 2005, please don't change it without thoroughly testing your applications, etc. as it could definitely break your environment! Just curious as to the value here. Also, I did some work with ...


0

Here is what i did: Created a new data flow to look up and insert non matching fields. Inserted the actual data flow.


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


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


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


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


1

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


0

From your comment, it seems you are looking for a particular string, "Jack". Accordingly, this will find all occurrences in both FirstName and LastName columns: SELECT firstname FROM Customers WHERE FirstName = 'Jack' OR LastName = 'Jack';


1

You should look here at Managed Tablespace Alerts, notably this bit where you can monitor by two criteria with warning and critical thresholds. By percent full For both warning and critical thresholds, when space used becomes greater than or equal to a percent of total space, an alert is issued. By free space remaining (in kilobytes (KB)) For ...


2

'Cannot generate SSPI context' is a generic error. It can be caused by many issues, like an outaded password, clock drift, Active Directory access permissions, failure to register an SPN and so on and so forth. There is no solution to this problem. The only 'solution' is to investigate the cause, as per KB811889 and/or Troubleshooting Kerberos Errors. ...


0

If you are not sure of the string is from which column, you need to look into both the column and the number of rows in result will more compare to when it search in a single column but the output will have all the set of possible result for name "Jack". SELECT FirstName FROM Customer WHERE FirstName LIKE 'Jack%' OR LastName LIKE 'Jack%';


1

cascadus2 isn't a number. But you can strip out everything that isn't a number then convert that to a number. select regexp_replace('cascadus2', '[^\d]+', '')::numeric result: numeric ------- 2


1

Quick and dirty, but I'm assuming you'll be putting this into a more formalizing procedure for spitting out names. DECLARE @Firstname nvarchar(50) DECLARE @Lastname nvarchar(50) SET @Firstname = 'Marc-Oliver' SET @Lastname = 'Saint-Onge' SELECT @FirstName, @Lastname IF CHARINDEX('-', @FirstName) <> 0 SELECT SUBSTRING(@Firstname, 1, 1) + '.' + ...



Top 50 recent answers are included