sql

Update only date on datetime field on Pl/SQL

So i need to update some dates on an Oracle Database, the field is a datetime, but i only want the date updated and leave the time as it is... There query goes like this: update table SET field = to_date('07312010','MMDDYY'); But it's overriding the hours, minutes and seconds from the field, i want to update the date but i want th...

Does double-escaping a wildcard in SQL nullify the escape?

I have an interesting behavior that I would like to better understand so I don't get hung up misusing it by accident. The following is an example of escaping a '%' in a WHERE clause: select * from #z where b like '%e%' ESCAPE 'e' In this table with these values: create table #z (a int, b varchar(10)) insert into #z values (1, 'e25%'...

How to combine aggregate functions in MySQL?

I'm just learning MySQL - is there a way to combine (or nest) aggregate functions? Given a query: SELECT user, count(answer) FROM surveyValues WHERE study='a1' GROUP BY user; This will give me the number of questions answered by each user. What I really want is the average number of questions answered per user...something like: SEL...

Oracle Query Optimization Help - Multi Pass

I need to query the orders table to get a count of all orders for yesterdays transactions, grouped by the ship date. Then I need to have an additional column to give the total orders for the ship date for all transactions. When I added this second column, time of processing grew exponentially (which was expected) to 109s. Is there any...

Where to find info on ODBC SQL dialect/grammar, incl. built-in functions/operators?

I'm looking into writing a C++ database library that will run on top of either ODBC or on top of another library that itself uses ODBC (possibly DTL, OTL, or SOCI). At this point I need to know what SQL functions (MIN, MAX, AVG, EXISTS, IN, etc.) and operators (+, -, /, *, etc.) I'll have available through the ODBC SQL dialect without n...

Foxpro: Check whether table exists via vfpoledb

I access data in .dbf files via System.Data.OleDb (vfpoledb.dll). How can I find out whether table exists via SQL command? Something similar to the following on SQL server: IF (EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'TheTable')) BEGIN --Do Stuff END ...

help with t-sql linked server

Hi Based on the 2 databases below: Database_A on Server_1 Database_B on Server_2 I have created a linked server to Database_B on Server_1 instance by name 'LS_B'. I have a huge script file which basically creates required tables, functions, views, and stored procs on Database_A. These functions, views and stored procs in turn ref...

How do I modify this query without increasing the number of rows returned?

I've got a sub-select in a query that looks something like this: left outer join (select distinct ID from OTHER_TABLE) as MYJOIN on BASE_OBJECT.ID = MYJOIN.ID It's pretty straightforward. Checks to see if a certain relation exists between the main object being queried for and the object represented by OTHER_TABLE by whether or not ...

SQL Server composite key experience

I want to make a composite key (well that's the idea but I'm open to other suggestions) on a Documents table. It would consist of two columns, year (2010,...) and an ID, which would be autoincrementing but it should restart itself every year. So keys like these 2010-1, 2010-2, ..., 2011-1, 2011-2, ... and, preferrably those keys should ...

Adding a Byte[] to a datarow

I'm trying to write a test that returns a data-reader with one of the columns being a byte[]. I figured I can create a datatable and create a reader from that. var cboTable = new DataTable("CBOTable"); var colValue = new SqlBinary(ASCII.GetBytes("Hello This is test")); cboTable.Columns.Add("ByteArrayColumn"); cboTable.Rows.Add(colValu...

SQL to select rows that match a URL with wildcard

I have a table in the db with one column containing a URL like http://site.com/users/*/profile I am given a URL (like http://site.com/users/234/profile) and want to select all the rows in the db that match the url (in this case * is a wildcard). I was thinking of using Regex to do this, by replacing the * with regex for any character....

Help with MySQL statement

I have written the following SQL statement in MySQL : USE my_database; SELECT * FROM some_table WHERE some_column IN (1, 2, 3); This returns a set of rows that have a column value which is a key into a row of another table (call it some_other_table). a b c d <--this is the column with the key 1 2 3 I want to say, ...

PHP for Business systems?

Hi all, I Currently work within the IT industry in a hardware/software/desktop support role, but wish to move my career direction into software development/solution design/etc.. I have built a few application in the PHP framework language CodeIgniter(that I have noticed a lot of you like!) My question is: (and I can answer it for myse...

SQL LEFT JOIN problem

I'm trying to select the latest updated (field) pages (table), excluding pages that are hidden (visible = "n"). However, I want to group pages by slug so I only get one result per slug. The following query kinda works, but it only selects the latest updated page for every slug — then, if that page happens to be hidden, it removes it fro...

SQL: Group By on Consecutive Records

A slightly tricky SQL question (we are running SQL server 2000). I have the following table, StoreCount - WeekEndDate StoreCount 2010-07-25 359 2010-07-18 359 2010-07-11 358 2010-07-04 358 2010-06-27 358 2010-06-20 358 2010-06-13 358 2010-06-06 359 2010-05-30 360 2010-05-23 360 2010-05-16 ...

How to best write a SQL SPROC to clone table rows but changing select fields

Let's say I have the following table fID Field1 Field2 1 a dennis 1 f mac 2 j clownbaby 1 k charlie 3 t frank 7 d dee and I want to take all the rows with 1 in the first column and insert them in the same table with a number I could pick as an argument to my stored procedure. So that i...

Shortening a repetitive 1400-line MySQL UNION query using PHP

I have a MYSQL table that is 1275 fields wide. Each row in the table represents a single class of students, with 17 fields per student, X up to 75 students per class, so 17 X 75 = 1275 fields. I have devised an SQL UNION query that successfully pulls the students into another table, with each student on a single row. Now, I want t...

In SQL can a sequenced range selection be done more efficiently than my algorithm (see code) that uses a cursor?

I need to collapse multiple ranges of sequential numbers (1 or more) to sets of their minimum and maximum values. I have unique integers (no duplicates) stored in a table column. The obvious way (to me) to solve this problem is to use a cursor (see my algorithm below) and iterate through every integer. However, it seems inefficient to ...

Linq - get top 2 records through criteria

Hi folks, i have data in following format: colA colB 2010 10 2010 20 2010 30 1999 99 I need to obtain output as follows using Linq/Lambda expression: colA colB 2010 10 1999 99 colB could be any non-99 value but t...

There has to be a better way to do localized database fields

So far there've been several questions regarding this, and they've all come down to the same answer: one table for the language-neutral data, 1-* to a table with the translations and an indexed language ID field. This has several problems: Twice as much CRUD. Need for Ajax CRUD if you want a decently friendly web UI. More than twice t...