sql

SQL count query not returning correct results

Struggling getting a query to work…….. I have two tables:- tbl.candidates: candidate_id agency_business_unit_id tbl.candidate_employment_tracker candidate_id The candidate employment can have duplicate records of a candidate_id as it contains records on their working history for different clients. The candidates tables is unique f...

Is it reasonable to divide data into different tables, based on a column value?

If I have a large table with a column which has a rather limited range of values (e.g. < 100), is it reasonable to divide this table into several tables with names tied to that column value? E.g. a table like with columns: table "TimeStamps": [Id] [DeviceId] [MessageCounter] [SomeData] where [DeviceId] is the "limited range" column w...

SQL - ordering a common table expression

Using this sample table: drop table Population CREATE TABLE [dbo].[Population]( [PersonId] [int] NOT NULL, [Name] [varchar](50) NOT NULL, [MotherId] [int] NULL, [FatherId] [int] NULL ) ON [PRIMARY] insert Population (PersonId, [Name], MotherId, FatherId) values (1, 'Baby', 2, 3) insert Population (PersonId, [Name], Mothe...

identify stored procedures and triggers that carries a particular string in sql server

Hi Guys, In sql server 2005 I need to identify all the stored procedures and triggers where a particular table name is used for ex: i want to search for "Table1" Please advise ...

How to access joined table attributes in Rails3 ?

I am having trouble accessing the attributes of a join in Rails3. There is two models/tables : Place and Address. One place can have many addresses (i.e. a specific street address, a "corner of" address, etc.). For historical reasons, the tables do not follow standard Rails conventions: class Place < ActiveRecord::Base set_table_name ...

Discrete Derivative in SQL

I've got sensor data in a table in the form: Time Value 10 100 20 200 36 330 46 440 I'd like to pull the change in values for each time period. Ideally, I'd like to get: Starttime Endtime Change 10 20 100 20 36 130 36 46 110 My SQL skills are pretty rudim...

Doctrine and join order (root class must have at least one field selecteD)

Hello, I have the following Doctrine query: Doctrine_Query::create()->select('td.*')->from('Model_Transaction t')->innerJoin('t.details td on td.fieldName = ?', 'CALLER_NUMBER')->fetchOne() The "details" relation is defined like this, in the Model_Transaction_Abstract class: $this->hasMany('Model_TransactionDetail as details', array...

In Oracle: how can I tell if an SQL query will cause changes without executing it?

I've got a string containing an SQL statement. I want to find out whether the query will modify data or database structure, or if it will only read data. Is there some way to do this? More info: In our application we need to let the users enter SQL-queries, mainly as part of the applications report system. These SQL queries should be al...

Anyone know of a method / tool to compare ad hoc SQL queries?

Hi, I have to convert a lot of legacy SQL queries to stored procs (rewriting and tidying) and I'm looking for an efficient way to compare the results one by one to ensure I haven't modified the behaviour. I currently use SQLDelta but it requires me to pipe the results of each query into tables and transfer one to a separate server usin...

MySQL match existence of a term amongst comma seperated values using REGEXP

Hi There, I have a MySQL select statement dilemma that has thrown me off course a little. In a certain column I have a category term. This term could look something like 'category' or it could contain multiple CSV's like this: 'category, bank special'. I need to retrieve all rows containing the term 'bank special' in the comma separat...

Selecting not null column

I have a table with varbinary(max) column and nvarchar(max) column. One of them is null and the other has a value. I would like to return the column that has the value as a varbinary(max) column. So far I have tried this, that does not work: SELECT A = CASE A WHEN NULL THEN B ELSE A END FROM Tabl...

How to perform locale based sorting while using hibernate as ORM

I use server based sorting(in the db) using orderby clause. But now that I want to support multiple locales, how should I pass the locale information to the DB via hibernate layer. I have pre-defined views to which I add criteria and order in the hibernate layer. ...

Nested SQL query takes too long

Hi! I'm looking for a way to optimize one SQL query that I have. I'm trying to get how many poems with a certain genre. Query looks like this: SELECT COUNT(*) FROM `poems` WHERE `id` IN ( SELECT `poem_id` FROM `poems_genres` WHERE `genre_title` = 'derision' ...

Avoiding column name redundancy accessing SQL databases?

Hello, I'm working on a program using a SQL database (I use SQLite). What my program does is the following: Creates the tables in the database if they don't exist (on startup) Executes SQL queries (SELECT, UPDATE, DELETE) - decided by the user from the interface What I saw doing this is that there is a lot of redundancy. I give the ...

asp.net in process memory, sqlite?

Has anyone used SQLite before with asp.net? Is there a SQLite Linq provider? Do you recommend something other than SQLite? Would like to hear your thoughts. Will this work as the master database and then backup to a sql server 2005 db? Can you run a hybrid of the two? ...

SQL Server Date Casting Issue

On my install of SQL Server if I perform the following SELECT CAST('2008-05-03 00:00:00' AS DATETIME), CAST('2008-05-03T00:00:00' AS DATETIME) Then I get the following result 2008-03-05 00:00:00.000 2008-05-03 00:00:00.000 Now this is odd in itself as I'm not sure why it's parsing the first date as yyyy/dd/mm (my login is set to br...

To check on NULL or 0

What is faster in SQL to check value for NULL or 0 I want to have the fastest way to check is value already in table. For example which is faster : IF ((SELECT ID FROM [SomeTable].[dbo].[BlockedSubscriberNumbers] WHERE VALUE = @myVal) is null ) BEGIN .... END ELSE BEGIN .... END or IF ((SELECT ID FROM [SomeTable].[dbo].[Bloc...

Formatting old Query to LINQ statement

The following query is something i inheritted from an old app that adds 2 attributes to an excel stored in the database. SELECT row_number() over(order by id) as num,[id] as mailsort, 0 as pages,[xmlRecord].query('/sst-statement/*') FROM dbo.RPA200_preproc as [sst-statement] where rpatype = 201 order by id for xml auto returns an xml...

Sql Server sysprocesses query

I have the following query which runs fine on all of my sql server 2005/2008 databases SELECT sysprocesses.spid FROM master.dbo.sysprocesses However for one of my databases it give me a binding error on the spid column (cannot bind multipart identifier). I've check the compatibility mode of the db and it's set to 2005 so I'm sure th...

Predictive Ordering Logic

I have a problem and was wondering if anyone could help or if it is even possible to have an algorithm for something like this. I need to create a predictive ordering wizard. So based on previous sales, we will determine that that a certain amount of an item is required. E.g 31 apples. Now i need to work out the number of cases that nee...