I am not sure if select recursion is possible and thought I would throw this challenge to the dba community. The preference would be to create a view that does the work on the backend instead of writing frontend VB code.
Below is script that creates and populates a temp table along with the desired result-set.
create table #myTest
([id] int identity (1,1),
[Parent] int ,
minutes smallint
)
insert into #myTest (parent,minutes) values (null,1)
insert into #myTest (parent,minutes) values (1,2)
insert into #myTest (parent,minutes) values (1,4)
insert into #myTest (parent,minutes) values (3,8)
I have a database with three tables: Employees, Assets, and Recovery.
I have the following SELECT statement:
SELECT Employees.EmployeeID, SUM(Assets.Amount) AS [Case Value:], SUM(Recovery.Recovery) AS [Recovery:] FROM Assets INNER JOIN Employees ON Assets.EmployeeID = Employees.EmployeeID INNER JOIN Recovery ON Employees.EmployeeID = Recovery.EmployeeID GROUP BY Employees.EmployeeID
Here is the challenge:
You will always have data in the Employee table, but not neccesarily in the Assets or Recovery table.
If there are no Assets or Recovery I would still like the query to show 0.00 for the Assets or Recovery for each Employees.EmployeesID
With the above SELECT statement it will only return values that have data created in each of the three tables.
How do you modify the SELECT statement so a row is returned for every Employees.EmployeeID?
Hi there, Any tips on my problem would be most welcome...
right, the scenario.
A web Blog
blogger1 posts a blog_entry, e.g I love the simpsons blogger2 comments on that blog_entry, e.g No, I hate the simpsons Blogger3 comments on that comment, ie, How can you hate the simpsons.
so you can comment on a comment on a comment etc.. lool.
right, i have got two tables... Blog_entry & comment. i need to be able to search for a blog_entry + all the comments on that blog_entry..
at the moment i can search for the comments on the blog_entry using the FK in the comment table.
blog_entry INSERT INTO Blog_Entry VALUES(0001,'I love the Simpsons');
comments INSERT INTO Comment VALUES(0001,' No I hate the simpsons, cID 1000);
but i need to be able to search for the comments on comments
INSERT INTO Comment VALUES(0001,' How can you hate the simpsons, cID1000);
hopefully you can see the problem here, with only one comments table how can i get the search for the comment on the comment.. there’s nothing linking them... I could make a sub comments table, and use a FK (as with the blog_entry & first comment)
but then I would have to make another sub sub table to be able to get those comments on the first sub table... this would go on and on for each comment on comment.
you can see the cID1000 (comment PK) I can't use this to get the comment because its duplicating the PK...
So, I need to be able to search for the comments on comments… eg. I need to be able to search for blogger2, and any comments that were made on his comments.
Someone I know mention using recursion to get the comment on comment info, is this right ?
Hehe, I hope you understand what im asking here… the is my first exploration of SQL, so any tips, hints, would be most welcome….
Thanks loads…
PS: if there anything you don’t understand about what I have written, or what im asking… please say so…
I am trying to build the value off hierarchy that will be later inserted in the linage and Depth column. I am trying to do so using recursion . the rulles for recording the linages as as follows Lineage = parent.Lineage + Ltrim(Str(ParentID,6,0)) + '/'
here is my code below
with BuildHierarchy as ( SELECT AN.AccessID,AN.ChildID,AN.Child,AN.ParentID,AN.CGI D, 1 as Depth,AN.Lineage,AN.node,AN.PercentOwnership, AN.Notes FROM chart_hiera2 as AN WHERE AN.Depth Is Null and AN.AccessID = @accID union all SELECT AN.AccessID,AN.ChildID,AN.Child,AN.ParentID,AN.CGI D,Cast(AN.Depth as smallint) +1 ,Cast(BH.Lineage+ Ltrim(Str(AN.ParentID,6,0)) + '/' as varchar(255)),AN.node,AN.PercentOwnership, AN.Notes FROM chart_hiera2 as AN inner join BuildHierarchy BH on AN.ParentID=BH.ChildID WHERE AN.Depth>=0 AND AN.Lineage Is Not Null AND AN.Depth Is Null and AN.AccessID = @accID)--@accID --and T.AccessID = @accID) select * from BuildHierarchy
but it does not increment Depth or builds Lineage .What am I doing wrong?
Guys, I desperately need your help. what i need to do is call a function getdate to return dates recursively
like so classdate=getdate(class#, repeat, sportcategory,date) while (classdate <yearend) begin classdate=getdate(class#, repeat, sportcategory, classdate) end
I want to use the date returned and keep calling that function till the yearend is reached, to get a bunch of dates declare @startDate datetime
with cteStartDate as ( select x= classdate, repeat, class#, sportcategory, yearend from sports union all select x=getdate( classdate, repeat, yearend), repeat, class#, sportcategory, yearend
from cteStartDate where x < cteStartDate .yearend
)
I tried using cte for this, it only returns one date for everything, as opposed to an array of dates, how do i solve this problem.Any insight will be greatly apprecaited.
Hi there! I have an application that uses stored procedures wuth CTE statements to populate trees from database. Every second I need to populate 100-300 trees each one of them has 15-20 nodes. I checked those procedures with profiler and payed attention that populate procedure with CTE is slower that others (without CTE) per 1000 times! Would procedures with recursion faster than same ones with CTE? I am asking because I read somewhere that CTE is becomes slower while populating little chunks of data. Is that right and I should use recursions?
And second question: here the code -
Code Snippet
;WITH dt (Id, NodeText, PId, State, RowNumber) AS ( SELECT Id, NodeText, PId, State, ROW_NUMBER() OVER(ORDER BY Id) AS RowNumber FROM DataTable WHERE Id = 1
UNION ALL
SELECT d.Id, d.NodeText, d.PId, d.State, ROW_NUMBER() OVER(ORDER BY d.Id) AS RowNumber FROM DataTable AS d INNER JOIN dt ON dt.Id = d.PId )
SELECT * FROM dt
This is how I populate a tree with CTE. Could you give me some example how to do exactly same but with recursion?(without CTE). Thank you!
Hi Y'all,I receive an error while using recursion:The maximum recursion 100 has been exhausted before statement completion. Can someone tell me where i can alter the default value?Thanks in advance
I have a multi-level folders table, named folders with attributes of folder_id, parent_id and user_id, and have another table, users, contains all the user_id. I need to list all the users for each folder which has parent_id = 0 and its all sub-folders' users into one table. I have created a function to return a table with folder_id and user_id for one single folder. However, I don't know how to use this function to get the sub-folder's users and merge them together as one single table. Here is my function: CREATE FUNCTION [dbo].[FolderUsers] (@fid int) RETURNS Table AS Return (select folder_id, f.user_id from folders f, users u where f.user_id = u.user_id and folder_id = @fid)Go where @fid is the top folder with parent_id = 0 at here, the next level sub-folder's parent_id would be = @fid. I am thinking to have recursive call from the parent_id = @fid that returns another table and have to concatenate to the called table. I have been thinking of store procedure, "insert into" and so on, but don't know how to implement it. Do you have any good inspiration for me? Thank you in advance!
// I need to write a query to extract data from two tables where I save information on my tables in one and the relationship between the tables in the second. //CREATE TABLE [dbo].[tblPages]( // [PageID] [int] NOT NULL, // [PagePath] [varchar](max) , // [MenuName] [nchar](10), // [Directory] [varchar](15)) //CREATE TABLE [dbo].[tblPageRelation]( // [PageRelationshipID] [int] NOT NULL, // [TopPageID] [int] NOT NULL, // [BasePageID] [int] NOT NULL) // What I would like to do is do the following in SQL is a combination of this // SELECT * FROM TblPageRelation PR LEFT OUTER JOIN TblPages P on PR.TopPageID = P.PageID //WHERE P.DIRECTORY = 'MENUBLOCK' UNION SELECT * FROM TblPageRelation PR LEFT OUTER JOIN TblPages P on PR.TopPageID = P.PageID //WHERE P.DIRECTORY = P.DIRECTORY //public void recursivemove (String Directory) // {// Find Rows that Have this Block Name associated with em // For each returned row find the rows that share the same Directory // } //}
Hi Everybody, I have a table that contains two field Group_ID, ParentGroup_ID this maintains parent Child relation ships between groups now I need a stored procedure in SQL Server that would give the ID's of all Parents of a specified group. it is possible through recursion in stored procedure. I have not much idea of recursion in procedure. Can anybody help, by providing a related sample or code? I would be highly thankful.
I've encountered some strange behavior in a recursive procedure I'mwriting for a bill of materials. First let me ask directly if what Ithink is happening is even possible:It seems like the procedure is not following the recursion in serialorder, but in parallel. In other words, after one instance of theprocedure calls itself, it continues executing lines below therecursion before the recursion is done. Is that possible? I lookedfor SQL Server Options that might deal with recursion or threading butI couldn't find anything.Now let me explain what's happening in terms of the BoM. All the rowsI expect are returned, but not in the correct order. Let's assume thefollowing tree:1|-2| |-5| | |-7| | -8| -6| -9|-3| |-10| |-11| | |-13| | -14| | |-15| | |-16| | -17| -12| -18| -19| -20| |-21| -22-4-23|-24-25-26This is stored in table P using MemberID and ParentID fields. Forexample,MemberID ParentID-------- --------1 NULL2 13 14 15 26 2(etc...)Based on how I wrote the recursion (I will provide the procedurebelow), I would expect output when starting from MemberID of 1 to looklike this:MemberID Depth Sort-------- ----- ----2 1 15 2 27 3 38 3 46 2 59 3 6(etc... basically, the line order of the graphical tree above, or acounter-clockwise traverse around the tree)Instead, I get this (I'll provide the whole thing because I don't seea pattern):MemberID Depth Sort-------- ----- ----2 1 15 2 23 1 210 2 37 3 34 1 36 2 39 3 423 2 48 3 411 2 413 3 512 2 524 3 525 3 618 3 614 3 615 4 719 4 726 4 720 5 816 4 817 4 921 6 922 6 10Call me crazy, but it looks like my tree was parsed in the same orderthat a set of dominos arranged in the same shape would topple. Theonly way I could see that happening is if the recursion is non-linear,allowing both children and siblings to be parsed simultaneously. Itwould also explain why my sort counter didn't increment properly, butthe depth counter is always correct.Now here are the procedures. There's also a Qty column, since this isa BoM after all, but I didn't need to mention it for my illustrationof the problem above.CREATE PROC makebom @root bigint---- This would be called by the client to find all the parts andquantities-- under a specific part (@root)--ASSET NOCOUNT ONCREATE TABLE #result (MemberID bigint, Qty bigint, Depth bigint, sortbigint)EXEC bomrecurse @root, 1, 0SET NOCOUNT OFFSELECT MemberID, Qty, Depth, sort FROM #result ORDER BY sortGOCREATE PROC bomrecurse @root bigint, @depthcounter bigint,@sortcounter bigint---- This is the recursive procedure, called once by makebom, butrecalling-- itself until the whole tree is parsed, filling the #result table--ASDECLARE @memberid bigint, @qty bigint, @nextdepth bigintDECLARE children_cursor CURSOR LOCAL FORSELECT MemberID, Qty FROM PWHERE ParentID = @rootORDER BY MemberIDOPEN children_cursorFETCH NEXT FROM children_cursorINTO @memberid, @qtyWHILE @@FETCH_STATUS = 0BEGINSET @sortcounter = @sortcounter + 1INSERT INTO #result VALUES (@memberid, @qty, @depthcounter,@sortcounter)SET @nextdepth = @depthcounter + 1EXEC bomrecurse @memberid, @nextdepth, @sortcounterFETCH NEXT FROM children_cursorINTO @memberid, @qtyENDCLOSE children_cursorDEALLOCATE children_cursorGOI'm surprised this even worked as well as it did because I'm a newbiewhen it comes to stored procedures and I put this together fromexamples I found around this group, online and in the T-SQL Help. Sofeel free to comment on other aspects of my code or approach, but I'mmost interested in understanding the behavior of this recursion.
I have a table describing a hierarchy structure and the number of levels is very large, say 10000. Can I control the recursive CTE to get the first 1000 levels? Thanks!
I have a bit of an issue with an app I'm working on. The app integrates two different SQL Server applications - both of which employ recursive triggers to some extent. My integration basically serves to establish a link between different tables in the applications, and maintains consistency between the two applications (where common fields exist) by employing INSERT, UPDATE, and DELETE triggers.
In order to prevent infinitely recursive triggers (AppA.TableA's update trigger updates AppB.TableA. AppB.TableA's update trigger updates AppA.TableA...and so on, and so on) I need to be able to somehow selectively prevent these triggers only from executing recursively. For example, if the trigger in AppA is what calls the trigger in AppB, I do not want AppB's trigger to fire (and vice versa).
Further Information:
The apps may be on the same, or different, SQL servers.
I'm fully aware of the database-wide options to disable trigger recursion (ALTER DATABASE), but I can't disable recursion for the balance of the triggers in the databases.
The integration will run on either SQL 2000 or SQL 2005 - and perhaps one server on 2000, and one on 2005 (depending upon the deployment).
I'm perfectly amenable to handling it in the trigger code, if possible, but I'm at a bit of a loss as to how to properly and efficiently manage that.
I know that SQL Server will kill infinitely recursive triggers once it detects them, but that doesn't exactly solve my original problem. Thanks very much for any input you can offer.
I have a table describing a hierarchy structure and the number of levels is very large, say 10000. Can I control the recursive CTE to get the first 1000 levels? Thanks!
Hello my friendsThis is my sql table structureFK = ID int, Empnaam varchar(200), PK = EmpID int With this table, where i insert values, employees can hire other employees. Now i want to see with a function in visual studio who hired who. I get stuck when a person hired more than 1 person....This is my stored procedure :set ANSI_NULLS ON set QUOTED_IDENTIFIER ON GO ALTER PROC [dbo].[ShowHierarchy] ( @Root int ) AS BEGIN SET NOCOUNT ON DECLARE @EmpID int, @EmpNaam varchar(30) SET @EmpNaam = (SELECT EmpNaam FROM dbo.Emp WHERE EmpID = @Root) PRINT REPLICATE('-', @@NESTLEVEL * 4) + @EmpNaam SET @EmpID = (SELECT MIN(EmpID) FROM dbo.Emp WHERE ID = @Root) WHILE @EmpID IS NOT NULL BEGIN EXEC dbo.ShowHierarchy @EmpID SET @EmpID = (SELECT MIN(EmpID) FROM dbo.Emp WHERE ID = @Root AND EmpID > @EmpID) END END Thanks in advance!Grtz
It seemed quite easy at first glance. I Built it up via string concatenation and thought to execute the dynamic sql with sp_exec and get the result. As I don't like dynamic sql I was wondering If there is any other way..
I just don't have the skills to work this out...maybe there is an SQL guru out there that can solve this:I can't work out how to do a recursive query so I'm using this function:Public Shared Function FindFriendsFriends(ByVal userID As Integer)Dim iUserID As Integer = userIDDim friendAdapter As New TableAdapters.FriendsTableAdapterDim oFriends As New FrontEnd.FriendsFriendsDataTableDim oTemp As New Data.DataTableDim oContainer As New Data.DataTableoFriends = friendAdapter.GetFriendsByUserID(iUserID)For InLoopCounter As Integer = 0 To oFriends.Count - 1oTemp = friendAdapter.GetFriendsByUserID(oFriends.Rows(0)("UserID"))oContainer.Merge(oTemp)NextReturn oContainerEnd FunctionWith this SQL statement in the table adapter is:SELECT tblFriends.FriendHashID, tblFriends.cCreated, tblFriends.UserOwnerID, tblUsers.UserID, tblUsers.Displayname, tblUsers.EmailAddress, tblFriends.RequestStatus, tblFriends.FriendUserID, tblUsers.ProfilePhoto FROM tblFriends INNER JOIN tblUsers ON tblFriends.FriendUserID = tblUsers.UserID WHERE (tblFriends.UserOwnerID = @UserID) AND (tblFriends.RequestStatus = 2) UNION SELECT tblFriends_1.FriendHashID, tblFriends_1.cCreated, tblFriends_1.UserOwnerID, tblUsers_1.UserID, tblUsers_1.Displayname, tblUsers_1.EmailAddress, tblFriends_1.RequestStatus, tblFriends_1.FriendUserID, tblUsers_1.ProfilePhoto FROM tblFriends AS tblFriends_1 INNER JOIN tblUsers AS tblUsers_1 ON tblFriends_1.UserOwnerID = tblUsers_1.UserID WHERE (tblFriends_1.RequestStatus = 2) AND (tblFriends_1.FriendUserID = @UserID)I want to replace the SQL statement with a recursive query that I simply pass the UserID to and get rid of the function which is very inefficient!
Ok, here's the problem. I have CA's Unicenter ServiceIT Enterprise Edition 5.0 running on a production box that has NT4 (SP6) and SQL server 2000 running on it.
Just before we continue, this is about backing up the database that ServiceIT connects to.
I know that you can schedule EM to make backup's of a specific database over a certain time, but this is part of the problem. What i have since discovered is that ServiceIT will not run on a database backed up and restored by EM.
It will however run on the restored backup created through the dos command pdm_backup (comes with ServiceIT).
What i am trying to figure out is to try a find a way that i can run a dos prompt command to do the following tasks at a specific time of day:
Stop the Paradigm Server Daemons (service) Run a DBCC CHECKDB on the database AHDTEST50. If there are no errors, create a verbose backup using the dos command pdm_backup -v -fC:ackup[todaysdate] If there are errors run a script to correct them, then run a backup Restart the Paradigm Server Daemons
I wrote a stored procedure that searches for user on my table depending on the search option chosen by the person doing the search. But my challenge is displaying users without a picture. On my members table, i used -1 to indicate a user that doesn't have or set his/her picture, this makes it difficult for me to track that person when the person searching chooses to display members with or without pictures. What happen is that my inner join of the photos table dat reference the user primarypictureid does not exist in the photos table. How do i overcome this challenge. My script
IF (@withpicture = 'True') SET @withpicture = '%[0-9][^-]'
IF(@countryId = '-1') SET @countryid = '%'
BEGIN SELECT m.UserName, m.MemberId, m.Gender, m.PrimaryPictureId, DATEDIFF(year,m.BirthDate,GETDATE()) AS 'Age', c.CountryName, p.PicFileName FROM Members AS m INNER JOIN Photos AS p ON m.PrimaryPictureId = p.PictureId INNER JOIN Countries AS c ON m.CountryId = c.CountryId WHERE (m.Gender = @searchfor) AND (DATEDIFF(year,m.BirthDate,GETDATE()) BETWEEN @firstage AND @secondage) AND (m.CountryId LIKE @countryid) AND (p.PictureId LIKE @withpicture) AND (m.UserName <> @username) ORDER BY m.BirthDate ASC END
I have a challenge for all DBAs. I am getting INCONSISTENT TIMINGS for Querry Results from a SQL Server 2000 Standard Edition on a HP Proliant 2 CPU Server with 4 GB RAM and SCSI DrivAFAe. Our Database is 72 million records and have 8 columns. Most of them are indexed, which are used in the “Where…� clause. In addition to independent indexes, we also have a covering index for 3 most frequently searched fields. My challenge is that out of the 7000 odd queries that hit the server with various search conditions in a Week, 5% of the queries return result in less than a minute. The same query at a different Time or with a different Value, returns results inconsistently. For e.g. searching FirstName = “Anna�; LastName = “Williams� returns result in 0.01 sec. Searching for FirstName “Benjamin�; LastName = “Watson� returns in 5 minutes. Any kind of help is welcome and will be highly appreciated. - Santy san.rely@gmail.comNote: Edited to fix white on white font.
I have a query that I am trying to optimize. It works on some 9000 records and runs too slow. What the query does is takes the multiple assignment of a single contact record to multiple attributes (a.k.a many-to-many). For example:
Membership table. Contact_ID 1 2 3
Relate table 1 1 1 3 1 4
Relate Item 1 item1 2 item2 3 item3 4 item4
The query will take all ocurrences of the related items and place them in a single field while delimiting by comma "item1" , "item3", "item4"
Here is the query as it exists now:
select CONTACT_ID, UNION_NAME into #tmp from MEM_UN MU inner join MEM_UN_REL MUR on MU.UNION_ID = MUR.UNION_ID order by CONTACT_ID, UNION_NAME
insert into #unionlist (CONTACT_ID, UNIONS) select distinct CONTACT_ID, UNIONS = '' from MEMBERSHIP
while exists(select CONTACT_ID from #tmp) BEGIN update #unionlist set UNIONS = UNIONS + '"' + ( select min(UNION_NAME) from #tmp where #unionlist.CONTACT_ID = #tmp.CONTACT_ID ) + '",' where CONTACT_ID in (select CONTACT_ID from #tmp)
update #unionlist set UNIONS = UNIONS + '"",' where CONTACT_ID not in (select CONTACT_ID FROM #tmp)
delete FROM #tmp where UNION_NAME in ( select min(UNION_NAME) from #tmp tmp2 where #tmp.CONTACT_ID = tmp2.CONTACT_ID ) END
I believe that the slow down is in the process of deleting from #tmp every time it loops through the recordset.
On march 23, Micorosoft was supposed to benchmarch their SQL server 7.0 versus Oracle 8i. I didn't watch the benchmark... I figured that I would be able to read about it in the news. But it seems like there is some moritorium on the subject. I haven't found any result information whatsoever.
Does anybody know who won the challenge? Did microsoft win $1 million from oracle?
Tom Mack, MCSE Database Administrator Advancia Corporation
We're having to work with some legacy data. The tables in the so-called database seem to have way more nulls than actual data. One table appears to have around 100 or more columns in it. It has close to 40,000 rows.
This Db has pretty much 0 normalization present.
IOW, your worst nightmare.
Is there a way we could run a query that would return the total number cells inthat contain NULL and another that could return the total number of data-bearing cells so we could come up with a % or a ratio.
Can anyone tell how I can parse the WHERE clause of an SQL statement to check for special characters such as ''' (single quotes) in fields of type varchar?
Well i wanted to prove to some guys that cursors are not really that important:shocked: . :D So this code is suppose to remove duplicate tuples from a table without temporary tables or cursors:D. Except it needs some optimization(and alot of system down time, not sure about that:confused: ). I would like it, if some one could find an instance of the table when the below code fails or some way to optimize the code or anything;) .
--trashtable for real data create table abc (col1 tinyint, col2 tinyint, col3 tinyint)
--trash values for trash table insert into abc values (1,1,1) insert into abc values (1,1,1) insert into abc values (1,1,1) insert into abc values (1,1,1) insert into abc values (2,2,2) insert into abc values (2,2,2) insert into abc values (2,2,2) insert into abc values (3,2,1) insert into abc values (2,2,3) insert into abc values (3,2,4)
--check that there are ten rows select * from abc --check that there are only five distinct rows select distinct * from abc
--run code : next 15 line as a batch declare @lp tinyint declare @col1 tinyint,@col2 tinyint,@col3 tinyint set @lp=1 while @lp>0 begin if not exists (select top 1 * from abc group by col1,col2,col3 having count(col1)>1) set @lp=0 else begin select top 1 @col1 = col1,@col2 = col2,@col3 = col3 from abc group by col1,col2,col3 having count(col1)>1 delete from abc where col1=@col1 and col2=@col2 and col3=@col3 insert into abc values(@col1,@col2,@col3) end end
--only distinct values left in trash table select * from abc
--think code can be optimized --just wanted to prove: can be done without cursors or temporary tables
Here is the table:CREATE TABLE [child]([pk_child_id] [int] NOT NULL ,[fk_parent_id] [int] NOT NULL ,[code] [char] (2)NOT NULL ,[dt] [datetime] NOT NULL ,[newcode] [int] NULL)There is a situation where there will be more than one record with thesame [fk_parent_id] value, but different values for the [code]field.If one of those records has a [code]= 5, but the [dt] is AFTER asimilar record where [code]= 6 or [code]= 7 (but same [fk_parent_id]value), I need to set [newcode] = 10. How can I pull this off? Again,the group of records can have different [code] values, different [dt]values, but a common [fk_parent_id].Help!
This code is attempting to find records that have a RegJrnID that doesnot occur more than one time in the table.The reason that I want to find records with non-duplicated RegJrnIDvalues is to create "reversal" records for these such that the reversalrecord has identical values for every column except the TaxableAmountwhich will contain a negative amount. (see: example data below)./* Set up */CREATE TABLE t1(RegJrnID INTEGER, InvoiceDate VARCHAR(8), InvoiceNumberVARCHAR(20), TaxableAmount DECIMAL(32,8))/* Example data */INSERT INTO t1 VALUES (1, '20060101', '2321323', 100.00)INSERT INTO t1 VALUES (9, '20060213', '2130009', 40.01)INSERT INTO t1 VALUES (3, '20060101', '9402293', 512.44)INSERT INTO t1 VALUES (1, '20060104', '2321323', -100.00)INSERT INTO t1 VALUES (4, '20060105', '9302221', 612.12)INSERT INTO t1 VALUES (5, '20060105', '0003235', 18.11)INSERT INTO t1 VALUES (6, '20060111', '5953432', 2101.21)INSERT INTO t1 VALUES (3, '20060111', '9402293', -512.44)INSERT INTO t1 VALUES (7, '20060115', '4234444', 44.52)INSERT INTO t1 VALUES (8, '20060115', '0342222', 95.21)INSERT INTO t1 VALUES (6, '20060119', '5953432', -2101.21)INSERT INTO t1 VALUES (2, '20060101', '5440033', 231.01)/* Show what's in the table - just because */SELECT * FROM t1 ORDER BY RegJrnID, InvoiceDate/* Query for records to reverse */SELECT *FROM t1 a/* Ignore records that have already been reversed */WHERE a.RegJrnID != ALL/* This subselect finds reversed records (i.e. those that have aduplicate RegJrnID) */(SELECT b.RegJrnIDFROM t1 bGROUP BY b.RegJrnIDHAVING COUNT(*) > 1)/* User selection criteria are appended here *//* AND InvoiceNumber >= '5000000' AND InvoiceNumber <= '7500000' *//* Make the results look pretty (optional) */ORDER BY RegJrnID/* Housekeeping */DROP TABLE t1
Hello Expert,Here I am asking your help.I have a table with following data:TaskID ParentTaskID TaskName ProjectName1 1 BA Rail2 22 FA Financial3 1 BA.1 Rail4 1 BA.2 Rail5 22 FA.1 Financial6 22 FA.2 FinancialNow I want the following format:ID ParentID Name1 1 Rail2 1 BA3 2 BA.14 2 BA.25 22 Financial6 5 FA.17 5 FA.2I need to create following hierarchy if I could tranform the data theabove way in the Project Dimension:Rail--BA----BA.1----BA.2Financial--FA----FA.1----FA.2Please help and thanks in advance,Soumya
