Does anyone know how to get around this error ? this sql works fine, but for the 'cumulinvoiced' partitioning function. i'm trying to add a cumulative total, but it doesn't seem to work on inline views. SELECT a.mois, a.supid, a.status, COUNT (a.status), SUM (COUNT (a.status) OVER (ORDER BY a.status, a.supid, a.dossier, a.mois)...
I have two tables. I am trying to find rows in one table which do not exist in second table based on values in two columns. (I have simplified the tables to include the two columns only). There are no primary/foreign keys between the two tables. Seems simple enough but I am having a brain block now! DDL: CREATE TABLE [dbo].[Table_1]( ...
In a project at work i have to improve performance. The data of the app is spread over many databases. I was told that is better for organizing the data. Whatever. Is there a performance penalty when i do a select over some tables spread on several databases instead of a select on those tables in one database? ...
When I look at the execution plan for a particular query I find that 77% of my cost is in a Clustered Index seek. Does the fact that I'm using a clustered index mean that I won't see performance issues due to the columns that I am outputting? Would it be better for me to create a Non-Clustered version of this and include all of the c...
I was going through Questions every good .Net developer should be able to answer and was highly impressed with the content and approach of this question and so in the same spirit, I am asking this question for Database/SQL Developer. What questions do you think should a good Database/SQL programmer be able to respond to? EDIT : I am ma...
Say I have a text file that looks like this: date 1/1/2010 a,b,c a,b,d ... I want to import it into a table so it looks like this: 1/1/2010,a,b,c 1/1/2010,a,b,d ... What is an elegant way to do that? My best idea so far is to use a data flow package, and use a flat file source to read in the file (ignoring the first line) and lo...
Just to head off the quick-repliers, I'll mention that I'm not using MySQL so I can't use GROUP_CONCAT, and that I've also looked at this question already. The example (this is not the data i'm working with but it accurately describes my case) Person table, with ID, Name columns Person_CountriesVisited table, with PersonID, Country colu...
My understanding is that, in keeping with Interbase v6, Firebird 2.5 does not support the SQL-92 INTERVAL keyword. At least, so suggests this reference and my repeated SQLCODE -104 errors trying to get INTERVALs to work under Firebird's isql(1). How, then, do I account for the irregularities in our civil reckoning of time -- months are...
I need to let a company push information up to my site. The best way to explain what I am talking about is to explain how it is currently done with their previous website: This company uploads a CSV file to an FTP set up by the website. The website then processes the CSV file and puts it into an SQL database so that it can be used by ...
How can i get the number of rows like mysql_num_rows but without having a mysql resource to work from, because i don't know the future scale of my webapp and it could take an hour just to get the number of rows ;) thanks! ...
Edit 1 (clarification): Thank you for the answers so far! The response is gratifying. I want to clarify the question a little because based on the answers I think I did not describe one aspect of the problem correctly (and I'm sure that's my fault as I was having a difficult time defining it even for myself). Here's the rub: The result s...
Hey everyone, I have a table that looks something like this: ID | Keyword | Category | Sub-Category | Sub-Sub-Category | Sub-Sub-Sub-Category Do i need to split it up in two tables (a keyword table and a categories table with parent id) if one keyword can only belong to one category,sub-category...etc. m...
SQLite behaves differently when dealing with aggregation than many other RDBMS's. Consider the following table and values: create table foo (a int, b int); insert into foo (a, b) values (1, 10); insert into foo (a, b) values (2, 11); insert into foo (a, b) values (3, 12); If I query it thus: select a, group_concat(b) from foo; Norm...
I have a database with legacy data that stores transactions and uses a "bucket" method for determining account balances. I need a way to get aged past due for accounts. Table Transactions TransactionId TransactionType (CHARGE,RECEIPT) Amount PostDate To get the current balance: SELECT SUM(CASE TransactionTypeId WHEN RECEIPT THEN Amoun...
I have been researching a way to get the SQL statements that are built by a generated Migration file. These extend Doctrine_Migration_Base. Essentially I would like to save the SQL as change scripts. The execution path leads me to Doctrine_Export which has methods that build the SQL statement and executes them. I have found no way of ...
The query below selects the rows from table_1 which do not exist in table_2 based on values in the 4 columns. The mismatch could be due to one or more columns. I would like to extend the query to tell me which column(s) had the mismatched values, either by showing the column name or its value. I can do this in a cursor but prefer to do...
I run a blog where the community can post time-sensitive community links (sports scores and such). After a certain time, those posts are no longer useful, so I want to delete them in batch via a MySQL query, but I don't know how. I imagine that getting rid of those posts entirely is more than just deleting from the wp_posts table, right?...
is there a limitation of database name can't be created as numeric? mysql> CREATE DATABASE 1; ...results in: ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '1' at line 1 ....or am I doing something wrong? ...
I'm not sure what I'm doing to cause this error. The query: INSERT INTO node (type, language, title) VALUES ('bout', 'en', 'the title 3') The error: #1062 - Duplicate entry '0' for key 2 The table: CREATE TABLE `node` ( `nid` int(10) unsigned NOT NULL auto_increment, `vid` int(10) unsigned NOT NULL default '0', `type` varch...
Basically I need to return some data from a SQL Server table in the following XML format: <querydata> <entity name="Person.Contact"> <row> <field name="FirstName">Gustavo</field> <field name="LastName">Achong</field> </row> <row> <field name="FirstName">Catherine</field> <field name="LastName">Abel<...