In the query below is it better for performance to move the case statement to a UDF? If so why? SELECT id, name, case when v1 = 'Y' then 'something' when v2 = 'K' then 'something else' when v3 is null then 'dont know' else 'default' end from table ...
I have a search engine in PHP. When a search normally it's OK. Search text is 'company', and in the database there is 'company' in the field... The problem is when the search text is &company or -company and the data is &company or -company there is no match. why? problem with the - and & string... ...
A colleague of mine is currently designing SQL queries like the one below to produce reports, which are displayed in excel files through an external data query. At present, only reporting processes on the DB are required (no CRUD operations). I am trying to convince him that it would be better to use a ruby ORM in order to be able to di...
I've been trying to solve various deadlocks we are seeing in production. We've enabled deadlock tracing. The traces show lots of blocking on KEYLOCKs like this: 01/15/2010 08:25:07,spid15s,Unknown,keylock hobtid=72057594047758336 dbid=2 objectname=tempdb.dbo.MyTable indexname=IX_MyTable id=lock36977900 mode=X associatedObjectId=720575...
How would I query MySQL database to return roles that are still not assigned (like available to him). With table user_roles: |user_id|role_id| | 1 | 1 | | 1 | 2 | | 1 | 4 | | 1 | 7 | How can I query roles table to return role name and id of all the roles that are not assigned. role_id: 3,5,8,... ...
I have a SQL Server 2000 database. When I run the following command select * from sysindexkeys This display the appropriate records. I then do a DBCC command for the sysindexkeys. It doesn't display anything. Strange there is no page having the sysindexkeys records. Then how the query display the list of records. ...
I have a Linq to SQL class. There is a one to many relationship in my database. The relationship maps correctly in the designer, and an EntitySet<> property is created in the designer. When I run the code, the EntitySet<> does not populate with any data, even though there are associated records, they do not populate into the EntitySet...
There are 4 functions provided in SQL Server 2008 for full-text search: CONTAINS, CONTAINSTABLE, FREETEXT, FREETEXTTABLE How to use it them with Linq ? ...
I have a table with two columns of GMT time in seconds, and offset in minutes like this: SELECT TOP 1 StartTime, OffSet FROM MyTable; 1247242537 -420 -- as example What SQL function I can use to convert it to a datetime in yyyy-MM-dd hh:mm:ss as TimeStamp? The SQL I am using is SQL Server 2005. ...
I need to call a stored procedure multiple times, I'm using informix. I would like to know if calling a procedure multiple times with the same connection is the same generating the string with the multiple calls to the stored procedure and executing that as a query. this is an example of the code: IfxCommand cmd = new IfxCommand("store...
I'm updating tables with millions of records and I need to be as efficient as possible. Is there a point at which adding more criteria to the where clause will actually hurt rather than help? For example, if know I want to set a column to 3 I could use this query: update mytable set col = 3 Or I could update the record only if it's d...
I need to go to two tables to get the appropriate info exp_member_groups -group_id -group_title exp_members -member_id -group_id I have the appropriate member_id So I need to check the members table, get the group_id, then go to the groups table and match up the group_id and get the group_title from that. ...
I want to query a column with two enumerations values. If enum==a or enum==b matches for that column in databse then return me. Does anybody know to do this? I cna make for one enum, but how to add another enum into this. System.Collections.ArrayList list = (System.Collections.ArrayList)_mapper.Run(delegate(ISession session, object[] a...
Okay, I tried searching around, but couldn't come up with a decent idea on what to query on, so I figured I'd post a question. How can I pull back master/detail information without having the master data duplicated along with every line of detail data? Given the following scenario: Master - Cars ============= decimal car_id (pk) stri...
Please look at following image, I have explained my requirements in the image. I can't use here WHERE UsageTypeid IN(1,2,3,4) because this will behave as an OR condition and fetch all records. I just want those records, of first table, which are attached with all 4 ShipmentToID . All others which are attached with 3 or less Shipmen...
Hi, I'm planning on moving a database from C: drive to E: drive because the database is growing and the C: drive does not have enough capacity to handle that. I wonder if I need to changes anything in the connection on the web.config page in order to access the database. The database still has the same name, is still on the same serv...
Dear All, Is there a way in by sql statement or vba code to return all tables from access file? "I don't know the name of the tables" like when you want to have all fields in a table you use '*' regardless the names of the fields. but how to get all tables?!!!! ...
ID | Date | Status | Physician ID 44397479 10/13/2009 17:40 NOSHOW 44397480 10/13/2009 16:40 CHECKED OUT 108 44397481 10/13/2009 14:32 CHECKED OUT 108 44397482 10/14/2009 10:44 RESCHEDULED 108 44397483 8/26/2009 12:35 RESCHEDULED ...
hello i'm newbie in sql (postgresql) i have 2 tables as result of 2 differen selects all calls our customer contacts number contact_id and contact_id name 3213 12 12 jonh 3213 34 16 michael 3213 43 ...
UserA-UserB-UserC-UserD-UserF Users connected by '-' know each other. And I need an algorithm for these 2 tasks: Calculate the path from UserX to UserY For UserX,calculate all users that is no more than 3 steps away. Is there an efficient solution? EDIT My purpose is not to prove it right or wrong,but to calculate the result real...