Tag Info

New answers tagged

0

I used Database restore (from .BAK) softwares,called: 1) SqlRestoreSetup 2) Apex SQL Restore


0

I've made a couple of assumptions here but one of these should work or be close: select inventory.Sku,inventory.SKU_DESCRIPTION,WAREHOUSEID from inventory join warehouse using (warehoueid) where manager = 'lucille smith' Or Select inventory.sku, inventory.sku_description, inventory.warehouseid from inventory, warehouse where inventory.warehouseid = ...


6

It is possible to set up a method to grant rights to run a job that a user does not have enough authority to run on its own. Erland Sommarskog has written a lot on how to grant permissions through stored procedures using counter-signatures. He has a solution at: http://www.sommarskog.se/grantperm.html#countersignatures The key point is: "To be able to ...


0

Adding the ORDER BY clause made it run almost instantaneously and I realized that it was only returning records from the first date because, duh, it was a SELECT TOP 10 query, there were more than 10 records from the first date. If I turned it into a SELECT * query I got everything in the range and if I added the ORDER BY clause I got it quickly. Thank ...


7

To start off, NULL does not mean "no value" it means "Unknown value" in SQL Server. There is a session setting called ANSI_NULLS that could make your queries behave as you would like them to, however, it's deprecated and will be forced to ON (which you don't seem to like) in a future version: http://msdn.microsoft.com/en-us/library/ms188048.aspx I get what ...


3

The NULL problem is a thorny issue with SQL. It is basically a mistake that is now burnt into all SQL software on the planet. We have to deal with it. value <> 26 or value is null is a good way to implement this logic. There are other formulations of the same semantics. If you know that value is never -1 (for example) you can say ISNULL(value, -1) ...


2

I would like to post this as answer what is point in upgrading to SQL server 2000 which is outdated completely without any support. Even it is not good for testing purpose. Its like using typewriter in era or super computers. Dont uninstall personal edition first. Install enterprise move databses by backup restore method. If you restore master msdb ...


0

I would try to answer the questions point wise After few days if i check in task manager, it shows the load of 31 GB Memory but actually maximum is alloted to SQL is 28 GB and nothing is running on he server First please note that task manager is not correct place to look for SQL Server memory utilization especially when SQL Server service account has ...


0

Is this what you are looking for? select * FROM Comment cm left join [Delete] del on del.[Target]=cm.Id where cm.[Target] = '".$_GET[''pp'']."' and Type = 'comment' and del.[Target] is null and deleted=0


-1

did you have a look in BOL (Books online) ? ...


1

In the where clause, trade the Ic alias with IFNULL(c.new_ic,c.old_ic). SELECT `c`.`fullname` AS `fullname`, IFNULL(`c`.`new_ic`,`c`.`old_ic`) AS `Ic`, `bla`.`loan_no` AS `Loan_No`, `bla`.`total_loan_amt` AS `Total_Loan_Amt`, `bla`.`monthly_installment` AS `Month_Inst_Amt`, `bla`.`disb_date` AS `disb_date` FROM (`customers` ...


3

Most likely you are not putting necessary quotes around your code. Try something like this. sqlCommandString1 = "Select Correction FROM Wlookup " + "WHERE Citystate = '" + ComboBox1.Text + "'" Otherwise it is interpreted as SELECT Correction FROM Wlookup WHERE Citystate = Washington as opposed to: SELECT Correction FROM Wlookup WHERE Citystate = ...


2

Although you limited the memory to 28 GB out of 32 GB, this is not the only memory that SQL Server uses. The running programs and needs within Windows take some additional space. If you want to closely tune the memory use, I recommend reading this, which works well for my servers. See Jonathan Kehayias at: ...


2

Simple, but "dirty": with t as ( select 1 as id, 'Y' as received from dual union select 1 as id, 'N' as received from dual union select 2 as id, 'N' as received from dual union select 2 as id, 'N' as received from dual union select 2 as id, 'Y' as received from dual union select 3 as id, 'N' as received from dual union select 3 as id, 'Y' as ...


1

If I understand your logic correctly, a two-level aggregation should do what you need. You don't need PL/SQL or client-side processing (i.e.: .NET). SELECT MIN(Received) Received FROM ( SELECT ID, MAX(Received) Received FROM your_view GROUP BY ID ); http://sqlfiddle.com/#!4/b2f85/3/0 Or @Balazs style is even cleaner, two-level aggregation without the ...


7

This is a frequent problem with poorly chosen clustered keys. Time series in general should be organized by time, since most queries ask for time ranges. Case in point, your query. If you have a correlation between id and timestamp then you can add an appropriate id based predicate: SELECT TOP 10 * FROM messages m INNER JOIN ...


0

You could rewrite with a LEFT JOIN: SELECT IT.[iInternalTransactionID] , IT.[iInternalAccountID] , C.[iContractID] , IT.[dtTransactionDate] AS TransactionDate , (CL.[sFirstName] + ' ' + CL.[sLastName]) AS ClientName ... -- ### unchanged up to here ##-- , G.TermNo , G.TermNo * G.MaxMonthlyAmount AS TotalAmount -- ### end ### -- FROM ...


0

Since you have identified that the sub-select (SELECT ...) CPH cannot join with higher level nesting, how about changing your sample rewrite as something like: INNER JOIN ( SELECT MAX([iContractID]) AS [iContractID] , MAX([iTermNo]) AS [iTermNo] , MAX([cMonthlyAmount]) AS [cMonthlyAmount] FROM [tbl_ContractPaymentHistory] WHERE ...


0

OK thought I'd attempt to do some benchmarks that didn't rely on any "warm cacheing" so that hopefully they'd be a more realistic test (also using Postgres, to see if it matches the same characteristics of other posted answers): My benchmarks using postgres 9.3.4 with a large-ish database, (hopefully large enough to not fit in RAM cache): Using this test ...


0

There seems to be something wrong here: the 0 cost on the index full scan is suspicious and if I had to guess I would say that you're missing something: probably the stats on the index. This in turn leads the optimizer to believe that it can run the FULL INDEX SCAN "for free" and goes on with a suboptimal plan. This could also be a rounding error problem, ...


2

Clearly you need a "Competitor" table if you wish to represent them, which I assume you do! Competitor Name, Age, Sex, Contact etc. Personally I would create a new table called "Discipline Type" which would differ from "Race Type". The two are functionally independent - race type could be "Triathlon" while "Discipline Type" could be ...


0

I believe you want to show all categories. If so, you will need to make all of your joins RIGHT OUTER JOINS. Why? Associativity and precedence for outer join operators is poorly defined and unpredictable You need to keep going 'outer' once you've started. Because you're using a RIGHT join, each preceding join must also be a RIGHT join. If not, the NULLs ...


3

It sounds like you are looking for a FULL [OUTER] JOIN. Per documentation: FULL OUTER JOIN First, an inner join is performed. Then, for each row in T1 that does not satisfy the join condition with any row in T2, a joined row is added with null values in columns of T2. Also, for each row of T2 that does not satisfy the join condition with any row ...


1

It seems you want to keep you PHP code simple and make the SQL code more complicated, while it should be otherwise. Example: lets say that - user A chooses: "color = 'red', size = 'Medium', category = any" - user B chooses "color = any, size = any, category = 'Tshirts'" The conditions could be respectively as simple as: -- userA WHERE color = $color AND ...


0

Hi I got solution to this problem. What I did is go to SQL Server configuration manager and go to Instance1 of DR site and remove port 1433 from IPAll. Restart both the instance at DR and now it will point correctly. Please try and let me know. Khaled Bakhtiar.


0

I looked at this again, and I figured out how to get the answer, but it's a horrible hack (DDL and DML below - just changed values from my last answer). The reason that I changed some values was that on running my queries with my orignal data, there are 2 results returned for ticket 3 by the second query which is incorrect. This was because there was a match ...


0

Please find the below results for the scenario. Hope it will help. mysql> create table product1(product varchar(50),price float); Query OK, 0 rows affected (0.10 sec) mysql> create table product2 like product1; Query OK, 0 rows affected (0.10 sec) mysql> desc product1; ...


1

You want something like this (see below for DDL and DML). This sort of query would be easier with the likes of EXCEPT and MINUS, but we'll have to wait for those... -- first query gets price difference between tables SELECT p1.* FROM product1 p1, product2 p2 WHERE p1.pname = p2.pname AND p1.price <> p2.price -- second query finds extra products in ...


1

In SQL (except in MySQL) you can only select columns that are part of the GROUP BY clause, or aggregate functions on any of the other columns. So you have two options: Add the additional columns to the GROUP BY clause: GROUP BY Rls.RoleName, Pro.[FirstName], Pro.[LastName] Add some aggregate function on the relevant columns: SELECT Rls.RoleName, ...


1

I've got this far (DDL and DML below) SELECT ticket_id, c_max_date FROM ( SELECT ticket_id, MAX(FROM_UNIXTIME(comment.comments_timestamp)) AS c_max_date FROM comment WHERE comment.comments_timestamp < UNIX_TIMESTAMP(NOW() - INTERVAL 2 YEAR) GROUP BY ticket_id ) AS mytab; +-----------+---------------------+ | ticket_id | c_max_date | ...


2

If you want to keep the existing rows and don't want the table renamed but only a few columns altered/changed/dropped, you can do this with a single ALTER TABLE statement. This will only fail if there is no index on (id): ALTER TABLE table_1 CHANGE COLUMN id id_table_1 int NOT NULL AUTO_INCREMENT, -- ADD PRIMARY KEY -- (id_table_1), CHANGE ...


0

From what I understand of your problem your "duplicity" is fine. If your saying that all of the shops act totally independently then yes in your example shop E and shop D might have the same data but the data is not related so it might look like a duplicate, but I would not consider that a duplicate. Another example, I am answering this question now, but ...


2

This was rather longshot but since the OP says it worked, I'm adding it as an answer (feel free to correct it if you find anything wrong). Try to break the internal query into three parts (INNER JOIN, LEFT JOIN with WHERE IS NULL check, RIGHT JOIN with IS NULL check) and then UNION ALL the three parts. This has the following advantages: The optimizer has ...


0

Yeah, I found the solution. It is because my oracle database is located on another computer on the network. So, when I try to export to like 'c:\' directory, oracle is trying to export to its own C:\ directory not mine. I used network address of the folder as export directory and the problem was solved. Use for example \\OSMAN\dump_directory


3

Your syntax for using the database link is incorrect, you've got the link and table identifiers in the wrong order. It should be: select ... from table_name@db.link.name Having too many . in a table name results in an ORA-00933 error, like you're getting.


0

You can get the date format of dd/mm/yy by using wmic command. This command allows you to get current date without getting affected by regional settings. **@echo off SETLOCAL EnableDelayedExpansion for /f "skip=1 tokens=1-6 delims= " %%a in ('wmic path Win32_LocalTime Get Day^,Hour^,Minute^,Month^,Second^,Year /Format:table') do ( IF NOT ...


2

You've done a lot with one night's experience with MERGE statements! The DELETED column prefix of the OUTPUT clause should contain the values updated in the MERGE UPDATE SET statements, I think you're on the right track with Attempt 2.


2

Updates are tracked by MERGE by providing: the $Action value UPDATE; the old values in the memory table deleted; and the new values in the memory table inserted. Therefore both in the case where a match is found, and in the case where there is no match by target, the values of the identity column is available in the inserted memory table.


2

Try this query below. Note it only lists the bug and its derived creation date (i.e.: doesn't include the bugnotes, etc). An OUTER join was preferred to return all bugs, regardless of whether or not they have any associated bugnotes. SELECT bugid , bugtitle , MIN(notetime) created FROM bugs b LEFT OUTER JOIN bugnotes USING (bugid) GROUP BY ...


1

I would store this as two different INT/BIGINT, one for each side of the decimal point. To make it easier to query, you can also add a computed column that renders the data as a varchar. In other words: CREATE TABLE MyData ( IntegerPart INT NOT NULL , FractionPart INT NULL /* Null of no fraction */ , HumanFormat AS CAST(IntegerPart AS VARCHAR) ...


1

I finally figured out and resolved my problem through a lot of trial and error. For those who do not have their original ibdata1 file, and only have their .frm and .ibd files, here's how I restored my data. Download and install the MySQL utilities at -> http://dev.mysql.com/downloads/utilities. Go into your command/terminal to open the MySQL utility, ...


0

The phrase "but not use the JOIN ON syntax" may mean you should use the older 8i style of joins that doesn't use words "JOIN ON" at all, or it may mean you should use "JOIN USING" rather than "JOIN ON". Hopefully they meant the latter.


1

I have no idea what I'm doing™, but the following, equivalent query seems to do the trick for me (at least, it was equivalent before you had edited your query to use a LEFT JOIN...): SELECT m.id, sub.cnt FROM m JOIN ( SELECT d.m_id, COUNT(1) AS cnt -- (+ other functions) FROM d WHERE d.m_id IN ( SELECT COLUMN_VALUE FROM TABLE( NEW ...


1

Hmmm.... this is an interesting one. The Oracle optimizer is a bit of a black box as far as mere mortals like myself are concerned... Jonathan Lewis wrote a 536 page book on the topic and that's just the **fundamentals* (and it ain't bedtime reading!). Two questions. a) Did you flush the caches before/after each query - maybe the first affects the second - ...


0

The query is joining 2 tables (receipts, sales) that both have a many-to-one relationship with product. This creates a kind of cartesian (cross) product and will give wrong results in the SUM() calculations. To avoid that, you need to do the summations for the two tables in two different subqueries to avoid errors. Something like this will work: SELECT ...


1

Firstly, the oracle docs are VERY clear that you shouldn't use data pump as SYS: http://docs.oracle.com/cd/E11882_01/server.112/e22490/dp_export.htm#SUTIL824: Do not invoke Export as SYSDBA, except at the request of Oracle technical support The problem will almost certainly be permissions related. The user that is running the Oracle database is ...


4

There is nothing specific about SELECT here. There is no semantic difference between SELECT, select and SeLeCt. The issue being described is that if you issue these three semantically identical statements: SELECT thing FROM mytable; select thing from mytable; Select Thing From MyTable; the database treats them as three entirely different entities, ...


1

Check the Oracle documentation here. There is a hash associated with queries in the shared pool (what Oracle calls the query cache) - so you could issue two - one with caps, one with lower case and check the hash and see if they're the same. [EDIT - removed rash speculation]


2

I think that you want something like this (see below for table defs). Something appears to have happened my SQLFiddle demo. SELECT bs.bugid, bs.bugtitle, bn.bugnotesid, bn.notetime, bn.notetext FROM bugs bs, bugnotes bn JOIN ( SELECT bugnotesid, MIN(notetime) FROM bugnotes GROUP By (bugid) ) mytab ON bn.bugnotesid = mytab.bugnotesid WHERE ...


1

I hope you are sitting down because I have a hardcore reality check for you. InnoDB and the MySQL Query Cache have been fiercely known to be at war with one another. I am a staunch advocate of disabling the Query Cache with a DB that mostly or all InnoDB. Here are my posts where I discuss why: Sep 05, 2012 : Is the overhead of frequent query cache ...



Top 50 recent answers are included