I would like to form a query where an associated collection has been restricted, ideally with Hibernate Criteria or HQL, but I'd be interested in how to do this in SQL. For example, say I have a Boy class with a bidirectional one-to-many association to the Kites class. I want to get a List of the Boys whose kites' lengths are in a range...
I have to retrieve only particular records whose sum value of size field is <=150. I have table like below ... userid size 1 70 2 100 3 50 4 25 5 120 6 90 The output should be ... userid size 1 70 3 50 4 25 For example, if we add 70,50,25 we get 145 which is <=150. How would I...
Consider the following table structure with data - AdjusterID | CompanyID | FirstName | LastName | EmailID ============================================================ 1001 | Sterling | Jane | Stewart | [email protected] 1002 | Sterling | David | Boon | [email protected] 1003 | PHH ...
I have a pretty large table: 20+ million rows and I need to update about 5% of that - or 1 million rows. Unfortunately, I am updating the (int) column that is being used as the clustered index. My question is: What is the fastest way to update these rows? I have tried updating the rows directly: update t1 set t1.groupId = t2.groupId...
In oracle if I define a cursor and during the same time if there is an update on the Table then what happens to my Cursor? Does it become invalid or Updates does not matter? ...
Hi there, Can you tell me if error handling is worth putting into this stored procedure? -- ********************************************** -- Delete and create a new 'Truncate' function... -- ********************************************** IF EXISTS(SELECT name FROM sysobjects WHERE name = 'truncate_description...
I have a colleague who wants to attempt the following query: INSERT INTO table (ColumnA, ColumnB, ColumnC) VALUES (?, (SELECT Id FROM ColumnD WHERE x=y), ?) Sybase complains about this as it does not seem to allow subqueries in the VALUES portion of the query. Does anyone know of a way around this problem? ...
If inner subquery returns zero rows then what will be the answer? No rows return, outer query result will be shown, error… ...
Hi, I have a project to update all reports on an SSRS instance and thought I would get a list of all reports into Excel so I can tick them off as I update each one. Easy I thought. I dont use it often (at all) but XML seemed to lend itself to this. I would have something like: <Server> <ReportFolder> <ReportFolder> <Report>...
Hi In our product we have a generic search engine, and trying to optimze the search performance. A lot of the tables used in the queries allow null values. Should we redesign our table to disallow null values for optimization or not? Our product runs on both Oracle and MS SQL Server. ...
I am using Python with psycopg2 and I'm trying to run a full VACUUM after a daily operation which inserts several thousand rows. The problem is that when I try to run the VACUUM command within my code I get the following error: psycopg2.InternalError: VACUUM cannot run inside a transaction block How do I run this from the code outsid...
Do you place separators (commas, and, or operators) at the front of the line? Select Field1 , Field2 --, Field3 From [some_table] as ST Inner Join [other_table] as OT ON ST.PKID = OT.FKID Where [this] = [that] and [one_other] > 53; I think the best feature is to help expose important operators (AND/OR). As a secondary adva...
Hello Im running a business and i have a mysql line problem that i cant get to function, I have a one line sql entry that im trying to join entrys from "tblCustomerCreditCards" and make them appear on a line im trying to draw from "tblCustomerAddresses" my line for my tblCustomerAddresses is select CustomerID,FullName,AddressLine1,Cit...
Hello, I'm try to figure how to create sql server stored procedure that when a condition is met executes an executable, with command line arguments, on a remote machine within the network. I could write my own little server application to handle the communication sent from the stored procedure for the executable, but I'm not certain ...
Does anyone know of a tool that can do that? Linguine maps seems to only work on old hibernate xml files. And the hibernate tool task hbm2hbmxml seems to have a bug so that I can't do the two step process "annotations->hbmxml->diagram" Best, Anders ...
Hi, I need a bit of help constructing a query that will let me filter the following data. Table: MyTree Id ParentId Visible ===================== 1 null 0 2 1 1 3 2 1 4 3 1 5 null 1 6 5 1 I expect the following result from the query: Id ParentId Visible ===================== ...
I am trying to do this Version.find(:all, :joins=>"JOIN editions ON versions.edition_id=editions.id JOIN products ON editions.product_id=products.id", :select=>"products.name, versions.name AS what") but ActiveRecord is not respecting the AS keyword... any ideas? Edit: Since both fields are called "name" they are colliding, so I only...
Is there a Java equivalent to PHP's mysql_real_escape_string() ? This is to escape SQL injection attempts before passing them to Statement.execute(). I know I can use PreparedStatement instead, but let's assume these are one shot statements so preparing them will result in lower performance. I've already changed the code to use Prepare...
Hello Can anyone tell me which is he better appraoch in MySQL whin it comes to finding the row count of a table: is it better to do SELECT COUNT(*) FROM TABLE_NAME or to look up the row count in the table TABLE in the INFORMATION_SCHEMA? This would be a frequent operation for calculating pagination counts for pages? I should add t...
I'm getting and error using this syntax: update table set field1 = (field1+' - '+field2) where field1 = 'somevalue' It's not too happy with doing this for me. I know that the '+' for concatenation works in my select statements, so that is the right syntax. There is something else at play here... and I tried removing the parenthesis ...