|
Hello everyone,
I'm doing a query for user names and email address from a database. Unfortunately, due to circumstances out of my control, data is poorly/incorrectly formatted and sometimes leads to *mostly* duplicate entries for the same thing.
Example:
SELECT DISTINCT(email), firstname + ' ' + lastname <br />
FROM users <br />
WHERE compdate BETWEEN '1/1/2006' AND '1/31/2006' <br />
AND email IS NOT NULL <br />
AND email <> ' ' <br />
ORDER BY TLH_1_1240
will return the following:
1 test01@test01.com John Doe
2 test01@test01.com John T. Doe
As you can see, the email is the same and the name is similar enough, at least for my purposes.
The DISTINCT is obviously working in a way that I don't understand and carrying over to distinct names. I just want distinct email addresses and the name that comes up associated with them first. Your thoughts?
Thanks in advance for your help!
-------------------
abort, retry, fail?
-- modified at 11:57 Friday 13th January, 2006
|
|
|
|
|
Try something like:
SELECT
email,
MIN(firstname + ' ' lastname) AS fullname
FROM
users
WHERE
compdate BETWEEN '1/1/2006' AND '1/31/2006' AND
email IS NOT NULL AND
email <> ' '
GROUP BY
email
|
|
|
|
|
You know, I can't tell you how many times I did something just a *little* different than that without the results . All it took was just a that MIN. Thanks for your help!
-------------------
abort, retry, fail?
|
|
|
|
|
Hai
I am using SQL server 2005 BETA . After logging into SQL Server using WindowsAuthentication.
When I try to run comand GRANT CONNECT TO [MyUser] on query Analyser.
An error message is returned
Cannot find the user 'MyUser', because it does not exist or you do not have permission.
I had created a usernamed 'MyUser'. How can i solve this problem
Regards
|
|
|
|
|
hi, i'm trying to connect to a sql connection, in my computer, but it keeps sending me this error:
An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified) (Microsoft SQL Server)
i tried to use the : "server=(local)" in the connectionstring, but it doesn´t matter, what can i do?, is there a way that a i can modify the default settings?...where?
Thanks.
|
|
|
|
|
In VB i am using this string:
sqlConnection = New SqlConnection("server=localhost;uid=sa;pwd=secret;database=EventMngt")
and works fine for both SQL2000 and 2005.
|
|
|
|
|
Is this sql server express or the full version. I would guess that the full version does allow remote connections by default ut i know that express does not. If it is express its an easy fix anyway.
Jonathan
|
|
|
|
|
This is because the default installation for SQL 2005 only lets you connect on the same computer (Shared memory protocol). To connect remotely with TCP, Named Pipes or whatever, you have to set up the protocols you want to use.
Go to Start -> Programs -> Microsoft SQL 2005 -> Configuration tools -> SQL Server Configuration Manager
Then open the treenode called SQL Server 2005 Network Configuration and click on Protocols for MSSQLSERVER.
Should be able to figure the rest out.
Regards,
Chad
|
|
|
|
|
Chad is correct. I had the same problem when trying to access SQL 2005 Express from a different computer. Did the steps he provided and all is well. Just my two cents worth 
Paul
|
|
|
|
|
Its the SQL 2005 Express edition, and the thing is that all that i'm trying to do is access my own pc, i mean (local), and it keeps sending me the same error even if i use: "server=localhost" or "server=(local)", i even tried the examples that other guys told me (i mean the ones in the forum), but it doesn't work... what i'm i doing wrong?
|
|
|
|
|
OK, the express edition is quite different to SQL server 2005. The comment above was quite correct but perhaps not explained completely.
The link below will give you full details on how to do this so there is no point me repeating any of it. Read it completely as its a good overview of how the express editions work.
Pay particular attention to the sections "Configure for network connectivity" and "Programmatically opening a database from a remote computer". These explain exactly how to do what you want to do.
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnvsdev05/html/vs05b1.asp[^]
Jonathan
|
|
|
|
|
Ok Here is thew error:
An OleDBParameter with ParameterName 'TableNumber' is not contained by this OleDbParameterCollection.
Here is the code:
private void UpdateTable(string strTable, decimal decSubtotal)
{
try
{
objUpdateSubtotal.Parameters["Subtotal"].Value = decSubtotal;
objUpdateSubtotal.Parameters["TableNumber"].Value = strTable;
oleDbConnection1.Open();
objUpdateSubtotal.ExecuteNonQuery();
oleDbConnection1.Close();
}
catch(System.Exception caught)
{
MessageBox.Show(caught.Message);
}
}
And here is the Sql statementr:
UPDATE tables
SET Subtotal = ?
WHERE (TableNumber = ?)
What am I missing?? (I built the statement in VS query builder in the same project and verified it)
Thanx in Advance !
Jude 
|
|
|
|
|
It means that your objUpdateSubtotal object doesn't have a parameter in its collection, called "TableNumber". We can't tell you exactly what's wrong, because you haven't supplied the code that builds your objUpdateSubtotal object.
RageInTheMachine9532
"...a pungent, ghastly, stinky piece of cheese!" -- The Roaming Gnome
|
|
|
|
|
Ok, here is the statement that VS created when I dragged and dropped an OleDbCommand on the form:
private System.Data.OleDb.OleDbCommand objUpdateSubtotal;
I then changed the CommandText field to :
UPDATE tables SET Subtotal = ? WHERE (TableNumber = ?)
through the query builder.
Thank you!
|
|
|
|
|
TheJudeDude wrote: when I dragged and dropped an OleDbCommand on the form:
Mistake number one. It's always much better to write your own DB code than to rely on the designer to do it for you. You have MUCH greater control, easier to debug, and you won't end up making mistake number 2.
Mistake number 2. Just changing the SQL statement will not add the Parameter objects to the Parameters collection. OleDb doesn't use named parameters so you have to make sure that the parameter objects you add are done in the correct order as your SQL statement expects them.
You're missing something like this:
OleDbCommand cmd = new OleDbCommand("UPDATE tables SET Subtotal = ? WHERE (TableNumber = ?)", connectionString);
cmd.Parameters.Add("@Subtotal", OleDbType.Single, 4).Value = decSubtotal;
cmd.Parameters.Add("@TableNumber", OleDbType.Char, 10).Value = strTable;
This code is, of course, not guaranteed to work because there is so much more we don't know about your code, database field types and widths, ...
RageInTheMachine9532
"...a pungent, ghastly, stinky piece of cheese!" -- The Roaming Gnome
|
|
|
|
|
Gotcha...just received the book "Mastering C# Database Programming" by Jason Price.
Well, what I did was look at the Data-->Parameters(Collection)...and the TableNumber had the word "Original_" in front of it...
What should be my next investment in futhering my knowledge in C# and .Net?
Thanx
|
|
|
|
|
we are using an object database for our OLTP but for reporting we have got some issues about performance as the cpu becames a bottleneck.And we want to be able to run on low end computers...
One of our team members suggested to replicate the object database to a SQL table.But just a single one.The most denormalized thing ever.
is this the fastest way we can get in reporting?
*we dont want a harddisk,ram,cpu bottleneck.( run on cheap staff)
|
|
|
|
|
erdsah88 wrote: The most denormalized thing ever. is this the fastest way we can get in reporting?
Reporting databases tend to be denormalised because what is efficient for reporting is the complete opposite to what is efficient for OLTP.
ColinMackay.net
"Man who stand on hill with mouth open will wait long time for roast duck to drop in." -- Confucius
"If a man empties his purse into his head, no man can take it away from him, for an investment in knowledge pays the best interest." -- Joseph E. O'Donnell
|
|
|
|
|
Colin Angus Mackay wrote: Reporting databases tend to be denormalised because what is efficient for reporting is the complete opposite to what is efficient for OLTP.
thanks. I will give it a try.I am getting use to the gray aeras in my code.
|
|
|
|
|
I want to produce a table of counts, by month, for each part type I have in inventory. Here is my table defintion:
tbl_MainReport.ReportID - primary key
tbl_MainReport.Author - text
tbl_MainReport.POI_Time - purchased date
tbl_MainReport.PartType - 1..5 (1=wrench,2=hammer,3=driver,4=plier,5=knife)
tbl_MainReport.PartSubType - 1..8
tbl_MainReport.Description - memo
I have created a SQL statement that comes close, but groups by the "raw" date (MM/DD/YYYY HH:mm:ss) and not just by months:
TRANSFORM Count(*) AS TotalByPartType
SELECT tbl_MainReportInfo.POI_Time
FROM tbl_MainReportInfo
GROUP BY tbl_MainReportInfo.POI_Time
ORDER BY tbl_MainReportInfo.POI_Time
PIVOT tbl_MainReportInfo.PartType;
This creates a listing like this:
POI_Time | 1 | 2 | 3 | 4 | 5
m/d/yyyy h:mm:ss #
m/d/yyyy h:mm:ss #
m/d/yyyy h:mm:ss #
...
But I want something like this:
POI_Time | 1 | 2 | 3 | 4 | 5
JAN ## ##
FEB ## ##
...
Can you help? Should I just revert to coding up a "Recordset"?
Thanks,
Johnny
|
|
|
|
|
john john mackey wrote: Can you help? Should I just revert to coding up a "Recordset"?
No. Your SQL Statement is almost there.
You need to group by month - which you don't have. So you'll have to create it from the POI_Time which you do have.
Your GROUP BY clause needs to look something like this:
GROUP BY datepart(year, POI_Time), datepart(month, POI_Time)
ColinMackay.net
"Man who stand on hill with mouth open will wait long time for roast duck to drop in." -- Confucius
"If a man empties his purse into his head, no man can take it away from him, for an investment in knowledge pays the best interest." -- Joseph E. O'Donnell
|
|
|
|
|
Thanks for the clue, but I'm getting an error when I do:
TRANSFORM Count(*) AS TotalByPartType
SELECT tbl_MainReportInfo.POI_Time
FROM tbl_MainReportInfo
GROUP BY DatePart("m",tbl_MainReportInfo.POI_Time)
ORDER BY tbl_MainReportInfo.POI_Time
PIVOT tbl_MainReportInfo.PartType;
Error is: You tried to execute a qry that does NOT include the specified expression 'POI_Time' as part of an aggregate function.
I have also tried removing the ORDER BY and changing the SELECT to SELECT datepart("m",tbl_MainReportInfo.POI_Time)...
but have not been successful.
Johnny
|
|
|
|
|
If got a table named Horses with column Id and Horse_Name
and a table named Riders with column Id and Rider_Name.
Now i want a dataset using the third table named Stables and column IdHorses, IdRiders and Rider_NF.
How should i create a select statement selecting the Horse_Name and Rider_Name sorted on Rider_NF
Anny suggestions and code are welcom
|
|
|
|
|
Your SQL statement will look like this:
SELECT h.Horse_Name, r.Rider_Name, s.Rider_NF
FROM Stables AS s
INNER JOIN Horses AS h ON h.Id = s.IdHorses
INNER JOIN Riders AS r on r.Id = s.IdRiders
ORDER BY s.Rider_NF
ColinMackay.net
"Man who stand on hill with mouth open will wait long time for roast duck to drop in." -- Confucius
"If a man empties his purse into his head, no man can take it away from him, for an investment in knowledge pays the best interest." -- Joseph E. O'Donnell
|
|
|
|
|
It gives a error on Horses and Riders as a invalid object name.
I produced something like this and it seems to work:
SELECT *, Boekelo_Riders.Rider_Given_Name + ' ' + Boekelo_Riders.Rider_Family_Name
AS Rider_Full_Name
FROM Boekelo_Horses, Boekelo_Riders, Stables
WHERE Stables.IdHorse=Boekelo_Horses.Id
AND Stables.IdRider=Boekelo_Riders.Id
ORDER BY Stables.rider_NF, Boekelo_Horses.horse_name
The only thing i am missing is to add a extra column that contains a substitute for the rider_NF that is contained in a Country table.
In that table is the fullname of the riders national federation.
It may be that my solution contains a pitfall.
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
