Tag Info

New answers tagged

0

Well, perhaps not the best way, however, I managed to do it via this: (I ignored that extra third table, wasn't sure ... not sure how it affects things) ;) (the w_tab1 and w_tab2 are just your sample data ... ) Tested on Oracle 12. with w_tab1 as ( select 28 idproduct, 18 totalout from dual union all select 26 idproduct, 8 totalout ...


1

If you are simply interested in adding a column Users_ID to the Content table, then the following will likely do the trick. (Assuming this is indeed SQL Server - see the 2nd code block for the MySQL version). /* add the Users_ID column to the table */ ALTER TABLE dbo.Content ADD Users_ID INT NULL CONSTRAINT FK_Content_Users_ID FOREIGN KEY ...


0

where will that data be saved by default ? Agent history clean up: distribution calls EXEC [distribution_db_name].dbo.sp_MShistory_cleanup @history_retention = 48 This PROC deletes from MSsnapshot_history , MSlogreader_history, MSdistribution_history, MSqreader_history and MSmerge_history and bunch of other tables. This is the entire code for a ...


0

First thing if transaction log is not damaged you should go for tail log backup. That would cause minimal data loss. Then restore would be Weekly full backup with no recovery 10:05PM Differential backup with no recovery All log backups after this differential backup with no recovery and finally tail log backup with stopat command with recovery Please ...


3

You need to restore last FULL backup, the Differential, the 10:15 transaction backup and the 10:30 one with the STOPAT = '2015-05-22 22:24:00' clause.


0

This is actually a very good question since MyISAM stores the count in its header, whereas InnoDB requires a full count (See my answer to the post Why doesn't InnoDB store the row count?) SOLUTION Use INFORMATION_SCHEMA.TABLES to create the SQL for counting each table ALL DATABASES SET group_concat_max_len = 1048576; SELECT ...


0

There are reasons why Oracle might change exec plan without statistics updates. There are Dynamic sampling, Bind variables peeking and Performance baselines. These features should help Oracle to adapt the execution plan with actual load and actual data characteristics. There can be various reasons why Oracle decides to use different exec plan to the same ...


-1

Basically, yes in the short term. If it were intending to go a different way it would generate a different plan, and no database (to my knowledge) backtracks and finds a different plan once it has started processing. If you are looking at a plan generated before a query was run then any statistics (rows, number of iterations of lopped constructs, and so ...


3

The short answer is that no, the same SQL doesn't automatically lead to the same execution plan. For example, one may have a small table and the optimizer may realise that there's no point in using an index since an FTS (Full Table Scan) would be cheaper than trawling through an index and then performing lookups. The "No" answer is the reason that Oracle ...


0

Some years ago I tested different solutions (amongst others some similar to those from @ErwinBrandstetter) for merging overlapping periods on a Teradata system and I found the following the most efficient one (using Analytical Functions, newer version of Teradata have built-in functions for that task). sort the rows by start date find the maximum end date ...


-1

Excellent collection of SQL SERVER Puzzles [ 90 Puzzles ] @ http://msbiskills.com/tsql-puzzles-asked-in-interview-over-the-years/ Enjoy ! Happy Learning..


2

Use a CASE statement in the column list to check if ParentID is 1 or null. SQL Fiddle PostgreSQL 9.3 Schema Setup: create table YourTable ( Name varchar(10), ItemID int, ParentID int ); insert into YourTable(Name, ItemID, ParentID) values('A', 1, null); insert into YourTable(Name, ItemID, ParentID) values('B', 2, null); insert into ...


1

You can use this insert statement to insert some totally useless data in there (assuming the FK accuracy is not important here) but it satisfies your conditions. DELIMITER $$ DROP PROCEDURE IF EXISTS Events_insertion$$ CREATE PROCEDURE Events_insertion() BEGIN DECLARE i INT; DECLARE dTime DATETIME; SET i = 1; SET dTime = NOW(); ...


2

Lets say that the media tables has 1K rows and the emails one has 100K rows. Then this will insert into the emails_media_xref all possible combinations, 1K x 100K = 100M, that is a hundred million rows. Warning, this may take some time: INSERT INTO emails_media_xref (emails_id, media_id) SELECT emails.id, media.id FROM emails CROSS JOIN ...


5

Sure : select * from database_n.dbo.Table_1 UNION ALL select * from database_n+1.dbo.Table_1 UNION ALL select * from database_n+2.dbo.Table_1 -- etc.


0

Based on the answer of Mark, I was able to define the exact query as it follows: INSERT INTO subscription(username,name,startdate,enddate,saledate,pkgid) SELECT username, max(name), max(startdate), max(enddate), max(saledate), 41 FROM subscription group by username


0

The problem is that the query has to retrieve 13.7 billion rows from FIELDDATA in aggregate. This is caused by two significant issues with your query. One will be easy to solve, one will be harder. The easy problem is with the index. As one commented suggested, you need to add a nonclustered index on FIELDDATA(ID, FIELDID). The clustered index scan is ...


2

No loops are needed in this (please!). You should be doing an INSERT...SELECT. Always run the SELECT part of the statement on it's own first, to make sure it is only selecting what you expect to be inserted. INSERT INTO subscription(username,name,startdate,enddate,saledate,pkgid) SELECT DISTINCT username, name, startdate, enddate, saledate, 41 ...


0

SELECT SUM(Room.price) AS totalincome FROM Room INNER JOIN booking ON booking.roomNO=room.roomNo INNER JOIN hotel ON hotel.hotelN0=room.hotelNo WHERE hotel.hotelName='Sofitel' AND Date BETWEEN 'YYYY-MM-DD' AND 'YYYY-MM-DD'


1

Not in a T-SQL function, but executing stored procedures can be done in SQLCLR functions. An example of using a .NET / C# SQLCLR user-defined function to execute a stored procedure is shown in the following article (which I wrote): Stairway to SQLCLR Level 2: Sample Stored Procedure and Function


0

In MySQL You can try a function create function modul_id() returns INTEGER DETERMINISTIC NO SQL return @modul_id; create view v (your select... where modul_id = modul_id()...); select * from (select @modul_id:=14 modul_id) m, v; not tested on your query...


3

You have an error in your query. The CTE m will return not a single row, but as many rows as in FCS_FP_TOMB. Then you are putting all of those rows into the row generator CTE n, whereas a row generator construct like you're using should get a single row. What I would do is to define m to return a single row with the minimum and maximum values, and then ...


0

If I understand, you want to get all trip_id values that appear in the table, against one stop_id on one or more rows, and against another stop_id on other rows. This should do it: select trip_id from stop_times where stop_id = 1538 or stop_id = 1540 group by trip_id having count(distinct stop_id) > 1; Example: http://sqlfiddle.com/#!9/e78aa/1 ...


1

If you need to execute sql file use sqlcmd documented in the MSDN: Run Transact-SQL Script Files Using sqlcmd This did the trick for me: sqlcmd -S serverName\instanceName -i script.sql -o output.txt


3

You can convert your string to XML and use a cast as xs:int ? to check if the value is an integer or not. declare @T table(C varchar(100)); insert into @T(C) values ('Widget (1234)'), ('Different Widget (123)'), ('Many Widgets (2x) (4567)'); select T.C, T2.X.value('(for $n in X return $n/text()[1] cast as xs:int ?)[1]', 'int') as Value from @T as T ...


2

First of all, I have to ask: Why are you doing this? If you have a performance issue, pursue it. However, you only mention that you are seeing scans instead of seeks. Scans are not always bad - they can be the most efficient method of pulling large amounts of data since sequential file access is less costly in I/O terms than random access. The commenters ...


2

Simpler view For this objective: The point of which is to give averages of the numeric columns for each hour .. it seems just as good to truncate to full hours, which is simpler and cheaper: CREATE OR REPLACE VIEW spp.rtprices_hourly AS SELECT date_trunc('hour', "interval") AS hour , node_id , round(avg(rtlmp), 2) AS rtlmp , ...


3

If the data you need is always at the end of the string, would something like this work? declare @test varchar(200); set @test= 'Many Widgets (2x) (4567)'; select substring(@test, (len(@test) - charindex('(',reverse(@test))) + 2, charindex('(',reverse(@test)) - 2 ); We can reverse the string, find the ...


0

This is a good start: select S.FIRST_NAME, C.CLASS_NAME, C.DURATION_OF_CLASS FROM STUDENTS S INNER JOIN PAYMENTS_PER_STUDENT A ON S.STUDENT_ID=PAYMENTS_PER_STU_ID INNER JOIN CLASSES_R C ON A .CLASSES_CLASS_ID = C.CLASS_ID; I found it + here.


0

Most people just can say that cannot be done in MySQL's dialect of SQL (and they are right), but they do not propose an alternative. If you cannot change the database structure, use @MatthewSwain option. If you can, however, transform it into the following structure (which you will only have to do once) it can simplify the query a lot, specially if you have ...


2

In SQL Server you can do this with a recursive CTE, but unfortunately this isn't supported by mySQL. Instead, you need a recursive function/sp which calls itself to get the full tree, such as the following: set @@session.max_sp_recursion_depth=255; delimiter $$ create procedure get_bu_tree ( in parent int ) begin create table if not exists ...


0

I'm not entirely sure if I'm right, but as far as I know solution 1 will not work since you can't add two foreign keys in the same column and even if it is possible the values will probably overlap, such as existing a UserId = 1 and a CompanyId = 1 causing you not to know which of them is the true one, furthermore thinking of a big solution you may even ...


1

This is a very common question, and unfortunately the answer is that we've fallen into one of the most serious deficiencies that MySQL has. In MSSQL, we could use recursive CTEs to accomplish this type of result. Just about every other RDBMS has some implementation that will allow for recursive querying. Unfortunately, in order to accomplish this cleanly ...


5

If you have a table where the relationship between the numbers and letters is stored, it's rather trivial. If you haven't you can simply add it - every time you run the query - as a CTE or derived table: SELECT COALESCE(test.a, test.b, test.c, test.d) AS test, map.letter FROM test LEFT JOIN ( VALUES (1, 'e'), (7, ...


0

Well, in the article you reference, he specifically states "The first category of scripts fall out of the realm of database changes." That said, I've worked with plenty of legacy codebases that had select statements in source control; they were just in the application code. (So, subject to all the problems you mention and hidden from the DBA.) Before ...


13

That is just documented behavior. I don't think anyone messed with the settings. See data type precedence on MSDN. As noted in the comments the empty string gets converted to 0 in any numeric type and to 1900-01-01 00:00:00.000 when converted to a date. EDIT: I think your real problem is that your design is so that you have to join on fields of a ...


0

You can use a sort of UNPIVOT with CROSS APPLY VALUES, like this: DECLARE @Addresses TABLE ( [Address] nvarchar(100), [City] nvarchar(100), [ZIP] char(5), [State] nvarchar(100) ) INSERT INTO @Addresses VALUES ('#500-75 O''Connor Street','Ottawa','K4B 1','Ontario'), ('#9900 2700 Production Way','Burnaby','V5A 4','British Columbia'), ('00, ...


0

If you want to know which column matched the parameter your best bet is probably to use a union query such as the following. set @param = '%new%'; select address, city, zip, 'address' as matched_on from mls_data where address like @param union all select address, city, zip, 'city' as matched_on from mls_data where city like @param union all select address, ...


0

In SQL*Plus/SQL Developer, when you use &VariableName to get user input, this doesn't get unset when the variable is used. The variable stays set until you unset it. You can do this with UNDEFINE VARIABLENAME Depending on your version, which you haven't specified, this might be the reference you need: ...


1

Cardinality in this sense refers to the number of unique values, usually when referring to a column. For example, in a table containing information about employees, the gender column would be said to have low-cardinality (few distinct values, repeated many times) whereas telephone number would have high-cardinality (many unique values). It seems odd to me ...


0

You should use the nodes() method of the XML data type to shred your XML. If I understand you correctly you want to fetch the nodes from the root node where the attribute type is line and from that node you want the nodes where the attribute type is 'item'. I will just assume that the XML you have provided with numeric node names is not the actual XML you ...


1

Core feature is the window function lag(). Also pay special attention to avoid deadlocks and race conditions with concurrent deletes and inserts (which can affect which rows to delete!): CREATE OR REPLACE FUNCTION remove_vendor_price_dupes(_vendor int) RETURNS integer AS $func$ DECLARE del_ct int; BEGIN -- this may or may not be necessary: -- ...


2

The answer may be referring to cardinality as in the number of rows within the table. This link has an explanation of each definition of cardinality in a database: http://www.programmerinterview.com/index.php/database-sql/cardinality-in-sql/


4

Generally, you can always wrap a query as subquery if you don't want to output all rows: SELECT title FROM (SELECT ...) sub; But you can also use expressions in ORDER BY, not just input or output columns. So there is no need for this (like @ypercube already commented). For the case at hand, it must be mentioned that you are using the additional module ...


0

Is there any way the XML can be better formed (I understand this isn't always possible)? I've never come across dynamic element names before, and while that certainly doesn't mean it can't be done, it's much more difficult. However, if you're able to get some static items in your XML, it makes it much easier to deal with. If the XML was stored in a similar ...


-1

As i understand you need the XML upload option which customized but not hard coded one you need. SELECT * FROM T UPDATE T SET XmlCol =( SELECT * FROM OPENROWSET( BULK 'C:\SampleFolder\SampleData3.txt', SINGLE_BLOB ) AS x ) WHERE IntCol = 1; GO The answer is available in MSDN, Please refer the below link MSDN Article


1

In this case, if you cant serialize this in the application code you can use try/catch or an applock begin tran if APPLOCK_TEST ('public','idx_create','exclusive') = 1 begin EXEC sp_getapplock @Resource = 'idx_create', @lockmode = 'Exclusive', @DbPrincipal='public' print 'I can do it' -- EXEC ...


0

Jynus's answer is very good, but i would also add that in some cases more than one table in a query can have the same field name which is ambiguous and must be resolved. Although I guess you could use a full table name as a prefix. In my experience when dealing with extremely long table names that can have very similar spelling, using aliases makes the ...


0

I think this query will give the results: SELECT *, (t.mass / MIN(mass) OVER (PARTITION BY mark1) - 1) * 100 AS devValue, CASE WHEN t.mass = MIN(mass) OVER (PARTITION BY mark1) THEN 'min' + mark1 ELSE CASE WHEN t.mass > MIN(mass) OVER (PARTITION BY mark1) THEN '+' ELSE '-' ...


8

In some cases, MySQL requires an alias, for example, a self join: mysql> CREATE TABLE test (id SERIAL, name text); Query OK, 0 rows affected (0.02 sec) mysql> SELECT name FROM test JOIN test on test.id=test.id; ERROR 1066 (42000): Not unique table/alias: 'test' mysql> SELECT T1.name FROM test T1 JOIN test T2 on T1.id=T2.id; Empty set (0.00 sec) ...



Top 50 recent answers are included