Рекурсивная ведомость материалов CTE

Это мой первый вопрос, так что простите меня, если я недостаточно ясен.

Мне поручено получить общую стоимость всех компонентов сборки (ведомость материалов). По сути, я хочу запросить таблицу, в которой хранится распределение заказов на покупку для компонентов, чтобы я мог получить стоимость, в настоящее время связанную с неопубликованной сборкой.

Это становится сложно, потому что компонент сборки может быть самой сборкой, и в этом случае мне нужно запросить другую таблицу, содержащую информацию о том, связаны ли другие сборки с этой основной. (У меня есть проверки, чтобы убедиться, что перераспределения не происходит, но возможно, что ничего еще не было выделено, и это нормально) В этом случае запросите таблицу распределения заказов на покупку для компонентов ТОГО номера сборки и добавьте эти затраты в Итого для родительской сборки.

Я впервые использую CTE, и мне не очень повезло. Может ли кто-нибудь помочь определить, что я здесь делаю не так?

Якорь втягивает все компоненты, за исключением подсборок, а также их удельную стоимость и количество из заказа на поставку для основной сборки, для которой я пытаюсь определить стоимость.

Рекурсивная часть должна извлекать компоненты, стоимость и количество для сборок, которые были «связаны» существующими в таблице BM10200_AssemblyQtyDetail. Если родительская сборка находится в столбце TRANNUM, то TRX_ID этой строки является связанной сборкой, которая является компонентом подсборки основной сборки.

USE HT
GO
WITH BOMCost (Assembly, Component, PriceFromPO, Qty, BOMLevel)
AS
(
-- Anchor member definition
SELECT asl.TRX_ID, asl.ITEMNMBR, asl.UNITCOST, asl.SERLTQTY,
0 AS BOMLevel
FROM HT.DBO.BM10400 AS asl
WHERE asl.TRX_ID = 'ASM0002909'
UNION ALL
-- Recursive member definition
SELECT asl.TRX_ID, asl.ITEMNMBR, asl.UNITCOST, asl.SERLTQTY,
    BOMLevel + 1
FROM HT.DBO.BM10400 AS asl
INNER JOIN HT.DBO.BM10200_AssemblyQtyDetail AS bqd 
    ON asl.TRX_ID = bqd.TRANNUM
INNER JOIN BOMCost AS bc
    ON bqd.TRX_ID = bc.Assembly
)


-- Statement that executes the CTE
SELECT Assembly, Component, PriceFromPO, Qty, BOMLevel
FROM BOMCost

Повторюсь, проблема здесь в том, что это ТОЛЬКО возвращает компоненты основной сборки без каких-либо затрат, связанных с подсборкой. ТАК просто компоненты верхнего уровня. У меня есть запись в таблице связывания, связывающая сборку для подсборки этого BM с основной сборкой, но она не вытягивает компоненты для этого номера сборки. Я думаю, это может иметь какое-то отношение к объединениям рекурсивных разделов. Любая помощь приветствуется!

Вот сценарий данных. ASM0002909 - это сборка с компонентом, который сам по себе является сборкой. ASM0002914 создается одновременно для ASM0002909 и состоит из двух компонентов. Я хочу получить только стоимость того, что фактически было получено от заказа на поставку, поэтому не имеет значения, необходимы ли другие компоненты, но они еще не получены. Вот что все должно быть суммировано (SERLTQTY * UNITCOST), чтобы получить общую стоимость основной сборки.

Таблица серийных партий сборки

Но вот результат, который я сейчас получаю из своего запроса. Он должен выполнить шаг рекурсии один раз и вернуть результаты ASM0002914. Результаты моего CTE

Я установил sqlfiddle с двумя основными таблицами, данными и с sql-запросом, который я сейчас использую, который все еще не собирает компоненты подсборок и возвращает ошибку превышения лимита рекурсии. http://sqlfiddle.com/#!3/bd1b98/6


sql sql-server recursion common-table-expression
person Dezryth    schedule 10.03.2014    source источник
comment
Думаю, вам нужно опубликовать здесь несколько примеров данных, чтобы было понятнее.   -  person JNK    schedule 11.03.2014
comment
Можете ли вы создать sqlfiddle со своими таблицами и тем, что у вас есть на данный момент?   -  person Ryan Gates    schedule 11.03.2014
comment
Конечно, дайте мне немного разобраться в этом, впервые используя sqlfiddle.   -  person Dezryth    schedule 11.03.2014
comment
Вот sqlfiddle sqlfiddle.com/#!3/bd1b98/6   -  person Dezryth    schedule 11.03.2014

Ответы (1)


arrow_upward
2
arrow_downward

Возможно, мне что-то не хватает, но похоже, что ваш JOIN критерий исключает рекурсию:

INNER JOIN BOMCost AS bc ON bqd.TRX_ID = bc.Assembly

ASM0002909 не равно ASM0002914, поэтому в нижней части нет результатов.

Обновлять:

WITH BOMCost (Assembly, Component, PriceFromPO, Qty, BOMLevel)
AS
(
-- Anchor member definition
    SELECT asl.TRX_ID, asl.ITEMNMBR, asl.UNITCOST, asl.SERLTQTY,
    0 AS BOMLevel
    FROM DBO.BM10400 AS asl
    WHERE asl.TRX_ID = 'ASM0002909'
    UNION  ALL
-- Recursive member definition
    SELECT asl.TRX_ID, asl.ITEMNMBR, asl.UNITCOST, asl.SERLTQTY,
        BOMLevel + 1
    FROM DBO.BM10400 AS asl
    INNER JOIN DBO.BM10200_AssemblyQtyDetail AS bqd 
        ON asl.TRX_ID = bqd.TRX_ID
    INNER JOIN BOMCost AS bc
        ON bqd.TRANNUM = bc.Assembly

)


-- Statement that executes the CTE
SELECT DISTINCT Assembly, Component, PriceFromPO, Qty, BOMLevel
FROM BOMCost

Демонстрация: SQL Fiddle

person Hart CO    schedule 10.03.2014
comment
Я считаю, что вы правы, что объясняет, почему я получаю записи только от ведущего. Я просто не уверен, как правильно присоединиться к этому и получить результаты от BM10400, используя столбец TRX_ID, где столбец TRANNUM совпадает с asl.TRX_ID - person Dezryth; 11.03.2014
comment
Трудно разобраться, не зная структуры таблиц в игре, вам может пригодиться добавление еще одного cte, у которого есть родительские / дочерние поля trx_id вместе. - person Hart CO; 11.03.2014
comment
Козел, ты имеешь в виду два столбца, такие как ParentAssembly и SubAssembly? Кроме того, как только я изменил свою внутреннюю ссылку соединения на cte, я начинаю получать ошибку максимальной рекурсии. Пожалуйста, проверьте sqlfiddle! Спасибо за помощь - person Dezryth; 11.03.2014
comment
@Dezryth почти поместил это в вашу скрипку, просто нужно было немного изменить критерии соединения. Проблема здесь в том, что каждый элемент в вашем якоре будет присоединяться к обеим записям в части рекурсии. Итак, я добавил DISTINCT. - person Hart CO; 11.03.2014