How to use XPATH in MySQL select?
Say I have a table called "xml" that stores XML files in a single column "data". How would I write a MySQL query that run an XPath and return only rows matching that XPath? ...
sql
SQL statement problem
Hi, I have this code: SELECT idcallhistory3, callid, starttime, answertime, endtime, duration, is_answ, is_fail, is_compl, is_fromoutside, mediatype, from_no, to_no, callerid, dialednumber, lastcallerid, lastdialednumber, group_no, line_no FROM "public".callhistory3 WHERE (starttime >= ?) AND (...
Sniffing queries to SQL Server
Is there a way to sniff SQL queries sent to a SQL Server db on any level (above transport level)? Perhaps there's some kind of a tracer in ASP.NET or built-in log in SQL Server ? ...
SQL how to search a many to many relationship
I have a database with two main tables notes and labels. They have a many-to-many relationship (similar to how stackoverflow.com has questions with labels). What I am wondering is how can search for a note using multiple labels using SQL? For example if have a note "test" with three labels "one", "two", and "three" and I have a seco...
SQL Precedence Query
I have a logging table which has three columns. One column is a unique identifier, One Column is called "Name" and the other is "Status". Values in the Name column can repeat so that you might see Name "Joe" in multiple rows. Name "Joe" might have a row with a status "open", another row with a status "closed", another with "waiting" an...
SQL: get Nth item in each group
Hi, I have a user table like this user_id | community_id | registration_date -------------------------------------------- 1 | 1 | 2008-01-01 2 | 1 | 2008-05-01 3 | 2 | 2008-01-28 4 | 2 | 2008-07-22 5 | 3 | 2008-01-11 For each community, I would like ...
Start stored procedures sequentially or in parallel
We have a stored procedure that runs nightly that in turn kicks off a number of other procedures. Some of those procedures could logically be run in parallel with some of the others. How can I indicate to SQL Server whether a procedure should be run in parallel or serial ie: kicked off of asynchronously or blocking? What would be...
How do I supply the FROM clause of a SELECT statement from a UDF parameter
In the application I'm working on porting to the web, we currently dynamically access different tables at runtime from run to run, based on a "template" string that is specified. I would like to move the burden of doing that back to the database now that we are moving to SQL server, so I don't have to mess with a dynamic GridView. I thou...
char vs varchar for performance in stock database
I'm using mySQL to set up a database of stock options. There are about 330,000 rows (each row is 1 option). I'm new to SQL so I'm trying to decide on the field types for things like option symbol (varies from 4 to 5 characters), stock symbol (varies from 1 to 5 characters), company name (varies from 5 to 60 characters). I want to optimi...
One 400GB table, One query - Need Tuning Ideas (SQL2005)
Hi, I have a single large table which I would like to optimize. I'm using MS-SQL 2005 server. I'll try to describe how it is used and if anyone has any suggestions I would appreciate it very much. The table is about 400GB, has 100 million rows and 1 million rows are inserted each day. The table has 8 columns, 1 data col and 7 columns us...
Can I write SQL using speech recognition?
I have wrist pain when I type and I would like to start writing SQL statements, stored procedure, and views using speech recognition. ...
Testing for whitespace in SQL Server
I've got some blank values in my table, and I can't seem to catch them in an IF statement. I've tried IF @value = '' and if @value = NULL and neither one catches the blank values. Is there any way to test whether or not a varchar is entirely whitespace? AHA! Turns out I was testing for null wrong. Thanks. ...
SQL Server Template-Driven Data Entry Question
Based on the answers to this question about dynamically accessing tables, I've decided to take a couple steps back and get some advice on the larger picture. I am revisiting the database design of a Windows Forms application which I am rewriting for the Web using ASP.NET. Also, I have ported our database to Sql Server, so it can handle...
Counting occurrences with SELECT DISTINCT, SELECT COUNT and UPDATE in Access
In Access, I have a table like this one: Date | EmployeeNum | Award 11-JAN-08 | 34 | GoldStar 13-JAN-08 | 875 | BronzeTrophy 13-JAN-08 | 34 | BronzeTrophy 18-JAN-08 | 875 | BronzeTrophy And I want to have a table count them like this: EmployeeNum | GoldStar | BronzeTrophy 34 | 1 ...
SQL query to calculate visit duration from log table
Hi! I have a MySQL table LOGIN_LOG with fields ID, PLAYER, TIMESTAMP and ACTION. ACTION can be either 'login' or 'logout'. Only around 20% of the logins have an accompanying logout row. For those that do, I want to calculate the average duration. I'm thinking of something like select avg(LL2.TIMESTAMP - LL1.TIMESTAMP) from LOGIN_LOG L...
How bad is ignoring Oracle DUP_VAL_ON_INDEX exception?
I have a table where I'm recording if a user has viewed an object at least once, hence: HasViewed ObjectID number (FK to Object table) UserId number (FK to Users table) Both fields are NOT NULL and together form the Primary Key. My question is, since I don't care how many times someone has viewed an object (after the f...
help with an index
Ok, I'm not great in mysql, but I know an index would help me out here, however I've done some plugging and can't find one to help... Anyone got any ideas? explain select `users_usr`.`id_usr` AS `id_usr`, `users_usr`.`firstname_usr` AS `firstname_usr`, `users_usr`.`lastname_usr` AS `lastname_usr`,`users_usr`.`social_usr` AS `socia...
Size of data obtained from SQL query via ODBC API
Hi, does anybody know how I can get the number of the elements (rows*cols) returned after I do an SQL query? If that can't be done, then is there something that's going to be relatively representative of the size of data I get back? I'm trying to make a status bar that indicates how much of the returned data I have processed so I want t...
Optimal DB structure for additional fields entity
I have a table in a DB (Postgres based), which acts like a superclass in object-oriented programming. It has a column 'type' which determines, which additional columns should be present in the table (sub-class properties). But I don't want the table to include all possible columns (all properties of all possible types). So I decided to ...
What's the shortest TSQL to concatenate a person's name which may contain nulls
3 fields: FirstName, MiddleName, LastName Any field can be null, but I don't want extra spaces. Format should be "First Middle Last", "First Last", "Last", etc. ...