sql

Mapping and query for three way many-to-many-to-one in NHibernate

UPDATE: I mistakenly pasted query code from the wrong overload. At the bottom is the fixed code Hi, let's say here's my domain. I'm tracking some sport Events like say car races. Each Race has Racers who take part in the race. Racer is a Driver in particular Race (it has the Driver, start lane, run-time, etc). Driver has things like...

ant sql task classpath problem

I have the following ant build.xml: <path id="antclasspath"> <fileset dir="lib"> <include name="*.jar"/> </fileset> </path> <property name="pathvar" refid="antclasspath" /> <echo message="Classpath is ${pathvar}"/> <sql driver="oracle.jdbc.driver.OracleDriver" url="jdbc:oracle:thin:@myserver.hu:1521:dbid" u...

SQL Server 2005/8 Query Optimisation Hints

I'm looking at educating a team on writing better SQL Server queries and was wondering what people's best hints were for improving performance. For instance I once had a DBA who insisted that count(*) would perform worse than count(1) (I have no idea whether she was right or whether it's still valid against the latest query optimisers)....

[DB2] ordering resultset ö together with o

I want to order a resultset. The columns which i want to use for ordering contain german umlauts like ö, ü and ä. I want column data with these sortet together with normal letters. Example: At it the Moment, the resultset is ordered like this: ABCOXYZÖ I want it to be ordered like this: ABCOÖXYZ Thanks ...

"Lost" SQL server database

This is a really strange one. I am using SQL Server Express 2005, and have the following connection string (in a DotNetNuke web.config): Data Source=ELECTROMORPH\S15304561;Integrated Security=True;User Instance=True Note that there is no AttachDbFilename parameter - so I'm not sure how SQL server even knows what to connect to. But it...

Need some help trying to do a simple SQL INSERT with some simple data checking.

Hi folks, I have the following data in a table. Name LeftId RightId ------------------------------------------ Cat 1 Cat 2 Dog 4 Dog 5 Dog 7 Dog 69 Gerbil 12 ...

SQL query to insert the available slots into a table (avail) from booked slots table.

I want a sql query to insert the available slots into a table (avail) from booked slots table. I have two tables .I have a book table with bookstarttime and bookendtime columns .these are timestamp objects.I have another table with availstarttime and availendtime.these are also timestamp columns.I have to get the available slots left be...

Finding out which locks that are acquired in a query on SQL Server?

I have a SQL statement from my application. I'd like to know which locks that statement acquires; how can I do that with SQL server? The statement has been involved in a deadlock, which I'm trying to analyze; I cannot reproduce the deadlock. I'm running on MS SQL Server 2005. ...

WHILE loop in SQL

I have two tables: Holdings and Transactions Holdings data looks like: 06/30/2009, A, 100 06/30/2009, B, 1200 06/30/2009, C, 100 06/30/2009, D, 100 Transactions data looks like: A, 06/05/2009, 100 B, 06/02/2009, 400 B, 06/13/2009, 400 B, 06/28/2009, 400 C, 06/17/2009, 100 D, 06/30/2009, 100 Ok, so what I need to accomplish is t...

SQL Query With Row_Number, order by and where clause.

I have the following SQL querry: select      ID, COLUMN1, COLUMN2 from      (select ID, COLUMN1, COLUMN2, row_number() over (order by 2 DESC) NO from A_TABLE) where      NO between 0 and 100 What I am trying to do is to select the first 100 records of the query select ID, COLUMN1, COLUMN2 from ATABLE order by 2 DESC And here are the ...

Why does Perl's DBI complain about "Fetch attempted on unopen cursor"?

Here is my script: $db_handle=DBI->connect("$dbstr", "", "", {RaiseError => 0, AutoCommit => 0, PrintError => 1}) || die "Connect error: $DBI::errstr" ; $result=$db_handle->selectrow_array("set isolation to dirty read"); Note: $dbstr is a valid database name. I am not a database programmer. What am I doing wrong which is causing the...

How do I get non-Table data into SQL Server Reporting Services?

Given: A C# calculation engine that loads an object model, crunches huge amounts of numbers, and saves the results to a couple of gigantic mega-indexed database tables in SQL Server. Those tables provide data to web interfaces, other software modules, and SQL Server Reporting Services 2005 reports. I managed to make the engine a lot fa...

Structuring internal mail system

I have two tables in my database: Company table (ID, CompanyName, CompanyUsername, CompanyPassword) Employee table (ID, CompanyID, Name, Username, Password) Right now I am designing an internal mail system for which employees can write to each other, but also write directly to the company account. My internal mail table has these fi...

linq query to join two tables and get the count from one table values from the other

I have two tables Customers, Orders Customers CustomerID FName LName Orders OrderId CustomerID OrderDate I want to make a linq statement that can join these two tables and get FName, LName, Count of orders for each customer ...

SQL: search for a string in every varchar column in a database

I have a database where a misspelled string appears in various places in different tables. Is there a SQL query that I can use to search for this string in every possible varchar/text column in the database? I was thinking of trying to use the information_schema views somehow to create dynamic queries, but I'm not sure if that will wor...

Is using IN (...) the most efficient way to randomly access a MySQL table?

I have a table with 2.4M+ rows, and no indexes. I am 100% sure all the rows have one column (we'll call this id) that is unique, it is of type VARCHAR(255). I now have a file of approximately 10,000 id's and need to pull the entire row for each. Is using IN(...) my best option? Should I add an index? I was thinking for some thinking o...

SQL Dynamic DatePart when using DateDiff

Is there a way to pass the DatePart parameter of DateDiff as a variable? So that I can write code that is similar to this? DECLARE @datePart VARCHAR(2) DECLARE @dateParameter INT SELECT @datePart = 'dd' SELECT @dateParameter = 28 SELECT * FROM MyTable WHERE DATEDIFF(@datePart, MyTable.MyDate, GETDATE()) < @dateParameter The only ...

[DB2] Problem with Replace Function

Hello! :-) In one of my resultsets i have column with char - data. I want to create another column, which carries altered data from the first column. There i want to exchange all 'ö' with oe. I tried it like this: Select NAME1, case when POSSTR(NAME1, 'ö') is not null then REPLACE(NAME1, 'ö', 'oe') end As __NAME1 from xyz; ...

SQL statement for evaluating multiple related tables

I have a Projects table, which lists the client info. Then I have four related jobs tables, for jobs within that project - Roofing, Siding, Gutters and Misc. The four tables have a projectID field to link them to the Projects table, and they all have a 'status' field. A project can have any combination of jobs. I want to be able to sele...

SQL code inside Java classes

Our current project does not use Hibernate (for various reasons) and we are using Spring's SimpleJdbc support to perform all our DB operations. We have a utility class that abstracts all CRUD operations but complex operations are performed using custom SQL queries. Currently our queries are stored as String constants inside the servic...