|
I want to select data from ranges like
Column1 Column2
25000 10
50000 20
75000 30
I want records like upto 25000, I get 10
upto 50000, I get 20
upto or more than 75000, I get 30
Can anybody suggest me a simple query for that?
Thanks in advance,
|
|
|
|
|
Use a sql case statement. http://doc.ddart.net/mssql/sql70/ca-co.htm[^]
SELECT Column1,
CASE
WHEN Column1 <= 25000 THEN 10
WHEN Column1 > 25000 AND Column1 <= 50000 THEN 20
ELSE 30
END AS Column2
FROM MyTable
Broken Bokken
You can't carry out a ninja-style assasination dressed as an astronaut. It's the luminous fabric; too visible. - Tripod
http://www.brokenbokken.com
|
|
|
|
|
Hi.
I have database structure like this:
Organizations(OrganizationID, Name)
Members(MemberID, OrganizationID, Name)
Courses(CourseID, OrganizationID, Name)
I need to return the Organization Name, the number of members of that organization and the number of courses in that organization in one sql statement so I'd have a table like
[OrganizationName] [Number of Members] [Number of courses]
[Org A] [10] [22]
[Org B] [92] [11]
...
Is it possible without using a stored procedure?
Can anyone help me out? Any help would be much appreciated.
|
|
|
|
|
One way:
select o.Name, (select count(*) as MembersCount from Members m where m.OrganizationID= o.OrganizationID),(select count(*) as CoursesCount from Courses c where c.OrganizationID = o.OrganizationID)<br />
from Organization o
There are other alternatives depending on what database you are using
Bob
Ashfield Consultants Ltd
|
|
|
|
|
|
No problem.
Bob
Ashfield Consultants Ltd
|
|
|
|
|
I know only two differneces.
1) ADO wroks on Connected architecture, while ADO.Net on DisConnected Architecture.
2)ADO is used to create Client Side Cursors while ADo.net create both client as well as server.
IF FRNDS U KNOW DIFFERENCES OTHER THAN THESE PLZ DO REPLY
|
|
|
|
|
Notice the deluge of responses your question elicited.
1. Learn to spell
2. Learn to type english - your location is the US so presumable english is your 1st language
3. Learn to use google - see the 2nd response to this
clickety[^]
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Good Morning All
I have a ASP Classic Application,that Connects to SQL Server, Sudddenly it gives a Timeout Exception like this
[Microsoft][ODBC SQL Server] Timeout Expired
What is Wrong
Thanks
Vuyiswa Maseko,
Sorrow is Better than Laughter, it may Sadden your Face, but It sharpens your Understanding
VB.NET/SQL7/2000/2005
http://vuyiswamb.007ihost.com
http://Ecadre.007ihost.com
vuyiswam@tshwane.gov.za
modified on Thursday, April 24, 2008 6:06 AM
|
|
|
|
|
To stop queries from taking a very long time to return and consuming resources on the server unnecessarily, and to stop them from blocking client software for an unbounded length of time, ODBC and other database communication libraries include timeouts on both the connection to the server and on executing a command.
Given your description I'd imagine you're using the ADO objects with the MSDASQL provider, possibly through a DSN, using the Microsoft SQL Server ODBC driver. You might get better results using the SQLOLEDB provider to remove some of the layers. In either case, you should be able to use the Command object's CommandTimeout property to set a more sensible timeout for your operation. The default timeout is 30 seconds.
If instead you're looking to reduce the amount of time taken to execute a given query, you need to look carefully at how the query is written and what indexes you have to support that query. Ideally you want a single index which contains all the columns that appear in your WHERE clause which are searchable (that is, using simple comparison operators to compare the column's actual value against some provided value). LIKE is searchable if you're searching for a prefix, that is, the wildcards appear after some static text (e.g. 'CP%'). Do be aware that the more indexes you have, the longer INSERT and UPDATE operations will take. Generally you have to plan your set of indexes across all the operations that will take place on a table.
Finally one reason that queries start to take longer amounts of time is if historical data accumulates in the table. Consider archiving data to a different table or database when it is no longer current, or even deleting it outright.
DoEvents: Generating unexpected recursion since 1991
|
|
|
|
|
Thanks
i will look into that
Vuyiswa Maseko,
Sorrow is Better than Laughter, it may Sadden your Face, but It sharpens your Understanding
VB.NET/SQL7/2000/2005
http://vuyiswamb.007ihost.com
http://Ecadre.007ihost.com
vuyiswam@tshwane.gov.za
|
|
|
|
|
I think Mike has explained things very well - one thing I will add is that the setting timeout value to 0(zero) will usually set it to an infinite timeout i.e. you will not get a timeout (you may need to check your documentation on this).
We do this on our Access databases as ODBC can be very slow.
Regards
Guy
Continuous effort - not strength or intelligence - is the key to unlocking our potential.(Winston Churchill)
|
|
|
|
|
Thank you very much guys,
This Application was wrote in ASP Classic and i have no control over the code, and i always set the timeout of the command object to "0" for infinite.
Thanks for the Advice it make sense..
Thanks
Vuyiswa Maseko,
Sorrow is Better than Laughter, it may Sadden your Face, but It sharpens your Understanding
VB.NET/SQL7/2000/2005
http://vuyiswamb.007ihost.com
http://Ecadre.007ihost.com
vuyiswam@tshwane.gov.za
|
|
|
|
|
I am working on a database create script for SQL server 2005. It's getting to be pretty large (20 tables so far) and the gist of the script is to dropall the tables, create the tables, and then add in any default data. I have the last two pieces working correctly, and previously I was dropping each table individually. I found some code to help drop all tables and modified it.
DECLARE @Count int
SELECT @Count = COUNT(*) FROM sys.Tables WHERE name NOT LIKE 'sys%'
WHILE @Count > 0
BEGIN
EXEC sp_MSforeachtable "DROP TABLE ? PRINT '--- Dropped Table ? ---' ";
SELECT @Count = COUNT(*) FROM sys.Tables WHERE name NOT LIKE 'sys%'
END
IF @Count = 0
BEGIN
SET @Message = '--- Tables dropped successfully ---'
END
ELSE
BEGIN
SET @Message = '--- Error dropping tables ---'
END
PRINT @Message
The problem is that because I have foreign keys, the scrip throws errors. I tried a try-catch but then the query hangs. Ideally, I want the while loop to ignore errors, or at least not display them so that any errors displayed are errors with the create script. I do not want the exec within the while to rollback, which is what I believe the try-catch is doing. Any ideas how to do this?
Broken Bokken
You can't carry out a ninja-style assasination dressed as an astronaut. It's the luminous fabric; too visible. - Tripod
http://www.brokenbokken.com
|
|
|
|
|
Try the following SQL statement:
First drop all the child tables and then go for parent tables:
USE NORTHWIND
<br />
SELECT <br />
OBJECT_NAME(c.parent_object_id) ParentTableName,<br />
OBJECT_NAME(c.referenced_object_id) ChildTableName<br />
FROM <br />
sys.foreign_key_columns c <br />
INNER JOIN <br />
sys.columns a ON a.object_id = c.referenced_object_id AND a.column_id = c.referenced_column_id <br />
Hope this helps!!
Harini
|
|
|
|
|
I used your solution, however, you must drop the parent tables first. The Child tables are the ones that error when you try to drop them. Here is the code in case anyone else wants to know how to do this. Just change {DatabaseName} to the name of the database you want to drop all the tables in.
USE {DatabaseName}
SET NOCOUNT ON
DECLARE @Message varchar (1000)
DECLARE @TableName varchar (100)
DECLARE @Count int
SELECT @Count = Count(c.parent_object_id)
FROM sys.foreign_key_columns c
INNER JOIN sys.columns a ON a.object_id = c.referenced_object_id
AND a.column_id = c.referenced_column_id
WHILE @Count > 0
BEGIN
DECLARE tableCursor cursor FOR
SELECT OBJECT_NAME(c.parent_object_id)
FROM sys.foreign_key_columns c
INNER JOIN sys.columns a ON a.object_id = c.referenced_object_id
AND a.column_id = c.referenced_column_id
OPEN tableCursor
FETCH NEXT FROM tableCursor INTO @TableName
WHILE @@FETCH_STATUS = 0
BEGIN
BEGIN TRY
EXEC ('DROP TABLE ' + @TableName)
PRINT '--- Dropped Table ' + @TableName + ' ---'
END TRY
BEGIN CATCH
END CATCH
FETCH NEXT FROM tableCursor INTO @TableName
END
CLOSE tableCursor
DEALLOCATE tableCursor
SELECT @Count = Count(c.parent_object_id)
FROM sys.foreign_key_columns c
INNER JOIN sys.columns a ON a.object_id = c.referenced_object_id
AND a.column_id = c.referenced_column_id
END
SELECT @Count = COUNT(*) FROM sys.Tables WHERE name NOT LIKE 'sys%'
WHILE @Count > 0
BEGIN
EXEC sp_MSforeachtable "DROP TABLE ? PRINT '--- Dropped Table ? ---' ";
SELECT @Count = COUNT(*) FROM sys.Tables WHERE name NOT LIKE 'sys%'
END
IF @Count = 0
BEGIN
SET @Message = '--- Tables dropped successfully ---'
END
ELSE
BEGIN
SET @Message = '--- Error dropping tables ---'
END
PRINT @Message
GO
Broken Bokken
You can't carry out a ninja-style assasination dressed as an astronaut. It's the luminous fabric; too visible. - Tripod
http://www.brokenbokken.com
|
|
|
|
|
an overnight datasync has failed because of a missnig table in SQL Server, does anyone know if there is a log anywhere which might show where it went or who dropped it?
Thanks
|
|
|
|
|
What recovery model are you using?
|
|
|
|
|
Simple
not my decision I've only been here a week...
|
|
|
|
|
Is it SQL Server 2005?
If it is, you can try this[^].
|
|
|
|
|
that is EXACTLY what i was looking for...
THANKS!
|
|
|
|
|
Hi all,
I want to build an application in which I have an XML files which contains the connection string and credentials to connect to multiple databases. I want to connect to these databases and run a stored procedure on these database and close the connection. Can anyone guide how to proceed on this. I want to have front end which will fetch the data from the XML file for database connection, make the connection, run the stored procedure and Close the connection. Any Idea How can this be implemented.
Sankalp
|
|
|
|
|
What have you done so far?
What exactly is the problem? Can't you read an XML file or connect to a database or execute a stored procedure?
|
|
|
|
|
Are you using .Net? Siunce you didn't specify, I'm going to respond as if you are.
You should ask google about the connection strings section of the web.config. The web.config is an xml file that is already prepared with the necessary reading functionality, and the connection strings section is built to store connection strings. This is a built in feature of ASP.NET
Also ask him about ADO.Net. Ado.NET is a library for connecting to a database. There are many articles on CP about connecting to databases from .NET.
This really belongs in a programming board corresponding to the language you are going to write the application in.
Broken Bokken
You can't carry out a ninja-style assasination dressed as an astronaut. It's the luminous fabric; too visible. - Tripod
http://www.brokenbokken.com
|
|
|
|
|
Hey everybody!
I created a table-value function, so I will be able to perform a SELECT with parameters:
SELECT * FROM get_user (1) -- returns user with userID "1"
The problem is that I can't find a way to call this function from C++ using ADO (not ADO.Net).
I've been searching the net for 2 days now, and I'm pretty close to give up...
Thanks in advance!!!
|
|
|
|