I have a very high-traffic table with a char(50) field which takes part in several indexes. This char(50) field allows NULLS, and in that case a NULL value is considered to be the same as a non-NULL, zero-length string for my purposes. I also disregard leading & trailing whitespace and, while I scrub the data before I insert it, it may...
This is a simplified version of a query I have. Say for each customer that has made an order, I want to know what percentage of their total order value was for green items. There can't be multiple items in an order, but it's possible for the order value to be zero so I want to avoid division errors. (And no, I am not able to change the ...
Say I have the following data Name Value =============== Small 10 Medium 100 Large 1000 Imagine that these represent the volumes of boxes. I have some items that I want to put in the boxes, and I want the smallest box possible. I need an SQL query that will: Return the row with the smallest row greater than my ...
Is it a good idea to store files in a different SQL Server 2005 database "FileDb" and point to it (files) in the "AppDb" by "FileId". Please take in consideration that I have a dedicated server and I can create as many Sql Databases as I want. update: Which perform better single database or multiple databsae? ...
Hello, I have a SqlDataAdapter that is being populated with 21 rows of data (4 columns). The sproc that drives it returns in a couple seconds in SQL Mgmt Studio, but the .Fill() takes 5 minutes. ArrayList ret = new ArrayList(); SqlDataAdapter da = null; SqlCommand cmd = null; cmd = base.GetStoredProc("usp_dsp_Stuf...
In my table I want to set default of a column to 30 days from now. This needs to be a real, not computed column. Something like alter table T_NAME alter column EXPIRATION set default CURRENT_TIMESTAMP + 2592000 is not valid, but you get the idea. I am sure I can do that with a before insert trigger, but I was just wondering if ther...
I have an app that uses SQLite to store data locally. There may be more than one file storing the data. All the files contain a list of items, but only one of them has the "correct" status for the current user (basically, there is a "user" db in $HOME and a "system-wide" one in /etc). Usually, both files will contain the same list of ...
What are the benefits of using the "xml" datatype versus storing the xml content inside a "text" datatype? Am I able to query by some specific xml attribute or element? What about indexing and query performance? Besides the postgresql manual what other online sources can you point me to? ...
Let's say I have a Product, Category, and Product_To_Category table. A Product can be in multiple categories. Product Category Product_to_category ID | NAME ID | Name Prod_id | Cat_id ===================== ============ =================== 1| Rose ...
CREATE TABLE DEPARTMENTS ( dept_num NUMBER (8) dept_name VARCHAR2 (20) NOT NULL, dept_loc VARCHAR2 (25), dept_phone CHAR (13), CONSTRAINT dept_num_pk PRIMARY KEY (dept_num) ) ; ...
I am working with an Oracle database that stores HTML as a Long datatype. I would like to query the database to search for a specific string within the HTML data stored in the Long. I tried, "select * from TABLE where COLUMN like '%form%'". This causes the following Oracle error because "like" is not supported for Long datatypes. O...
I am looking to create a view that pulls data from two tables "Schedule" and "Reference". Schedule has 50+ columns (it's almost completely denormalized -- not my design), most of which contain a value that could be joined to a column in the Reference table. How do I write the SQL statement to correctly join each column in Schedules to ...
I'm working on a a tool to generate TSV files for import into a database using bcp.exe and I'd like to validate my output. I can do this by comparing the file I generate to the files produced by exporting using bcp from an existing database. My problem is that the ordering can sometimes be different between files. I'd like a tool that wi...
I have an xml column in SQL Server 2005 that is the equivalent of: <Test foo="bar"> <Otherstuff baz="belch" /> </Test> I want to be able to get the value of the foo attribute of Test (the root element) as a varchar. My goal would be something along the lines of: select cast( '<Test foo="bar"><Otherstuff baz="belch" /></Test>' as xm...
I'm trying to do some SQL query validation programatically in C# (without invoking the actual database). Essentially, I'd like a user to be able to enter a view, UDF, or SP and have its dependencies validated immediately. The user would be entering these into a custom tool for defining database objects. Thus, if a user entered: CREAT...
Can someone show me the simplest way of perform a parametrized SQL query using Classic ASP in VBscript? A compilable example would be best. ...
Hello, I have this very weird results when trying to fetch the AVG() field from my "reponse" field. Query : SELECT AVG(Reponse.note) as noteMoyenne, Categorie.titre, Autorisation.typeEvaluateur, COUNT(DISTINCT Autorisation.id) as nbEvaluateur FROM reponses as Reponse, categories as Categorie, questions as Question, auto...
I have a SQL Server table that contains users & their grades. For simplicity's sake, lets just say there are 2 columns - name & grade. So a typical row would be Name: "John Doe", Grade:"A". I'm looking for one SQL statement that will find the percentages of all possible answers. (A, B, C, etc...) Also, is there a way to do this without ...
I'm trying to use ADO to create several tables at once, into MS Access. Is it possible to do multiple statements in the one operation? For instance: ... // I have omitted the field details CString sQuery = "CREATE TABLE [Table1] (..., PRIMARY KEY ([ID])); \nCREATE TABLE [Table2] (..., PRIMARY KEY ([ID]));"; oRecordset.Open(oDatabase.m_...
I'm looking for a package to typeset SQL statements in LaTeX. So far I have heard of listings and lgrind, are there any other suggestions? [edit] Added requirement: I'd like the package to be able in intelligently insert page breaks, so that where possible statements do not span multiple pages. Still reading documentation, so it is poss...