query

how do you get "real" sql distinct with hibernate criteria queries?

I have a Hibernate criteria query that is incorrectly pulling out max results. In many cases, when I specify 20 max results, the query actually only returns 1 or 5 results, because the restrictions return many duplicates. Criteria c = session.createCriteria(DomainObject.class); c.setResultTransformer(Criteria.DISTINCT_ROOT_ENTITY); c.cr...

sorting error in union query

SELECT DISTINCT rt . d_rev_id , rt . d_rev_code , rt . d_reason , rt . d_rev_status , rt . d_apb , rt . d_cb , pt . d_partid , pt . d_part_no , pt . d_ab , pt . d_abd , pt . d_status , rt . d_part_name , rt . d_part_desc , rt . d_part_type , pnv . d_pn_val , pnv . d_pn_id , cfv . d_optionname , rt . d_projectid , rt . d_abd , rt . d_apbd...

Sql Server query problem

Can anybody tell me why is this query not working? Select temp.CompanyName from ( SELECT c.CompanyName, o.OrderID, YEAR(o.OrderDate) As YEAR, Sum(od.UnitPrice * od.Quantity) from Orders o INNER JOIN [Order Details] od ON o.OrderID = od.OrderID INNER JOIN Customers c ...

Is C++ OTL SQL database library using parameterized queries under the hood, or string concat?

I've been looking at the OTL library for C++ database access. I'm unsure of whether the query I pass in is converted to a parameterized query for the underlying database, or if it's basically just concatenating all the arguments into one big string and passing the query to the database that way. I see that the query you pass in to it c...

Help with OR logic in Linq lambda query

I'm a bit stuck on the following linq query. I'm trying to find all orders which have a state other than "Completed", or which have been completed within the last month. _ordersRepository.GetAllByFilter( o => o.OrderStatus .OrderByDescending(os => os.StatusUpdate.Date) .First() // so at this point I ha...

SQL stored proc - help me write this one, please!

Hi Guys, Could you please help me with this one: In one of my scripts I am inserting some data in to some table "Item". One of the columns in the "Item" table is "ItemNumber". I want to be able to call some function (most probably a Stored proc) where it would return me a numeric number which I can use for the ItemNumber. I CAN NOT ...

When is it not appropriate to use Derived tables?

This SO post details some benefits in performance regarding Derived vs. Temporary tables. Other than performance, what situations exist for which a Derived table would not be appropriate. One answer per post with an example would be helpful. ...

Nhibernate Hql query retrieve DateTime empty

Hi I need hql query for retrieving objects with an empty value in a datetime property Something like that from Users u where u.LastLogon is empty LastLogon is a DateTime? property, datetime in the MS Sql Table How can you do that? ...

SQL stored proc - help me write this one, please! (part 2)

I have the following table with the value 501 in it.. CREATE TABLE _Numbers( Number numeric(20,0) NOT NULL PRIMARY KEY ) INSERT INTO _Numbers VALUES(501) How can I write a stored proc on this which returns me 501 and increments Number to next in sequence (i.e. 502)? I would like this behaviour repeated every time the stored proc i...

trouble with automatically generated sql query

I am having trouble with an automatically generated SQL query, that is based on several smaller queries stored in a table, and combined with what the user inputs. I will show the inputs for a query that works, and then the query itself. I will then show my inputs, and the query that fails. If someone could point me what input I have u...

Windows Search 4 Query - Delphi Example

The following web page describes querying Windows Search programmatically: http://msdn.microsoft.com/en-us/library/aa965362.aspx Does anyone have examples using Delphi/Pascal? Examples I have in mind are fairly simple: Search for certain file types. Search for specific text within files. Limit these above searches to a certain path....

Tracing an ajax error to a sql query

I am working within the confines of CMS software, that automatically generates a query for use in a google suggest like input field. The provided fields works fine, with the query they use which uses an inner join over two tables. I am trying to make a query which will grab information from just one table, and will returns results on e...

SQL query and stored procedure not producing desired results

I have this table and Stored proc function: Table: CREATE TABLE _DMigNumbers( Number numeric(20,0) NOT NULL PRIMARY KEY ); INSERT INTO _DMigNumbers VALUES(0) Stored proc function: CREATE FUNCTION read_and_increment() RETURNS NUMERIC(20,0) BEGIN DECLARE @number_just_read NUMERIC(20,0); SELECT number INTO @number_just_r...

Help with SQL query

I've got the next SQL query: SELECT FIRST_NAME, LAST_NAME FROM MYTABLE WHERE STATUS = 1 AND VARIABLE IN ( 'PR', 'AR' ) AND SPECIAL = 1; SPECIAL is an aditional condition which is added if I previously selected 'PR' (checkbox) in my form. The thing is, with this query, I'm no longer getting rows where VARIABLE is 'AR'. Now, the idea i...

using a mysql table to access a lookup table?

I have two tables. Lets say they look like this Table Sports: Column 1: id (integer) Column 2: name (varchar 100) Table Sport Articles: Column 1: id (integer) Column 2: data (text) Column 3: sport (integer) So what I want to do is select things from the sports articles. lets say I have the ID number already. all i want is the data an...

Help building an Query

Hi, I need some help building an Query. I have a table "Orders" with 3 fields (IDorder, IDcostumer and amount) and i'm trying to create an List where i add one row for each costumer with the total of amount. Can someone help me building this query? ...

Using a single common WHERE condition for UNION in SQL

I am trying to do something like this: SELECT a.date AS EnrollDate, a.id, a.name, b.address FROM student a JOIN Location b ON a.id=b.id UNION SELECT a.date AS EnrollDate, a.id, a.name, b.address FROM teacher a JOIN Location b ON a.id=b.id WHERE a.date>'2010-01-01' ORDER BY EnrollDate But the WHERE condition applies to the se...

Drowning in a Sea of Nulls

An application I inherited tracks lab test results performed on material samples. Data is stored in a single table (tblSampleData) with a primary key of SampleID and 235 columns representing potential test results. The problem is that only a few tests are performed per sample, so each row contains over 200 nulls. Actually, there is a sec...

Wordpress / PHP - Cache custom queries ?

Hi So I'm making a plugin that gets a set of posts (using wp_query), and it does this every time you refresh a page. Can I cache the query results so the page gets generated faster? If so, how do I do it? because WP_cache functions don't work even if I enable cache in the config file. A alternative to wp_cache seem to be transients. ...

SQL query to avoid duplicates and order by a row

Need a SQL query using joins I need a help Table name: RVW_TSK RVW_ID UPC_CD CMPL_DATE 00001  10101010  10-10-2009 00002  10101010  13-10-2009 00003  20202020  5-11-2008 00004  20202020  8-11-2008 Expected result is like: RVW_ID UPC_CD CMPL_DATE 00002 10101010 13-10-2009 00004 20202020 8-11-2008 I want the lates...