sql

Transfering SQL to NHibernate Criteria APi

I have this SQL here that needs to be transfered to NHibernate Criteria API: select nn.* from NetworkNumber nn inner join (select number, max(Version) ver from NetworkNumber group by number) b on b.number=nn.number and b.ver=nn.version If anyone has a good knowledge of it, I'd appreciate a lot. In the meantime - I'll keep trying and...

SQL Query: Each GROUP BY expression must contain at least one column that is not an outer reference.

Getting this error: Each GROUP BY expression must contain at least one column that is not an outer reference. RowId ErrorDatetime ErrorNum ErrorMessage 824 2010-09-24 08:01:42.000 9001 Account 55 not found 823 2010-09-24 08:00:56.000 9001 Account 22222222222 not found 822 2010-09-24 05:06:27.000 ...

Subtracting count of rows in subquery from current query

In SQL Server 2005, given the following resultset ID | InstanceNumber | IsArchived 5000 | 1 | True 8347 | 2 | True 9343 | 3 | False 11048 | 4 | False What I would like to return is this: ID | InstanceNumber | IsArchiv...

Is clean SQL achievable on Linq to SQL?

Is clean (or should I say performant) SQL achievable in Linq to Sql? I wanted the Linq to Sql produce this code: SELECT C.CustomerID, COUNT(O.CustomerID) AS N FROM Customers C LEFT JOIN Orders O ON O.CustomerID = C.CustomerID GROUP BY C.CustomerID And I follow this code: http://stackoverflow.com/questions/695506/linq-left-join-group-...

SQL query that looks within the same table

Apologies - am not good at SQL. Perhaps this is a simple query - I don't know. What I have is a basic forum table - e.g. id poster title type content postedon parentID in this scheme, if a user asks a question, it is type = 0, and if it is an answer, type = 1. If the entry is an answer, I update the parentID to the id on the question...

I Need some sort of Conditional Join.

Okay, I know there are a few posts that discuss this, but my problem cannot be solved by a conditional where statement on a join (the common solution). I have three join statements, and depending on the query parameters, I may need to run any combination of the three. My Join statement is quite expensive, so I want to only do the join w...

mysql left join query takes much time to execute

This is my query can anyone tell me why this query taking much time excute... select gf_film.film_id, film_name, DATE_FORMAT(film_release_date,'%d') as date, DATE_FORMAT(film_release_date,'%m') as month_ori, DATE_FORMAT(film_release_date,'%M') as month, DATE_FORMAT(film_release_date,'...

SQL books that target Reporting?

Any suggestions on books that might focus on developing SQL queries for reports? Just to clarify, I am not looking for books covering SQL Server Reporting Services, but rather books that might cover writing SQL queries to serve as the basis for reports. Thank you. EDIT I just want to further clarify I am quite familiar with SQL syntax. ...

SQL Query Help: Grouping and ordering in SQL

I was trying to achieve this in SQL, lets say i have a table like below ID DATE 7 2009-12-06 7 2009-01-06 7 2009-12-19 7 2009-12-09 7 2009-20-06 9 2009-07-06 9 2009-11-06 10 2009-01-06 10 2010-12-06 10 2009-04-06 11 2009-08-06 11 2009-10-16 11 2009-11-19 12 2009-12-26 13 2009-04-16 13 2009-09-06 ...

using MAX aggregate between two tables

I have two tables, employer and position: Employer eID eName Position eID salary I need to match my eID between the two tables, determine what the max salary is, and print only the eName. Any suggestions as to how I can do this? I have tried multiple ways, but nothing seems to work. I am not sure where to put in the max(salary) ...

How to query a CLOB column in Oracle

Hi, I'm trying to run a query that has a few columns that are a CLOB datatype. If i run the query like normal, all of those fields just have (CLOB) as the value. I tried using DBMS_LOB.substr(column) and i get the error ORA-06502: PL/SQL: numeric or value error: character string buffer too small How can i query the CLOB column? ...

Sql query to convert nvarchar to int.

I have to query for total amount of a column using an aggregate function. The column data type is NVARCHAR(MAX). How can I convert it to Integer? I have tried this: SELECT SUM(CAST(amount AS INT)), branch FROM tblproducts WHERE id = 4 GROUP BY branch ...but I'm getting: Conversion failed when converting the ...

SQL Server error on stored procedure parameters

I'm getting a SQL Server error on a stored procedure (although it appears to run and run correctly). When I view the stored procedure call in my code, it is underlined, and the highlighted error message says: "Procedure sp_ReferenceSite has no parameters and arguments were supplied." My procedure is called like this: execute dbo.sp_Re...

What does "%Type" mean in Oracle sql?

I'm getting my first experience with Oracle and TOAD (I know SSMS). I came across this "%Type" next to an input parameter in an update procedure and I have no idea what it is or what it means. I found links on Google related to "%Rowtype". Is the same thing or something entirely different? If this is vague, I apologize. As always, thank...

Transform multiple queries into single row

I have a report that I would like to base on a single SQL statement. The problem is the data is based on several SQL statements. For example. SELECT COUNT(*) as 'Cases Opened' FROM tblCases WHERE DateAssigned BETWEEN @StartDate AND @EndDate SELECT COUNT(*) as 'Cases Closed' FROM tblCases WHERE ClosedDate BETWEEN @StartDate AND @End...

Raw copy of database leads to `Can't find file:`

To migrate my database faster, I tried copying the raw files (MYD and MYI) files of a database into another machine. All the tables are working fine except two tables that were partitioned. My directory structure looks like this: table1.frm table1.MYI table1.MYD table2.frm table2.par table2#P#p0.MYD table2#P#p0.MYI table2#P#p1.MYD tabl...

How to use ObjectQuery with Where filter separated by OR clause

Hi guys, Could somebody help me to answer how to rewrite raw SQL filter WHERE (...) OR (...) with ObjectQuery bilder, please? String queryRaw = "SELECT ls.LocaleName, ls.IsActive, ls.LocaleDescription " + "FROM RoutesEntities.Locales AS ls "; //" WHERE ls.LocaleName = 'en' OR ls.LocaleName = 'de' " ...

mysql get table column names in alphabetical order

Is it possible to query a MySQL database to get the column names of a table in alphabetical order? I know that SHOW COLUMNS `table_name`; or DESCRIBE `table_name`; will give me a list of the columns in a table (along with other info), but is it possible to alter the query in order to get the columns sorted alphabetically. Adding O...

SqlDataAdapter issues

Hi, There are several queries to be performed which return the DataTable object. In order to speed up the development I created a private method which should return the dataset by taking the query string as an argument. the method is the following: private DataTable getDataTable(string query) { DataTable dt = new DataTable...

how to select most frequent items from database?

Hi, In the database there are column values which appear in multiple rows. The example rows are the following (in csv format): AFAB19476C2CEEEE101FFA45FD207BA8B6185B29,539EE0643AFC3A3BE3D20DC6BE7D5376DC536D34,9800,58,29,24,34,2 A801DA9B2F4116A7A1B14A13532B2177C7436C43,91850E4C50536D45C9CEAFE5FB5B3A87154EB754,9800,15,15,15,15,1 4C1E...