sql

Get earliest timestamps for user's cards in a mysql data table

I have a database of users and a database of cards. The users see the cards repeatedly. I kept a record of every time the users saw the cards and how long they looked at the card. Now that I've got that data, I want to analyze it by considering the first time that each user looked at each card. I got a list of the average msToAnswer ...

collecting mysql statistics

What would be the easiest way to count the new records that are inserted into a database? Is it possible to include a count query in with the load query? Or is something more complex needed, such as recording the existing last record and counting everything added after it? edit: I have a cron job, that uses LOAD DATA INFILE in a scrip...

Python SQL Select with possible NULL values

Hi, I'm new to python and have hit a problem with an SQL query I'm trying to perform. I am creating an SQL SELECT statement that is populated with values from an array as follows: ret = conn.execute('SELECT * FROM TestTable WHERE a = ? b = ? c = ?', *values) This works ok where I have real values in the values array. However in some...

How can I temporarily disable all constraints in a Table in Firebird 2.1?

I want to disable all Foreign key constraints and re-enable them after, is there some way to do that? I know that SQLServer allows that, but what about Firebird? ...

Can i use column number instead of column name in where condition of a select query in SQL Server 2005?

hi, can we use column number instead of column name in where condition of a select query in SQL Server 2005 so that even if the column name is so long. thanks in advance. ...

SQL portability gotchas

My company has me working on finishing a back end for Oracle for a Python ORM. I'm amazed at how much differently RDBMSes do things even for the simple stuff. I've learned a lot about the differences between Oracle and other RDBMSes. Just out of sheer curiosity, I'd like to learn more. What are some common "gotchas" in terms of portin...

Optimize this SQL Query

This SQL query disgusts me. I didn't write it, but it's a massive cause of issues on our servers. I'm willing to split it up into multiple queries and do some of the processing via PHP (like, the RAND()). $sql = "SELECT a.code, a.ad_id, a.position, a.type, a.image, a.url, a.height, a.width FROM " . AD_TABLE ." a, " . USER_GROUP_TABLE ...

Compare strings by their written representation - is it possible?

For example I have a column that contains string (in English - "A", "B" ) "AB1234" and I'd like to compare it to the string "AB1234" (in Russian "A", "B" ), for Example. Is there any built-in function to achieve this? The best way I found is to use Translate func where i enumerate all needed symbols. ...

Coldfusion - variable field name when looping through database query results

I have a set of column names in a table - e.g. foo1, foo2, foo3, foo4. I want to refer to these column names dynamically through a loop: <cfloop index="i" from="1" to="4"> <cfset foo = Evaluate("query.foo" & i)> </cfloop> The above doesn't work - ColdFusion throws a "variable not defined" error, even though query.foo1 is a valid ref...

How to Make this SQL Task Faster to Complete

Suppose table 1 Have 1,000,000 rows. In table 2 there are 50,000 rows INPUT Table 1 Id User InternetAmountDue 1 joe NULL Table 2 InternetUserId UserName AmountDue 21 kay 21.00 10091 joe 21.00 I want to merge data from table 2 to table 1 as follows: If user exists in Table 1, upda...

Return nullable type in SQL Server function

Is it possible to create a SQL Server function which returns a nullable string? I would like to create a function which returns DBNull if the value is 0 or an empty string: Create FUNCTION [dbo].[SetDBNullNvarChar] (@input nvarchar(1000)) RETURNS (needs to be nullable) AS BEGIN if (@input = '' OR @input = 0) BEGIN RETURN n...

Resultset logic when selecting tables without a join?

Dumb question.. what logic does the database use to determine the result set if you do a select without a join such as: select * from table1, table2 ...

Select data from a record collection type.

I have a stored proc in oracle 11g server that has an out variable of record. I cannot edit this procedure. I am creating a function that will call the procedure and return the information in the record set. I looked at the following question asked here: past question My Question is can I create a type of table for a record and query i...

math functions in active record sql queries? (pow, sqrt etc)

Active Record is not recognizing common SQL functions like POW and SQRT in the "find" method or "find_by_sql." How do I work around this? There seems to be no literature out there :-( ...

Compare rows in a spreadsheet with records in a database

I have an Excel spreadsheet provided as a report, when really its more of a database, with the row number acting as primary key. I need to compare some columns in this set with a different set of records from an Access 2007 database. To make matters more complicated, this needs to be done automatically, without user input, on a regular...

Django ORM equivalent

Hi all, I have the following code in a view to get some of the information on the account to display. I tried for hours to get this to work via ORM but couldn't make it work. I ended up doing it in raw SQL but what I want isn't very complex. I'm certain it's possible to do with ORM. In the end, I just want to populate the dictionary a...

Enabling TCP/IP protocol through t-sql script?

How to enable tcp/ip through t-sql script on sql server 2008? ...

Looking for exercises to learn SQL, using the Northwind database

I am trying to become more familiar with SQL by writing queries against the Northwind database. I am looking for some exercises that would help me to learn SQL and features of SQL Server. It is important that the exercises have solutions, and in complicated cases, it would be great if there was an explanation for the query. Thanks ...

Formatting RAISERROR messages in SYBASE ASE 12.5

In a stored procedure, I am trying to test if a parameter is null or less then 1, and if so, raise an error with a friendly message, and the value of the parameter. Here is my code: IF @ID IS NULL OR @ID <= 0 BEGIN RAISERROR 27001 'ID is ?????. ID cannot be null or less then zero.' RETURN 27001 END What I would like back is e...

Continuing Inserts in Oracle when exception is raised

Hi, I'm working on migration of data from a legacy system into our new app(running on Oracle Database, 10gR2). As part of the migration, I'm working on a script which inserts the data into tables that are used by the app. The number of rows of data that are imported runs into thousands, and the source data is not clean (unexpected nul...