FireBird adding dropping constraint
In Firebird I want to generate the scripts for adding all the constraints in a database and also I want to generate the script for dropping all the foreign key constraints. How do I do this? ...
sql
SQL database queries basic question AVG Difference
I have a really basic question on SQL. Relational Schema: Student (Sid, Sname ) Class (Sid, ClassName, mark) ClassName = { MATH, ENGLISH, PHY, .......} I want to list out all the student who have taken MATH, and their MATH class average minus the student's math mark Select DINSTINCT S.Sname, (E.Mark - AVG (E.Mark)) From Student ...
To create a trigger in sql for my requirement
The following is my tables table1 ========= id col1 col2 ===================================== 1 firstrow firstrow2 2 secrow secrow2 table2 =========== id orignalvalue updatedvalue =============================================== 2 secrow2 ...
generate scripts automatically
Hi Guys, I need to generate automatic sql script of SP, views, triggers etc. Is there any tool available that i can use where i just need to put in the name for example SP name and it generates a create script for that for me. sql server 2005 Thanks ...
SQL Query to join different three tables...
hi here we have 3 tables .table1 and table2 have seq as common column.table3 contains terms related to 1 and 2 .now we want to find the number of seq in which each row in table 3 occurs. table 1 col1 col2 seq1 m1 seq2 m1 seq3 m2 seq4 m3 seq1 m2 table2 col1 col2 seq...
ASP.NET Cache and Output Cache for Controls
Hi there You know I have the way to Cache the data I've got from the SQL Server over data caching. In addition I can output cache web user controls. Whats about a web user control contains data from a SQL database? Does it make sense to cache the data and also cache the control? What is the best solution for the combination of these t...
Are there uses for TransactionScope in Entity Framework 4 in situations that do not span multiple context objects?
I am working on my first large project that uses EF4 and have been pondering concurrency situations as I am implementing certain business scenarios. I understand that EF has built in support for optimistic concurrency by setting Concurrency Mode to Fixed on entity properties. This seems to be sufficient in most cases. Several things I...
Column is invalid error when using derived table
I'm using ROW_NUMBER() and a derived table to fetch data from the derived table result. However, I get the error message telling me I don't have the appropriate columns in the GROUP BY clause. Here's the error: Column 'tblCompetition.objID' is invalid in the select list because it is not contained in either an aggregate function or...
SQL moving to vertical records
I have a table (2 million rows) in Informix v11.10, replicated (50+ node) environment Basic layout is like so: ID (PK) (int) division (int) company (int) feature1 char(20) feature2 int ... feature 200 char(2) There are several issues I have with the current layout: There are 200 "features" associated with this record but may...
SQL Using ORDER BY with UNION doesn't sort numbers correctly (e.g. 10 before 8)
I've tried looking for the answer, and read many threads on this site, but still can't find the answer I'm looking for. I am trying to sort a series of numbers that are real look-ups and also one * which isn't, I can sort fine when I don't need to add the fake * but not after. I have tried SELECT DISTINCT MasterTable.ClassName, Master...
I need to show the monthly inventory data
I have a table some thing like as follows for Inventory details. InventoryTable. InventoryTableID DateCreated quantity ItemName ------------------------------------------------- 1 2010-02-04 12 abc 2 2010-03-10 4 abc 3 2010-03-13 5 xyz 4 201...
Poorly performing Mysql subquery -- can I turn it into a Join?
I have a subquery problem that is causing poor performance... I was thinking that the subquery could be re-written using a join, but I'm having a hard time wrapping my head around it. The gist of the query is this: For a given combination of EmailAddress and Product, I need to get a list of the IDs that are NOT the latest.... these or...
SQL Query comparing rows from single table
Hi, here we are comparing gene table records like: first will take gene a and compare this with all genes a,b,c for example aa,ab,ac same will take b then ba,bb,bc so on....... so here matching results are a nd b is 2 because matching records are 589,822 common gene terms for b c count is 1 because matching record 586 and for all other ...
calculating "Max Draw Down" in SQL
edit: it's worth reviewing the comments section of the first answer to get a clearer idea of the problem. edit: I'm using SQLServer 2005 something similar to this was posted before but I don't think enough information was given by the poster to truly explain what max draw down is. All my definitions of max draw down come from (the firs...
How to use plain SQL in Kohana 3.x
I dont want to rely on all those ORM/Querybuilder etc. tools in Kohana 3.x. I just want do use plain old SQL to Insert a new row in my table of my MySQL Database. How can I do that? ...
SQL QUERY FOR JOINING MANY TABLES.
Possible Duplicate: SQL Query to join different three tables Hi all Here i have 3 tables TABLE1:(goseq_relation table) id seqid goterms 1 SEQ1 T1 2 SEQ1 T2 3 SEQ2 T3 4 SEQ2 T4 5 SEQ3 T5 6 SEQ3 T6 Here id is a composite primary key. TABLE2:(motifseq_relation) id seq...
Comparing values between two rows of data and only showing the columns that are different
In a previous application version we were using a particular field for a primary key, but because the field may represent different identities across various systems we have made it a non significant field(ie not a primary key or part of a composite primary) however since we dont have another system yet users still use that field as a pr...
How do you run a complex sql script within a python program?
I have a large SQL script that creates my database (multiple tables, triggers, and such. Using MySQL), and I need to execute that script from within a python program. My old code did this: sql_file = open(os.path.join(self.path_to_sql, filename), 'r') sql_text = sql_file.read() sql_stmts = sql_text.split(';') for s in sql_stmts: c...
converting date into char in AS400
Hi everyone , I am trying to convert the current date - 2 months into format yyyymmdd and subtract 1900000 from it. Oracle was easy for me SELECT TO_CHAR(ADD_MONTHS(SYSDATE,-2),'YYYYMMDD') - 19000000 FROM DUAL Can some one suggest me the same for AS400 . Thanks in advance. ...
SQL : Using GROUP BY and MAX on multiple columns
Hey, I have an issue with an SQL Query. Lets take this example data itemID catID attrib1 attrib2 1 1 10 5 2 1 10 7 3 1 5 10 4 2 18 15 I want to return the best item for each category (with attrib1 having priority over attrib2) Obviously, "SELECT catI...