Tag Info

New answers tagged

0

Suppose that you want sem3 then query will be Case 1# Select stureg.regno,stureg.pro,stureg.name,stureg.start_day, sem.regno,sem.curSemmester,sem.name from student_register as stureg left outer join semester as sem on stureg.regno=sem.regno where stureg.pro REGEXP '^H' and sem.CurSemmester='3' group by regno order by regno ; Case 2# Select ...


0

This sort of thing can be done fairly elegantly using Window Functions. While I was researching this answer, I found that MySQL doesn't support them. The code that follows is will likely have some performance problems since it uses nested GROUP BY select statements. Proceed with caution. SELECT UserID -- potentially add a date column here , ...


0

As was stated in the comment You're not inserting a NULL value, you're inserting "" If you want to prevent an empty string ("") and NULLs from being used as ai_name, you need a trigger. In your particular case, you need a BEFORE INSERT trigger: DELIMITER $$ CREATE TRIGGER check_for_blank_ai_name BEFORE INSERT ON actionitems FOR EACH ROW BEGIN ...


1

If the FuncWeight field in the #Temp2 table is truly defined as float(3), then the most likely problem is that the datatype of @constVal is DECIMAL(x, 0) or just DECIMAL. Either of those would round up the 0.7 values to be just 1, though the 0.3 and 0.35 values would round down to 0. Nothing in that posted code or data indicates what could force all values ...


1

Use Microsoft Performance Analyzer or Media Experiance Analyzer with Windows Performance Recorder. Any of these is capable of answering your question in great detail, quickly and foul-proof, using system ETW capabilities. This article is a good walk through: Analyzing Storage Performance using the Windows Performance Analysis ToolKit. At the simplest, it ...


1

Likely, the issue is caused by the very long IN lists. Instead of using the IN list, create a temporary table with the values you want to filter on. Then rewrite the query as a join: CREATE TABLE #FilterOutputArea (...) INSERT #FilterOutputArea VALUES ('E00139237') ... etc... CREATE TABLE #FilterColumnID () INSERT #ColumnID VALUES (298) ... etc... SELECT ...


0

There is no create table on mongo. What you need to do is read from the source collection and insert to the target using a javascript foreach loop. I am going to give you an example and not the solution for your case: db.previous_data.find().forEach(function(InsRec){db.my_hll.insert({"_id" : "+ InsRec._id +"});}) Since you are using group by instead of ...


0

Run Performance Monitor, then click Resource Monitor on the opening page. You can then use the disks tab to see all the action. Look under Disk Activity. It will show you the culprits.


2

One approach would be to use optimistic locking. Before displaying the update form, you retrieve all the original values from the database. Or, at a minimum, a column that is guaranteed to change on update, such as last_update_date or similar. When updating you check that all values are still the same in your where clause. If the update fails to update any ...


0

Please try this: UPDATE( SELECT Ord.OrderID, Ord.Completed FROM Order1 Ord WHERE EXISTS( SELECT * FROM Product P INNER JOIN Delivery D ON Delivery.Delivered = 0 AND P.ProductID = Delivery.DeliveryID WHERE Ord.OrderID = P.OrderID ) ) SET Completed = 'NO' ; or without a derived table: ...


1

To pass multi-values correctly in this stored procedure, I would need to add the following code to the dataset parameter that I am using: =join(Parameters!<your param name>.Value,",") This is basically going to join multiple values into an array and pass it through the @Flag parameter. The next step is adding SQL to the stored procedure to receive ...


0

Let me know if you have better solution than this one? SELECT TO_NUMBER (TO_CHAR (CurrDate, 'YYYYMMDD')) AS date_key, CurrDate AS alt_full_date_key, TO_NUMBER (TO_CHAR (CurrDate + 1, 'WW')) AS week_num_of_year, TO_NUMBER (TO_CHAR (CurrDate + 1, 'IW')) AS week_num_of_year_iso, CASE WHEN currdate >= NEXT_DAY (TRUNC ...


0

Mark the database file a read-only during the schema change, or run the schema change as sql commands from your app.


0

Well I copied every index that the query was calling from the prod to the replication server and updated stats…finally works…14 seconds…better than 12 minutes! 


0

ISO Definition is here, You need to check if Jan-01 is Monday, if it is, then you can use ISO week format model 'IW'. If it isn't you can still use that but subtract 1 from that value. WITH x AS (SELECT to_char(trunc(SYSDATE,'YYYY'),'DY') y_b FROM dual) ,y AS (SELECT to_date('01-05-15','mm-dd-rr') AS d FROM dual UNION ALL SELECT ...


3

You can use an outer apply instead of a cross join. Using this simplified table structure. create table T1(ID1 int); create table T2(ID2 int); Add three rows to T1 insert into T1 values(1); insert into T1 values(2); insert into T1 values(3); Query with outer apply. select T1.ID1, T2.ID2 from T1 outer apply ( select T2.ID2 ...


1

seharusnya seperti ini(supposed to be like this): SELECT person.name, person.id, license.expiry_date, COUNT(car) FROM person JOIN license ON license.person_id = person.id JOIN car ON car.owner_id = person.id WHERE person.name = 'Charles Bannerman' GROUP BY person.name, person.id, license.expiry_date, car.car;


0

My guess is the query in your question looks like PostgreSQL information_schema. The current database is assumed when you connect to it. I believe you want the MySQL equilavent Here is my suggestion for the Top 10 tables in the Current Database. SET @lineitem = 0; SET @topitem = 10; SELECT line,tbl,units,dat,ndx,siz FROM (SELECT ...


0

I would use something like the following. This is pretty much what I have been using for years, with your error message logging worked into the CATCH block. CREATE PROCEDURE [SchemaName].[ProcedureName] ( @Param DataType ... ) AS SET NOCOUNT ON; DECLARE @InNestedTransaction BIT; BEGIN TRY IF (@@TRANCOUNT = 0) ...


0

Here it is WITH x AS (SELECT DBMS_XMLGEN.CONVERT(REGEXP_REPLACE('Here I am#44; THis is me there#39;s nowhere else on earth I rather be Here I am#44;', '(#[[:digit:]]+;)','&\1'), 1 ) AS message FROM dual) SELECT message, UTL_I18N.UNESCAPE_REFERENCE(message) conv_message from x;


0

If it's stored procedure, perhaps PIVOT tblConsolidadoJob table and insert results into hash table and then use very same hash table on a left join? Something like this: SELECT * INTO #temporary FROM ( SELECT CodJob, Quant, TipoCons, Perc FROM tblConsolidadoJob ) AS SourceTable PIVOT ( MAX(Perc) FOR TipoCoins IN ...


-2

something is wrong here. Back up the table again inc. all the NOT NULL columns, drop it and restore it If it doesn't work, please paste your backup and restore procedures here. about you last question: yes, you can. In our world you can do everything that makes logical sense and even more.


1

Well, if you want to update ALL entries, why don't you just get rid of the condition, and execute: UPDATE blog_entry SET allow_view_entry = 'everyone' Otherwise, you can use a wildcard indeed, and build your statement like this: UPDATE blog_entry SET allow_view_entry = 'everyone' WHERE entry_id LIKE '%245%' using the common wildcards that go with the ...


0

I believe i have discovered the problem. One TXT type column as appeared with 67285Bytes and every time i select them with an where clause the SQL Server Engine lock the entire Table because the data volume on the select.


0

In windows use \\ or / while using in path of file. your command should be as follow SELECT * INTO OUTFILE 'C:\\Users\\Jim Smith\\Desktop\\zyzyz.csv' FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '\"' LINES TERMINATED BY '\r\n' from limit 5; or SELECT * INTO OUTFILE 'C:/Users/Jim Smith/Desktop/zyzyz.csv' FIELDS TERMINATED BY ',' ...


1

DELETE FROM fps WHERE fps.id NOT IN (SELECT DISTINCT fps_id FROM fmmp) The sub query should find a list of fps ids that are being referred to, so you can delete any fps entries that are not in that list.


1

The simplest way in Postgres is with DISTINCT ON: SELECT DISTINCT ON (1,2,3,4) sp.game_id, sportsbook_id, spread_type, spread_duration, game_update_count FROM spreads sp LEFT JOIN schedule sch USING (game_id) WHERE date >= '2012-01-01' AND date <= '2012-01-02' ORDER BY 4,3,2,1, game_update_count DESC; Details: Select first row in ...


0

Instead of using a group by, have you tried using a Window Function? Try this: SELECT * FROM ( SELECT spreads.game_id, sportsbook_id, spread_type, spread_duration, row_number() over (partition by spreads.game_id, sportsbook_id, spread_type, spread_duration order by update_time desc) priority FROM spreads LEFT JOIN schedule ON ...


1

Sharding, or database partitioning, is usually done to allow parallel processing of chunks of data. Groups of records residing in different shards (partitions) can be processed independently of one another, thus effectively multiplying the database server capacity. The biggest problem to solve when deciding the partitioning scheme is to ensure that data ...


0

I worked it out, in-case anyone would like to know the following Query did it: SELECT CONCAT( 'SELECT `ost_ticket_event`.ticket_id', GROUP_CONCAT(' , `t_', REPLACE(state, '`', '``'), '`.timestamp AS `', REPLACE(state, '`', '``'), '`' SEPARATOR ''), ' FROM `ost_ticket_event` ', GROUP_CONCAT(' LEFT JOIN `ost_ticket_event` AS `t_', REPLACE(state, ...


0

Since you're working with SQL Server 2008, you can take delete first approach as I outlined on this SO answer http://stackoverflow.com/a/15508174/181965 A option, that might sound stupid but it works, is to delete first and use the OUTPUT clause. I created a simple control flow that populates a table for me. IF EXISTS ( SELECT 1 FROM sys.tables AS T ...


0

I don't know SQLServer specifics, but usually this sort of thing is due to table statistics being different in the two databases. Look at the query plans to see if they are different. Run the SQLServer version of "analyze table" or "analyze schema" commands. If these things don't help, check how the databases are set up. Is it possible that the data is ...


0

Since you said you only want fields from table A in your comment, you just need to test existence like Evaldas said. I copied your original query from the hibernate post and rewrote it here. I hope it helps! select this_.id as id3_, this_.form_id as form2_20_3_, this_.access_rule_id as access3_20_3_, this_.state_id as state4_20_3_, ...


3

You can try this IF EXISTS(select * from test where id=30122) update test set name='john' where id=3012 ELSE insert into test(name) values('john'); Other approach for better performance is update test set name='john' where id=3012 IF @@ROWCOUNT=0 insert into test(name) values('john'); and also read this bad habits to kick on schema prefix


1

Perhaps using MERGE? Since you tagged SQL Server. http://msdn.microsoft.com/en-us/library/bb510625.aspx


0

You can use this to significantly increase the readability of your queries. Rather than using a short alias, use your alias to describe the data that you are joining to, for example SELECT transaction.unique_id, authorisingUser.name AS authorising_user_name, requestingUser.name AS requesting_user_name FROM transactions AS transaction JOIN users ...


1

If your schema has a useful naming convention it could work like this: SELECT f.forum, p.post_count, p.comment_count FROM forum f LEFT JOIN ( SELECT forum_id, count(*) AS post_count, sum(c_ct) AS comment_count FROM posts p LEFT JOIN ( SELECT post_id, count(*) AS c_ct FROM comments GROUP BY 1 ) c USING (post_id) ...


0

You have to edit the Forward Engineering Template (FE Template). Simple said, hard done. As each class of entities (table, views, SPs, etc.) have their own block of generation code, it has to be done to each major entry points. As for the SQL Server 2005 Erwin's template at its 7.3 version, the code below, injected at the beginning of the 'Create Entity' ...


0

I think it would be helpful to know why you are deleting the information from the source table as well as what kind of table it is(stage, etl, dim, etc..). It wouldn't help either if you specified whether you want to delete the table itself or just data in the table. Since you specifically said "also delete the data in the source table" I will take it that ...


2

Using recursive CTE. Is this what you wanted? sample data: /* CREATE TABLE #Table ( CategoryID INT , Name VARCHAR(MAX) , IsParent BIT , PCategoryID INT ); INSERT INTO #Table (CategoryID, Name, IsParent, PCategoryID) VALUES (1, 'IIT', 1, 0), (2, 'IIT Maths', 0, 1), (3, 'IIT Chemistry', 0, 1), (4, 'IIT Physics', 0, 1), (5, ...


7

Query 1 will show you All records from student with a Class value of 7, and then any related Library_Book records if they exist. Query 2 will show you all records from student, and related Library_Book records if they exist and if the matching row in student has a Class of 7. Query 3 will show you All records from student with a Class value of 7, and ...


0

Assuming that you are prepared to compromise on the absolute correctness of the answer in order to obtain practical performance, you could do the following: Create an index on score (assuming descending, but either way is workable) Create a maintenance job to rebuild this index periodically (how often depends on your willingness to have your rank results ...


0

If you have all the logic that you want to check within your stored procedure, then storing the time "that it happened" would only be storing the time you actually checked the table. If you are executing this stored procedure every hour via an Agent job then you only know it happend withing that previous hour. Using a trigger on the inserts would provide a ...


0

Why must it sort if I have an index on both carid and timestamp? Is there a better method to use here? Because you don't have an index on both carid and timestamp! You have an index on carid, and a different index on timestamp. If you want the query to use both, you need an index on (carid, "timestamp") as ypercube mentioned. Likewise, I'd try ...


1

before the edit i created a table to test here the create code ( you will have to alter your table and add unique identifier for this to work ) CREATE TABLE `milad` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `cCode` int(10) unsigned NOT NULL, `gDate` date NOT NULL, `buy` int(10) unsigned NOT NULL, PRIMARY KEY (`id`) ) then i inserted the date ...


8

From a DBA perspective, the key difference between OLAP and OLTP is the tuning method you apply to the queries. The read/write ratio doesn't really tell you anything useful. I have a little "magic quadrant" that I use to illustrate the difference (in your case, consider BI/DW and ETL the same as OLAP): Basically, if you must touch a lot of data to ...


0

Maybe there is one ' to much at this point after < 1) change ((%s-long),2)) < 1)' (u'devtesting', to ((%s-long),2)) < 1) (u'devtesting', Hope it helps


-5

Since the database is created in a normal SQL Server instance, so it's an OLTP database as OLAP databases are created in Analysis Server instances only. Moreover, you cannot insert, update or delete from OLAP databases, so as a software developer you don't have the option to use OLAP as the core database for your solution. Not only that, you cannot query ...


1

I tried to put together an approach that would work on 2008 and is more set-based. Here is what I came up with. It did end up being more complex than I had hoped, but it might be an interesting approach for you to benchmark against your current approach on larger data sets. For what it's worth, this script runs in about 15ms for the provided data set on ...


1

Found a similar question asked here: http://www.sqlservercentral.com/Forums/Topic911571-338-1.aspx. Adapting the solution posted in that url, I got the following. Give it a try: EDIT. Disclaimer: This works on SQL Server 2008 R2. Did not notice the MySql tag. If you are not on a DBMS that has windowed aggregates, disregard. Setup test table from your data: ...



Top 50 recent answers are included