sql

Enforcing effective dated records in sql

Suppose I have a record with a column called "Name". Each record can be effective dated, so I also have an effective date and an expiry date column. The "Name" column has to be unique, but only within overlapping periods of the effective date and expiry date. I'm looking for some sort of efficient way to enforce this from within the s...

MS Access error "ODBC--call failed. Invalid character value for cast specification (#0)"

Does anyone have an idea what this error means or how to solve it? I am using Access 2003 and SQL2005. It comes up when trying to add a record on a particular subform. [Microsoft][SQL Native Client] Invalid character value for cast specification (#0) This MS bug report describes the same message, but it is a bug in SQL Server 6.5 that...

Truncate Table Within Transaction

Can the SQL "truncate table" command be used within a transaction? I am creating an app and my table has a ton of records. I want to delete all the records, but if the app fails I was to rollback my transaction. Deleting each record takes a very long time. I'm wondering if I use truncate table, can I still rollback the transaction an...

SSRS - How to check all rows in a table?

I have a table in a report and a textbox that changes its background color based on the value(s) in the table. Right now I have the background color expression for the textbox set to: =iif(Me.Value = ReportItems![NewValue].Value, "Yellow", "Transparent") "NewValue" is the name of one of the columns in the table. The above works fine ...

Decimal(3,2) values in MySQL are always 9.99

I have a field, justsomenum, of type decimal(3,2) in MySQL that seems to always have values of 9.99 when I insert something like 78.3. Why? This is what my table looks like: mysql> describe testtable; +---------------+--------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra ...

How can we build such an extremely complex SQL statement?

[Book] isbn(PK),title,category_id,subcategory_id,price [Author] isbn(FK),author_id(PK),name [Category] category_id(PK),name [SubCategory] sub_category_id(PK),category_id(FK),name I have a database (not designed by me) that contains the above four tables. I want to have a book list having the following format: isbn, title, author na...

select inbetween elements in mysql?

I am trying to implement the pagination in php. I am using the Mysql as back end database. I am trying to implement the pagination logic. I would be having lots of record. But the user will see only 10 at a time. Now to show the first page, i do a SELECT * from USERS LIMIT 10. Now to get the next 10 and the subsequ...

SQL qn:- Case expression

Hi, I have data in table a that i want to quench and create into another table b. Wondering how to do this. I was thinking of doing nested CASE expressions. But is this do-able? For eg:- Table a:- S En Eg -0.2 7888 99 90 9000 788 100 999 888 I want to create another table b, that does this:- select...

Database schema for organizing historical stock data

I'm creating a database schema for storing historical stock data. I currently have a schema as show below. My requirements are to store "bar data" (date, open, high, low, close volume) for multiple stock symbols. Each symbol might also have multiple timeframes (e.g. Google Weekly bars and Google Daily bars). My current schema puts th...

Summing values from 3 tables

Hi, I have 3 tables, each table will show records of users posting messages. I have managed to sum the number of times each user has posted a message on each table and now I want to sum these 3 individual values together for each user. Here is what I have managed so far: Table USERMESSAGE: SELECT U.SenderID, COUNT(U.SenderID) AS U_NUM...

Simple SQL query in Access fails with a missing semicolon error.

So, I'm learning Access 2007, Visual Basic, and SQL at the same time. Not ideal. I have this code attached to a button in a standard wizard-generated interface. I'm trying to copy a line from tblA to tblB. Every time the code is executed I get the message, "Run-time error '3137' Missing semicolon (;) at end of SQL statement." I'm guess...

Have PL/SQL Outputs in Real Time.

Is it possible to have Outputs from PL/SQL in real time? I have a pretty huge package that runs for more than an hour and I'd like to see where the package is at a particular time. Anyways, I currently do this with a log table, which gets filled up with hundreds of log descriptions per run, I'm just curious if this is possible. Thanks!...

MySql select just one and order by date and blog?

Hello I want to sort data from mysql. The shema looks like this: id int(11) objectId int(11) type tinyint(4) field tinyint(4) value int(11) date int(10) id objectId type field value date 1 1631 0 10 2918 1183 746534 2 1631 0 11 1108 1183 746534 ...

SQL to produce Top 10 and Other

Imagine I have a table showing the sales of Acme Widgets, and where they were sold. It's fairly easy to produce a report grouping sales by country. It's fairly easy to find the top 10. But what I'd like is to show the top 10, and then have a final row saying Other. E.g., Ctry | Sales ============= GB | 100 US | 80 ES | 60 ... ...

Updating only first occurence of an id in SQL

Due to an added field in a MsSql Database, I have to change the records in a table from NULL to something more usefull. I've got a table with addresses. Because customers have multiple addresses we'd like to mark certain addresses as default and others as something like shipping. How can I, using a query, change only the first of ever...

sql to compare two strings in MS access

I am trying to compare a series of strings like the following rodeo rodas carrot crate GLX GLX 1.1 GLX glxs the comparision need not be case sensitive i am trying to write a sql where i am updating the first string with the second string if they match approximately. Here except the second string all the other exampl...

Sending email with recordset data

Hi there, I've got a classic ASP page listing some properties from a database and with each property there is a small contact form - to enable a user to 'request a callback' from the property agent associated with that property. However, whichever form I complete - the email to the agent always includes details of the first property on ...

MYSQL Create table + Alter table incorrect syntax?

I'm trying to create a table in Navicat and immediately add a foreign key relation after that. The syntax however seems to be incorrect... Is this even possible? CREATE TABLE `Bld` ( `id` int(10) NOT NULL PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; ALTER TABLE `Bld` ( CONSTRAINT `fk_Bld_Bld...

SQL : in clause in storedprocedure:how to pass values

I want to write an SQL Server 2005 stored procedure which will select and return the user records from the user table for some userids which are passed to the stored procedure as parameter. How to do this ? I can pass the user ids as a string separated by comma. So that i can use the select * from users where userid in (userids) ...

Where to find an SQL database for medical information?

Where can I find sample data of medical information? Data on medicines, diseases, etc. and preferably in a format easy to import into an SQL database. ...