sql

Files with .sql extension identified as binary in Mercirial

I generated a complete set of scripts for the stored procedures in a database. When I created a Mercurial repository and added these files they were all added as binary. Obviously, I still get the benefits of versioning, but lose a lot of efficiency, 'diff'ing, etc... of text files. I verified that these files are indeed all just text...

Send the total rows/last row, included with the resultset

I have this procedure used for getting items on the current page. I would also like the include an OUT var having the total number of items so I can calculate the total number of pages. USE [some_name] GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER PROCEDURE [dbo].[GetFilteredProductDetails] @start int, @end int AS B...

getting a property associate with the result of an aggregate function

I have a table used to store array elements, this elements belong to a frame, each element have a date and a comment (among other elements). Something like this: MASTER_FRAME(K) ARRAY_ID(K) ARRAY_INDX(K) DATE COMMENT 1 1 1 27-08-2010 'List 1 Newer' 1 1 2 27-0...

Is there any MySQL Aggregate Function for "CONTAINS"?

Say I have this data set user | group --------+------- [email protected] | A [email protected] | B [email protected] | A [email protected] | B [email protected] | A [email protected] | B [email protected] | C I want to convert this into a table like this: user | IN_A | IN_B | IN_C --------+-------+-------+------- [email protected] | TRUE | TRUE | FALSE [email protected] | TRUE | FALSE | FALSE c@...

How do I return results for all records in a joined table query when they are missing from one of the joined tables?

I have the following SQL query SELECT tbl_product.prod_id, ISNULL (prod_code, '') AS prod_code, ISNULL (prod_category, '') AS prod_category, ISNULL(prod_title, '') AS prod_title, ISNULL(prod_price, 0.0) AS prod_price, IS...

Is there anything faster than SqlDataReader in .NET?

I need to load one column of strings from table on SqlServer into Array in memory using C#. Is there a faster way than open SqlDataReader and loop through it. Table is large and time is critical. EDIT I am trying to build .dll and use it on server for some operations on database. But it is to slow for now. If this is fastest than I have...

Which database for chat logging?

I'm implementing a chat system. What kind of database I should use for logging chat messages? I need to do some queries to it. For example "give me all the links from user x between these dates" I've heard some good things about MongoDB for this kind of stuff. How about other NoSQL solutions? Redis? Cassandra? How about old SQL solut...

Mysql insert with foreign key referring to primary id

Let's say I hava a table object: id, primary key auto-increment obj_id, foreign key (object.id) val, integer Is it possible to insert a record with obj_id = id. Not having an error with foreign key constraint? Insert into object (val) values (123) ...

Sql query - how to get when a row first got a certain value

Hi, I have a table with rows like this: ID StatusId Date 1 1 2001-01-01 2 1 2001-01-02 3 2 2001-01-03 4 3 2001-01-04 5 1 2001-01-05 6 2 2001-01-06 7 2 2001-01-07 8 1 2001-01-08 9 1 2001-01-09 I need to g...

Display all values in a GROUP BY

How do I get all the values in a group by statement? mysql>select * from mytable; +------+--------+--------+ | name | amount | status | +------+--------+--------+ | abc | 12 | A | | abc | 55 | A | | xyz | 12 | B | | xyz | 12 | C | +------+--------+--------+ 4 rows in set (0.00 sec) mysql>sele...

How can I use column values as column names in MySQL

I have a table that looks like this: |date |category_id|val |2010-08-09|1 |2 |2010-08-09|2 |45 |2010-08-10|3 |1500 |2010-08-10|2 |4 I would like to select from this table, that each category id is a column so the end result looks like this: |date |1 |2 |3 |2010-08-09|2 |45 |NULL...

Is there a LIKE Clause for querying XML in SQL

I'm using SQL Server 2005 and am wondering if there is any way to do a LIKE clause in my xml searches. Here's a sample query: SELECT Id, CaseNumber, XmlTest FROM UploadReport WHERE CASE WHEN @lastName != '' AND XmlTest.exist('(/CodeFiveReport/Parties/Party/Name/LastN...

Oracle & java dynamic 'Order by' clause

Hi guys, I am trying to build a dynamic sql query in java (shown below) sqlStr = "Select * " + "from " + tableName if(tableName!=null){ if(tableName.equals("Table1"){ sqlStr = sqlStr.concat("order by city desc"); }else if(tableName.equals("Table2"){ sqlStr = sqlStr.concat("order ...

CommandText is not initialized

I have this code Dim LogData As sterm.markdata = New sterm.markdata() Dim datelistquery As String Dim properdate As String If Session("TenHolStDateHP1") = "%" or Session("TenHolStDateHP1") = "" Then If Session("passedPropId") = "" Then 'QUERY USED IF NO DATE SELECTED AND A PROPID HAS NOT BEEN PASSED datelistquery = "S...

SQL Stored Proc: Help converting to trigger

Hi all, I'd like to convert a stored proc into a trigger. The stored proc is supposed to be run after inserts/updates, but users forget to execute it more often than not! The stored proc inserts recalculated values for some rows: --Delete rows where RowCode=111 DELETE FROM dbo.TableA WHERE [year]>=1998 AND RowCode=111 --Insert new va...

INSERT INTO SELECT,copy value+ 'string'

Hello, I need to copy a row .Copied row,I need to change value, this value + 'copy' I made this sql..but it's not work.. INSERT INTO prizes_i18n ( lang_id , translation_name , translation_desc , name , lang_path) SELECT prizes_s.lang_id , prizes_s.translation_name + 'copy' , prizes_s.translation_desc ...

SQL query: self-referencing foreign key relationship

I have two tables, tabSparePart and tabSparePartCategory. Every spare part belongs to a spare part category. I need all spare parts that belong to a specific category. But the problem is that a spare part category could be a "subcategory" of another, they reference each other (the "main categories" have 'null' in this FK column). Let's...

Need lowest price in each region in a mysql query

Hi guys, I am trying to write up a query for wordpress which will give me all the post_id's with the lowest fromprice field for each region. Now the trick is these are custom fields in wordpress, and due to such, the information is stored row based, so there is no region and fromprice columns. So the data I have is (but of course cont...

Linked server (read data from pgsql and insert it into SQL Server)

I have a SQL Server 2005. I have created a linked server to PG SQL server. It works great. SELECT * FROM OpenQuery(POSTGRESQL_SERV, 'SELECT comp_id,comp_name FROM company WHERE comp_type = 5') I need to read all data from PG SQL and insert it into SQL Server. How to create a while ? (also unique id`s) Thx. ...

use RETURNING-data in another statement

Hello, I want to use data 'theID' from a PostgreSQL-RETURNING statement INSERT INTO ... RETURNING theID; further in another statement. How can this be done (without cursors)? My full statement (copying some data and delete them from source table; tab1 & tab2 definition are equal): DELETE FROM tab1 WHERE tab1id IN ( INSERT INTO...