|
if there no restrictions of this script,there must have some repeat results,like if the tableb have 3 rows,the tablec have 3 rows,there will be exists 9 rows,how to solve this? 
|
|
|
|
|
My previous post for this thread was only example how to do the insert from select statement. For more specified case then it must have other conditions on select statement and so. 
I Love T-SQL
"Don't torture yourself,let the life to do it for you."
If my post helps you kindly save my time by voting my post.
www.aktualiteti.com
|
|
|
|
|
I have created sql function and stored procedure to calculate work hours of an employee.
timein,timeout,workingHours are the fields.
eg:
TimeIn TimeOut WorkingHours
9:20:00 AM 11:59:59 PM 14:39:59
00:01:00 AM 3:56:40 AM 3:55:40
I calculated workHours using datediff.I have to calculate the sum of WorkingHours also.What can I do for this??Really I m confused..
|
|
|
|
|
Did you try SUM function to get sum of working hours.
example:
select sum(workinghours) from mytable
Or post your query and we will see how concretely to add too sum of working hours.
I Love T-SQL
"Don't torture yourself,let the life to do it for you."
If my post helps you kindly save my time by voting my post.
www.aktualiteti.com
|
|
|
|
|
Hi,
I have CLR procedure which sends request to server and get the url, I need to open that url into browser. I am trying to do it in the application code by Process.Start("IExplore.exe",url). When I debug the code it runs fine but when it deploy to SQL Server to call from CLR procedure it returns error "Request Failed".
Hope someone can help to solve this.
Thanks
|
|
|
|
|
I have two Tables LIKE Emp(EmpNo,EmpName,DeptNo),Dept(No,Name).
Emp Table looks like:
1 xxx 1,2
2 yyy 2,3
3 zzz 1
Dept Table:
1 aaaa
2 bbbb
3 cccc
My Output will be:
1 xxx aaaa,bbbb
2 yyy bbbb,cccc
3 zzz aaaa
How can I do this in Sqlserver2005
|
|
|
|
|
Your database model is faulty IMAO.
How about:
Emp
EmpID Empname
1 xxx
2 yyy
3 ZZZ
Dept
DeptID Deptname
1 aaaa
2 bbbb
3 cccc
Emp_Dept
EmpID DeptID
1 1
1 2
2 2
2 3
3 1
then you can use
SELECT EmpName,DeptName
FROM Emp e
Join EmpDept ed
ON e.EmpID = ed.EmpID
JOIN Dept d
ON ed.Deptid = d.DeptID
And if you really need to get the deptnames as commaseparated values you can have a look in this thread[^] how to do that.
"When did ignorance become a point of view" - Dilbert
|
|
|
|
|
Ignore this response - it was wrong. Monday morning syndrome.
Do you work in 2 departments? I admit that some corporate structures work in this way, but for the purpose of the OP's question it was unecessary to add a many-to-many relationship between employees and departments. His original structure of 1 department hads many employees, ie a one-to-many relationship (or to put this another way each employee works in just 1 department) would have worked just fine, with just one join in the query.
modified on Monday, August 9, 2010 7:55 AM
|
|
|
|
|
No, if you look at the example data in the OP, it is a many-to-many relationship. Employee xxx works in two departments, aaaa and bbbb. Department aaaa has two employees, xxx and zzz.
|
|
|
|
|
My bad, I interpreted as the nameof the first employee to be "xxx 1" - I now see the deptNo field is a comma-separated list 
|
|
|
|
|
J4amieC wrote: Monday morning syndrome.
I know everything about that, except it usually happens to me any day of the week
"When did ignorance become a point of view" - Dilbert
|
|
|
|
|
Ignore this answer. I mis-interpreted your source data.
The above answer, although correct confuses your question somewhat.
All you need is to join employee table to department table using the foreign/primary key between employee and department.
SELECT EmpNo, EmpName, DeptName
FROM Emp
INNER JOIN Dept
ON Emp.DeptNo=Dept.No
modified on Monday, August 9, 2010 7:54 AM
|
|
|
|
|
TRY THIS:
SELECT Emp.EmployeeID, Emp.FirstName,
STUFF((SELECT ',' + DeptName FROM Dept WHERE Dept.DeptID = Emp.DeptID FOR XML PATH('')),1,1,'') AS DeptNames
FROM Employees Emp
|
|
|
|
|
Ｈｉ Ｆｒｉｅｎｄｓ，
Ｉ ｉｎｓｔａｌｌｅｄ Ｏｒａｃｌｅ １１ｇ ｉｎ ｍｙ ＸＰ ６４ｂｉｔ ＯＳ， ｎｏｗ Ｉ ｔｒｙ ｔｏ ｒｕｎ ｓｏｍｅ ｓｃｒｉｐｔｓ ｉｎ ＳＱＬＰＬＵＳ． Ｈｏｗ ｃａｎ Ｉ ｃｈｅｃｋ ｔｈｅ ｃｕｒｒｅｎｔ ｄｅｆａｕｌｔ ｐａｔｈ ｕｎｄｅｒ ＳＱＬＰＬＵＳ？ Ｎａｍｅｌｙ， ｗｈｅｒｅ ｓｈｏｕｌｄ Ｉ store these scripts? Ｆｒｏｍ ｔｈｅ ｉｎｓｔａｌｌａｔｉｏｎ ｓｕｍｍａｒｙ， Ｉ ｋｎｏｗ ｗｈｅｒｅ ｔｈｅ ＯＲＡＣＬＥ＿ＨＯＭＥ ａｎｄ ＯＲＡＣＬＥ＿ＢＡＳＥ ａｒｅ， ｄｏ Ｉ ｈａｖｅ ｔｏ ｓｅｔ ｅｎｖｉｒｏｎｍｅｎｔ ｖａｒｉａｂｌｅｓ？ Ｃｏｎｆｕｓｉｎｇ．．．．Ｈｏｐｅ ｓｏｍｅｏｎｅ ｃａｎ ｇｉｖｅ ｍｅ ａ ｓｕｇｇｅｓｔｉｏｎ．
Ｔｈａｎｋｓ
|
|
|
|
|
Your post has so much spaces, it is very hard to read. Fix it so, people may look at it
|
|
|
|
|
The default SQLPlus directory is %ORACLE_HOME%\dbs
The SQLPATH environment variable will allow you to specify the default search path for scripts.
Personally I don't use SQLPlus, I find it a PITA.
"When did ignorance become a point of view" - Dilbert
|
|
|
|
|
I think on Windows it is not necessary to set Oracle's environment variables manually (different from Linux). Scripts can be stored quite anywhere, as you can give the path e.g.
START e:\temp\myscript.sql
But Oracle has often some problems when the path contains spaces (perhaps also with national characters or when the path is "long"), so just avoid that - Oracle is still an 1980ies legacy software.
|
|
|
|
|
thanks so much Guys...^_^
|
|
|
|
|
Dear guru's,
I'm a beginner with "database-programming". My standard task is to read some xml-file in a dataset, then I "hack around" with some relations and display the result in any tabular form (e.g. DataGridView). This is a lot of programming for each different view which I want to have on my data.
But I feel, this is stupid and there are much more effective ways to manage this. My question: How are such tasks (different views on data, without a lot of programming) done?
Any tip for a beginner?
Thanks in advance
modified on Thursday, August 5, 2010 4:22 PM
|
|
|
|
|
Can you elaborate on what you consider different views of the data ?
Do you mean that the same data is filtered, sorted, grouped, but basically the same ?
You can add functionality to the Gridview to do sorting.
A little bit more detail could get you better suggestions.
|
|
|
|
|
Yes, I mean what you say: Some grouping, sorting, summing up of columns,....
Best regards
|
|
|
|
|
I am trying to get the questionID from two different Select statements and
then insert into a table one by one.
DECLARE @NewID INT
This one is inserting a new surveyID, Output is SurveyID
insert into survey(title,description, surveystatus,CreatedBy,date )
values('New Survey','New Survey',1,'test',Getdate())
SELECT @NewID = SCOPE_IDENTITY()
Copying the questions with the new surveyID
INSERT SurveyQuestions(surveyid, questions,answertype)
SELECT @NewID ,questions,answertype
FROM SurveyQuestions
WHERE surveyid='81'
The problem is below here. I want to get the value of the questionId of the
first select statement then the value of the questionID of the second select
statement and insert into the table SurveyChoices one by one.
Both the select statements can have 1 or 2 or 3 rows or more but the both the
select statements will have exact number of rows.
select QuestionId from surveyquestions where surveyid=@NewID and answertype
<> 'T'
select QuestionId from surveyquestions where surveyid='81' and answertype <>
'T'
Here i am using the insert statement using the value form First select
statement and second select statement
INSERT Surveychoices(QuestionId,choice)
SELECT questionID,choice((This is the value of the First select statement.)
FROM Surveychoices
WHERE questionid=questionID(This is the value of the second select statement.)
|
|
|
|
|
Hi,
HI,
SELECT @vCount=('select COUNT(['+@v_COLUMN_NAME+']) from '+@DataTable)
print @vCount
This is my stored procedure part to find the count of a column specified.But here i m getting a conversion error
vCount is declared as int
seeism
|
|
|
|
|
Got the answer
create table #countlists (ICount int)
exec ('insert into #countlists(ICount) select COUNT(['+@v_COLUMN_NAME+']) from '+@DataTable+' where '+@v_COLUMN_NAME+' is not null')
set @vCount=(select ICount from #countlists)
drop table #countlists
|
|
|
|
|
How can I use transaction on multiple database? I use SQL Server Compact Edition.
Thanks...
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
