How to find row after query ?
I have a query, and I need to find the row number that the query return the answer. I do not have a counter field. How do I do it? Thanks in advance. ...
sql
Query of queries outside ColdFusion
Hello. My experience of using Adobe ColdFusion, even if still somewhat limited, was absolutely joyful and pleasant. Of all good things I could say about ColdFusion, one feature completely blew me off my feet. It might be neither very effective, or useful in production, but anyway, I am talking about the so-called "query of queries" fe...
Derby - constraints.
In the Derby server, how can you use the information in the system tables of the schema to create a select statement in order to retrieve the constraint names for each table? ...
How do I create and synchronize a combined reporting-only db from two live dbs?
I need to quickly implement a read-only database containing data pulled from two identically structured live databases. The live dbs are actually company dbs from a Dynamics accounting system so I'm happy for any Dynamics specific advice but this is mostly a SQL question. It's a fairly old version of Dynamics from before Great Plains wa...
Django select_related() for multi-join query using model & filters
Hi, I have two Models: Job & Location: class Job(models.Model): title = models.CharField(max_length=20) company = models.CharField(max_length=20) location = ForeignKey('Location') class Location(models.Model): country = models.CharField(max_length=20) state = models.CharField(max_length=20) city = models.CharField(m...
Persistent temp tables in SQL?
Is it possible to have a 'persistent' temp table in MS-SQL? What I mean is that I currently have a background task which generates a global temp table, which is used by a variety of other tasks (which is why I made it global). Unfortunately if the table becomes unused, it gets deleted by SQL automatically - this is gracefully handled b...
COUNT in a query with multiple JOINS and a GROUP BY CLAUSE
I am working on a database that contains 3 tables: A list of companies A table of the products they sell A table of prices they offered on each date I'm doing a query like this in my php to generate a list of the companies offering the lowest prices on a certain product type on a certain date. SELECT a.name AS company, c.id, M...
Fast Relational method of storing tree data (for instance threaded comments on articles)
I have a cms which stores comments against articles. These comments can be both threaded and non threaded. Although technically they are the same just with the reply column left blank when it's not threaded. My application works on sqlLite, MySQL and pgsql so I need fairly standard SQL. I currently have a comment table comment_id artic...
Replacing xml namespace prefixes in SQL Server with XQuery
I have an xml structure in an existing SQL Server 2005 database table with a particular namespace defined, I need to query out the XML and in the process, change the namespace prefix to become the default namespace. The old xml has the namespace defined on the root node and child nodes and I know how to replace the root easily enough, bu...
Session Problem ASP.NET - SQL VS 2008
I have an odd session problem. I store a session in a global session class and access it on different pages. Anyway, most of my pages work fine except this one page which is really preplexing me. I set the session in one page and access it on the next for a SQL select / update /delete and with my FormView / GridView /Details View. Needle...
HTML/ASPX textbox to store non-english characters to SQL database
I have this textbox on an .aspx page, which when submitted it stores whatever typed in the textbox into a nvarchar column in MS SQL 2005. It works fine, until you try putting chinese characters. These characters are converted to question marks (?) in the database. How do you store non-english characters from a <input type="text"> to da...
How to make left outer join a UD-fuction with any sql table?
i have a table. i have a user defined function(UDF). My UDF returns me a table. i need left outer join my table and my UDF table. ...
SQL Script to take a Microsoft Sql database online or offline?
Hi folks, If I wish to take an MS Sql 2008 offline or online, I need to use the GUI -> DB-Tasks-Take Online or Take Offline. Can this be done with some sql script? ...
MySQL MyISAM table performance problem revisited
This question is related to this one. I have a page table with the following structure: CREATE TABLE mydatabase.page ( pageid int(10) unsigned NOT NULL auto_increment, sourceid int(10) unsigned default NULL, number int(10) unsigned default NULL, data mediumtext, processed int(10) unsigned default NULL, PRIMARY KEY (pageid...
How can I resolve my collation conflict in a better way?
I have a collation problem with my database and I have developed my own solution. Solution: DECLARE @new_collation varchar(128), @conflict_collation varchar(128), @cmd_holder varchar(2000), @cmd_complete varchar(2000), @schema varchar(128), @table_name varchar(128), @constraints_name varchar(128), @column_name varchar(128...
How to make faster this statement : "paramDate Between startDate and NULL" ?
This query is taking long time when endDate is null (i think that its about case statement, before case statement it was fast) SELECT * FROM HastaKurumlari WHERE CONVERT(SMALLDATETIME,'21-05-2009',103) BETWEEN startDate AND (CASE WHEN endDate IS NULL THEN GETDATE() ELSE endDate END) What should i use, when endDate is null to ma...
Mysql Call Stored procedure from another stored procedure
Hi! I am sorry for the incomplete information. Here's my two sp's: SP1 - DELIMITER $$ DROP PROCEDURE IF EXISTS SP1 $$ CREATE PROCEDURE SP1(InputCustomerID int, InputOrderID int) BEGIN // Some sql statements and finally put it into the actual table tb1 in the database END $$ DELIMITER ; SP2 - In SP2 i am assigning input value of ...
Should I use an auto-generated Primary Key if I'm just doing a lookup table?
I have a table which has two varchar(Max) columns Column 1 Column 2 ----------------------- URLRewitten OriginalURL its part of my url re-writing for an asp.net webforms site. when a url comes in I do a check to see if its in the table if it is i use the OriginalURL. My question is, if all I'm doing is querying the table for ...
Custom sort in SQL Server
Hi, I have a table where the results are sorted using an "ORDER" column, eg: Doc_Id Doc_Value Doc_Order 1 aaa 1 12 xxx 5 2 bbb 12 3 ccc 24 My issue is to initially set up this order column as efficiently and reusably as possible. My initial take was to set up a scal...
SQL through classic ADO - Undefined Function 'Round' ?
I'm working on a legacy product and i have some SQL being executed through ADO, to an Access database with linked tables to SQL Server. I'm getting the error 'Undefined function 'Round' when i execute the SQL but if i take the query and run directly in Access it works fine. I know that EVERYTHING is correct and that this is a machine s...