I'm trying to do a query like this: Widget.find(:all, :conditions => ["name like %awesome%"]) However, I'm getting a "malformed format string" exception from sanitize_sql, specifying the "%" as the problem. How can I perform this query? ...
I have a normalized database and need to produce web based reports frequently that involve joins across multiple tables. These queries are taking too long, so I'd like to keep the results computed so that I can load pages quickly. There are frequent updates to the tables I am summarising, and I need the summary to reflect all update so f...
I want to both fetch and use in a where clause the value returned from a sub-select in MySQL. Is this possible? It seems unecessary to write the sub-query out twice - however if I need to, will MySQL be clever enough to only run it one? I have tried the following which does not work: SELECT (SELECT 1 FROM table WHERE somereallycompl...
If I have a sql query that uses 'distinct' (in oracle), would it be faster than retrieving the non-distinct then getting the unique results via java programming? I heard somewhere that oracle sql distinct is heavy, but is it heavier than manual 'distinction' via java programming? Thanks, Franz ...
Hi, To put it in a nutshell - In an oracle db, I want to make a column varchar2(16) which is now varchar2(8), without affecting the values presnet. I have already tried this and it does weird things. The query I tried was - alter table SOME_TABLE modify (SOME_COL varchar2(16)); But the values(some not all) already present in the table ...
We have a teradata database that is filled with BIGINT datatypes. SAS is the primary analytic engine for the organization. The SAS access library chokes on BIGINT and therefore forces all code to cast variables into a integer or decimal before returning them to SAS. Example: proc sql; connect to database (blah blah); create table sas_da...
MSBUILD - When executing DDL file I get this error: Unexpected System.Reflection.TargetInvocationException error executing SQL command Before this execution two other ones are executed with no problems. ...
Problem: I have a large database table (~500k records) which has a list of dates stored in a varchar2(15) column. These dates are stored in varying formats, ie. some are yyyy-mm-dd, some are mm/dd/yyyy, some are dd/mm/yy, some are mm/dd/yy, etc. Ie: 1994-01-13 01/13/1994 01/13/94 13/01/94 13/01/1994 etc I need to be able to shift thes...
Same as http://stackoverflow.com/questions/688537/ except in mysql. Suppose I have two tables, t1 and t2 which are identical in layout but which may contain different data. What's the best way to diff these two tables? To be more precise, I'm trying to figure out a simple SQL query that tells me if data from one row in t1 is ...
I have some legacy SQL and I want to split it up into it's constituent parts & then add more criteria, an order by clause, etc. Are there any existing Java libraries out there that interpret SQL like this? So I want to do something like deconstructedSQL.getCriteria().add("price > 100"); or deconstructedSQL.getOrderBy().add("price")...
Putting this as simply as I can, I have the following table structure: Date | Type | Title Say Type is a value in the range 1-10, I have 1,000s of records in the table, and I want the 5 most recent records of unique type. So the result would be something like: 2009-06-04 14:32:00 | 4 | Zeppo 2009-06-04 14:31:00 | 2 | Groucho 2009-06...
I have a big SQL file that does not fit into memory and that needs to be executed against Microsoft SQL Server 2008. It seems that the sqlcmd.exe tool always loads it into memory first which is impossible in this case. Any ideas? unfortunately, i cannot split the script because it is generated by Redgates excellent SQL Data Compare. the...
I want to substract exactly 6 month from a given date. How do you do this in Oracle SQL? ...
I have a SQL Dts job pull an XML file and doing a SQLXMLBulkLoad using an XSD file. Is there a way to specify in the XSD to append a static string column to every record of a certain table? ...
I have a table in SQL that has four columns. Two of these are property and value columns that store performance metrics for one of our servers. We're constantly coming up with new performance metrics and we didn't want to keep redesigning our schema, so thats why we designed the table this way. Trouble is, when I create a view to look a...
I have the following function ALTER FUNCTION [dbo].[ActualWeightDIMS] ( -- Add the parameters for the function here @ActualWeight int, @Actual_Dims_Lenght int, @Actual_Dims_Width int, @Actual_Dims_Height int ) RETURNS varchar(50) AS BEGIN DECLARE @ActualWeightDIMS varchar(50); --Actual Weight IF (@ActualWeight...
My google searches on how to split a string on a delimiter have resulted in some useful functions for splitting strings when the string is known (i.e. see below): SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER FUNCTION [dbo].[Split] (@String varchar(8000), @Delimiter char(1)) returns @temptable TABLE (items varchar(8000)...
It it ok to have a table with just one column? I know it isn't technically illegal, but is it considered poor design? EDIT: Here are a few examples: You have a table with the 50 valid US state codes, but you have no need to store the verbose state names. An email blacklist. Someone mentioned adding a key field. The way I see it, t...
Suppose I am making jdbc call and I had fetched data from db to resultset. But due to some network issue I lost my connection with db. (Connection, statement and resultset is not closed in DB). So can I still able to iterate resultset ? ...
I am trying to write this, query with zf select but without success SELECT * FROM `subscribers` WHERE id IN (Select subscriber_id From gs_relations Where group_id=55) I was trying with ssomething like this: $gs_relations = new GSRelations(); $part = gs_relations->select()->from('gs_relations',subscriber_id')->where("group_id=$group_i...