|
Bingo!!! THANK YOU!!
The final 'order by' on the above in SQL Server, orders the entire rowset. But the above got me thinking, along with Colins original answer of adding a 0, 1 column.
I thought a group by on the individual columns may work, but since my country_id's are all over the place (secondhand data!), that was a no-go. Then thought about adding a 'magic' column to help with the order by.
Finally I've got this, which is exactly what I've been wanting to achieve (n.b. I've got several breakdowns of the UK: United Kingdom (Mainland), United Kingdom (Northern Ireland), etc ):
SELECT country_id, country_name, 0 as seed
FROM country
WHERE country_name LIKE 'United Kingdom%' OR country_name = 'United States'
UNION ALL
SELECT country_id, country_name, 1 as seed from country
WHERE country_name NOT LIKE 'United Kingdom%' AND country_name != 'United States'
ORDER by seed, country_name
I only vaguely remembered that there was a way to do this because I got asked it in an interview earlier in the year, but just couldn't remember how to do it!
Once again THANK YOU!!!!!!!
 
|
|
|
|
|
There is one more thing. If you want to avoid the seed to be a part of the query you can try the following
select ctm.country_id, ctm.country_name from
(SELECT country_id, country_name, 0 as seed
FROM country
WHERE country_name LIKE 'United Kingdom%' OR country_name = 'United States'
UNION ALL
SELECT country_id, country_name, 1 as seed from country
WHERE country_name NOT LIKE 'United Kingdom%' AND country_name != 'United States'
ORDER by seed, country_name) as ctm
-- modified at 12:32 Monday 10th October, 2005
|
|
|
|
|
Hi all...
I want to find whether a table exists in Access Database or not. I know how to do it in MS SQL Server, we can see the result using Select query from sysobjects table and we have the list of all Tables, SPs, Views, functions...etc. But if my program gets .mdb file i.e. Access Database, my program must find whether a perticular table is created in that database or not.
Is there any way to do it???
Thanx in advance....
|
|
|
|
|
Rohan_bhat_31 wrote: want to find whether a table exists in Access Database or not.
Try this.
SELECT MSysObjects.Name
FROM MSysObjects
WHERE MSysObjects.Name = "tbl_name"
DEBUGGING : Removing the needles from the haystack.
|
|
|
|
|
Hey..thanx buddy....
but where this table is stored??? and there must be some log files also generated behind the scene...like SQL Server...???
looking 4ward 2 see ur reply...
|
|
|
|
|
Rohan_bhat_31 wrote: but where this table is stored??? and there must be some log files also generated behind the scene...like SQL Server...???
Access is completely different than SQL Server. An Access database is basically a binary file, so there is no real "table".
There are no log files. No transaction nothing.
I guess the question becomes what are you attempting to do?
DEBUGGING : Removing the needles from the haystack.
|
|
|
|
|
Hi,
How to get list of all registered databases on mySQL server?
Millan
|
|
|
|
|
hello, I have a question...how can I delete in MySQL the current row...the one where I am currently positioned? is this possible? Thank you.
|
|
|
|
|
hi
handling transaction in asp .net code behhind provides better performance or handling it in SQL Server's Stored procedures in queries like removing list of an entity?
thanks
|
|
|
|
|
Guys, I'm confused so help me out
when trying to connect to a DB (MS Access) that has a password using a connection string like this
"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=\somepath\mydb.mdb;Jet OLEDB:Database Password=MyDbPassword;"
i used to get that error about a workgroup file missing
so i created one, -which btw i have no idea how it works!- and added it to the connection string
something like this
Provider=Microsoft.Jet.OLEDB.4.0;Data Source=\somepath\mydb.mdb;Jet OLEDB:System Database=system.mdw;"
after deploying my application, and installing it, only the user that uses the same PC where i created the workgroup can access the DB, apprently because it joined the workgroup by a simple click
the rest get the following error
System.TypeInitializationException: The type initializer for "CrystalDecisions.CrystalReports.Engine.ReportDocument" threw an exception. ---> System.Runtime.InteropServices.COMException (0x80040154): COM object with CLSID {CF76A644-314B-404D-8D45-F08B51FF990B} is either not valid or not registered.
which btw have nothing to do with the CR nor the merge modules...because after long discussion and search, it all pointing to the added security for the DB
so, guys, why do i need to create a workgroup? why not just a password?
it is a multi user DB ...
If the workgroup is required, then, how can i add users if i'm using domain log-in mode?..i tried using the user IDs they are using to access the domain...still, nothing worked
One more thing, the users' levels are defined within the application, so their access mode on the DB level is the same
|
|
|
|
|
Ranoush wrote: when trying to connect to a DB (MS Access) that has a password using a connection string like this
"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=\somepath\mydb.mdb;Jet OLEDB:Database Password=MyDbPassword;"
i used to get that error about a workgroup file missing
Change your connection string.
strConnection = _T("Driver={Microsoft Access Driver (*.mdb)};"
"Dbq=c:\\DatabasePath\\dbaccess.mdb;Pwd=MyDbPassword;");
or
strConnection = _T("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\\DatabasePath\\dbaccess.mdb; Pwd=MyDbPassword;");
That should get rid of the need for a system db.
DEBUGGING : Removing the needles from the haystack.
|
|
|
|
|
Hello all,
There has got to be an answer for this. I am running Visual Studio 2003 and SQL Server 2000 Client on Windows XP and created a database project. Everything is working great. I can look at tables, and connect. What I can not do is generate scripts, and design. It is like it is not an option. I reinstalled the client tools from SQL. I have tried everything, but generate Create script will not show up. If anyone is familar with this can you please help. Here is my id pitchiah4u@hotmail.com
Pitchiah
Pranav Softsol Pvt Ltd.
|
|
|
|
|
I have a datagird and a checkbox web control within the datagrid, I am trying to step through each datagriditem and detect whether the checkbox is checked or not, however when testing this application, the checkbox always shows unchecked? I have bound the checked property to a column in the datagrid, and added a button shich will execute code to delete all lines whose checkbox is checked. Please Help!
|
|
|
|
|
Hello
I just installed Visual studio 2005 RC1 and after that Sql server 2005 september ctp. The installation of both programs went well and no error was shown.
Unfortunatly there is not any trace of Management Studio. Under Start Menu\Programs\Microsoft SQL Server 2005 CTP i only have Configuration Tools and nothing else.
In the installation of sql server 2005 september ctp i choosed all client tools (i choosed every checkbox for all programs).
Does anyone have any good idea?
|
|
|
|
|
The trick is to install SQL 2005 september ctp before installing Visual Studio 2005 RC1.
|
|
|
|
|
I am new to SQL.
I am facing a problem.
I have two tables as under:
Sales
+-------+-------+---------------+---------------+
|ID |Date |ProductID |Sold_Qty |
+-------+-------+---------------+---------------+
|1 |5/25/05|1 |4 |
|2 |5/25/05|2 |3 |
|3 |5/26/05|1 |2 |
|4 |5/26/05|1 |1 |
|5 |5/26/05|2 |2 |
+-------+-------+---------------+---------------+
SalesReturn
+-------+-------+---------------+---------------+
|ID |Date |ProductID |Returned_Qty |
+-------+-------+---------------+---------------+
|1 |5/26/05|1 |2 |
|2 |5/27/05|2 |1 |
|3 |5/27/05|1 |2 |
+-------+-------+---------------+---------------+
How to create a query that can tell me about each product sales activity (How many sold and how many returned)
And can preview as under
+---------------+---------------+---------------+
|ProductID |T_Sold_Qty |T_Returned_Qty |
+---------------+---------------+---------------+
|1 |7 |4 |
|2 |5 |1 |
+---------------+---------------+---------------+
Note: I have tried "SELECT SUM([Sold_Qty]) As T_Sold_Qty WHERE ProductID=1"
This query works only in one table and for only one ProductID
What about the following Query?
"SELECT Products.ProductID, (SELECT SUM([Sales.Sold_Qty]) As T_Sold_Qty FROM Sales WHERE Sales.ProductID=Products.ProductID), (SELECT SUM([SalesReturn.Returned_Qty]) As T_Returned_Qty FROM SalesReturn WHERE SalesReturn.ProductID=Products.ProductID) FROM Products,Sales, SalesReturn "
Shoaib Nawaz
-- modified at 16:38 Thursday 6th October, 2005
|
|
|
|
|
I'm sure this can be done without all these subqueries - I've never even tried to put a sub-query in the columns list of a SELECT before and I have no idea if it would work.
Anyway, I think that something like this would be best:
SELECT Products.ProductID,
SUM(Sales.Sold_Qty) AS T_Sold_Qty,
SUM(SalesReturn.Returned_Qty) AS T_Returned_Qty
FROM Products
INNER JOIN Sales ON Sales.ProductID = Products.ProductID
INNER JOIN SalesReturn ON SalesReturn.ProductID = Products.ProductID
GROUP BY Products.ProductID
Does this help?
My: Blog | Photos
"Man who stand on hill with mouth open will wait long time for roast duck to drop in." -- Confucious
|
|
|
|
|
I used this query at actual senerio.
It is too time consuming. It took 5 min approximatly for 10 to 50 records in each table.
Table: Products, InvoicesDetails, PurchasesDetail, SalesReturnsDetails, PurchasesReturnsDetails
SELECT DISTINCT Products.ID, (SELECT SUM([InvoicesDetails.SoldQuantity]) As T_SoldQty FROM InvoicesDetails WHERE Products.ID=InvoicesDetails.ProductID) AS T_SoldQty_, (SELECT SUM([PurchasesDetail.PurchasedQty]) As T_PurchQty FROM PurchasesDetail WHERE Products.ID=PurchasesDetail.ProductID) AS T_PurchQty_, (SELECT SUM([SalesReturnsDetails.ReturnedQty]) As T_SRetQty FROM SalesReturnsDetails WHERE Products.ID=SalesReturnsDetails.ProductID) AS T_SRetQty, (SELECT SUM([PurchasesReturnsDetails.ReturnedQty]) As T_PRetQty FROM PurchasesReturnsDetails WHERE Products.ID=PurchasesReturnsDetails.ProductID) AS T_PRetQty
FROM Products, InvoicesDetails, PurchasesDetail, SalesReturnsDetails, PurchasesReturnsDetails;
Thanks for your reply, I will check your method after transforming into the actual senerio.
Thanks Again!
Shoaib Nawaz
|
|
|
|
|
SELECT Products.ProductID,
SUM(Sales.Sold_Qty) AS T_Sold_Qty,
SUM(SalesReturn.Returned_Qty) AS T_Returned_Qty
FROM Products
INNER JOIN Sales ON Sales.ProductID = Products.ProductID
INNER JOIN SalesReturn ON SalesReturn.ProductID = Products.ProductID
GROUP BY Products.ProductID
I have tried but it is problematic. (Sentax error)
Shoaib Nawaz
|
|
|
|
|
Sorry, I don't see it - Where did SQL Server say there was a syntax error?
My: Blog | Photos
"Man who stand on hill with mouth open will wait long time for roast duck to drop in." -- Confucious
|
|
|
|
|
I have a set of querys that pull data from a bunch of tables in a database and return totals for important datapoints for our company (ie Total Revenue for the day, for the month...quarter and so on). I have build a windows "dashboard" to display these values and auto refresh every 5 minutes. The problem with this is if 10 or so people have this installed on their system, the database is getting constantly hammered every 5 minutes and pulling from our most important tables. There has got to be a better way to do this and I figured the smart readers of codeproject would give me a suggestion or advice.
Thanks
JGA
|
|
|
|
|
When people learn about relational databases there is a lot of emphasis put on normalisation of the data model. However, like all optimisations, it really depends on the situation. Obviously you have a data model and it works reasonably well for most of the things that you do. However, a highly normalised data model is inefficient for reporting.
What I suggest is to create a report table (or tables) that contain duplicate information to the main database, however these tables are flattened so that hold data in the format that your SELECT statement is currently creating. For example. Say you have a SELECT statement being kicked off for your "dashboard" like this
SELECT A.C1, A.C2, B.C3, C.C4, C.C5, D.C6<br />
FROM A<br />
INNER JOIN B ON A.PKA = B.FKA<br />
INNER JOIN C ON A.PKA = C.FKA<br />
INNER JOIN D ON C.PKC = D.FKC
You create a single reporting table that contains C1..C6 in a flattened format. You have a process that once every 5 minutes updates the report table. All your "dashboard" users access the report table meaning that your main database is hit only once per 5 minutes for the data and the users access the fast tables that are pre-formatted for their needs.
Does this help?
My: Blog | Photos
"Man who stand on hill with mouth open will wait long time for roast duck to drop in." -- Confucious
|
|
|
|
|
yeah, that does help. Thanks!
|
|
|
|
|
Hey,
I have sql script files that when loaded into sql query analizer work as expected.
How do I load and execute the entire contents of the file from with in a C# windows
app.
I tried
m_cConn = new SqlConnection();
m_cConn.ConnectionString = sConnStr;
m_cConn.Open();
m_cCmd = new SqlCommand(sSQL);
m_cCmd.Connection = m_cConn;
m_cCmd.ExecuteNonQuery();
m_cConn.Close();
etc..
The sSQL is a string with the contents of the sql file loaded into it.
The file contains DB create / drop, table create / drop, procedure create / drop ,
use, update and insert statements.
Thanks in advance.
For Example:
--create the database
use master
declare @DBName varchar(50)
set @DBName = '[NetPortalDB]'
if exists(select * from sysdatabases where name=@DBName)
drop database [NetPortalDB]
create database [NetPortalDB]
GO
use [NetPortalDB]
-- Adding downtime login
if not exists (select * from master.dbo.syslogins where loginname = N'downtime')
BEGIN
declare @logindb nvarchar(132), @loginlang nvarchar(132) select @logindb = N'master', @loginlang = N'us_english'
if @logindb is null or not exists (select * from master.dbo.sysdatabases where name = @logindb)
select @logindb = N'master'
if @loginlang is null or (not exists (select * from master.dbo.syslanguages where name = @loginlang) and @loginlang <> N'us_english')
select @loginlang = @@language
exec sp_addlogin N'downtime', N'downtime', @logindb, @loginlang
END
GO
-- Adding processmore login
if not exists (select * from master.dbo.syslogins where loginname = N'processmore')
BEGIN
declare @logindb nvarchar(132), @loginlang nvarchar(132) select @logindb = N'master', @loginlang = N'us_english'
if @logindb is null or not exists (select * from master.dbo.sysdatabases where name = @logindb)
select @logindb = N'master'
if @loginlang is null or (not exists (select * from master.dbo.syslanguages where name = @loginlang) and @loginlang <> N'us_english')
select @loginlang = @@language
exec sp_addlogin N'processmore', null, @logindb, @loginlang
END
GO
if not exists (select * from dbo.sysusers where name = N'downtime' and uid < 16382)
EXEC sp_grantdbaccess N'downtime', N'downtime'
GO
if not exists (select * from dbo.sysusers where name = N'processmore' and uid < 16382)
EXEC sp_grantdbaccess N'ProcessMORe', N'processmore'
GO
then goes on to create tables and populate them
|
|
|
|
|
The GO is not part of SQL. It is used within Query Analyser to delimit batches.
I put together a little application that will allow you to process files with GO delimiters in them within .NET. You can find it on my blog[^].
Does this help?
My: Blog | Photos
"Man who stand on hill with mouth open will wait long time for roast duck to drop in." -- Confucious
-- modified at 4:03 Thursday 6th October, 2005
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
