sql

Query for restricting associated entities

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...

how to find Sum(field) in condition ie "select * from table where sum(field) < 150"

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...

Need select query

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 ...

How to speed up a massive update to the clustered column?

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...

Update on rows selected by a Cursor in SQL

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? ...

SQL Error handling

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...

Mixing Parameterized Query and Sub-query on Insert

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? ...

SubQuery related

If inner subquery returns zero rows then what will be the answer? No rows return, outer query result will be shown, error… ...

XML to nest folder content

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>...

How do NULL values affect performance in a database search?

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. ...

PostgreSQL - how to run VACUUM from code outside transaction block?

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...

Is Separator First Formating (SFF) with SQL Code Formating easier to read/maintain?

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...

MYSql JOIN Line Help

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...

Sql Server 2005 executing exe on remote machine or connect to server application

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 ...

Ant task for generating ER diagram from JPA/Hibernate annotated classes

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 ...

Filtering out children in a table with parentid

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 ===================== ...

Joins Using AS in :select with ActiveRecord

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...

Java equivalent for PHP's mysql_real_escape_string()

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...

Getting row count for a table in MySQL?

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...

update sql syntax - reset a field to itself with a concatenation - sql server 2005

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 ...