Recursive Data From Two Different Table
Oct 2, 2007
Hi All,
Im trying to retrieve multiple recursive data from two different columns with different conditions.
This is what I wrote.
WITH days15CTE AS
(
SELECT Days = 'From 1 to 15 Days'
, Num_Loans = count(DISTINCT AccountID)
, PBO = sum(Balance)
FROM Loan
WHERE CommencedOn >= GETDATE() - 15
, Loanstatus = Status
FROM LoanApplication
WHERE Status = 'Deliquent'
)
, days30CTE AS
and rest...........
But I get this error message.
There is an error in the query. Incorrect syntax near ','.
Incorrect syntax near ','.
Incorrect syntax near ','.
Can any body let me know where I went wrong please?
View 5 Replies
ADVERTISEMENT
Jan 10, 2014
With my new data, need result for below scenario ( use CTE )
´`````````````
CREATE TABLE
´´´´´´´´´´´´´´´´
CREATE TABLE [dbo].[Travel_Master](
[Load_Id] [int] NULL,
[Mode_Id] [nchar](2) NULL,
[Mode_Info] [nchar](10) NULL,
[Code] ....
===============================
Insert DATA TABLE 1
===============================
INSERT INTO [dbo].[Travel_Master] ([Load_Id] ,[Mode_Id] ,[Mode_Info] ,[Has_Nodes]) VALUES ( 1,'AP' ,'AIR' ,'Yes')
INSERT INTO [dbo].[Travel_Master] ([Load_Id] ,[Mode_Id] ,[Mode_Info] ,[Has_Nodes]) VALUES ( 1,'FL' ,'FLIGHT' ,'Yes')
INSERT INTO [dbo].[Travel_Master] ([Load_Id] ,[Mode_Id] ,[Mode_Info] ,[Has_Nodes]) VALUES ( 1,'FP' ,'FLIGHT-Pas' ,'No')
INSERT INTO [dbo].[Travel_Master] ([Load_Id] ,[Mode_Id] ,[Mode_Info] ,[Has_Nodes]) VALUES ( 1,'SE' ,'SEA' ,'Yes')
INSERT INTO [dbo].[Travel_Master] ([Load_Id] ,[Mode_Id] ,[Mode_Info] ,[Has_Nodes]) VALUES ( 1,'SP' ,'SHIP' ,'No')
INSERT INTO [dbo].[Travel_Master] ([Load_Id] ,[Mode_Id] ,[Mode_Info] ,[Has_Nodes]) VALUES ( 1,'RD' ,'ROAD' ,'No')
INSERT INTO [dbo].[Travel_Master] ([Load_Id] ,[Mode_Id] ,[Mode_Info] ,[Has_Nodes]) VALUES ( 1,'BU' ,'BUS' ,'No')
===============================
Insert DATA TABLE 2
===============================
INSERT INTO [Travel_Quantity]([Load_Id],[Mode_Sno],[Mode_Id],[Mode_Parent_Sno],[QA]) VALUES ( 1,'1' ,'AP' ,'-1','4' )
INSERT INTO [Travel_Quantity]([Load_Id],[Mode_Sno],[Mode_Id],[Mode_Parent_Sno],[QA]) VALUES ( 1,'2' ,'FL' ,'1','2' )
INSERT INTO [Travel_Quantity]([Load_Id],[Mode_Sno],[Mode_Id],[Mode_Parent_Sno],[QA]) VALUES ( 1,'3' ,'FP' ,'2','1' )
INSERT INTO [Travel_Quantity]([Load_Id],[Mode_Sno],[Mode_Id],[Mode_Parent_Sno],[QA]) VALUES ( 1,'4' ,'SE' ,'-1','0' )
INSERT INTO [Travel_Quantity]([Load_Id],[Mode_Sno],[Mode_Id],[Mode_Parent_Sno],[QA]) VALUES ( 1,'5' ,'SP' ,'4','1' )
INSERT INTO [Travel_Quantity]([Load_Id],[Mode_Sno],[Mode_Id],[Mode_Parent_Sno],[QA]) VALUES ( 1,'6' ,'RD' ,'-1','6' )
INSERT INTO [Travel_Quantity]([Load_Id],[Mode_Sno],[Mode_Id],[Mode_Parent_Sno],[QA]) VALUES ( 1,'7' ,'BU' ,'6','3' )
========
RULE
========
1.QA of Parent Row value Greater than 0- Consider Parent and 1st Node.From the available result,
2.HasNodes = Yes, Display Parent and 1st Node
3.HasNodes = No, Display Parent Only.
================
Expected Result
================
Mode_Info|Mode_Detail|QA
AIR||4
AIR|FLIGHT|2
ROAD||6
===============
My Query
=============
learning CTE To apply for HasNodes Rules.
;with MyCTE1 as (
select
CONVERT(nvarchar(MAX),RTRIM(T2.Mode_Info)) Mode_Info, T1.Mode_Parent_Sno, T1.Mode_Sno, T1.QA ,T2.Has_Nodes
from [Travel_Quantity] T1
left join [Travel_Master] T2 on T1.Mode_Id = T2.Mode_Id
) ,
MyCTE2 as (
[code]....
View 4 Replies
View Related
Aug 25, 2015
Msg 240, Level 16, State 1, Line 14
Types don't match between the anchor and the recursive part in column "ParentId" of recursive query "tmp". Below is query,
DECLARE @TBL TABLE (RowNum INT, DataId int, DataName NVARCHAR(50), RowOrder DECIMAL(18,2) NULL, ParentId INT NULL)
INSERT INTO @TBL VALUES
(1, 105508, 'A', 1.00, NULL),
(2, 105717, 'A1', NULL, NULL),
(3, 105718, 'A1', NULL, NULL),
(4, 105509, 'B', 2.00, NULL),
(5, 105510, 'C', 3.00, NULL),
(6, 105514, 'C1', NULL, NULL),
[code]....
View 2 Replies
View Related
Jul 23, 2005
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
View 4 Replies
View Related
Apr 15, 1999
I am using tables with recursive relationships extensively. For example the table tbComponent has a primary key called Co_ID and a foreign key called Co_Co_ID which references the field Co_ID. This allows a component to have unlimited child components, and each child component can have an unlimited number of tiers of children. I have a few question for which I have seen no documentation on.
1. How can I create a view or a SP that will return a component record and all of its children and children's children records down to the last/lowest child record?
2. I need to be able to do a search in this table. Example:
Table: tbComponent
Columns: Co_ID Integer
Co_Co_ID Integer
Co_Name Text
Co_Attribute Text
Co_Category Text
Note: the data for Co_Category comes from a lookup table with also has a recursive relationship to itself where a category can have an unlimited number of tiers of children categories.
A typical group of records could be something like this:
Co_ID Co_Co_ID Co_Name Co_Attribute Co_Category
1 1 Car Blue Ford
2 1 Body Steel Parts
3 2 Door Front Parts
4 3 Invoice April 1 1999 Accounting
Ok, say there is over a million records in this table. Say I want to query this table and return all of the invoices for cars between March 1 1999 and May 1 1999. Say for example that there are less records where Co_Name has a value of Car then there are records with a value of Invoice, so logically I would set some kind of criteria to limit only invoices with where Co_Name = "Car". That's easy, I can return a result set of all the records Where Co_Name = "Car" and I can hold these in a view or a temp table. Now I need to query this View or temp table and see if it has any children records records Where Co_Name = "Invoice" . The problem is that the Invoice child could be a child record directly under the "Car" record, or 10 levels of children records down. The logic for this would be:
If Co_Name = "Car" Then Select * Where Co_Co_ID = 1, then take all of those record's values in Co_ID and run another statement Where Co_Co_ID = X and so on until there are no more children to search. If any of those records have a value of Invoice in Co_Name then return them.
Please don't give any speeches on Normal Relationship Database design suggesting a typical design using something like a Car table, a parts table, and a invoice table. I used the example above to demonstrate the dynamics of doing a search on a recursive relationship. I understand relationship theory and a recursive relationship is what we need to use in our situation.
Thanks for any and all input!
View 1 Replies
View Related
May 16, 2006
I am fairly new to SQL and I am currently trying to createa SQL table (using Microsoft SQL) that has a recursiverelationship, let me try to explain:I have a piece of Data let's call it "Item" wich may again contain onemore "Items". Now how would I design a set of SQL Tables that arecapable of storing this information?I tried the following two approaches:1.) create a Table "Item" with Column "ItemID" as primary key, somecolums for the Data an Item can store and a Column "ParentItemID". Iset a foreign key for ParentItemID wich links to the primarykey"ItemID" of the same table.2.) create separate Table "Item_ParentItem" that storesItemID-ParentItemID-pairs. Each column has a foreign key linked toprimary key of the "Item" Column "ItemID".In both approaches when I try to delete an Item I get an Exceptionsaying that the DELETE command could not be executed because itviolates a COLUMN REFERENCE constraint. The goal behind these FK_PKrelations is is that when an Item gets deleted, all childItems shouldautomatically be deleted recursively.How is this "standard-problem" usually solved in sql? Or do I inned toimplement the recursive deletion myself using storedprocedures or something ?
View 3 Replies
View Related
Apr 18, 2008
I have a recursive query, using common table expressions, like this:
WITH TaskHierarchy (GUID, ParentGUID, Title, Complete, HierarchyLevel)
AS
(
SELECT GUID, ParentGUID, Title, Complete, 1 HierarchyLevel
FROM Task
WHERE ParentGUID = @GUID
UNION ALL
SELECT t.GUID, t.ParentGUID, t.Title, t.Complete, th.HierarchyLevel + 1 HierarchyLevel
FROM Task t
INNER JOIN TaskHierarchy th
ON t.ParentGUID = th.GUID
)
SELECT (COUNT(*) - SUM(CAST(Complete AS INT))) Outstanding FROM TaskHierarchy
The result is a number. I need access to this number. Ideally, I would like to store it in a variable, but anything would work as long as I can access it after the query.Anyone know of a way?
View 1 Replies
View Related
Jul 20, 2005
I have a database which stores information about organisms collectedduring sediment toxicology research. For each sample, organisms insediment are collected and identified taxonomically (Order, Family,Genus, Species).Taxonomy lookup information in the database is stored in a recursivetable in the form:TSN (taxa serial number)Rank (Order, Family, Genus, Species)NameParent_TSN (related Taxa at higher taxonomic level)When the number of a particlar organism collected is entered into thedatabase, the count is stored along with the lowest level TSN theorganisms were identified to.Okay - now the problem. Depending on the type of analysis being done,a user may want organism counts at the lowest level, or rolled up to ahigher taxonomic level (usually Family). Can I write a recursivefunction which will cycle through the Taxonomy database, and providethe name of the organism at the appropriate taxonomic level? Is this areasonable approach with regard to speed and efficiency?Something Like:SELECT sample_id, 'Get Name Function(Rank, TSN)', Sum([count]) ASNoTaxa FROM dbo.tblbenthicResults could then be grouped and summed on the Name, to summarisedata for each sample/taxa.Is this a reasonable approach? Or is there a better one? Did I explainthe problem well enough?Thanks in advance,Tim
View 6 Replies
View Related
Sep 20, 2015
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
View 5 Replies
View Related
Feb 25, 2008
Hello, I am hoping someone can help me in this. I am looking to write a stored procedure that will return the heirarchy of an organization. I will display how the heirarchy might look and then list the tables involved.
John Smith
- Jacob Jones
- Lisa Thompson
- Samuel Barber
- Paul Smith
- John Jackson
Ok, so Jacob, Lisa, an Samuel report up to John Smith. Paul and John Jackson report up to Samuel Barber.
Here are the tables:
Users holds the user_id, first_name, last_name, and reports_to_user_id.
User_Roles holds the user_id, role_type_id
Role_Types holds the role_type_id, and the type (which could be Administrator, Standard, Guest) for example. In addition, Role_Types also has ranking which must be taken into consideration as well. 1 being the top rank and 9 being the lowest.
Thanks very much in advance,
Saied
View 12 Replies
View Related
Jun 22, 2015
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.
View 5 Replies
View Related
May 22, 2008
Hi All,
I have a requirement here to import data from XML file to SQL Database. The XML schema contains of various elements and one of the element is recursive ie. Parameter node contains parameter node within it and it can have n number of iterations. I have given the sample schema below:
<xs:element minOccurs="0" name="Parameter">
<xs:complexType>
<xs: sequence>
<xs:element minOccurs="0" name="ID" type="xs: string" />
<xs:element minOccurs="0" name="Description" type="xs: string" />
<xs:element minOccurs="0" name="Type" type="xs: string" />
<xs:element minOccurs="0" name="Parameter">
<xs:complexType>
<xs: sequence>
<xs:element minOccurs="0" name="ID" type="xs: string" />
<xs:element minOccurs="0" name="Description" type="xs: string" />
<xs:element minOccurs="0" name="Type" type="xs: string" />
<xs:element minOccurs="0" name="Parameter">
<xs:complexType>
<xs: sequence>
<xs:element minOccurs="0" name="ID" type="xs: string" />
<xs:element minOccurs="0" name="Description" type="xs: string" />
<xs:element minOccurs="0" name="Type" type="xs: string" />
</xs: sequence>
</xs:complexType>
</xs:element>
<xs:element minOccurs="0" name="Parameter">
...............
</xs: sequence>
</xs:complexType>
</xs:element>
</xs: sequence>
</xs:complexType>
</xs:element>
But all the nodes contain the data which has to be imported to a single table dbo.Parameters. I cannot use Union ALL since i dont know how many iterations I will have in the file. Is there any way to do this operation in Data Flow Task using XML Source? Can anyone help me on this?
Thanks,
Dhileep
View 1 Replies
View Related
Nov 14, 2006
Hey,
I'm new to this whole SQL Server 2005 thing as well as database design and I've read up on various ways I can integrate business constraints into my database. I'm not sure which way applies to me, but I could use a helping hand in the right direction.
A quick explanation of the various tables I'm dealing with:
WBS - the Work Breakdown Structure, for example: A - Widget 1, AA - Widget 1 Subsystem 1, and etc.
Impacts - the Risk or Opportunity impacts for the weights of a part/assembly. (See Assemblies have Impacts below)
Allocations - the review of the product in question, say Widget 1, in terms of various weight totals, including all parts. Example - September allocation, Initial Demo allocation, etc. Mostly used for weight history and trending
Parts - There are hundreds of Parts which will eventually lead to thousands. Each part has a WBS element. [Seems redundant, but parts are managed in-house, and WBS elements are cross-company and issued by the Government]
Parts have Allocations - For weight history and trending (see Allocations). Example, Nut 17 can have a September 1st allocation, a September 5th allocation, etc.
Assemblies - Parts are assemblies by themselves and can belong to multiple assemblies. Now, there can be multiple parts on a product, say, an unmanned ground vehicle (UGV), and so those parts can belong to a higher "assembly" [For example, there can be 3 Nut 17's (lower assembly) on Widget 1 Subsystem 2 (higher assembly) and 4 more on Widget 1 Subsystem 5, etc.]. What I'm concerned about is ensuring that the weight roll-ups are accurate for all of the assemblies.
Assemblies have Impacts - There is a risk and opportunity impact setup modeled into this design to allow for a risk or opportunity to be marked on a per-assembly level. That's all this table represents.
A part is allocated a weight and then assigned to an assembly. The Assemblies table holds this hierarchical information - the lower assembly and the higher one, both of which are Parts entries in the [Parts have Allocations] table.
Therefore, to ensure proper weight roll ups in the [Parts have Allocations] table on a per part-basis, I would like to check for any inserts, updates, deletes on both the [Parts have Allocations] table as well as the [Assemblies] table and then re-calculate the weight roll up for every assembly. Now, I'm not sure if this is a huge performance hog, but I do need to keep all the information as up-to-date and as accurate as possible. As such, I'm not sure which method is even correct, although it seems an AFTER DML trigger is in order (from what I've gathered thus far). Keep in mind, this trigger needs to go through and check every WBS or Part and then go through and check all of it's associated assemblies and then ensure the weights are correct by re-summing the weights listed.
If you need the design or create script (table layout), please let me know.
Thanks.
View 4 Replies
View Related
Dec 23, 2004
we have a table called evaluation_questions, the table has following fields
queId,Question, level, parentId
the queId is primary key(auto number),where as the field "question" will have question, heading or the subheading,
the level describes the hierarchy of the field "Question", 0 means its a heading, 1 means a subheading and 2 means a question.
where as the parentId means describes the immediate parentId, like if it is 1, then the parent will be English heading....
queIdQuestionlevelparentId
1English00
2Reading11
3Writing11
4recognizing words12
5Fluency22
so English is a heading as level is 0 and has no parent as parentId is also 0.
Reading is a subheading as level is 1 and has a prent English as its parentId=1 which is the queId oof English
same is the case with writing
where as recognizing words and fluency both are questions as the level is 2 and their parentId is 2 which means they come under reading.
Output:
Now What i want is to retrieve the all the questions and headings under a specified heading.like if i pass parentID as a parameter to stored
procedure i should get all the headings and questions under a specified parentID.i need to fill the dataset with it.
View 3 Replies
View Related
Sep 20, 2007
I am attempting to create a stored procedure that will launch at report runtime to summarize data in a table into a table that will reflect period data using an array type field. I know how to execute one line but I am not sure how to run the script so that it not only summarizes the data below but also creates and drops the table.
Any help would be greatly appreciated.
Current Table
Project | Task | Category | Fiscal Year | Fiscal Month | Total Hours
---------------------------------------------------------------------------------------------------------
Proj 1 | Task 1 | Cat 1 | 2007 | 01 | 40
Proj 1 | Task 1 | Cat 2 | 2007 | 02 | 20
Proj 1 | Task 1 | Cat 3 | 2007 | 03 | 35
Proj 1 | Task 1 | Cat 1 | 2008 | 01 | 40
Proj 1 | Task 1 | Cat 2 | 2008 | 02 | 40
Proj 1 | Task 1 | Cat 3 | 2008 | 03 | 40
Proposed Table
Project | Task | Category | Fiscal Month 01 | Fiscal Month 02 | Fiscal Month 03 | Fiscal Year
---------------------------------------------------------------------------------------------------------------------------------------------------
Proj 1 | Task 1 | Cat 1 | 40 | 0 | 0 | 2007
Proj 1 | Task 1 | Cat 2 | 0 | 20 | 0 | 2007Proj 1 | Task 1 | Cat 3 | 0 | 0 | 35 | 2007
Proj 1 | Task 1 | Cat 1 | 40 | 0 | 0 | 2008
Proj 1 | Task 1 | Cat 2 | 0 | 40 | 0 | 2008
Proj 1 | Task 1 | Cat 3 | 0 | 0 | 40 | 2008
Thanks,
Mike Misera
View 6 Replies
View Related
Aug 18, 2015
How to purge data in transaction table or we can delete some data and store in separate table in data warehouse?
View 7 Replies
View Related
Feb 9, 2007
I need to help in writing stored procedure to recursively delete Categories and ads for those categories
Simplified table views:
Category: CategoryID, Name, ParentCategoryID
Ads: AdID, Name, CategoryID
Please help
View 16 Replies
View Related
Jun 1, 2007
Hello Everyone,
I have a purchase order table that holds say 2 columns. PO and OrgPO. That is Purchase Order # and Original Purchase Order # respectively. Assume i have the following rows in the table.
PO OrgPO
-- ------
po1 NULL
co1 po1
co2 co1
co3 co2
po2 NULL
cpo1 po2
po3 NULL
Now what i would like to report in the output is the PO and along with the lastly generated change order for that po. For eg,
PO LastCO
-- ------
po1 co3
po2 cpo1
po3 po3
Currently i 'm using function to achieve this effect and i believe this is not the efficient way. I would like to generate this in a much efficient way. Please help me to achieve this.
View 2 Replies
View Related
Jun 11, 2004
Hi
I have problem is getting list of all the Tree Level Employees.here is the my table structure
Manager ~ SubOrdinate
1 ~ 2
1 ~ 3
1 ~ 4
2 ~ 5
2 ~ 6
2 ~ 7
3 ~ 8
3 ~ 9
4 ~ 10
5 ~ 11
SO ON
I NEED THE WAY , HOW I CAN GET THE HIRE
LEVELS ( EG IF I PASS MANAGER(EMPLOYEE NO) 1 IT SHOULD DISPLAY HIS 1 LEVELS AND 2, 3 SO ON LEVELS OF LEVELS
OUTPUT COULD BE ,
PASSED EMPLOYEE NO : 1
LEVEL ~ EMPLOYEE
1 ~ 2
1 ~ 3
1 ~ 4
2.1 ~ 5
2.1 ~ 6
2.1 ~ 6
3.1 ~ 7
SO ON THAT EMPLOYEE RELATED INFORMATION
( ITS LIKE MLM LEVELS)
CAN ANYBODY HELP IN THE ,
THANKS
View 3 Replies
View Related
May 12, 2006
is there any way to define a recursive cte within another cte. I have defined a recursive cte which works great for finding the different versions of a given item. What I would like to do is to define a set of items for which I want to find the different versions.
View 1 Replies
View Related
Jun 10, 2007
Hello,
I have the following tables:
Article(articleID,CategoryID,ArticleTitle)
Categories(categoryID,ParentID,CategoryTitle)
I am trying to retrieve the main category ID for a specific article ID.
For example lets say I have this data:
Article:
1, 10 , "some title"
2,10,"some title"
3,11,"some title"
Categories:
1, NULL , "some title"
2, 1, "some title"
10, 2, "some title"
11, 10 , "some title"
In this example I want to know who is the main category of article 3.
The query should return the answer: 1
Thats because:
The article ID 3 is inside category 11.
Parent for category 11 is 10.
Parent for category 10 is 2.
Parent for category 2 is 1
and Parent for category 1 is NULL, which means category 1 has no parents and it is the main category.
Query will return article id, category id, main_category_id, ArticleTitle, CategoryTitle (some union between 2 tables)
Do you have any suggestions for such query?
Thanks all.
View 1 Replies
View Related
Jan 18, 2008
Recursive quey to show products with "custom defines fields" related by Classifications, instead of per product Hello, I’m working on a project .. .
I’m desperating due to the
complex (for me and also I think for some others) sql query that I need to
write, to show the products with his “custom defined fields� that are inside a ProductsFieldsByClassification
table that holds this mentioned “custom defined fieds� according to the Classifications
table, on where the Products can be found trought the productsClassifications
table.
CustomFields can be defined
and set for the products, trought his Classifications (instead of define a
custom field for each product (that consume a lot of data), I decide to use it as
I explain)
I will to know the properly
SQL QUERY to show a list of products with the ProductsFieldsByClassifications and ProductsFieldsValuesByClassifications:
As example on
a Requested ID_Classification = 16 (Torents/Games/Dreamcast/PAL), the products must be show with the
ProductsFields and Values that has the DBA for the:
·
requested
ID_Classification
o
PAL (ID_Classification: 16)
·
AND all
the Classifications that belongs above (trought ID_ParentClassification) that are :
o
Torrents (ID_Classification:
1) that will show the products values for the “Size�
o
Games (ID_Class..:4) ß this classification has no CustomFields so none from this one.
o
Dreamcast (ID_Class..:14 )
that will show his ID_Classification(14) product field “Levels� value (but not “AllowSave� as not have value for any product)
Hmnn i show a graphic that i design for (feel to click over to see at correct resolution)
I also write asp.net tutorials. For those interested see my blog at http://abmartin.wordpress.com
View 2 Replies
View Related
Feb 26, 2008
i have a table like this
parentid | childid | description
1 2 blah
1 3
1 4
2 23
2 24
5 8
3 10
and i want to give the parentid 1 and get all the children
i have a cursor now like this but i dont know how to make it recursive any help?
1
2 DECLARE @childid nvarchar(50)
3 DECLARE ItemStruc CURSOR FOR
4
5 SELECT cmponent_prt_no , parent_part_no
6 FROM oauser_prod_structure
7 WHERE parent_part_no = @parentid
8
9 OPEN ItemStruc
10 FETCH NEXT FROM ItemStruc
11 INTO @childNum , @parentid
12
13 WHILE @@FETCH_STATUS = 0
14 BEGIN
15
16 print @childNum +'is the a child to: ' +@parentid
View 12 Replies
View Related
Sep 21, 2004
I have a tough issue with a query.
I have the following structure
Table: Users
UserId ParentUserId
1 1
2 1
3 2
4 1
5 4
6 5
I need to write a stored procedure that takes in UserId as parameter and returns everyone under him/her.
So if Param=1 it would return result set of 2,3,4,5,6
Anyone done this before or have any ideas?
Thanks,
ScAndal
View 1 Replies
View Related
Jul 20, 2005
Hi,
Does anyone know how to do an sql recursion queries?
I believe it involves a view with a union.
I have a User Table and in that table i have a employee_id and a
boss_id. What i'd like to do is to find all employees under a certain
boss.
For example,
Employee_ID Boss_ID
1
2 1
3
4 3
5 2
So if i'd like to know who are under the employee_id = 1 it will return
employee_id 2 and 5 since employee 2 also is the boss of employee_id =
5.
To do that i'd have to have recursion query.
Thanks,
View 2 Replies
View Related
Oct 4, 2005
I have a list of categories that I have to find the path to each my table is set up like ID CategoryNum CategoryName ParentID 1 AA Top Level 1 02 AB Top Level 2 03 BA Second Level 1 1I need my query/stored proc to return Tope Level 1/Second Level 1 if I pass in the integer of 3I can do this in programming but cant seem to wrap my head around it in SQL ServerTIA for the help
View 6 Replies
View Related
Dec 14, 2000
I'm using exec sp_dboption 'ilgadmin', 'recursive triggers',true
I made
create trigger
dbo.templates_ondelete
on templates for delete
as
begin
delete pages
where templateid = (select [id] from deleted)
end
go
create trigger
dbo.pages_ondelete
on pages for delete
as
begin
delete pageItems
where pageid = (select [id] from deleted)
end
go
The second trigger must be started by the first one.
But it doens't work because if I do
delete from templates where id = 2 more than one page (7 pages) are deleted and the second trigger doesn't work (it can only delete one by one in pageitems!)
Is there any option in SQL Server that I forgot or can I use an other methode
Best regards
Axel
View 2 Replies
View Related
Nov 24, 1999
Hey there, Hopefully someone has a sec to help me out. I have a pretty tough query here for ya.
I want to turn this:
Parent | Child
Fred | Mark
John | Cindy
Mark | John
John | Suzy
into this:
Ancestor | Level | Descendant
Fred | 1 | Mark
Fred | 2 | John
Fred | 3 | Cindy
Fred | 3 | Suzy
Mark | 1 | John
Mark | 2 | Cindy
Mark | 2 | Suzy
John | 1 | Cindy
John | 1 | Suzy
I want to do this in a single select statement. No loops or cursors. Please tell me you are as stumped as I am so that I don't feel so dumb.
Thanks,
Jason
SQL DBA/MCSE
View 1 Replies
View Related
Dec 3, 2006
CREATE TABLE EMPLOYEE(
Initialschar(3)Not Null,
EmpNamechar(25)Not Null,
CellPhonechar(20),
ExperienceLevel char(12)Not Null,
SupervisorInitials char(3)
CONSTRAINTEmployeePKPRIMARY KEY(Initials),
CONSTRAINTExperienceLevelCK CHECK (ExperienceLevel in
('Junior', 'Senior', 'Master', 'SuperMaster')),
CONSTRAINT SupervisorFK FOREIGN KEY (SupervisorInitials)
REFERENCES EMPLOYEE (Initials)
ON DELETE NO ACTION
ON UPDATE NO ACTION
);
INSERT INTO EMPLOYEE VALUES ('JLM', 'Joe Morrone', '(603) 489-2245', 'Master', NULL)
INSERT INTO EMPLOYEE VALUES ('BNF', 'Brenda Fowler', '(603) 467-8894', 'SuperMaster', NULL)
INSERT INTO EMPLOYEE VALUES ('JG', 'John Gromek', '(603) 678-9987', 'Junior', 'BNF')
INSERT INTO EMPLOYEE VALUES ('BEK', 'Barbara Kimball', '(603) 884-2312', 'Master', 'BNF')
Write a query which reports each employee's name and
-- experience level and the name of his/her supervisor
need help with this query,
the ouput is this ;
Employee ExperienceLevel Supervisor
------------------------- --------------- -------------------------
Barbara Kimball Master Brenda Fowler
John Gromek Junior Brenda Fowler
(2 row(s) affected)
Enhance your query to report all employees, whether or
-- or not they have a supervisor.
some help would be gratifull.
View 3 Replies
View Related
Jun 5, 2002
Hi,
I have a table with 2 fields in it Circuit_ID and Parent_Circuit_ID and I'd like to pass a stored procedure a circuit_ID and get a resultset back of all Circuit_ID's that are associated with the original.
That means it needs to look at each record and do another search on all circuits that have that parent ID, recursively.
Does anyone have any good ideas on the best way to do this.
Appreciate any help.
Steve
View 1 Replies
View Related
Nov 1, 2007
I have a table of product types, the table is layed like this:
TypeID
TypeName
ParentID
each type has a ParentID equal to one of the other types TypeID (except the very top level types) to build a hierarchy with several levels.
I need a way of giving a function a single TypeID, and have it return a table that has the the TypeID of every type underneath it in the hierarchy. it could go down several levels (like 10 or more) so it needs to recursively work...
I'm just completely lost...
I can get this to work just fine in VB... but in VB i can just use a nice little for each loop...
any ideas?
View 1 Replies
View Related
Feb 1, 2005
I am trying to write a recursive function which allows me to provide a hierarchical structure from an employee table but I am getting an error
Server: Msg 512, Level 16, State 1, Procedure GetEmployee, Line 10
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
Is there a way in which I can write a recursive function to do this. Or would I need to use cursors, multiple functions???
Any help or examples would be great. Thanks for your help
CREATE FUNCTION GetEmployee(@EmpID as int)
RETURNS @Employee Table
(
StaffNo int,
ParentStaffNo int
)
AS
BEGIN
DECLARE @ManagerID int
SET @ManagerID = (SELECT [id] FROM eii.dbo.test WHERE Parent_ID = @EmpID)
IF @ManagerID IS NOT NULL and @ManagerID > '' and @ManagerID <> @EmpID
INSERT INTO @Employee
SELECT [id],parent_id FROM eii.dbo.test Where [id] = @ManagerID
UNION
SELECT * FROM GetEmployee(@ManagerID)
RETURN
END
View 1 Replies
View Related
Jan 27, 2006
Hey all,
I am writing a procedure to perform a search against products in a website. It is a hierarchical setup, with websites, catalogs, category hierarchies, categories and products. So I need to get all the products that match the search, that are in categories that are in category hierarchies (category-to-category relationship), that are in catalogs that are in 1 website.
I can get the category that a matching product is in, but from there I need to recursive go up the ladder of category hierarchies until it reaches a point where the parent hierarchy is null, and then join that record with the catalogs that are in the 1 website. Here's what I thought might work:
Code:
SELECT ctlgs_CatalogCategoriesToProducts.*
FROM COMMERCE_PRODUCTS
INNER JOIN ctlgs_CatalogCategoriesToProducts ON COMMERCE_PRODUCTS.ITEMNO = ctlgs_CatalogCategoriesToProducts.ItemNo
INNER JOIN ctlgs_CatalogCategoryHierarchies ctlgs_CCH ON ctlgs_CatalogCategoriesToProducts.CategoryID = ctlgs_CatalogCategoryHierarchies.CategoryID
*** some recursive stuff***
WHILE (ctlgs_CatalogCategoryHierarchies.ParentHierarchyID IS NOT NULL)
BEGIN
INNER JOIN ctlgs_CCH ON ctlgs_CatalogCategoryHierarchies.ID = ctlgs_CCH.ParentHierarchyID
END
******
INNER JOIN ctlgs_Catalogs ON ctlgs_CatalogCategoryHierarchies.ID = ctlgs_Catalogs.RootHierarchyID
INNER JOIN ctlgs_CatalogsToWebsite ON ctlgs_Catalogs.ID = ctlgs_CatalogsToWebsite.CatalogID
INNER JOIN ctlgs_Websites ON ctlgs_CatalogsToWebsite.SiteID = ctlgs_Websites.ID
WHERE
ctlgs_Websites.ID = @website
AND COMMERCE_PRODUCTS.BLOCKED = 0
AND (
COMMERCE_PRODUCTS.ITEMNO LIKE @searchTerm
OR COMMERCE_PRODUCTS.DESCRIPTION LIKE @searchTerm
OR COMMERCE_PRODUCTS.DESCRIPTION2 LIKE @searchTerm
)
I've never used WHILE before, so I'm not sure what exactly you can put in there. Any advice would be greatly appreciated, thanks!
View 3 Replies
View Related
