کمک در ساخت یک sql function

[amir3321]

با سلام
می خواستم بدونم کسی می تونه من رو در مورد نوشتن صحیح این تابع بازگشتی کمک کنه من زیاد sql کار نکردم


SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [dbo].[gettreepart] (@parentid bigint ,@productid bigint)
RETURNS @treepartorgin TABLE (partid bigint,[type] nchar(20))
AS

begin
Declare @partid bigint
Declare @type nchar(20)


Declare parts1 Cursor For
Select partid,[type] From dbo.treepartview where parentid = @parentid and productid =@productid and productid2 =@productid
Open parts1
Fetch From parts1 Into @partid,@type



While @@FETCH_STATUS = 0
Begin



Insert Into @treepartorgin (partid,[type])
Values(@partid,@type)
if(@type = 'montage')
begin
select partid,type from dbo.gettreepart(@partid,@productid)
end
Fetch Next From parts1 Into @partid,@type

End


Close parts1
Deallocate parts1
RETURN
end


این تابع می خواهد کل یک درخت رو براساس سرگروه بازیابی کند
من موقعی که می خواهم از خود تابع درون تابع استفاده کنم یک خطا می دهد
اینم جداول

USE [siepold]
GO

/****** Object: Table [dbo].[treepart] Script Date: 03/11/2012 11:13:14 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[treepart](
[partid] [bigint] NOT NULL,
[parentid] [bigint] NULL,
[qtym] [decimal](18, 2) NULL
) ON [PRIMARY]

GO

USE [siepold]
GO

/****** Object: Table [dbo].[part] Script Date: 03/11/2012 11:12:57 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[part](
[id] [bigint] IDENTITY(900000,1) NOT NULL,
[dwgno] [nvarchar](50) NULL,
[descfa] [nvarchar](100) NULL,
[type] [nchar](20) NULL,
[descen] [nvarchar](100) NULL,
CONSTRAINT [PK_part] PRIMARY KEY CLUSTERED
(
[id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

[baktash.n81@gmail.com]

سلام

من برای پیمایش درخت از CTE استفاده می کنم ... تو Help خود SQL کامل و جامع توضح داده ... یه نگاهی بهش بنداز ...
اسکریپت به این شکله ...

USE AdventureWorks2008R2;GOWITH DirectReports(ManagerID, EmployeeID, Title, EmployeeLevel) AS ( SELECT ManagerID, EmployeeID, Title, 0 AS EmployeeLevel FROM dbo.MyEmployees WHERE ManagerID IS NULL UNION ALL SELECT e.ManagerID, e.EmployeeID, e.Title, EmployeeLevel + 1 FROM dbo.MyEmployees AS e INNER JOIN DirectReports AS d ON e.ManagerID = d.EmployeeID )SELECT ManagerID, EmployeeID, Title, EmployeeLevel FROM DirectReportsORDER BY ManagerID;GO

[amir3321]

با سلام من یک کد از نمونه شما تهیه کردم ولی نمی دونم چرا خروجی اون خطا داره

USE siepold ;
GO
WITH parts(partid, parentid, dwgno,[type], levelp)
AS ( SELECT partid, parentid, dwgno,[type], 0 AS levelp
FROM dbo.treepartview WHERE parentid =900265 and productid =10013 and productid2 =10013
UNION ALL SELECT e.partid, e.parentid, e.dwgno,e.[type], levelp + 1
FROM dbo.treepartview AS e
INNER JOIN parts AS d ON e.parentid = d.partid )
SELECT distinct partid,parentid,[type], dwgno, levelp
FROM parts order BY parentid;
GO



این کد بدون خطا اجرا می شود ولی فکر کنم با توجه به تعداد بیشتر خروجی نیاز به یک تغیر دیگر هم داشت مثل کد زیر

USE siepold ;
GO
WITH parts(partid, parentid, dwgno,[type], levelp)
AS ( SELECT partid, parentid, dwgno,[type], 0 AS levelp
FROM dbo.treepartview WHERE parentid =900265 and productid =10013 and productid2 =10013
UNION ALL ( SELECT e.partid, e.parentid, e.dwgno,e.[type], levelp + 1
FROM dbo.treepartview WHERE productid =10013 and productid2 =10013 )AS e
INNER JOIN parts AS d ON e.parentid = d.partid )
SELECT distinct partid,parentid,[type], dwgno, levelp
FROM parts order BY parentid;
GO

ولی به شرط دوم که گذاشتم ایراد می گیرد ممکنه یک نگاهی به این کد بیندازید
و یا اینکه چطور می تونم در select اخر partid رو بصورت کامل distinct کنم الان این کار بطور کامل انجام نمی شود چون ممکنه parentid تکراری هم داشته باشد
با تشکر

[baktash.n81@gmail.com]

خوب دستورات که از نظر Syntax مشکل نداره ... اگه پیغام خطا رو بزاری ممکنه بتونم کمکت کنم ...

در مورد اینکه نتایج Select آخر رو Distinct کنید ... کلا Distinct باید کل رکورد یکسان باشه تا نمایشش نده کنه ...

شما بگو دقیقا چه خروجی می خوای من کد رو برات تغییر بدم ...

[amir3321]

با تشکر از جناب بکتاش عزیز
من این کوئری رو داخل یک ویو ریختم و با جابجایی محل شرط مشکل حل شد و لی با توجه به نظر شما اگر مثلامن فیلدlevelp رو به خروجی اضافه کنم چون از یک قطعه ممکنه در دوسطح استفاده شده باشد پس دوبار partid رو تکرار می کنه
ممنون می شوم یکم راجع به این روش برام توضیح بدهید نحوه کارکرد این cte چگونه است یکم برام گنگه

WITH parts AS (SELECT partid, parentid, dwgno, type, 0 AS levelp
FROM dbo.treepartview
WHERE (parentid = 900265) AND (productid = 10013) AND (productid2 = 10013)
UNION ALL
SELECT e.partid, e.parentid, e.dwgno, e.type, d.levelp + 1 AS Expr1
FROM dbo.treepartview AS e INNER JOIN
parts AS d ON e.parentid = d.partid
WHERE (e.parentid <> 900266))
SELECT DISTINCT partid
FROM parts AS parts_1

[baktash.n81@gmail.com]

یه ذره باهاش کار کنی یواش یواش متوجه می شی ... در این مورد که یک پیمایش درخت هست .. ما رکورد اول را می گیریم (در Select اول) بعد می بینیم این رکورد پدر چه رکوردهایی هست نتیجه رو با رکورد اول اجتماع می کنیم ... و این کارو انقدر تکرار می کنیم که دیگه رکورد ما پدر هیچ رکوردی نباشه ... یه جورایی شبیه تابع بازگشتی ه ...

[amir3321]

یک سوال داشتم جناب بکتاش
می خواستم بدونم اگه بخواهم خود سر مجموعه اصلی رو هم به جواب اضافه کنم چطور می تونم این کار رو انجام بدهم یعنی parentid رو
و دیگر اینکه اگر بخواهم مقداری رو بصورت آبشاری از سرمجموعه تا زیر مجموعه آخر در هم ضرب کنم تا qty نهایی هر partid رو بدست بیارم چکار باید بکنم
این sp رو نوشتم خوب خطا دارد


USE [siepold]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO



-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
alter PROCEDURE [dbo].[getchildofparent]

@partid bigint ,
@parentid bigint,
@productid bigint


AS
BEGIN
begin tran

SET NOCOUNT ON;

-- set @serial = (select maxid from tblmaxid where tablename ='serialfiles')+1

WITH parts AS (SELECT partid, parentid, qtym, type, 0 AS levelp
FROM dbo.treepartview
WHERE (parentid = @parentid)
UNION ALL
SELECT e.partid, e.parentid,( e.qtym * d.qtym)as qtym, e.type, d.levelp + 1 AS Expr1
FROM dbo.treepartview AS e INNER JOIN
parts AS d ON e.parentid = d.partid
WHERE (e.parentid <> @partid) )

SELECT DISTINCT parts_1.partid,parts_1.qtym, dbo.part.dwgno, dbo.part.type, dbo.part.descfa, dbo.part.descen
FROM parts AS parts_1 INNER JOIN
dbo.part ON dbo.part.id = parts_1.partid
where parts_1.partid <> @partid


if @@Error = 0
Commit Tran
Else
Rollback Tran

END



وقتی چک کردم این خطا را می دهد
Types don't match between the anchor and the recursive part in column "qtym" of recursive query "parts".

باز هم متشکرم

[baktash.n81@gmail.com]

خوب این خطا مربوط به فیلد qtym هست دیگه ... نوعش تو دوتا Select یکسان نیست ... ببین مشکلش از کجاست ... برای حلش می تونی از دستور Cast برای یکسان کردنشون استفاده کنی ... ممکنه وقتی ضربش می کنی نوعش رو عوض کنه .. تو با Cast برشگردون به همون چیزی که باید باشه ...

[amir3321]

با سلام خدمت جناب بکتاش
هردو فیلد qtym از یک نوع است چون از یک جا داره خوانده می شود اگر ممکنه یکم بیشتر راهنمایی بفرمایید یا یک نمونه برام در مورد ضرب ابشاری در یک واکشی درختوارهای قرار بدهید در ضمن من درست نمی دانم فیلد qtym مربوط به parent و child رو چگونه بدست بیارم این کوئری که نوشتم درست است که حالا از cast برای جایگزینی استفاده کنم

در مورد اضافه کردن خود parentid به لیست نهایی در select اول کوئری را به این صورت تغیر دادم


SELECT partid, parentid, type, 0 AS levelp
FROM dbo.treepartview
WHERE (parentid = 900265) or partid = 900265

یک چیز بسیار قابل ملاحظه زمان 3 ثانیه ای برای انجام این واکشی cte برای من بود که به نظرم خیلی زیاد بود آیا cte ذاتا زمانبر است

با تشکر فراوان

[baktash.n81@gmail.com]

فکر کنم بهتر باشه شما جداولتون و صورت سئوال اینجا قرار بدین ...

در مورد زمانبر بودن فکر می کنم عملیاتی که داری انجام می دی زمانبر باشه ... در کل CTE از روش حلقه و Cursor سریعتره ...

[amir3321]

با سلام خدمت جناب بکتاش
من در پست یک جدول part و جدول treepart رو گذاشتم ابتدا قطعات رو در جدولpart ذخیره می کنم و بعد ارتباطها رو در treepart به همراه تعداد مصرف در ان قرار می دهم من می خوام موقعی که یک سرمجموعه رو انتخاب می کنم بتونم تمام زیر مجموعه ها به همراه تعداد آنها رو واکشی کنم در مورد تعداد باید بگویم که تعداد بصورت آبشاری یعنی زیر مجموعه ضرب در سرمجموعه ان تا به بالا محاسبه می گردد

با تشکر

[amir3321]

استاد همچنان منتظریم

[baktash.n81@gmail.com]

اگه جداول رو بزاری اینجا بهتره ...

[amir3321]

با سلام وتبریک سال نو خدمت دوستان و جناب بکتاش
این دو تا جدول اصلی و ساده من است


USE [siepold]
GO

/****** Object: Table [dbo].[treepart] Script Date: 03/11/2012 11:13:14 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[treepart](
[partid] [bigint] NOT NULL,
[parentid] [bigint] NULL,
[qtym] [decimal](18, 2) NULL
) ON [PRIMARY]

GO

USE [siepold]
GO

/****** Object: Table [dbo].[part] Script Date: 03/11/2012 11:12:57 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[part](
[id] [bigint] IDENTITY(900000,1) NOT NULL,
[dwgno] [nvarchar](50) NULL,
[descfa] [nvarchar](100) NULL,
[type] [nchar](20) NULL,
[descen] [nvarchar](100) NULL,
CONSTRAINT [PK_part] PRIMARY KEY CLUSTERED
(
[id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO



-----------------------------
partid parentid qty
0 - 10
2 10 11
5 10 12
1 10 13
2 13 14
6 13 15
مثل مثال بالا من میخواهم با انتخاب partid شماره 10 تمام partid هایی که در زیر مجموعه اون قرار می گیرند به همراه qty انها که به صورت ابشاری در هم ضرب می شوند رو بدست بیاورم
ابتدا قطعات رو در جدولpart ذخیره می کنم و بعد ارتباطها رو در treepart به همراه تعداد مصرف در ان قرار می دهم من می خوام موقعی که یک سرمجموعه رو انتخاب می کنم بتونم تمام زیر مجموعه ها به همراه تعداد آنها رو واکشی کنم در مورد تعداد باید بگویم که تعداد بصورت آبشاری یعنی زیر مجموعه ضرب در سرمجموعه ان تا به بالا محاسبه می گردد
اگر بتونم از cte استفاده کنم دیگر نیاز به این ندارم که در یک جدول دیگر برای سر مجموعه های اصلی (یعنی محصولات) لیست تمام قطعات ان محصول رو قرار بدهم و یا در سطح application محاسبات تعداد در زیرمجموعه تا سر مجموعه رو محاسبه کنم .

اگر باز توضیح نیاز هست بگویید تا قرار دهم

[amir3321]

استاد باز هم ما منتظر راهنمایی های شما در سال جدید هستیم

[amir3321]

استاد باز هم ما منتظر راهنمایی شما هستیم

[amir3321]

استاد منتظر نظرات شما هستیم
تغییراتی بوسیله راهنمایی های شما دادم تقریبا مشکلم حل شد


create PROCEDURE [dbo].[getchildofparent_w_prd]


@parentid bigint,
@productid bigint,
@arrayfilterid varchar(500)

AS
BEGIN
begin tran

SET NOCOUNT ON;

--(select CHARINDEX(CAST(e.parentid as varchar),@stringarray))=0 )

WITH parts AS (SELECT partid, parentid, qtym as hp, type, 0 AS levelp
FROM dbo.treepartview
WHERE (parentid = @parentid) or (partid=@parentid) and (productid = @productid)and (productid2 = @productid)
UNION ALL
SELECT e.partid, e.parentid,CAST(e.qtym * d.hp AS decimal(18,2)) as g , e.type, d.levelp + 1 AS Expr1
FROM dbo.treepartview AS e INNER JOIN
parts AS d ON e.parentid = d.partid
WHERE e.parentid not in (select * from dbo.udf_PivotParameters( @arrayfilterid,',')) and (e.productid = @productid)and (e.productid2 = @productid))

SELECT DISTINCT parts_1.partid,parts_1.hp,parts_1.levelp, dbo.part.dwgno, dbo.part.type, dbo.part.descfa, dbo.part.descen,dbo.partdetail .[weight] as netweight,CAST(dbo.partdetail .[weight]*parts_1.hp AS decimal(18,2))as weight1
FROM parts AS parts_1 INNER JOIN
dbo.part ON dbo.part.id = parts_1.partid

INNER JOIN dbo.partdetail ON dbo.part.id = dbo.partdetail .partid
where parts_1.partid not in (select * from dbo.udf_PivotParameters( @arrayfilterid,','))


if @@Error = 0
Commit Tran
Else
Rollback Tran

END