|
Hello!
I am trying to retirve data from almost five tables simultaneously, but having problems in use of a single query!!!
Here is the sample of thouse five tables
students: stuid, classID, sectionID, termID, sessionID, stuName, other fields
Classes: classID, className
Sections: sectionID, sectionName
Terms: termID, termName
Sessions: sessionID, sessionName
NOw if i want to extract data with a single query in somewhat this form
stuName, className, sectionName, termName, sessionName
Can someone help me in this, as to how the best query can be formed?
thank you!!
-- modified at 1:28 Monday 30th July, 2007
|
|
|
|
|
SELECT stuName = s.stuName,
className = c.className,
sectionName = sc.sectionName,
termName = t.termName,
sessionName = ss.sessionName
FROM students s
LEFT OUTER JOIN Classes c ON s.classID = c.classID
LEFT OUTER JOIN Sections sc ON s.sectionID = sc.sectionID
LEFT OUTER JOIN Terms t ON s.termID = t.termID
LEFT OUTER JOIN Sessions ss ON s.sessionID = ss.sessionID
Eliz.K
<awww.oin1.com< a="">
|
|
|
|
|
Thank You Eliz!
One thing more, also if i want to get the record of a single student, can i used the where clause after joins like
where s.stuid = '2222'
???
thanks!!!
|
|
|
|
|
|
I am calling a store procedure on sql server 2000 table as below::
return SqlHelper.ExecuteDataset(cn, "abc",54);
It is giving the following exception...
Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding error.
can anyone suggest me the solution plz..
hiiiiii
|
|
|
|
|
hope procedure execution is take more time.
set connectiontimeout property of connection object (cn)
Regards
KP
|
|
|
|
|
Thanks,
But it is not working. can you tell me the way to attach timeout with sqlhelper??
hiiiiii
|
|
|
|
|
Your problem is not with the connectiontimeout. It is the command timeout. Somewhere in your sqlhelper there should be a command timeout. The default is 30 seconds. You will need to increase it to something larger. You may also want to look at your sql statements and consider creating indexes to help with the speed.
Hope that helps.
Ben
|
|
|
|
|
Increase your timeout time and/or profile your stored procedure and see what is taking up time.
"Real programmers just throw a bunch of 1s and 0s at the computer to see what sticks" - Pete O'Hanlon
|
|
|
|
|
I want to use stored procedure in place of select statement for cursor.
e.g.
DECLARE ProfInd_CURSOR CURSOR for
dbo.EM_GET_WORKHOURS 88,1,'01/01/2007','08/08/2007'
OPEN ProfInd_CURSOR
..............................
any idea
Do good and have good.
|
|
|
|
|
Modest Bird wrote: I want to use stored procedure in place of select statement for cursor.
I don't think that you can do this. What you can do is create a temporary table in your current procedure and fill it from nested procedure. select from this temporary table in cursor
|
|
|
|
|
Hello Modest,
You can try to use User Definied Function for your cursor instead of stored procedure.
Like this:
DECLARE cur CURSOR FORWARD_ONLY READ_ONLY FOR
SELECT * FROM dbo.fn_GetTestData(1)
Please get the sample from --> http://www.oin1.com/Technical/SQL/FunctionInCursor.htm[^]
Eliz.K
|
|
|
|
|
You can't. You have to provide a SELECT statement, and you cannot directly SELECT from a stored procedure's results.
You can replace the stored procedure with a user-defined function - either an inline table-valued function, which simply contains a single SELECT statement, or a multistatement table-valued function.
Alternatively, the INSERT statement can insert the results of a stored procedure into a table (e.g. INSERT table (columns) EXECUTE stored_proc). You would need to create a temporary table or table variable to store the results, with the appropriate schema, insert into the temporary table/table variable, then declare the cursor to select from that.
|
|
|
|
|
what string function or any expression that returns the first letter of each word in uppercase, all other letters in lowercase in mssql2000?
thank you in advance!!!
jyn
|
|
|
|
|
Not sure if there is any built-in function from MSSQL. But you can write your own function -->
select UPPER(SUBSTRING(colName, 1, 1)) + LOWER(SUBSTRING(colName, 2, LEN(colName))) from tblTable
Eliz.K
www.oin1.com
|
|
|
|
|
this does only first letter of of the text to uppercase.
not first letter of all words in the text
there is no in built function for this. need to write a udf for this.
Regards
KP
|
|
|
|
|
Oh ya... sorry for my mistake.
To look through every word will affect the performace. I have a way which is not very clever, but should be workable.
Write a user definied function, where you replace ' a' to ' A', ' b' to ' B'.. and so on.
Sorry...
Eliz.K
www.oin1.com
|
|
|
|
|
Hi guys
I am trying to execute a dts package which has a text file as it's source (comma delimited with .txt extension) and then loads it into a table in sql server 2000. When I execute it, it fails on the first datarow it reads on the second field, the first field's value it reads.
THis is the error message:
"The number of failing rows exceeds the maximum specified.
Readfile transform 'DTSTransformation_2', source column 'MSISDN' (DBTyPE_STR), destination column 'PhoneNo'
(DBTYPE_STR): File '0842473397' not found."
funny thing is that '0842473397' is not a file, it's the second value in the first data row in the text source file.
Baffled, Plz Help.
Kiefie
Jack of all trades, master of some.
|
|
|
|
|
Nevermind I got it, I should've used 'copy column' and not 'read file' transformation.
Kiefie
The man with a plan.
|
|
|
|
|
Hi,
Can any one tell me how to connect with SQl using OleDb or Odbc.
Suppose the database name is TestData.mdf. Now what would be ths exact connection string?.
|
|
|
|
|
|
Dang, you beat me to it 
"Real programmers just throw a bunch of 1s and 0s at the computer to see what sticks" - Pete O'Hanlon
|
|
|
|
|
Hi,
When I run the following query I get results:
SELECT *
FROM tblMyFullTextSearchTable
WHERE FREETEXT(fldMyTitle, 'religion');
..but when I run the following query, I get no results:
SELECT *
FROM tblMyFullTextSearchTable
WHERE FREETEXT(fldMyTitle, 'the');
I know for a fact there are titles with the word the in it.
Please help.
Regards
ma se
|
|
|
|
|
Try CONTAINS.
SELECT *
FROM tblMyFullTextSearchTable
WHERE CONTAINS(fldMyTitle, 'the');
|
|
|
|
|
This doesn't work either.
SELECT *
FROM tblMyFullTextSearchTable
WHERE CONTAINS(fldMyTitle, ' "the" OR "and" ');
What I am trying to achieve is what ever words are passed through, I need to search on it. So if I were to pass through the word "a" or "an" or "the" I need it to bring back data that contains these words.
Regards
ma se
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
