query

Infopath form-creating a date range to query

Is there a way to create a date range and query by that date range for an infopath form created with a database setup, actually a sql server. I have been trying to create a date range filter that can be used with the "drag query fields option" while developing a form. ...

grouping , based on similarity in percentage ( Oracle Query)

Hi I have a table that look like this : username | privilges user1 privilge1 user1 privilge2 user1 privilge3 user2 privilge1 user2 privilge5 user2 privilge3 etc ...... i would like to group privliges based on similarity in percentage to other user . for example in the table : user1 and user2 has...

CakePHP Model with "Between dates"

I have a large data set (over a billion rows). The data is partitioned in the database by date. As such, my query tool MUST specify an SQL between clause on every query, or it will have to scan every partition.. and well, it'll timeout before it ever comes back. So.. my question is, the field in the database thats partitioned is a dat...

Multi-Query SQL Condensed to One Result

There's information that my web-app needs from my database that I can only generate for the entire database with many queries populating a brand new table. Can anyone suggest to me a single query that returns the results the exact same as this populated table? First, here are the two tables that have the information. CREATE OR REPLACE ...

Query Optimization. Why did TOAD do this?

SQL Server 2008. I have this very large query which has a high cost associated with it. TOAD has Query Tuning functionality in it and the only change made was the following: Before: LEFT OUTER JOIN (SELECT RIN_EXT.rejected, RIN_EXT.scar, RIN.fcreceiver, ...

SQL Queries - Creating a Grid from Three Columns

My SQL table has three columns: TaskID VarID Hours 001 001 10 001 002 40 001 003 100 002 001 50 002 002 80 002 003 90 I want to produce output like the following TaskID VarID -> 001 002 003 001 10 40 100 002 50 ...

Problem on a query with NHibernate

Hi Guys, I have a very simple scenario, using NHibernate: one abstract base class "animal"; two concrete subclass "cat" and "dog" with discriminator column (1 for dog and 2 for cat); one normal class Person; Person have a many-to-one of animal. I want retrieve a list of person with a dog. How can I do this? thx a lot ...

What is a good query layer to be used when you write your own DAL? Do we need to write our own query processor and query object model?

So, here I am, about to roll my own data access layer. The key features that I needed in my DAL are asynchronous programming model, cloud based databases (eventual consistency, caching, REST protocols etc), multi-valued attributes, retrieving partial objects etc. I need to provide rich support for object oriented access - lists, dictiona...

Speeding up row counting in MySQL

Suppose, for illustrative purposes, you are running a library using a simple MySQL "books" table with three columns: (id, title, status) id is the primary key title is the title of the book status could be an enum describing the book's current state (e.g. AVAILABLE, CHECKEDOUT, PROCESSING, MISSING) A simple query to report how many...

How to delete all records in a table using SubSonic 3

I am a little stuck at the moment trying to delete all the records from a table using this approach: new Delete<Contact>().Execute(); This statement fails with a NullReferenceException in BuildDeleteStatement method at line: sb.Append(query.FromTables[0].QualifiedName); because, although FromTables has one entry, it is set to null....

Cannot insert explicit value for identity column in table 'table' when IDENTITY_INSERT is set to OFF.

I have below error when i execute the following script; Could you specify what it is and how it can be resolved? Insert table(OperationID,OpDescription,FilterID) values (20,'Hierachy Update',1) Server: Msg 544, Level 16, State 1, Line 1 Cannot insert explicit value for identity column in table 'table' when IDENTITY_INSERT is set to ...

Microsoft Access Parameter Query Fails Occasionally

I have a query that, basically, says this: SELECT DISTINCT [DB_ID] FROM [TableX] WHERE ([ForeignKey]=@ForeignKey); Once I have this, I return the first DB_ID (there should be only one). The routine I've written for calling this is in C# so that I can get the database id for any table name, no matter what the DB_ID is named. Most of ...

Concise way to check variable presence in MySQL INSERT QUERY using PHP

I am writing a PHP script to add items to a shopping basket. My shopping basket table has fields for the userid, product id, session id, notes and a few others. Some of the fields can be blank. For example: if someone isn't signed in, then I will store their session id in the table and, if they sign in, add their userid so I have a perm...

T-Sql Query - Get Unique Rows Across 2 Columns

I have a set of data, with columns x and y. This set contains rows where, for any 2 given values, A and B, there is a row with A and B in columns x and y respectivly and there will be a second row with B and A in columns x and y respectivly. E.g **Column X** **Column Y** Row 1 A B Row 2 ...

MySql & PHP: How to find which row has an item in the leading spot in a CSV list?

This is kind of a weird question so my title is just as weird. This is a voting app so I have a table called ballots that has a two important fields: username and ballot. The field ballot is a VARCHAR but it basically stores a list of 25 ids (numbers from 1-200) as CSVs. For example it might be: 22,12,1,3,4,5,6,7,... And another one ...

problems with xml output from C# webservice and/or xpath query

Hi! The webservice I've build returns XML by default, I added my own XML nodes which I need to query in a different application (Nintex). I do that with Xpath. But I can't seem to get the result I want... public class Service1 : System.Web.Services.WebService { public struct aduser { public String result; pu...

Query Optimization

I have 2 queries. First: SELECT * FROM `table` WHERE col='xyz' LIMIT 100 //Time Taken: 0.0047s Second: SELECT * FROM `table` WHERE col='xyz' ORDER BY Id DESC LIMIT 100 //Time Taken: 1.8208s The second takes a much longer time. I know why that is, it is because first I have to select the whole table, then do the ordering, whereas ...

Query displays first entry in table only - wrong!

$query = "SELECT A.Agent_Name, C.Country_Name, J.Job_Type FROM Line_Items LI, Agents A, Country C, Job J WHERE LI.Agent_ID = 1 AND LI.Agent_ID = A.Agent_ID AND LI.Country_ID = C.Country_ID AND LI.Job_ID = J.Job_ID"; $result = mysql_query($query); while($row = mysql_fetch_query($result)) { echo "Agent:" . $row['A...

Wordpress - multiple WP Query objects into one?

In Wordpress it's possible to create own WP Querys for the loop. An example is this: $my_query = new WP_Query(array('post_parent' => 3, 'post_type' => 'page')); Another example is this: $my_query = new WP_Query(array('cat' => 1, 'post_type' => 'post')); I want a loop that presents pages AND posts from the same loop. Now to my ques...

Outer join problem in SQL Server

This is my query. And I want all rows of BF and matched rows of FT. It returns only 2 rows but the real row count is 135. How should I change my query? SELECT BF.FaturaNo, K.KlinikAdi, FT.teslimAlindi, FT.TeslimAciklama, FT.kurumTeslimAldi, FT.kurumTeslimAciklama, BF.basilmisFatura_id, K.klinik_id FROM BasilmisFatura...