python list in sql query as parameter
I have a python list, say l l = [1,5,8] I want to write a sql query to get the data for all the elements of the list, say "select name from studens where id = |IN THE LIST l|" How do i accomlish this? ...
sql
Finding someone's age in SQL
In a SQL Server database, I record people's date of birth. Is there an straight-forward method of working out the person's age on a given date using SQL only? Using DATEDIFF(YEAR, DateOfBirth, GETDATE()) does not work as this only looks at the year part of the date. For example DATEDIFF(YEAR, '31 December 2007', '01 January 2008') retu...
Best way to select out of millions of rows in an Oracle DB
G'day! I have one million different words which I'd like to query for in a table with 15 million rows. The result of synonyms together with the word is getting processed after each query. table looks like this: synonym word --------------------- ancient old anile old centenarian old darkened ...
SQL User-Defined Functions: Fetching TOP n records in a user-defined function...
How come the following doesn't work? CREATE FUNCTION Test (@top integer) RETURNS TABLE AS RETURN SELECT TOP @top * FROM SomeTable GO I just want to be able to be able to specify the number of results to be returned. [SQL Server 2000.] Thanks! ...
Performance in SQL Mobile with one big column that's not being selected
I have a SQL Mobile database with one table. It has several columns with useful, often queried data and one column that stores a relatively large string per record (1000+ characters) that is not queried often. Imagine this fake schema, the "lifeStory" field is the large one. table1 String firstName String lastName String address String...
Oracle why does creating trigger fail when there is a field called timestamp?
I've just wasted the past two hours of my life trying to create a table with an auto incrementing primary key bases on this tutorial, The tutorial is great the issue I've been encountering is that the Create Target fails if I have a column which is a timestamp and a table that is called timestamp in the same table... Why doesn't oracle ...
Is there a 'START AT' equivilant in MS-SQL?
Some databases support commands such as: SELECT TOP 10 START AT 10 * FROM <TABLE> Essentially I need to pull the first 10 records, then the next 10, then the next 10 etc. Maybe there is another way to do this but in the past I've done it like the above for databases that support 'START AT'. ...
SQL Query without nested queries..
Let's say we have these tables; table user: - id - username - email table user2group: - userid - groupid table group: - id - groupname How do I make one query that returns all users, and the groups they belong to (as an array in the resultset or something..) ...
How do I query for the state of a session variable in PL/SQL?
I see that in PL/SQL it is possible to set the session state of a particular variable for the remainder of the session's lifetime. For instance: ALTER SESSION SET CURRENT_SCHEMA=<schema> How do I query for the current state of this schema so I can revert back to it after performing some other statements? ...
Question Mark in Insert Statement
I've got an SQL query that looks like this: INSERT INTO DB..incident ( incident_number --nvarchar(10) ) VALUES ( N'I?' ) What does the ? do in the value statement? EDIT:: Turns out there's some funny business via triggers and custom datatypes that occur on insert (we've got a bit of a messed up DB.) Given normal sett...
ORACLE SQL: Multiple SUMS dependent on CODE in One Statement
I believe there is a way to do this, but I'm not familiar with ORACLE 10g as many other people are. Here's the scenario: I'm currently converting Classic ASP pages to ASP.net 2.0. I have a query that is creating a report. It's reporting Sales vs. Previous Sales. What is happening currently is one query is going out to the database and g...
Best way to select the row with the most recent timestamp that matches a criterion
This is something that comes up so often I almost stopped thinking about it but I'm almost certain that I'm not doing this the best way. The question: Suppose you have the following table CREATE TABLE TEST_TABLE ( ID INTEGER, TEST_VALUE NUMBER, UPDATED DATE, FOREIGN_KEY INTEGER ); What is the best way to select...
Dates with no time or timezone component in Java/MySQL
I need to be able to store a date (year/month/day) with no time component. It's an abstract concept of a date, such as a birthday - I need to represent a date in the year and not a particular instant in time. I am using Java to parse the date from some input text, and need to store in a MySQL database. No matter what timezone the databa...
How to set a default row for a query that returns no rows?
I need to know how to return a default row if no rows exist in a table. What would be the best way to do this? I'm only returning a single column from this particular table to get its value. Edit: This would be SQL Server. ...
SQL many to many realtion table, but unique entires
Here is a nice underhand lob pitch to you guys. So basically I've got my content table with unique primary key IDs and I've got my tag table with unique primary key IDs. I've got a table that has an identity column as a primary key but the two other columes are the contentID and tagID. What do I need to do to the table to make sure t...
How do I ALTER the POSITION of a COLUMN in a Postgresql database?
How do I ALTER the POSITION of a COLUMN in a Postgresql database? I've tried the following, but I was unsuccessful: ALTER TABLE person ALTER COLUMN dob POSITION 37; ...
How to deal with SQL column names that look like SQL keywords?
One of my columns is called "from". I can't change it, I didn't make it. Am I allowed to do something like "SELECT from FROM TableName"? Or is there a special syntax to avoid the SQL server being confused? I am using Microsoft SQL Server (express i think) and Java JDBC, if that matters... Thanks Nathan ...
numeric(38,0) as primary key column; good, bad, who cares?
Hi, On my current project, I came across our master DB script. Taking a closer look at it, I noticed that all of our original primary keys have a data type of numeric(38,0) We are currently running SQL Server 2005 as our primary DB platform. For a little context, we support both Oracle and SQL Server as our back-end. In Oracle, our pri...
Is there a good alternative to SQL*PLUS for Oracle?
I am not a fan of using SQL*PLUS as an interface to Oracle. I usually use yasql, but it hasn't been updated since 2005 and can do with some improvements. A quick Google search shows yasql and SQLPal. I am using linux, so SQLPal is not an option. Are there any alternatives out there, or am I stuck with an interface that I do not lik...
Join statement, but need to only get data based on WHERE table column is empty.
I have this query statement and want to only get records that has a certain column empty (volunteers_2009.venue_id) Table is volunteers_2009, column I am looking to see if it is empty: venue_id Here is the current query: SELECT volunteers_2009.id, volunteers_2009.comments, volunteers_2009.choice1, volunteers_2009.choice2, voluntee...