Nested Tables...with StoredProcs
Apr 2, 2008
I've got a dataset bound to a Table in my report. Running the report takes a good 15-20 seconds as there's a metric ton of data coming back...that part works great as is.
Now I need to be able to Nest some additional Detail Data inside the main row group footer for each row (or not, depending if a field != null). I pass the subreport a value from the row, and it displays the returned data.
Normally if I was doing this from scratch I'd just include the data in the stored proc results, however I'm not doing it from scratch and I have no idea how to get this done so it's not taking 5 minutes to show the report
The "detail" stored proc might not even return data, it only really ever has 30-100 records. It seems almost a shame to keep requerying to check for results for all XThousand rows. Is there a way to maybe run it once and just keep re-filtering the data into the column I need?
ANY info would be great
Thanks
Steve
View 1 Replies
ADVERTISEMENT
Sep 14, 2007
I have a decision tree mining model that has two nested tables and that amount of inputs processes in under a minute. When I add a third nested table in what I think is exactly the same way (I've tried two different ones), it never returns from counting the cases. Is there a limit on the number of nested tables one can have in a DT model? It does process the rest of the objects and measure groups but can never seem to return from counting cases, perpetually showing "Counting Cases 0".
I should probably add that the only way to stop it from processing after it hangs for 5 mins or 5 hours is to stop and start the service. After I remove a nested table and replace it with one of the new ones, it flies right through again. Something seems magical about the third nested table.
Thanks!
Brian
View 3 Replies
View Related
Feb 9, 2004
Hi,
I am trying to revamp our product database with a view to making it search-optmised and would like some guidance (or confirmation of method, if you will!!). We currently use a three table structure (Product, Brand, Cat(egory)) along the lines of :
create table product
(prod_id int not null,
brand_id int not null,
cat_id int not null,
other stuff e.g. tech. specs, displayed text on web page, etc....
)
with corresponding brand_id and cat_id in the other tables. While this seems relationally sound I see it as being inefficient for searching, particularly after reading the theory behind nested sets.
A new function I am building will enable users to drill down through the product list or runs searches against all or part of the db :
e.g. all products from one category,
all products fitting certain search criteria,
products from several selected brands fitting certain criteria,
and any combination of the above you can think of!
The problem is, not all products have the same criteria list (in fact I would be surprised if any did) and may also be of more than one category (a digital camera with movie mode might easily fit into the digital camcorder search). I think I am correct in that a nested set would make the structure fit the requirement - things like criteria, displayable text, etc. could be nodes in their own right and each logical level might have its own criteria. For example, if a category is selected then certain text must be displayed and could list further categories or products. The next level down would then require its own displayable text - I am mainly thinking about SEO tags here. Also, I am not precious about retaining the current table structure and would like an open ended solution where I can add further data/functionality in a dynamic fashion, which nested sets seem to embody.
Does this make sense to anybody coz I think I've confused myself even more!!
Thanks in advance,
G
View 14 Replies
View Related
Dec 15, 2014
I need to perform some operations that seem to require multiple nested tables.
Is the following code the way I should be attempting this or is there a better way?
Here is the sample code:
SELECT tt.tdl_ID, tt.tx_ID, adj.Prof_Chgs, rc.Rvu_Comp
FROM Table1 as tt
LEFT JOIN (
SELECT tt1.Tdl_Id,
CASE
WHEN tt1.tdl_ID = '1000'
[code]....
View 8 Replies
View Related
Mar 3, 2008
I'm just wodnering if you have two select statements, one nested inside another, can you reference tables from the outer loop?
for example, say I would like to find all employees who have at least two clients and employees and clients have a one to many relationship.
select *
from Employee e
where
(
select count(*)
from Clients c
where c.EmployeeId = e.EmployeeId
) >= 2
This obviously doesn't work - but how would i go about doing something like this?
View 10 Replies
View Related
Aug 22, 2006
hi! I've been using sql server for a while but until recently have kept things pretty simple. now I'm trying to expand my horizons by trying to tackle some more complex applications, and one I'm really struggling with is nested relations. I hope this is in the right forum; if it is not, please feel free to move it, thank you!
here is my problem: I'm desigining a simple "Downloads" page in asp.net, and I have two tables set up. One is Downloads and the other is DownloadCategories. here is a simplified layout of the tables:
Downloads:
ID PK
Title
Description
CatID FK
DownloadCategories
ID PK
Name
Description
ParentID FK
basically CatID in downloads is a foreign key to the ID in downloadcategories, and ParentID is a foreign key to the ID in the same table, downloadcategories. This is set up because I want to support an infinite number of categories, each being able to support their own subcategories, which can go deeper into more subcats, and so on...
the problem is that I can't seem to get them to fill into the dataset I've created in vs 2005's designer. I have a procedure SelectAll which retrieves all rows, and SelectMain which retrieves only the topmost categories (where ParentID=Null). If I fill the datatable with SelectMain, I don't get any of the child categories, and if I call SelectAll, I get just a single table with all the rows, but no relations.
I have defined a relation in the datatable that mirrors that of the database, but no matter what I try, I cannot get it to show the relationship in the datatable when I fill it. am I doing something wrong? this is kind of how I have it setup:
dataset with Downloads and Categories datatables, relations from Categories to Downloads, and from Categories to Categories. I have the two queries added to the table adapter, and I call the SelectAll query to fill it, but all it does is fill the table with rows; it doesn't create any relations.
I hope this explanation of my problem makes sense. as I said I'm still very new to this complex stuff, and I'm hoping to get my head around it soon, because I really need the functionality (not to mention the skills!) so if you can take a moment to go over what I've explained and point out where my flaw might be, I would really appreciate it!!
if you need any more information to help, please let me know and i'll get right back to you. thanks a bunch!
-SelArom
View 3 Replies
View Related
Dec 24, 2007
How does cross-validation work in the case of models with predictable nested tables? Is it supported? For classification and regression with a flat structure, during the testing phase (that is, validation phase) of cross-validation I can think of the inputs being presented and comparing the predicted value with the real value. But in the case of nested tables, the input is not a subset of the attributes (a subset of the input vector), but whole input vectors. (For instance, complete itemsets in the case of association rules). Can you please explain some more how the validation phase works in the case of the association rules and decision trees with predictable nested tables?
thanks,
Gustavo Frederico
View 3 Replies
View Related
Mar 2, 1999
I have an sql file that contains several queries that are generating numbers to populate a sql table. The sql file is too large for a single sp so I am nesting them. I have 4 nested stored procedures. Each of the queries in each stored procedure dumps into its own global temp table. The final stored procedure needs to insert into a sql table all the information gathered in the global temp tables. So the final stored proc. looks something like:
"Create procedure usp_myProc_4 AS EXEC usp_myProc_3
INSERT INTO mySQLTable (a,b,c)
SELECT a, b, c FROM ##myTempTable (which was created in usp_myProc_1)
INSERT INTO mySQLTable (a,b,c)
SELECT a, b, c FROM ##myOtherTempTable
INSERT INTO......etc;"
I have done this befor and it worked fine. The only difference is that when I did this before these insert statements were being called from within an sp_makewebtask procedure.
Now when I try to save this final stored procedure it tells me "Invalid Object Name: ##myTempTable"
How do I call on these global temp tables from my final nested stored procedure?
Thanks for any help.
View 1 Replies
View Related
Oct 20, 2006
Hi,
I have a problem in design the tables. My main task is to learn how to give the Match Score.
I have hundreds of dataset and one of them is like this:
Test Record Number: 19
Prospect ID = 254040088233400441105260031881009
Match Score = 95
Input Record Fielding ( eg wordnumber[Field] ) : 1[1] 2[1] 3[11] 4[11] 5[11]
Prospect Word = 1 type = 1 match level = 4 input word = 1 input type = 1
Prospect Word = 2 type = 2 match level = 0 input word = NA input type = NA
Prospect Word = 3 type = 3 match level = 4 input word = 2 input type = 1
Prospect Word = 4 type = 11 match level = 4 input word = 3 input type = 11
Prospect Word = 5 type = 13 match level = 4 input word = 4 input type = 11
Prospect Word = 6 type = 14 match level = 4 input word = 5 input type = 11
Now I have all my data stored in the DB and I seperated them into 3 tables and their structures are:
1) prospect (id, testrecordnumber, prospectID, matchscore)
2) inputfieldind (id, prspid, inputword, inputfield)
3) prospectinfo (id, prspid, prospectword, prospecttype, matchlevel, inputword, inputtype)
and the prspid in table 2 & 3 refers to the prospectID in table 1.What I did was setting:
a) prospect table as case table with id AS key, prospectID AS input & predictable;
b) and the other two as nested tables with inputword/inputfield AS key & input, prospectword/prospecttype/matchlevel/inputword/inputtype AS key & input .
But it shows error for having multiply key columns...
And also I am thinking about using the Naive bayes algorithm. Can I also have some suggestion on this?
Thanks
View 3 Replies
View Related
Nov 23, 2007
Hi ...I can't figure out how to put nested tables into the Data Mining Model Training Transform (SSIS). Can anybody help me? some example please...!!!??
Diego B.
View 3 Replies
View Related
Aug 30, 2006
hi
i m trying to build microsoft association model using Microsoft association algorithm. i got
1) patient table(patientid, name, city)
2) diseases(diseaseid, dieseasename)
It is M:N [many to many] relationship between above tables, so
3)Patient_diseases(patientid,disease_id). [RELATIONSHIP TABLE]
i am trying to associate city in patient table --> disease in diseases table. I want to build association data mining model and use it on web form, such a way when the user enters city associated disease will be displayed.
should i select all 3 table to build the model? could help me to decide what tables should i select as Case and what tables as Nested? what attributes from the table should i select as key, input, predictive ?
i am using data mining tutorials on sqlserverdatamining.com to build this model. is there anything further during my model building i get into confusion? please suggest me where i can find complete resource or inform here.
i appreciate Mr.Jamie for his guidance so far in my academic project. i do have the book 'Data mining with sql server 2005'. I left with just one day to do this and document.
hoping someone could suggest. your help is much appreciated.
regards
raju
View 4 Replies
View Related
Jun 5, 2015
I have a requirement of creating nested tables in SQL server. how to create them. Just to give a background I am trying to move the RDBMS from oracle to SQL server.
Structure of tables is as follows. I have table 'Employees' with address as one of the column. I have one more table with columns Street, Town, Dist, State. When I query the table 'Employees' I should see the attribute name and values of all the columns in address table in address column.
Employees: with columns: ID, FirstName, LastName, dept, gender, dob, address
Address (Nested table): with columns : Street, Town, Dist, State
This was done in oracle using Nested tables and user defined data types. what is alternative for this in SQL server. How can I achive this requirement in SQL server.
View 2 Replies
View Related
Oct 17, 2007
Hello,
I have this INNER JOIN that is fine to show all possible combinations. But I need to show only rows that have one or more Null values in tbIntersect.
Should I use nested LEFT JOINT? How?
This is the SQL statement:
sSQL = "SELECT DISTINCT tbCar100.Car100_ID, tbCar100.Description100 AS [Caractéristique 100], " & _
"tbCar200.Car200_ID, tbCar200.Description200 AS [Caractéristique 200], " & _
"tbCar300.Car300_ID, tbCar300.Description300 AS [Caractéristique 300], " & _
"tbCar400.Car400_ID, tbCar400.Description400 AS [Caractéristique 400], " & _
"tbCar500.Car500_ID, tbCar500.Description500 AS [Caractéristique 500], " & _
"tbCar600.Car600_ID, tbCar600.Description600 AS [Caractéristique 600], " & _
"tbCar700.Car700_ID, tbCar700.Description700 AS [Caractéristique 700], " & _
"tbProducts.Prod_ID, tbProducts.PartNumber AS [Part Number] , tbProducts.Description AS [Description] , tbProducts.DateAdded AS [Date] " & _
"FROM tbProducts INNER JOIN (tbCar700 INNER JOIN (tbCar600 INNER JOIN (tbCar500 INNER JOIN (tbCar400 INNER JOIN (tbCar300 INNER JOIN (tbCar100 INNER JOIN " & _
"(tbCar200 INNER JOIN tbIntersect ON tbCar200.Car200_ID = tbIntersect.Car200_ID) " & _
"ON tbCar100.Car100_ID = tbIntersect.Car100_ID) ON tbCar300.Car300_ID = tbIntersect.Car300_ID) ON tbCar400.Car400_ID = tbIntersect.Car400_ID) ON tbCar500.Car500_ID = tbIntersect.Car500_ID) ON tbCar600.Car600_ID = tbIntersect.Car600_ID) ON tbCar700.Car700_ID = tbIntersect.Car700_ID) ON tbProducts.Prod_ID = tbIntersect.Prod_ID " & _
";"
Here is the content of the tbIntersect table:
Car100_ID Car200_ID Car300_ID Car400_ID Car500_ID Car600_ID Car700_ID Prod_ID ID
1 1 1 1 1 1 1 1 1
1 2 1 1 1 1 1 19
1 3 1 1 1 1 1 20
I need to return the rows that have null data, ex: second row because Prod_ID is NULL and third row because Car300_ID is NULL. In fact I need the data from the other joint tables that correspond to these ID fields.
Thanks
View 5 Replies
View Related
Oct 27, 2006
In the Mining Accuracy Chart, the predictable columns of nested tables does not show up in the "Select predictable mining model columns to show in the lift chart" table. The "Predictable column name" is empty.
Predictable columns in the case table shows up, but not the predictable columns in the nested table. What am I missing?
-Young K
View 1 Replies
View Related
Dec 11, 2006
Hello , i have 2 seperate tables of information about people.
Table A :with a key column Anumber contains mobile telephone numbers and
table B : with a key column named Bnumber contains mobile telephone numbers
These two key columns have the same data type and hold the same information (mobile phone numbers).
Some mobile numbers from table A exist in table B so i wanted to run a clustering algorithm in order to gain information from the two tables.
I created a new table C with all the distinct MobileNumbers found in the tables A and B ,set the Cnumber column as key columns and linked it with the equivalent columns Anumber , Bnumbers from tables A and B.
A--->C <---B
http://i115.photobucket.com/albums/n310/Slavetodark/Sql/2.jpg
http://i115.photobucket.com/albums/n310/Slavetodark/Sql/1-1.jpg
Although, when i desing a training model in the Business intelligent Studio ( New mining structure) and set table C as case table and A and B as nested tables in the "DataMiningWizard>Specify the columns used in your analysis" window the key columns from table B and C DO NOT appear at all
So if i click next i get a warning (You have not defined a key column for the nested tables).
I proceed, put the key columns manually from the mining structure tab (drag and drop the key columns from the data sourve view) but when i run the clustering algorithm the results doesnt at all make sence as you can see at
http://i115.photobucket.com/albums/n310/Slavetodark/Sql/4.jpg
http://i115.photobucket.com/albums/n310/Slavetodark/Sql/3.jpg
Do you have any suggestions about what might got wrong? Is it a bug or something i did?
Thnx for your time and sorry for the huge post!!
View 1 Replies
View Related
May 26, 2007
Hello,
Quick question, I hope, I am trying to create a table that has a column that is a nested table in SQL Server 2005 Express Edition. Any ideas how I could go about doing this?
Sincerely,
James Simpson
Straightway Technologies Inc.
View 4 Replies
View Related
Oct 26, 2006
I can't figure out how to put nested tables into the Data Mining Model Training Transform (SSIS). I can do a simple case table, but how do you get those nested tables with DM Training Transformation? Any ideas? Samples?
Thanks in advance,
-Young K
View 3 Replies
View Related
May 27, 2007
Lets take the following example:
Movie train table:
ID Class
1 +
2 +
3 -
4 +
5 -
Actor train nested table:
ID MovieID Gender
1 1 F
2 1 M
3 1 F
4 1 F
5 2 M
6 2 M
7 2 F
8 3 F
9 3 F
10 4 M
11 4 M
12 4 F
13 4 F
14 5 F
15 5 M
We want to build a classifier model in order to predict the Class of a Movie based on the Gender of movie's actors. To deal with the nested table Analysis Services maps each record of the nested table to an attribute of the case table. These attributes are named Actor(n).Gender with n = 1..15, and so they are dependent on the nested table record numbers. Both Microsoft Decision Trees and Microsoft Naive Bayes algorihms use these attributes without any modification.
We are implementing a Relational Naive Bayes algorithm and we are planning to aggregate such attributes in order to make them independent of the nested table record numbers.
Next step we tried to predict some unseen cases and here we face with
a very huge problem.
Lets take more two tables of unseen cases:
Movie test table:
ID Class
6 +
7 NULL
8 NULL
Actor test nested table:
ID MovieID Gender
1 6 F
2 6 M
3 6 F
4 6 F
16 7 F
17 7 M
18 7 F
19 7 F
20 7 F
21 8 M
22 8 M
23 8 F
Predicting the movie 6 Class is not a problem since the movie actors were included in the training dataset and when the records are mapped to attributes because they already exist in the model. But when you
try to predict movies (7 an 8) with unseen actors all new attributes are simply ignored in the ALGORITHM:redict call (in_ulCaseValues is zero!) because they do not exist in the model!
What is the solution?
View 3 Replies
View Related
Jan 24, 2008
I'm new to my company, although not new to SQL 2005 and I found something interesting. I don't have an ERD yet, and so I was asking a co-worker what table some data was in, they told me a table that is NOT in SQL Server 2005's list of tables, views or synonyms.
I thought that was strange, and so I searched over and over again and still I couldn't find it. Then I did a select statement the table that Access thinks exists and SQL Server does not show and to my shock, the select statement pulled in data!
So how did this happen? How can I find the object in SSMS folder listing of tables/views or whatever and what am I overlooking?
Thanks,
Keith
View 4 Replies
View Related
Jan 3, 2007
Can you give a whole SQL statement an alias so you can use it later?
Eg.
SELECT * FROM Employees WHERE age < 19
-- Could I call the above statement something like 'statement1' to use below as shown
SELECT * FROM Employees WHERE age < 25 AND NOT IN (statement1)
Soin effect I get a nested statement.
The reason I am asking about aliases is because this would need to be repeated for, E.g. age < 30 Then age < 35 and so on and so forth.
So basically, I just want to alias a qhole SQL statement
Any help would be greatly appreciated - George
View 14 Replies
View Related
Jul 20, 2005
Hi all,I have a query that looks like so:SELECT GLDCT AS [Doc Type], GLDOC AS DocNumber, GLALID ASPerson_NameFROM F0911WHERE (GLAID = '00181913')However by stipulating that GLAID = GLAID I cannot get the person_nameas not all the GLALID fields are filled in. from my reading of thehelpdesk I have a felling that a nested query might be the way to goor a self-join but beyond this I am lost!?Many thanks for any pointers in advance.Sam
View 3 Replies
View Related
Oct 15, 2007
I am trying to do some nested IF ELSE conditions. I get an error saying 'Error near work Begin'. Below is teh query and the variables comes in thru cursor.
Can somebody advise me on this and also let me know the best practices and alternative to this if any.
IF (@CCTable = 'Claiminassoc')
BEGIN
IF ( @ClaimCenterField = 'ClaimID' AND @VALUE ='Claim')
BEGIN
UPDATE dbo.Table SET ColName = 'Y'
WHERE ID = @ID AND CCTable = 'Claiminassoc' AND CCField = 'ClaimID'
AND DWField = 'CatastropheDesignationFlag'
END
END
ELSE IF (@CCTable = 'EmploymentData')
BEGIN
IF (@VALUE ='TRUE')
BEGIN
UPDATE dbo.Table SET ColName = 'Y'
WHERE ID = @ID AND CCTable = 'Claim' AND CCField = 'WagePaymentCont'
END
ELSE IF (@VALUE ='FALSE')
BEGIN
UPDATE dbo.Table SET ColName = 'N'
WHERE ID = @ID AND CCTable = 'Claim' AND CCField = 'WagePaymentCont'
END
END
View 4 Replies
View Related
Apr 2, 2008
Hi gang,
I have a challenge, which seems like it is probably trivial, but SQL chops are not up to the task, and I am hoping one of you hot-shot DBAs can throw me a bone!
I have a query that populates an OLAP Time dimension table (basically one row per day of the year over several years). What I want to do is expand that table to include each hour of each day over the time span.
The CTE I am using for the day population is:
Code Snippet
WITH dates(date)
-- A recursive CTE that produce all dates between 2006 and 2057-12-31
AS
(
SELECT cast('2006' AS dateTime) date -- SQL Server supports the ISO 8601 format so this is an unambigious shortcut for 1999-01-01
UNION ALL -- http://msdn2.microsoft.com/en-us/library/ms190977.aspx
SELECT (date + 1) AS date
FROM dates
WHERE
date < cast('2058' AS dateTime) -1
)
What I wanted to do was something like:
Code Snippet
WITH hours(hr)
AS
(
SELECT (DATEPART(hh,date) hr
UNION ALL
SELECT (hr + 1) AS hr
FROM hours
WHERE hr < 24
)
inserted just after
Code Snippet
FROM dates
in the initial CTE. But from what I have read, it seems as though nested CTEs are not allowed.
Can anyone show me how to do this?
Thanks in advance for ANY insight/input!
Cheers,
Chris
View 5 Replies
View Related
Feb 14, 2008
Greetings -
Can someone pls advise the maximum number of nested IIF statements allowed in a VS 2005 report builder layout textbox expression? I seem to be hitting a wall at about 10, but cannot find verification. In case the limitation is by characters, the full expression would run about 3,500. Any other limitations which may have a bearing?
Tks & B/R
View 1 Replies
View Related
Feb 2, 2008
Hi all! I have a problem with my stored procedure, What I'm trying to do here is whenever a user tries to register, the stored procedure will check if the username already exists, and if not it'll now check if the email has already been entered into the database then if not the stored procedure will go ahead and insert the values into the database. *If the username already exists it'll return -1, and if the email already exists then return -2.
SELECT Username FROM UserAccount WHERE Username = @UsernameIF @@ROWCOUNT = 0 SELECT Email FROM UserAccount WHERE Email = @Email
IF @@ROWCOUNT = 0 BEGIN
INSERT INTO UserAccount (Username, Password, Email, FirstName, LastName, Gender, BirthDate, Country, State, Zip, AdditionalInfo)
VALUES (@Username, @Password, @Email, @FirstName, @LastName, @Gender, @BirthDate, @Country, @State, @Zip, @AdditionalInfo)
END
ELSE BEGIN
RETURN -2
END
ELSE BEGIN
RETURN -1
END
END
Thanks!
View 3 Replies
View Related
Nov 8, 2006
Hi, I'm not sure if this is a good place to ask sql questions, so please bear with me here...I have a table like soid, parentidWhat I'm trying to do is to write a self join where given a random ID, it'll give me the whole tree of its decendents. (I don't need its parent)so say I have data liek soid parentid1 null2 13 2when I specificy 1, it'll give me 2 and 3, even though 3 is indirectly related to 1. when I say 2, it'll just give me 3. Thanks a lot. GREATLY appreicate it.
View 1 Replies
View Related
Oct 16, 2007
When running reports from data, is it faster using nested views approx 4 levels deep, or writing data to a temp tables then running the report?
View 3 Replies
View Related
Apr 7, 2008
What's worng, please help? SELECT TTarea,personel,Date FROM person_table WHERE TTarea = (SELECT TTarea FROM TTarea_table WHERE Center='CENTER_office') I have many TTarea and I want to send back from inner SELECT statement but give an error that inner select statement don't return many result.I want to return many result and I display many TTarea in the CENTER_office
View 2 Replies
View Related
Nov 12, 2003
Hi All,
Im inserting some data into a table and grabbing the new UserID with this statement
SELECT @UserID = @@IDENTITY
I would like to use the @UserID to Execute another SP within the same proc..
..something like this
Exec AnotherSP(@UserId)
But this doesnt seem to be working....Its seems to me that this is a much better approach performance wise rather than returning the UserID to the Business Logic Layer and calling another SP....Im I correct in that assumption....any assistance would be greatly appreciated.
Thanks in advance
AMRITZ
View 2 Replies
View Related
Jun 12, 2004
I think I am getting an endless loop here... anyone know how to fix it?
***********************
CREATE PROCEDURE TrigSendPreNewIMAlertP2
@REID int
AS
Declare @RRID int
Declare @ITID int
Declare @FS2 int
Declare @FS1 int
Declare crReqRec cursor for
select RRID from RequestRecords where REID = @REID and RRSTatus = 'IA' and APID is not null
open crReqRec
fetch next from crReqRec
into
@RRID
Declare crImpGrp cursor for
select ITID from RequestRecords where RRID = @RRID
open crImpGrp
fetch next from crImgGrp
into
@ITID
while @@fetch_status = 0
select @FS1 = @@Fetch_Status
EXEC TrigSendNewIMAlertP2 @ITID
FETCH NEXT FROM crImpGrp
into
@ITID
close crImpGrp
deallocate crImpGrp
while @@Fetch_Status = 0
select @FS2 = @@Fetch_Status
FETCH NEXT FROM crReqRec
into
@RRID
close crReqRec
deallocate crReqRec
GO
View 1 Replies
View Related
Apr 30, 2005
Hi,
Although I am quite familiar with MS Access-grade SQL, I am struggling a bit with proper grown up SQL Server. My usual approach to counting things in Access is to first create a query with the conditions on the data, then use this as the basis of a second query that does the actual counting of the presorted data. I believe the way to do this in SQL server is to use a nested query. I want to generate the top 10 highest counts for each pesticide detected (detection is level>0) for a client between two dates.
Currently I am using
<code>
SELECT top 10 Count(Pesticide) AS CountOfPesticide, Pesticide FROM (SELECT tblData.Pesticide, tblData.Level, tblData.Clast, tblData.Client FROM tblData WHERE (((tblData.Day>@sdate) AND (tblData.Day<@edate))) and (tbldata.level>0) and (tbldata.clast=@clast) and (tbldata.client=@client)) as monkeyboy GROUP BY Pesticide ORDER BY Count(Pesticide) ASC"
</code>
The results that the above SQL turns out though are not reliable. For example, if I set the dates to now and 14 days ago, it produces higher counts for some pesticides then if I set the dates to now and 30 days ago. Any pointers or general advice about nested sql is gratefully accepted!
thanks
Mike
View 3 Replies
View Related
Sep 13, 2005
Hi,Please can somone help me with a nested SQL query. I have two tables please see belowTable 1CallIDEmployeeIDCallSummaryCallStatusTable 2CallHistoryIDCallIDDataAddedCallActionI would like to return the CallID, EmployeeID, CallSummary and CallStatus from Table 1, and also display the last CallAction from Table 2.This is a helpdesk database so a Call will have many CallActions i.e. Open, Held, Assigned Internal. How do I return the last CallAction Added against the selected CallID, I know I use the DateAdded but not sure about nested statements.The results I would like to return to the user would look like this:-Call ID: 1EmployeeID: 1Call Sumary: SQL ProblemCall Status: OpenCall Action (Last Action): Assigned Internal.
View 2 Replies
View Related
Sep 26, 2005
Hi all,
I have a temp guy who is doing some work for us (seriously, I never wrote the query).
Now this is the scenario (hold tight). So we have a stored procedure as follows...
ALTER PROCEDURE createSegmentPoints AS
DECLARE @fLat INTEGER
DECLARE @fLon INTEGER
DECLARE @segId INTEGER
-- declare cursor to return the from lat and lon for all segments that do not have address point 109.
DECLARE c1 CURSOR FOR (SELECT From_Latitude, From_Longitude, id AS segment_id FROM Segments WHERE SegmentType != 109)
OPEN c1
FETCH NEXT FROM c1 INTO @fLat, @fLon, @segId
WHILE @@FETCH_STATUS = 0
BEGIN
-- insert into table the segId, from lat, from lon and returned segment id from function.
INSERT INTO test VALUES (@segId,@fLat,@fLon,dbo.points_test(@fLat,@fLon))
FETCH NEXT FROM c1 INTO @fLat, @fLon, @segId
END
CLOSE c1
DEALLOCATE c1
As you can see here I am using a Cursor, which in turn calls a function
with each row that is processed in the recordset. The function that is
called is as follows...
ALTER FUNCTION points_test(@x INTEGER, @y INTEGER)
RETURNS INTEGER
AS
BEGIN
-- function to find the closed segment point with address point 109 to the segment specified in procedure.
DECLARE @tempDistance FLOAT(4)
SET @tempDistance = 1000000
DECLARE @id, @seg, lat, lon INTEGER
DECLARE @distance, @xd, @yd FLOAT
DECLARE c1 CURSOR FOR (SELECT from_latitude, from_longitude, id FROM segments WHERE segmenttype = 109)
OPEN c1
FETCH NEXT FROM c1 INTO @lat, @lon, @id
WHILE @@FETCH_STATUS = 0
BEGIN
-- calucations to get distance.
SET @xd = (@lat-@x)
SET @yd = (@lon-@y)
SET @distance = SQRT((@xd*@xd) + (@yd*@yd))
-- test if you have shortest distance.
IF (@distance < @tempDistance)
BEGIN
SET @tempDistance = @distance
SET @seg = @id
END
FETCH NEXT FROM c1 INTO @lat,@lon, @id
END
CLOSE c1;
DEALLOCATE c1;
RETURN @seg
END
(This function works out an equation to get the shortest distance from
two parameters passed to the function calculated with data from each
row returned within the cursor)
As you can see here, this function contains ANOTHER cursor!! Ouch. The
fact that their is an SQL query in a function is a killer, but having
another embedded cursor there is also a killer - this has virtually
killer the application.
So, how best is it for me to correct this. Should I turn the function
into a stored procudure? But even if I do this, the nested cursor still
remains. I was thinking maybe to have the SQRT equations within the
SELECT expression and then wrapped in a MIN() to maybe get the lowest
value.
Any ideas would be of great help.
Thanks
Tryst
View 2 Replies
View Related
