Suppose there's a table named [Category], which has 2 columns:
CategoryID int,
ParentCategoryID int
Each category, except the top most, has a parent category, it's a 1-to-n parent-children relationship.
Now I want to write a stored proc./function that accepts a CategoryID input parameter, and output all the descendent CategoryIDs (son, grand son, ...). How to do that in MSSQL 2000?
Table Properties LocationID Location 1 Europe 2 Western Europe 3 Germany 4 Bavaria 5 Berlin
Represented data in this case means that I have location Europe with its child location Western Europe with child location Germany with child location Bavaria with child location Berlin. Europe > Western Europe > Germany > Bavaria > Berlin
Now I need SQL statement to perform search on following way: For example if I search for Western Europe it needs to return not just Western Europe but its child€™s to, in this case Germany, Bavaria and Berlin.
I am having problem to apply updates into this function below. I triedusing cursor for updates, etc. but no success. Sql server keeps tellingme that I cannot execute insert or update from inside a function and itgives me an option that I could write an extended stored procedure, butI don't have a clue of how to do it. To quickly fix the problem theonly solution left in my case is to convert this recursive functioninto one recursive stored procedure. However, I am facing one problem.How to convert the select command in this piece of code below into an"execute" by passing parameters and calling the sp recursively again.### piece of code ############SELECT @subtotal = dbo.Mkt_GetChildren(uid, @subtotal,@DateStart, @DateEnd)FROM categories WHERE ParentID = @uid######### my function ###########CREATE FUNCTION Mkt_GetChildren(@uid int, @subtotal decimal ,@DateStart datetime, @DateEnd datetime)RETURNS decimalASBEGINIF EXISTS (SELECTuidFROMcategories WHEREParentID = @uid)BEGINDECLARE my_cursor CURSOR FORSELECT uid, classid5 FROM categories WHERE parentid = @uiddeclare @getclassid5 varchar(50), @getuid bigint, @calculate decimalOPEN my_cursorFETCH NEXT FROM my_cursor INTO @getuid, @getclassid5WHILE @@FETCH_STATUS = 0BEGINFETCH NEXT FROM my_cursor INTO @getuid, @getclassid5select @calculate = dbo.Mkt_CalculateTotal(@getclassid5, @DateStart,@DateEnd)SET @subtotal = CONVERT (decimal (19,4),(@subtotal + @calculate))ENDCLOSE my_cursorDEALLOCATE my_cursorSELECT @subtotal = dbo.Mkt_GetChildren(uid, @subtotal,@DateStart, @DateEnd)FROM categories WHERE ParentID = @uidENDRETURN @subtotalENDGORod
The SQL statement below works without the WHERE statement but as soon as i try to use the where it stops working, I think it has something to do with the HAVING statement in the vwMultiAE2 view but I am not sure.It did the same thing when I had it in one view. Anyone know what is going on here?SELECT Ch, Reg, FMM, AE, Acc, Totals FROM vwMultiAE2 WHERE (g1ID = @g1 or @g1 = 'All') AND (g2ID = @g2 or @g2 = 'All') AND (g3ID = @g3 or @g3 = 'All') AND (AEID = @g4 or @g4 = 'All')
vwMultiAE2 > SELECT TOP 100 PERCENT Ch, Reg, FMM, AE, Acc, Totals, g1ID, g2ID, g3ID, AEID FROM vwMultAE WHERE Account_ID IN (SELECT Account_ID FROM vwMultAE GROUP BY g2ID, Account_ID HAVING ( COUNT(Account_ID) > 1 )) ORDER BY g2ID, Account_ID
I have to write a select query which need some logic to be implemented. Query is like select name,number,active, if active ="true" then select @days=days from tbl_shdsheet else @days='' end from tbl_emp In the above query there will be days row for that employee if active is true else there won't be any data for that emp in the tbl_shdsheet So how can i write queery for this.
i am facing a problems, hope that anyone who know the answer can help me.
i am using sql server 2000 and vs.net. i wan to retrieve a data from the table which only select 3 top row based on their different contingentID. the table have different contingent and each contingent have 5 row data. i wan to retrieve 3 top row based on the total group by contingentID and sum up the total to order by the total and generate the ranking based on total. what i can do is only retrieve every row of data in that table. so anyone know the answer please help me, or if u dun understand my question , can message me..thank your
Hai friends,, I have a table name "Student" it contain 2 fields no ,name no name 1 Raja 2 Larsen 3 Ravi 4 Ankit 5 Eban my questions is I have a webform random.aspx whenever any user open a webform random.aspx it should display anyone of name in a random order..... CODING:- Dim cn As New System.Data.sqlclient.SqlConnectionDim rd As sqlDataReader cn.ConnectionString = "Persist Security Info=False;User ID=sa;Initial Catalog=master;password=david;" cn.Open()Dim cmd1 As New SqlCommand("select no,name from Student", cn) ' how i can chage to random order.... rd = cmd1.ExecuteReader rd.Read() Response.Write(rd(0) & "." & rd(1)) rd.Close() thank u.. Ambrose...
Hi All,I am facing problem in MS SQL Server 2000. It is behaving slow forselect statements. It is even slower than MS ACCESS. For example, if iuse"Select count(*) from tbl;". i get the results after long time ifthere is more than 100k rows. What might be the possible reasons forthis??ThanksHoque
There are a series of traces of transactions for essentially the same units of work. Each includes a retrieving the same table "Select" from the database. The first 4 transactions ran concurrently. The last one ran about 7 minutes later with no other concurrent transactions.
The elapsed times for the database accesses for each of the first 4 is significantly higher than the last one. Can you think of any database optimazation that might improve this?
Are there any utilities available than can be used for stand-alone testing of SQL Server database performance? E.G. they would measure response time for reading/writing large/small amounts of canned data once/multiple times with concurrent/nonconcurrent access.
SQL Server Database is on 2000.I am not sure about the ISOLATION level in the Java code.
Basically I am new to SQL Sever....Please ans the above questions and provide me the direction to proceed on these questions
CREATE TABLE [dbo].[TransactionComponents]( [pkTransactionComponent] [int] IDENTITY(1,1) NOT NULL, [pkTransactionID] [int] NOT NULL, [ComponentID] [int] NULL ) ON [PRIMARY]
With the following data:-
INSERT [dbo].[TransactionComponents]([pkTransactionID], [ComponentID]) SELECT 1,5 UNION SELECT 1,6 UNION SELECT 1,7 UNION SELECT 1,8
[Code] ....
pkTransactionID and ComponentID both link to the same column on another table this enables a many to many relationship, what I need to figure out is a complete tree of relationships from one of the ID's in it. I think I need to write a recursive CTE to achieve this but I am not entirely sure how to write it. Below is my attempt:-
DECLARE @ID INT SET @ID = 1; WITH cteTxHeirachy (TxID, RelTxID, TxLevel)
[Code] ...
This returns:-
15 16 17 18 19 110
But the following are missing:-
10 2 2 11 2 12
3 and 4 should not be returned. I figured if I added the code that is commented out in the CTE that should give me everything but I think I get caught in an infinite loop.
The MLID in the header table will be generated automatically.All the Parents will be stored in the HEADER and their childs in the DETAIL.When a child is added to a parent,the Parent's MLID will be stored in the MLID field in the DETAIL table with the newly added child.That child will come to the PARENT table when a child is added to that.The MLITEM id in the parent table can be repeated when that item undergoes a rivision.But the MLID for this will be a new one.An item in the Parent Table can have any number of childs and these childs can have any number of children(there is no limit for the level.)
Now if i select an item id '1000' (for example from the Header Table) with a concatstring (it could be without a concat string also).all its childs and their children should be printed in a report like the following
************************************************** *************************** I NEED TO CREATE THE TREE USING BOTH THE HEADER(MSTHDRML) AND THE DETAIL TABLE(MSTDTLML) ************************************************** ***************************
How can this be done.Is it necessary to use a recursive function in a stored procedure to generate this ...... i have never used Recursive function in SQL SERVER Stored Procedures.Can anyone help me on this(with Code).if not stored procedure, then what else can be done for this.
Dear fellows, Can anybody tell me how can i apply recusive/Tree query using select statement. For example I've a table structure for an Organization as follows:
emp_id emp_name supervisor_id ---------- --------------- ------------------- 101 ZAFIAN 102 BRUNNER 101 108 CALLAHAN 102 105 RUSSO 102 110 SIM 102 103 DUELL 101 and so on
1. How can I get the above records in Hirarchical format starting from top or from anywhere else in the hierarchy?
In Oracle it can be done as follows: SELECT emp_id,emp_name,supervisor_id FROM employee_tbl CONNECT BY supervisor_id = PRIOR emp_id START WITH supervisor_id is null;
Please reply me at the following address if possible: faisal@visualsoft-inc.com
INSERT INTO Details SELECT '','7419-01',0 union all SELECT '7419-01','44342-00',1 union all SELECT '7419-01','45342-00',1 union all SELECT '7419-01','46342-00',1 union all SELECT '7419-01','47342-00',1 union all SELECT '7419-01','48342-00',1 union all SELECT '7419-01','49342-00',1 union all
[code]....
From the above table data i want a search query , for example if I search data with "52342-00" I want output to be below format using CTE.
I have searched the net for atmost two days to find the solution of this problem but we not able to get the solution. I would appritiate if any one could help me in solving this issue:
I want to write a trigger for deleting the EMPLOYEE with EMPID=1 and the trigger should delete all the employees as there is cascading among them i.e EMPID 1 is the Manager of EMPID=2 and so on..
I found a solution at: http://msdn2.microsoft.com/en-us/library/aa902684(sql.80).aspx but the solution does not work when i try to implement it . It deletes the record for abc,xyz in the above table but rest are not deleted by the trigger.
I have created a package which has to move huge volume of data in chuncks from source to target with a few sorter transformations and many lookup transformation. Once a chunck is completed the Execute SQL Task in the package updates the etldates into a control table against the chunkid. I want this run in a loop, as on completion of a chunk another should start and follow the same process until there are no more chuncks in the control table. First i execute the package manually, then i have written a trigger to call the SQL Server Job (etl) on update of the dates for the chunkids in the control table. The job when being called by the on update trigger is throwing up the following error. "The job failed. The Job was invoked by User fwt obin. The last step to run was step 1 (Package1)". Am i missing something basic here?
PS: So i tried putting the package into a for each loop container, performance suffers. A chunck which used to take 2 hours is taking more than 12 hours to complete. Then i removed sorter transformation and used db sorting in my source OLE db. Then thought of cacheing the lookups but did not know how and what to give so i gave up. While iam here, DB sorting/SSIS sorting? which is better?
I'm having trouble getting off the ground with the Web application walkthrough "Walkthrough: Creating a Web Application Using Visual C# or Visual Basic" in VS.NET Pro 2002 [Academic] documentation. After a bit of fishing around, and consulting the MS Knowledge Base, I got the pubs database installed. I also got the connection to work well enough that the dataset would fill in the IDE.
The problem is that when I try to run the web form, either from the IDE debug menu, or by accessing the .aspx file on localhost using Firefox, I get the error: SELECT permission denied on object 'titles', database 'pubs', owner 'dbo'. showing in the browser. My understanding is that this page is running as ASPNET, and I did already carry out the recommended commands to enable access: C:>osql -E -S MY-MACHINE-NAMEVSDOTNET -Q "sp_grantlogin 'MY-MACHINE-NAMEASPNET'" C:>osql -E -S MY-MACHINE-NAMEVSDOTNET -d Pubs -Q "sp_grantdbaccess 'MY-MACHINE-NAMEASPNET'" both of which commands returned successfully. Any suggestions as to what else I should do to get the necessary permissions to actually display the data in my browser? Does the IIS user account need permission also?
Thanks for any insight into this vexing problem. I must say that along the way, I have had some fun exploring the osql comand-line tool. Using the -E switch, I have been able to run select and upgrade queries, but this is all pretty much fishing in the dark. I would like to get back to actually working with the walthroughs in the Visual Studio documentation.
I would like to create a store procedures that will return me a full path by passing a PathID
I started with this code:
DECLARE @path_id int SET @path_id = 5; WITH fullPath (PathID, ParentPathID, Path) AS ( SELECT PathID, ParentPathID, Path FROM tblPath WHERE PathID = @path_id UNION ALL SELECT tblPath.PathID, tblPath.ParentPathID, tblPath.Path AS Path FROM tblPath JOIN fullPath ON tblPath.PathID = fullPath.ParentPathID ) SELECT * FROM fullPath
This code will return this: 1 NULL D: 2 1 Sections 3 2 Bin 4 3 Data 5 4 FinancialReport.doc
What I would like to get is something like this: D:/Sections/Bin/Data/FinancialReport.doc
Any help would be really appreciated. Lost too much time on it already. Thanks, pharvey
in few words i need subtotal only for who have children.I tried with rollup but i wasn't able to have it similar to the aspected.I put it the level just to let clearer the dependencies.
Let's say I have a scalar functions that I'd like it's input to be the output from the previous row, with a recursive CTE I can do the following:
;with rCTE(iterations,computed) as ( select 0 [iterations], 1e+0 [computed] union all select iterations+1,dbo.f(computed) from where rCTE where iterations < 30 ) select * from rCTE
Thus for each iteration, is the nTh fold of the function f applied to 1. [e.g 5 is f(f(f(f(f(1)))))]
However, for some illogical reason this relatively simple function did lots of read and write in tempdb. Can I reform this query somehow to just use lag instead? I know for a fact I only want to get let's say 30 iterations. It'd be very nice to be able to enjoy a window spool which will spawn a worktable with minimal IO.
I know I can put 30 rows into a table variable and do a quirky update across it, but Is there a nice way to do this without doing some sort of hack.
CREATE TABLE #tree ( Childid varchar(20), Parentid varchar(20) ) INSERT INTO #tree (Childid,ParentId) SELECT '123' , null UNION ALL SELECT '456' , '123' UNION ALL SELECT '789' , '456' UNION ALL SELECT '870' , '456' UNION ALL SELECT '985' , '870';
I have a hierarchical structure for mapping products to categories, categories go 3 levels deep (depth is defined in articlegroups.catlevel, 0 being the main category and traversing down to lower category level 2). Also, a product may be in more than 1 category(!).
product details are stored in `[products]` articlegroups are defined in `[articlegroups]` and the mapping of the products to the articlegroups are defined in `[products_category_mapping]`
Now, I want to retrieve index the full category path for each item, so with the data provided below, I'd expect these 2 rows as a result:
Now I can get the separate fields via a statement like this:
SELECT ga.slug_nl as slug_nl_0 FROM articlegroups ga INNER JOIN products_category_mapping pcm ON pcm.articlegroup_id=ga.id INNER JOIN products gp on gp.id=pcm.artikelid WHERE gp.id=2481446
I am trying to do a very small numbers table to compare A1c's against. However I am running into a issue when recursion hits the number 2.27 it starts to go out of my scope that I want with the next number being 2.27999999999999. Here is the code I'm using below. I need a Decimal(2,2) or Numeric (2,2) format with a range of 01.00 to 20.00. However every time I use Numeric or Decimal as the data type I get a error "Msg 240, Level 16, State 1, Line 5.Types don't match between the anchor and the recursive part in column "Number" of recursive query "NumberSequence"."
DECLARE @Start FLOAT , @End FLOAT ---DECIMAL(2,2) Numeric (2,2) SELECT @Start=01.00, @End=20.00 ;WITH NumberSequence( Number ) AS ( SELECT @start as Number UNION ALL SELECT Number + 00.01 FROM NumberSequence WHERE Number < @end
I have a recursive CTE on an inline table valued function. I need to set the MAXRECURSION option on the CTE, but SQL Server is complaining with "Incorrect syntax near the keyword 'OPTION'".
It works fine on non-inline function. I couldn't find any documentation indicating this wasn't possible.
I can use the MAXRECURSION option in call to the function
SELECT * FROM MyFunction () OPTION ( MAXRECURSION 0 )
but that means that the user needs to know the "MyFunction" uses recursive CTE, which defeats the purpose of the abstraction.
I have a family table and would like to group all related members under the same familyID. This is a replication of existing business data, 14,000 rows. The familyID can be randomly assigned to any group, its sole purpose is to group the names:
declare @tv table (member varchar(255), relatedTo varchar(255)) insert into @tv select 'John', 'Mary'union all select 'Mary', 'Jessica' union all select 'Peter', 'Albert' union all
[Code] ....
I would like my result to look like this:
familyID Name 1 John 1 Mary 1 Jessica 1 Fred 2 Peter 2 Albert 2 Nancy 3 Abby 4 Joe 4 Frank
Hello.I test some query on sql server 2000 (sp2 on OS windows 2000) and iwant to know why a simple query like this :select * from Table Where Column like '%value'is more slow on 2000 than on sql 7.And this case arrive only if the % character is in the begin.If you test this :select * from Table Where Column like 'v%alue'then it's more fast on 2000.I look the execution plan, there is a difference but this differenceis the same in all the case i test (for the two query i write in thismessage for example).I don't understand why and so, if someone have an explanation, andperhaps a solution ...Excuse my poor english language.And thanks for time people spend to answer me.
Hello, i have a question that the sql server 2000 is install in window 2000 server. If i want to update to window 2003. Is that any problem in sql server 2000. I am worry about whether we will have problem after update. What i need to do? Many thanks.
I have a query which was generated by a user using a report writter, so I have no control over what the user selected or how the query was generated.
The following query runs perfectly under SQL 2000 SP 4 32 bit and has for a long time. We are testing on a SQL 2005 SP1 (2153 Build) 64 bit with the exact same data.
The following query on SQL 2000 runs in 3 seconds, on SQL 2005 ran for 42 MINUTES before we cancelled it.
SELECT CustomerID = TABLEB.CustomerID, CustomerName = TABLEA.Group_Name_1_A, CustomerContact = TABLEA.Group_Name_2_A FROM TABLEA RIGHT OUTER JOIN TABLEB ON (TABLEB.CustomerID=TABLEA.CustomerID) RIGHT OUTER JOIN TABLEC ON (TABLEC.CustomerID=TABLEB.CustomerID) WHERE TABLEB.CustomerID IN (.. List of 38 CustomerIDs...)
Note: TABLEA and TABLEB are actually views into TABLEC. I don't know if that is relavant or not yet.
This appears to be this problem: http://support.microsoft.com/kb/318530 which existed in 2000 and was fixed with SP3. Can anyone confirm if this problem exists in 2005??
In diagnosing the problem, several things cause the query to work properly under 2005 and return in 3 seconds: Changing the WHERE TABLEB to WHERE TABLEA. Removing the RIGHT OUTER TABLEB, which is technically unneeded. The most interesting is shortening the WHERE IN clause to only 11 items, which is not possible for the report. Removing the PK from TABLEC OR making the PK index non-clustered.
The est execution plans between 2000 and 2005 is like comparing "Apples to Automobiles". That both start with "A" and that is about the only similarity.