Say I have a voting table, where users can vote on values up, down, or flat. Say a user gets a point each time the corrcet projection is made. At the end of each week I want to display some statistics. Something like: SELECT user_id, sum( user_points ) as sum_points FROM voting_results WHERE voting_date > ('2009-09-18' - INTERVAL 1 WE...
I have a SQL Server 2008 DB. I want to extract SOME tables (and associated schema, constraints, indexes, etc) and create a SQL Server Express DB. It isn't a sync of the target, we stomp on it. We ONLY need to do this in the file system (not across the wire). We are not fond of the synchronization stuff and at this point don't know how t...
I know I can do this with a CTE or another means but I am wondering if this can be done in one select query (no sub-selects). I want to find the most recent crt_ts for the po_nbr and then take the associated id. Note: The id column is not guaranteed to be sequential, I simplified it for this question. Code: create table temp ( ...
Help getting crushed by this i did DBCC CHECKDB and one of the DB gave Msg 823, Level 24, State 2, Line 2 I/O error 21(The device is not ready.) detected during read at offset 0x0000000001c000 in file 'E:\MSSQL2000\Data\FTSWE_Data.MDF'. I was working on a remote server, from my, local host accessing a remote DB. Also i was on VPN. ...
I am trying to concatenate some fields to return a single string for each row from an oracle table. This is in 10g. Here is my query: SELECT t.value || '|' || t.label || '|' t.label_abbrv || '||' "mylist" FROM list_value t WHERE t.value BETWEEN 195001 AND 195300; I'm getting the "FROM keyword not found where expected" error. Th...
Im using SQL Server 2005 . I have 2 WITH Clauses in my stored procedure WITH SomeClause1 AS ( SELECT .... ) WITH SomeClause2 AS ( SELECT .... ) But the error occurs Incorrect syntax near the keyword 'with'. If this statement is a common table expression or an xmlnamespaces clause, the previous statement must be terminated with a ...
select left(emailaddress, len(emailaddress) - charindex('@', emailaddress)) I am getting the result below: foo@ma Please correct the select statement below. I need to do this with tones of email addresses and extract the username ONLY. ...
So many times I just need a quick connection to an Oracle DB, where SQLPLUS handles the job. I imagine when people start using Oracle, the first thing they are told to do is to install Toad or SQLDeveloper. Even so, sometimes you don't want to wait for those tools to load, if you are performing some simple queries. I have a script tha...
I know how to get in SQL (SQL Server) the current date, but with the beginning of the day: select dateadd(DAY, datediff(day, 0, getdate()),0) (result:2009-09-17 00:00:00.000) I need to get (in SQL) the current date with the beginning of this hour. For example: 2009-09-17 17:00:00 (I don't care about the exact format) and I need to g...
Here's the background: Version: Oracle 8i (Don't hate me for being out of date. We are upgrading!) SQL> describe idcpdata Name Null? Type ----------------------------------------- -------- --------------------------- ID NOT NULL NUMBER(9) DAY ...
I m trying to get construct a sql statement where many to many relationship occurs. Basically i want to retrieve business based on 2 categories some business are included in both categories and queried through zip code. So far i got the zip code part but it doesnt distinguish the category and i don't have a table for the categories also ...
Hi all! Do you know a diagram to show the entries of an database? I guess something like the object diagram in UML. I know there is the entity-relationship model but it does not show the existing entries of the database. ...
I have a LINQ to SQL class that I am reading into a List. When reading in from the table, I get an error "String must be exactly 1 character long". I have looked over the Table definition for this file, and the .dbml file for this table, and there is no field that is exactly 1 char long. The table (a legacy table) has a lot of fields...
I am receiving the following error: ORA-12406: unauthorized SQL statement for policy I am executing a stored procedure that executes the following two Oracle Label Security stored procedures: SA_COMPONENTS.CREATE_GROUP SA_LABEL_ADMIN.CREATE_LABEL In the same transaction, but not the same procedure, I am trying to insert into a tabl...
i have a java program that is calling a Mysql stored procedure that is rolling back when it gets an SQLException. When i added the rollback (exit handler) to the store procedure the java program stopped getting the sql exception. i need for the java program to get a sql exception and the error message from mysql. does any one know how...
Lately, I have been doing some reading on indexes of all types and the main advice is to put the clustered index on the primary key of the table, but what if the primary key actually is not used in a query (via a select or join) and is just put for purely relational purposes, so in this case it is not queried against. Example, say I hav...
My initial answer to this problem has been to script it. Instead of using SQL, I've dipped into Python and normalised them. I am curious whether anyone can come up with a solution using SQL though. If a date occurs outside of business hours, I want to normalise the date to the next working day. I'll keep this really simple and say that ...
I have a query that joins 3 tables in SQL Server 2005, but has no Where clause, so I am indexing the fields found in the join statement. If my index is set to Col1,col2,col3 And my join is Tbl1 inner join tbl2 On Tbl1.col3=tbl2.col3 Tbl1.col2=Tbl2.col2 Tbl1.col1=Tbl2.col1 Does the order of the join statement make a difference as com...
I'm having some performance issues since a DB was moved from SQL2000 to SQL2008. We need to retrieve from a SQL View for a mail merge and for an application called FORMIC. It was fine under SQL 2000, but is now very, very slow. One thing I've noticed is that SQL Profiler shows that Excel does a simple "SELECT * FROM VIEW" where Word se...
here is my linq query: var test = from m in db.Members where m.UserId == null select m.Id; test.ToList(); UserId is a nullable Guid field on the members table that corresponds to the ASP.NET membership table aspnet_member. I am unable to generate a query through subsonic that will select where the userid IS null, only where ...