SQL Server 2012 :: Can Use Lag To Avoid Recursive CTEs?
May 13, 2014
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.
I am currently using 5 separate CTEs and I am wondering if I can use only 1 cte. What I am listing below is what 2 ctes looks like. Thus can you tell me if I can use the same cte that is listed in the sql below? If so, how to change the sql listed below to use only 1 cte?
Merge TST.dbo.LockCombination AS LKC1 USING (select LKC.lockID,LKC.seq,A.lockCombo2 from [LockerPopulation] A JOIN TST.dbo.School SCH ON A.schoolnumber = SCH.type
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.
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.
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 need to use WHILE to avoid Cursor under certains conditions.
My SELECT statement is:
SELECT ref, ano, numberofyears ,nreint, naoreint,degress, tabela, tax, taxamaxima,[evactual], [evaldepact],[ereintact],nrregbt,[taxAmtAno] FROM deprec ORDER BY [ref] ASC
numberofyears= 100 /tax for exemple for a good where lifecycle is 4 years ,ex: Tax = 25% Then 100/25 = 4 years
I see this WHILE script, but i need to run :
1. for each REF + Until years < 4 in this exemple, because i have goods years depend on Percent.
the WHILE script i see is:
DECLARE @table1 TABLE (Id int not null primary key identity(1,1), col1 int ) INSERT into @table1 (col1) SELECT col1 FROM table2 SET @num_rows=@@ROWCOUNT
SET @cnt=0 WHILE @cnt<@num_rows
[Code] .....
My doubt is how to make the LOOP for each REF until Year < 4 (like my example)
I faced this issue regarding the SQL Server 2005 CTEs
Hope to get an explanation here is the code,
Code Snippetwith MyCTE(i) as ( select i = 1 union all select i = i + 1 from MyCTE where i < 10 ) select i from MyCTE order by i
this will display the numbers from 10 rows containing values from 1 to 10, although the where clause is so clear i<10 if we replace it with i<=10, it will display 11 rows from 1 to 11
So as we know the recursion process here is limited by default to 100 (we can change it using option (maxrecursion n) ),this can yield that it is not limited to 100 but 101 even u changed the limit to 1000 it will not be 1000 but 1001, but this may be correct as the recursion counting is began after union all so it should be 100 and display 101 in the result set but my question is clear above (the max value displayed and the where clause)
And works perfectly, but ... how to make sure every item has an element "nodes" ? The case here is for the child leafs obviously. This, because on the client i have to inject this element "nodes" on a json version of this xml, and just wanted to avoid normalizing the structure on the client.
For the root I am using
FOR XML PATH('root'),TYPE; and for the hierarchy that follows FOR XML RAW ('node'), root('nodes'), ELEMENTS
I am trying to use CTEs so as to be able to reuse the results for subsequent queries. But I am having trouble with the syntaxes for CTEs and could do with some help here
Basically, this is how my final query would look like.
Code SnippetWITH T1 AS (SELECT id, parent_id, end_desc_id FROM GO WHERE (parent_id = - 1)) WITH T2 AS (SELECT g.id, g.parent_id, g.end_desc_id FROM GO AS g, T1 AS t WHERE g.parent_id = t.id) SELECT G.* FROM GO G, T2 as T WHERE G.id>=T.id AND G.id<=T.end_desc_id ORDER BY G.id, T.id
GO is defined as (id, parent_id, end_desc_id, path, value)
I am exporting the data from database to an excel template that will have 100+ columns and approx 4000 rows of data. Then the business user will make changes to some columns without modifying primary key columns and will send back to us where we will update the same to database.
In order to this am using an excel template by protecting the primary key columns with a password protection.
At template level am fine and whenever am trying to modify any primary key column it's not allowing and am totally good there. But when I use that excel template as a destination to load data from SSIS, all the protected columns are no longer protected and i could able to make changes.
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 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.
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?
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 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
I have a database in sql server 2000. In this i have used identity(seed,increment) property so that unique ids may be available. Now if requests from diffrent users arrives at same time on the server, will there be a conflict because of ids?
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 am new to installation of SQL Server 2005..I wanted to know while selecting Service Account Screen why Avoid running SQL Server Agent as the Local System account.????
We are running a shopping mall in Korea and got a database including a table of 4 million product prices, which is to be updated hourly basis. Updating 4million records requires at least 10 minutes to complete. During the update, our shopping mall exposed to customers does not respond quickly in fact very very slowly and we investigated and found out that many tables of SQL database during the update were being locked. As you know, site speed is top priority. We studied and found out that there are two ways to avoid having locked tables during update, those are "read uncommitted" and "snapshot" using the following lines.
set transaction Isolation level read uncommitted set transaction Isolation level snapshot
We tried numerous times the above two lines and still find our tables being locked during update and our customers are being disappointed.
My questions:
1. Is it possible at all in view of "the state of the art" to avoid having locked tables during update of 4 million records ?
2. if it is possible, would you please teach me like I am the beginner of database studies?
For your information, we are using 2005sql (64bit) in Windows 2003 (64bit).
i'll try to explain the problem with my bad english, so sorry.
In my firm i have a server (W2003 Server) with IIS 6.0 and SQL Server 2005 EE an it is joined in the main domain as all users and their workstation (window XP) are joined too.
The main role for this server is to be a web server and with IIS Manager I configured to verify user identities with Windows Integrated authentication without any anonymous access to web pages (ASP scripts).
I made a group called "Web Users" with read right on ASP scripts and his only one member is "Domain Users".
"Web Users" has of course rights to access this server from network.
This group is also a SQL Server login because it has rights to wiew an modify data at least in one db table trought ASP pages using ADO, but the problem is that they can also connect using ODBC.
Configuring SQL to accept only local connection i can avoid this, but as SQL Admin i can't connect too. So how is possible for web users interact with SQL only trought web pages ? Thank you very much
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
select distinct case when LastStatusMessageIDName = 'Program completed with success' then 'Office 2013 SP1 Installed Successfully' when LastExecutionResult = '2013' then 'Machine Does not have Office 2013' when LastExecutionResult = '17023' then 'User cancelled installation' when LastExecutionResult = '17302' then 'Application failed due to low disk space.'
[Code] .....
The below is the output for the given query,here i want to see only one comment value in my list and the count is also sum of all where comment should be Application will be installed once machine is online(Bold columns o/p)
Comment Machine Name Application will be Installed once machine is Online 4 Application will be Installed once machine is Online 12 Application will be Installed once machine is Online 42 Application will be Installed once machine is Online 120 Machine Does not have Office 2013 25 User cancelled installation 32 Application failed due to low disk space 41 Office 2013 SP1 already Exist 60
I need o/p like below:in single line
Application will be Installed once machine is Online 178 Machine Does not have Office 2013 25 User cancelled installation 32 Application failed due to low disk space 41 Office 2013 SP1 already Exist 60
We are using Microsoft Reporting Service 2005 to develop reports and we are accessing these reports through a J2EE application. The front end is implemented using Tapestry and we using JBoss as our applicaiton server. We are using Shared Data Sources for the reports and we set its data source type to SQL Server Analysis Services. In the credential tab, by default "Use Windows authentication" is selected. All other options are disabled.
When I access my reportserver through my web application, I am always prompted for a windows login and password. How can I avoid being shown the windows login prompt, since our web application will be used by several users and we do not want the users to type in a username/password everytime they want to access our reports.
