|
I have this query that suppose to show the first name and last name of users (workers) in a milestone where the names of the users should not be repeated
For example if john smith is repeated two times it should be selected once in the display.
I have written this statement but it has an error.
SELECT Profile.FirstName, Profile.LastName
FROM AssignedTo INNER JOIN
Profile ON AssignedTo.Username = Profile.Username CROSS JOIN
Milestone
WHERE (Milestone.MilestoneID = @MilestoneID) AND (AssignedTo.ProjectID = @ProjectID)
GROUP BY Profile.Username
Note: we can distinguish between users by using Username
Thank you in advance fro your corporation
|
|
|
|
|
The problem there is the Group By clause.
A Group by is for aggregation (e.g. Averages, Sums, etc.) and your first name and last name aren't part of an aggregation.
Try adding a DISTINCT clause after your select. See if that helps.
SELECT DISTINCT
Profile.FirstName,
Profile.LastName
FROM
AssignedTo
INNER JOIN Profile ON AssignedTo.Username = Profile.Username
CROSS JOIN Milestone
WHERE
(Milestone.MilestoneID = @MilestoneID)
AND (AssignedTo.ProjectID = @ProjectID)
Dad always thought laughter was the best medicine, which I guess is why several of us died of tuberculosis.
I can picture in my mind a world without war, a world without hate. And I can picture us attacking that world, because they'd never expect it.
|
|
|
|
|
Just so you know the following is no different than the query you wrote. In fact, using a group by in some instances is faster than using a distinct (atleast I read that somewhere about Oracle, although it could have changed with a new release of the database).
SELECT Profile.FirstName, Profile.LastName
FROM AssignedTo
INNER JOIN Profile ON AssignedTo.Username = Profile.Username
CROSS JOIN Milestone
WHERE (Milestone.MilestoneID = @MilestoneID)
AND (AssignedTo.ProjectID = @ProjectID)
GROUP BY Profile.FirstName, Profile.LastName
Mike Lasseter
|
|
|
|
|
Ya, I'm not sure about Oracle but as far as I know with Microsoft SQL there's no performance difference as the execution plan is normally identical.
It's purely personal syntax preference. I've always found DISTINCT easier to read than a heap of groups.
Dad always thought laughter was the best medicine, which I guess is why several of us died of tuberculosis.
I can picture in my mind a world without war, a world without hate. And I can picture us attacking that world, because they'd never expect it.
|
|
|
|
|
Hi.
We have an issue where we have a (hummingbird doc management) database that has a collation that is different to the SQL server's (2005) collation. In this case the database's collation has to match the server's collation.
Changing the collation on the database will be a bit of a nightmare and I was looking changing the server's collation.
It is really necessary to rebuild[^] the master database?
Could I not just backup/detach all the databases, uninstall SQL, reinstall it with the right collation and then restore/re-attach the databases? What sort of things should I look out for if I do that?
Thanks,
Nick
Dad always thought laughter was the best medicine, which I guess is why several of us died of tuberculosis.
I can picture in my mind a world without war, a world without hate. And I can picture us attacking that world, because they'd never expect it.
modified on Thursday, May 15, 2008 3:37 AM
|
|
|
|
|
Dears,
I've a web application in which every this is working okay offline, but after piblishing the app online i had a problem saving arabic data from the application into the database which in my case sql server 05 it is saved as ???????????????????? 'question marks' columns collations is set to Arabic_CI_AS and my fields are all nvarchar or nchar and when i manually enter any arabic data into the database server online it is saved correctly and i can also read it correctly from my application
it would be gr8 if some one has got a solution for this.
Thanks in advance
Yours
|
|
|
|
|
Asked in three forums
Does it matter what I write here? Someone just has to say something about it.
|
|
|
|
|
Hi all,
i want to know how can i save a picture in Access database and,
see when i retrived the information from database.
i m using a String for this,and store the path of picutre when i browse the file for open and then store it in database.
when i want to retrived the information from database then i reterive this string and set picutre with this help.
Is it right way?
Thanks in advance.
IN A DAY, WHEN YOU DON'T COME ACROSS ANY PROBLEMS - YOU CAN BE SURE THAT YOU ARE TRAVELLING IN A WRONG PATH
|
|
|
|
|
|
Hi guys
I would like to query the following but sql does not allow this
<br />
CREATE PROCEDURE sp_GetMinPunchForShift <br />
@fromDateTime DATETIME,<br />
@toDateTime DATETIME<br />
AS<br />
SELECT <br />
MIN(Tap_DateAndTime_DT), <br />
Tap_IsInPunch<br />
FROM TimeAtendancePunches<br />
WHERE Tap_DateAndTime_DT >= @fromDateTime AND <br />
Tap_DateAndTime_DT <= @toDateTime AND <br />
Tap_IsInPunch IS NOT NULL<br />
<br />
GO<br />
What i would like to do is return only the field "Tap_IsInPunch" where i find the min(Tap_DateAndTime_DT) in the where clause.
Thanx in advance
|
|
|
|
|
what database are you using...Selecting data out of a stored procedure isnt allowed by some servers, i'm pretty sure oracle doesnt or at least didnt allow that...Sql Server 2000 does..you also need a group by clause, which the server should have told you...you may want to test the query in a query window by itself before you put in into a sp, doing so always seems to make my life easier when i'm writing stored procedures.
|
|
|
|
|
I'm using a MS SQL 2000 database. I would like to return a different field than the one i'm doing the MIN on.
|
|
|
|
|
You'll probably have to return two fields with the second in a group by clause, or you could use a sub query
SELECT Tap_IsInPunch
FROM TimeAtendancePunches
WHERE Tap_IsInPunch IS NOT NULL
AND Tap_DateAndTime_DT = (SELECT MIN(Tap_DateAndTime_DT)
FROM TimeAtendancePunches
WHERE Tap_DateAndTime_DT >= @fromDateTime
AND Tap_DateAndTime_DT <= @toDateTime)
this way would not be the fastest, but it should work
SELECT Tap_IsInPunch, MIN(Tap_DateAndTime_DT)
FROM TimeAtendancePunches
WHERE Tap_IsInPunch IS NOT NULL
AND Tap_DateAndTime_DT >= @fromDateTime
AND Tap_DateAndTime_DT <= @toDateTime
GROUP BY Tap_IsInPunch
should also work and run faster you'll just have to have your code ignore the second column.
Hope this helps
|
|
|
|
|
Try something like this
SELECT <br />
Tap_IsInPunch<br />
FROM TimeAtendancePunches tp1<br />
WHERE Tap_IsInPunch IS NOT NULL<br />
and Tap_DateAndTime_DT = (<br />
SELECT <br />
MIN(Tap_DateAndTime_DT)<br />
FROM TimeAtendancePunches tp2<br />
WHERE Tap_DateAndTime_DT >= @fromDateTime AND <br />
Tap_DateAndTime_DT <= @toDateTime AND <br />
Tap_IsInPunch IS NOT NULL<br />
and tp2.Tap_IsInPunch = tp1.Tap_IsInPunch )
Bob
Ashfield Consultants Ltd
|
|
|
|
|
Thank you Ashfield
That works perfectly. What you did there is called an alias correct? I have added a join to this query
to check a value in a different table. Thanks again Ashfield
|
|
|
|
|
You are welcome. Some would call it an alias, personally I would call it a correlated sub query.
BTW, just be warned if using SQL Server 2000, ther is a bug that means if you nest more than 4 deep with sub queries it doesn't work - and before I get a load of comments to the effect that you should never nest sub queries that deep, it was an experiment to see if you could 
Bob
Ashfield Consultants Ltd
|
|
|
|
|
Hi.
I'm creating backup files of my database using SMO. The problem is that I can only store those files on the pc running the SQL Server.
Is there a way to get SMO to store the backup file on the pc running my SMO application?
Here is my code:
<br />
Backup backup = new Backup();<br />
backup.Devices.AddDevice("C:\\temp\\myBackup.bak", DeviceType.File);<br />
backup.Database = "MyDatabase";<br />
backup.SqlBackup(server);<br />
Thank you for any help!
Life is not short... the problem is only how you organize yourself
|
|
|
|
|
You have to consider that running backup is not done by your PC, it is done by the server. So think like a server, you need to have your drive mapped to the server, when it looks at C: it is the SERVERS local drive.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
That's Ok... My question was if somebody knows enough SMO to solve what I need...
The user may work on different pc's... I have to give him the possibility to create a backup file and store it on the pc where he is working (CD, USB drive,...) ...
Same way I need to send a backup file to the server from whichever pc in order to execute the backup.
Is it possible? Or is there any other solution?
Life is not short... the problem is only how you organize yourself
|
|
|
|
|
AFAIK you are going to have to have a mapped drive from the server to the client, if the client changes you have a problem. Sorry, no solution from here.
Ahh
Have SQL create the backup to a shared server folder and then use a UNC path to copy to the local client using your UI. 
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
i am trying to add a out put parameter for parameter array like this
string SpName = "USP_SHIPPINGMETHOD";//store procedure name
OracleParameter[] param = {
new OracleParameter("STORESHIPPINGMETHODID",this.StoreShipId),
new OracleParameter("STOREID", this.StoreId),
new OracleParameter("PRICE",this.Price),
new OracleParameter("ONLINESHIPPINGMETHOD",this.OnLineShippingMethodID),
new OracleParameter("ACTIVE",this.Active),
new OracleParameter("PERCENT",this.percent),
new OracleParameter("SHIPPING_METHOD_NAME",this.ShippingMethodName),
new OracleParameter("METHOD_TYPE",this.MethodType),
new OracleParameter("CUSTOMCRITERIAID",this.CriteriaID),
new OracleParameter("QTY",this.Qty),
new OracleParameter("ADDITIONALCOST",this.AdditionalCost),
new OracleParameter("ADDITIONALQTY",this.AdditionalQty),
new OracleParameter("TEMPID",this.AdditionalQty).Direction=ParameterDirection.Output,
};
int r = OracleHelper.ExecuteNonQuery(connection, CommandType.StoredProcedure, SpName, param);
r = int.Parse(param["TEMPID"].Value.ToString());
Error Cannot implicitly convert type 'System.Data.ParameterDirection' to 'System.Data.OracleClient.OracleParameter' (For TempId);
thanks
sam 
Web Developer
|
|
|
|
|
Hi we have been running the DTS package for Direct Deposits, for a while now and suddenly is very slow and data is not updated on applications for users.We get data from ftp files to the tables.
Thank you
|
|
|
|
|
This is more of a comment than a question! Not a lot to go on is there? To help we need to know a bit about the dts package, the tables etc etc.
A guess? Try checking your error messages fromt the dts steps.
Bob
Ashfield Consultants Ltd
|
|
|
|
|
Hi,
I have a column that contains varchar values, the structure that the values are built is always the same, first six characters is a string prefix, anything after the prefix must be int or it is invalid.
What I want to do is subtring the column starting at character 7 for the rest of the value, but how do I validate if that value returned by the substring is int.
something like if value is int, is there a way that I can do this?
So lets say the column contains the following values:
abcdef123321
abcdef78998745
abcdef4568547
abcdef123548/
it should only return the first 3 rows because the fourth row is invalid because of the "/" at the end
Thanks in advance
No matter how long he who laughs last laughs, he who laughs first has a head start!
|
|
|
|
|
You can use the sql ISNUMERIC
declare @variable varchar(100)<br />
set @variable = 'abcdef78998745'<br />
select isnumeric(substring(@variable,7,datalength(@variable)-6))<br />
--returns 1<br />
<br />
set @variable = 'abcdef4568547'<br />
select isnumeric(substring(@variable,7,datalength(@variable)-6))<br />
--returns 1<br />
set @variable = 'abcdef123548/'<br />
select isnumeric(substring(@variable,7,datalength(@variable)-6))<br />
--returns 0 as not numeric
Hope this helps
Bob
Ashfield Consultants Ltd
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
