This is basic stuff, but I'm somewhat unfamiliar with VBA and the Word/Access object models. I have a two column database of about 117000 records. The columns are 'surname' and 'count'. I want a user to be able to type SMITH in a textbox and hit submit. I then want to run something like SELECT table.count FROM table WHERE surname = str...
From this question, a neat answer about using COALESCE to simplify complex logic trees. I considered the problem of short circuiting. For instance, in functions in most languages, arguments are fully evaluated and are then passed into the function. In C: int f(float x, float y) { return x; } f(a, a / b) ; // This will result in ...
I have a table of vehicles with registration numbers, and want to select a subset of them that are between some user-supplied 'from' and 'to' values. So lets say the table looks like this: id reg_num 1 DD1111 2 DD1112 3 DE2245 4 EE5678 5 EF6547 The SQL I have so far looks like this: select * ...
We have a query that is taking around 5 sec on our production system, but on our mirror system (as identical as possible to production) and dev systems it takes under 1 second. We have checked out the query plans and we can see that they differ. Also from these plans we can see why one is taking longer than the other. The data, schame ...
My system does some pretty heavy processing, and I've been attacking the performance in order to give me the ability to run more test runs in shorter times. I have quite a few cases where a UDF has to get called on say, 5 million rows (and I pretty much thought there was no way around it). Well, it turns out, there is a way to work aro...
how do you count the number of columns in a table in oracle? ...
Hi, i have a table called category in which i have main category ids and names and each main category has sub category ids and names.i also have a product table with a category id column which either has a numeric sub category id or a 2 letter main category id eg:EL for electronics..my problem is how to get top categories ie., number of...
Hi, We have a Single Statement FUNCTION in SQL Server 2005 which uses CONTAINSTABLE(). All works fine when we pass a non empty search string. Is there a wildcard string we can pass to CONTAINSTABLE() so that it matches all records in a table. Kind regards, ...
SQL has always had a great feature: cascading deletes. You plan it in advance and when it's time to delete something, BAM! No need to worry about all those dependent records. However, nowadays it's almost taboo to actually DELETE anything. You flag it as deleted and stop showing it. Unfortunately, I haven't been able to find a solid sol...
Is there a way to tell if I have uncommitted work (ie DML) in a transaction? Maybe a data-dictionary view I can query? A method to find this out both from within and outside of the session running the open transaction would be welcome. Thank you ...
Table structure is like this - id Name Manager 1 aa NULL 2 hh 1 3 YY 4 4 Kk NULL 5 PP 4 So I want the result like- Name Manager aa NULL hh aa Kk NULL YY Kk PP Kk ...
Let's say I have an SQL statement that's syntactically and sematically correct so it executes. How can I in managements studio (or any other sql tool I guess) test sql-statements and if I notice that they broke something rollback? (in a sepparate query). ...
Hey, I have this query: SELECT page.id, revision.title, revision.number FROM page INNER JOIN revision ON page.id = revision.pageId Which will return something like: "1" "Page Version A" "1" "1" "Page Version B" "2" "1" "Page Version C" "3" Now I only want to return one row for each page, with the data from the late...
I have the attached trigger that runs if the tbl_repair_visit.TENANTSATISFACTION = 'Poor' on an update. The issue i have if we change the engineers name the Engineer column gets updated and the trigger runs again if TENANTSATISFACTION = 'Poor' How can i set this up to only run if the TENANTSATISFACTION = 'Poor' column is updated and ig...
How could I convert a year-week (eg 0852 or 200852) into a date (eg 2008-12-31 or specifically a week-ending day ie Saturday 2008-12-27 or a week-beginning day ie 2008-12-21) ? Any day of the week-ending will do, Friday, Saturday, Sunday or Monday. ...
Hello I am trying this query: SELECT ARTICLE_NO, USERNAME, ACCESSSTARTS, ARTICLE_NAME, date_format( ACCESSSTARTS, '%d %m %Y' ) AS shortDate FROM AUCTIONS WHERE upper( ARTICLE_NAME ) LIKE '%hardy%' LIMIT 0 , 10; Which works fine, but shortDate is null, and I am unsure why. The contents of the database is like s...
I am trying to figure out the best way to model a set of "classes" in my system. Note that I'm not talking about OO classes, but classes of responses (in a survey). So the model goes like this: A Class can be defined with three different types of data: A Class of Coded Responses (where a coded responses consists of a string label an...
Problem: I have to pull data from a SQL Server database and an Oracle database and put it together in one dataset. The problem I have is: The SQL Server query requires an ID that is only found from the return of the Oracle query. What I am wondering is: How can I approach this problem in a way such that performance is not harmed? ...
Hello. I am developing Web Custom control which able to apply filters to the LinqDataSource object. It is generic control since it should operate with object of certain type. The control knows what field of object it should operate by following field /// <summary> /// Method to get compared column from object /// </summary>...
Suppose I have 2 tables, Products and ProductCategories. Both tables have relationship on CategoryId. And this is the query. select p.ProductId, p.Name, c.CategoryId, c.Name AS Category from Products p inner join ProductCategories c on p.CategoryId = c.CategoryId where c.CategoryId = 1; When I create execution plan, table ProductCateg...