SQL Multiply Columns in where statment
I am newer to sql I have a table: MyTable column1 column2 ----------------- 5 3 2 2 I need a query that will give me back all of the rows where column1 * column2 > 10? ...
sql
How do I select a static string in a SQL query and ensure its type is VARCHAR2?
I have a query that is executed on a remote database: select /*+ DRIVING_SITE(rd) */ 'test' as tst, rd.id from mytable@remotedb rd When I execute this query I get: ORA-22992: cannot use LOB locators selected from remote tables Every column in mytable@remotedb is either INTEGER or VARCHAR2. If I remove 'test' as tst there is no pro...
MySQL Stored Procedure - IF/ELSE on the data returned
Essentially what I need to do is pull someone's age from the database which is stored as an integer and then perform a check as to whether they are over 18 or not. The simple select of the row returns their age. The goal is to have the Row return either a "yes" or "no" if they are over the age of 18. Normally I'd do this logic when the...
Is it possible to execute a query for each database in mysql databases and sum or combine results using only mysql command environment?
I have the following query in mysql 5.1.41: select distinct table_schema from information_schema.tables where table_schema like '%dog%'; I want to take the output of that command: +-------------------+ | table_schema | +-------------------+ | dog_a | | dog_b | +-------------------+ and then use the da...
NULL Value Substitution on constant
For fun I been playing with the built-in Optimizer for Oracle in Toad today. One of the optimizations it suggests is the following AND emp.pay_type = NVL('FT', UID) Instead of AND emp.pay_type = 'FT' I'm confused on what is happening here and because of it, confused on also on why this would improve performance. Since FT is a strin...
How do I trim out middle initial from a person's full name in SQL?
I'm look for the simplest way to split up first and last name wile trimming out the middle initial. The current layout of the field is [Last Name], [First Name] [MI]. Also, middle initial is not always there. My current code is below, I'm just not sure how to trim out the middle initial from first name without writing a case statement...
Help optimizing ActiveRecord query (voting system)
I have a voting system with two models: Item(id, name) and Vote(id, item_id, user_id). Here's the code I have so far: class Item < ActiveRecord::Base has_many :votes def self.most_popular items = Item.all #where can I optimize here? items.sort {|x,y| x.votes.length <=> y.votes.length}.first #so I don't need to do anything ...
counting mysql values
I have a table with two fields, TAG_ID and TAG_DESC my primary is tag_id. I'm looking for a query that will display each TAG_DESC and the amount of times it occurs next to it. Thanks, ...
Django LEFT OUTER JOIN on TWO columns where one isn't a foreign key
I have two models like so: class ObjectLock(models.Model): partner = models.ForeignKey(Partner) object_id = models.CharField(max_length=100) class Meta: unique_together = (('partner', 'object_id'),) class ObjectImportQueue(models.Model): partner = models.ForeignKey(Partner) object_id = models.CharField(max_...
Drop tables by date it were created
Hi, I have a large database that were used to archive tables before implementing structural change on it. We had this database for years, I want to create a dynamic script to check today's date and drop any table in this database that were created 3 years or older. Thanks ...
String Comparison differences between .NET and T-SQL?
In a test case I've written, the string comparison doesn't appear to work the same way between SQL server / .NET CLR. This C# code: string lesser = "SR2-A1-10-90"; string greater = "SR2-A1-100-10"; Debug.WriteLine(string.Compare("A","B")); Debug.WriteLine(string.Compare(lesser, greater)); Will output: -1 1 This SQL Server code: ...
SQLite view across multiple databases. Is this okay? Is there a better way?
Using SQlite I have a large database split into years: DB_2006_thru_2007.sq3 DB_2008_thru_2009.sq3 DB_current.sq3 They all have a single table call hist_tbl with two columns (key, data). The requirements are: 1. to be able to access all the data at once. 2. inserts only go to the current version. 3. the data will continue to be...
Help with a select count query
I want to show all cities that have have a count > 5. I have tried to limit my results anything over a count of 5 but it isn't working. SELECT user.city, Count(user.city) AS cnt FROM user Inner Join zip ON zip.zip = user.zip WHERE cnt > 5 GROUP BY user.city WHERE cnt > 5 **<--------------- It fails here** cnt has already been defined ...
Access 2003: How combine two count queries
I am writing my first little Access 2003 application. I have two queries that I cannot figure out how to combine. What I am looking for is to get all the routes numbers in a date range, count the total rows per route in the date range, and count how many were late. I have these two queries that get me part of the way there, just can't ...
Calling a Stored Procedure dynamically using Groovy SQL
I've a grails application service where I'm trying to call a stored procedure dynamically. Here is a sample snipper of what I'm trying to do: myService.callProcedure (procedureName, inputParams) My stored procedure takes input and out params. How I can call a procedure on the fly without registering output parameters and then get all...
Dynamic Tables?
Hello, I have a database that has different grades per course (i.e. three homeworks for Course 1, two homeworks for Course 2, ... ,Course N with M homeworks). How should I handle this as far as database design goes? CourseID HW1 HW2 HW3 1 100 99 100 2 100 75 NULL EDIT I guess I need to rephrase my question. As of r...
sql 2005 grouping data that is dynamic
I have data that looks like this Investor Contact IBM James IBM Dean IBM Sean Microsoft Bill Microsoft Steve I need the data to look like this Investor Contact IBM James,Dean,Sean Microsoft Bill,Steve OR if the above is impossible Investor Contact1 Cont...
mysql JOIN ON IF() ??
Hi there, I am trying to use sql such as this: SELECT t.*, t2.* FROM templates t LEFT JOIN IF(t.t_type = 0,'templates_email', IF(t.t_type = 1,'templates_sms','templates_fax')) t2 ON t.t_id = t2.t_id; Is it possible to do something like that? basically I want to join on one of three tables based on the value from the row. Is this re...
How to perfom recursive query without cursors
I have 2 tables Person id ---- 1 2 Order Id Qty --------- 1 3 1 0 2 2 How can I get all persons with orders greater than 0 that have never placed a 0 order? So the result of this would only Person.Id 2 I think I know how to do this with cursors but want to find a different way. EDIT: I think in an attempt to make the...
mysql where count(column_name) = 1?
Hi there, Here is the query I am using: SELECT k_id, COUNT(k_id) AS k_count FROM template_keyword_link WHERE k_id IN(1,2,3,4,5) GROUP BY k_id; This query returns something like 1 | 6 2 | 1 3 | 4 4 | 1 5 | 9 I want to add something like AND COUNT(k_id) = 1 so I end up with 2 | 1 4 | 1 However I get invalid use a group function...