Приветствую. Проблема с созданием Join'а.

Имеется вот такой SelectQuery.

SELECT
    [tbl_OfferingInMovement].[ID] AS [ID],
    [tbl_OfferingInMovement].[OfferingMovementID] AS [OfferingMovementID],
    [tbl_OfferingInMovement].[OfferingID] AS [OfferingID],
    [Offering].[Name] AS [OfferingName],
    [Offering].[Code] AS [OfferingCode],
    [Unit].[Name] AS [UnitName],
    [OfferingMovement].[StoreID] AS [StoreID],
    [tbl_Store].[Store] AS [Store],
    [tbl_OfferingInMovement].[Quantity] AS [Quantity],
    [tbl_OfferingInMovement].[BasicPrice] AS [BasicPrice],
    [tbl_OfferingInMovement].[BasicAmount] AS [BasicAmount],
    [tbl_OfferingInMovement].[SignedQuantity] AS [SignedQuantity],
    [tbl_OfferingInMovement].[SignedAmount] AS [SignedAmount],
    [tbl_OfferingInMovement].[Description] AS [Description],
    [OfferingMovementType].[ID] AS [OfferingMovementTypeID],
    [OfferingMovementType].[Name] AS [OfferingMovementType],
    [tbl_OfferingInMovement].[PlanQuantity] AS [PlanQuantity],
    [tbl_OfferingInMovement].[PlanAmount] AS [PlanAmount],
    [OfferingMovement].[StatusID] AS [OfferingMovementStatusID],
    [tbl_OfferingInMovement].[Price] AS [Price],
    [tbl_OfferingInMovement].[Amount] AS [Amount],
    [OfferingMovement].[CurrencyID] AS [CurrencyID],
    [tbl_Currency].[Currency] AS [Currency],
    [OfferingMovement].[CurrencyRate] AS [Rate],
    [tbl_OfferingInMovement].[ConsignmentID] AS [ConsignmentID],
    [Consignment].[Number] AS [ConsignmentNumber],
    [Offering].[OfferingChargeMethodID] AS [OfferingChargeMethodID],
    [OfferingMovement].[Number] AS [OfferingMovementNumber],
    [tbl_OfferingInMovement].[TZR] AS [TZR],
    [tbl_OfferingInMovement].[PrimeCost] AS [PrimeCost],
    [tbl_OfferingInMovement].[PrimeAmount] AS [PrimeAmount],
    [Offering].[Volume] AS [Volume],
    [Offering].[Weight] AS [Weight],
    [Offering].[BasicPrice] AS [OfferingBasicPrice],
    [tbl_OfferingInMovement].[TZRAmount] AS [TZRAmount],
    [tbl_OfferingInMovement].[DocumentID] AS [DocumentID],
    [tbl_OfferingInMovement].[ContractID] AS [ContractID],
    [tbl_OfferingInMovement].[InvoiceID] AS [InvoiceID],
    [tbl_Document].[Title] AS [DocumentNumber],
    [tbl_Contract].[Title] AS [ContractNumber],
    [tbl_Invoice].[InvoiceNumber] AS [InvoiceNumber],
    [tbl_OfferingInMovement].[PurchaseID] AS [PurchaseID],
    [tbl_Purchases].[Number] AS [PurchaseNumber],
    [OfferingMovement].[SupplierID] AS [SupplierID],
    [tbl_OfferingInMovement].[Date] AS [Date],
    [OfferingMovement].[ModifiedOn] AS [ModifiedOn],
    [OfferingMovement].[DateOfShipment] AS [DateOfShipment],
    [AccountBillingInfoItelon].[Name] AS [ItelonName],
    [AccountBillingPayerInfo].[Name] AS [PayerName]
FROM
    [dbo].[tbl_OfferingInMovement] AS [tbl_OfferingInMovement]
LEFT OUTER JOIN
    [dbo].[vw_Offering] AS [Offering] ON [Offering].[ID] = [tbl_OfferingInMovement].[OfferingID]
LEFT OUTER JOIN
    [dbo].[tbl_Unit] AS [Unit] ON [Unit].[ID] = [Offering].[DefaultUnitID]
LEFT OUTER JOIN
    [dbo].[vw_OfferingMovement] AS [OfferingMovement] ON [OfferingMovement].[ID] = [tbl_OfferingInMovement].[OfferingMovementID]
LEFT OUTER JOIN
    [dbo].[tbl_OfferingMovementType] AS [OfferingMovementType] ON [OfferingMovementType].[ID] = [OfferingMovement].[TypeID]
LEFT OUTER JOIN
    [dbo].[tbl_Store] AS [tbl_Store] ON [tbl_Store].[ID] = [OfferingMovement].[StoreID]
LEFT OUTER JOIN
    [dbo].[vw_OfferingMovement] AS [Consignment] ON [Consignment].[ID] = [tbl_OfferingInMovement].[ConsignmentID]
LEFT OUTER JOIN
    [dbo].[tbl_Currency] AS [tbl_Currency] ON [tbl_Currency].[ID] = [OfferingMovement].[CurrencyID]
LEFT OUTER JOIN
    [dbo].[vw_Document] AS [tbl_Document] ON [tbl_Document].[ID] = [tbl_OfferingInMovement].[DocumentID]
LEFT OUTER JOIN
    [dbo].[vw_Contract] AS [tbl_Contract] ON [tbl_Contract].[ID] = [tbl_OfferingInMovement].[ContractID]
LEFT OUTER JOIN
    [dbo].[vw_Invoice] AS [tbl_Invoice] ON [tbl_Invoice].[ID] = [tbl_OfferingInMovement].[InvoiceID]
LEFT OUTER JOIN
    [dbo].[vw_Purchases] AS [tbl_Purchases] ON [tbl_Purchases].[ID] = [tbl_OfferingInMovement].[PurchaseID]
LEFT OUTER JOIN
    [dbo].[tbl_AccountBillingInfo] AS [AccountBillingInfoItelon] ON [AccountBillingInfoItelon].[ID] = [OfferingMovement].[InfoItelonID]
LEFT OUTER JOIN
    [dbo].[tbl_AccountBillingInfo] AS [tbl_AccountBillingInfoPayer] ON [tbl_AccountBillingInfoPayer].[ID] = [tbl_OfferingInMovement].[PayerID]

Предпоследний Join(AS [AccountBillingInfoItelon]) создан мною. Работает отлично.

Вот его код. Заранее извиняюсь за синтаксис VBA.

    Set oTable = TSConnector.Services.GetNewItemByUSI(AccountBillingInfoTableName)
    Set oJoin = oSelectQuery.Items(0).Joins.Items(4).CreateCopy
    oJoin.UID = TSConnector.GenGUID
    oJoin.RightField = oJoin.RightField.ParentFields.Items(40)
    oJoin.LeftField = oTable.Fields.ItemsByName("ID")
    oJoin.LeftTableAlias = "AccountBillingInfoItelon"
    oJoin.JoinType = jtLeftOuter
    oSelectQuery.Items(0).Joins.AddItem oJoin
    oSelectQuery.Items(0).Joins.Items(oSelectQuery.Items(0).Joins.Count - 1).CanDisable = False

Последний создаётся не правильно.

    Set oJoin = oInvoiceSelectQuery.Items(0).Joins.Items(0).CreateCopy
    oJoin.UID = TSConnector.GenGUID
    oJoin.LeftField = oTable.Fields.ItemsByName("ID")
    oJoin.LeftTableAlias = "tbl_AccountBillingPayerInfo"
    oJoin.RightField = TSConnector.Services.GetNewItemByUSI(InvoiceTableName).Fields.Items(31)
    oJoin.JoinType = jtLeftOuter
    oSelectQuery.Items(0).Joins.AddItem oJoin
    oSelectQuery.Items(0).Joins.Items(oSelectQuery.Items(0).Joins.Count - 1).CanDisable = False

Проблема с RightField. Поле верное, то что нужно [PayerID]. Но мне надо чтобы оно доставала его именно из [tbl_Invoice]. А она же в SQL подставляет [tbl_OfferingInMoment]. Где можно изменить таблицу в Join'е или ещё где-то в SelectQuery. Целую неделю мучаюсь не могу понять.

Нравится

8 комментариев

В коробке в скрипте wnd_IWTGraphWizardScript есть функция:

function GetPathByColumn(Column) {
	var Result = Column.Field.ParentFields.ParentTable.UID + snpFields + Column.Field.UID;
	var ParentJoin = Column.ParentJoin;
	while (ParentJoin) {
		Result = ParentJoin.RightField.ParentFields.ParentTable.UID +
			snpFields +
			ParentJoin.RightField.UID +
			jtLeft +
			ParentJoin.LeftField.ParentFields.ParentTable.UID +
			snpFields +
			ParentJoin.LeftField.UID +
			snpSegment +
			Result;
		ParentJoin = ParentJoin.ParentJoin;
	}
	return Result;
}  

Возможно, подобным образом можно не только считывать, но и записывать название таблицы. Если разобраться не получится, можно попробовать подключится в отладчике и посмотреть, как устроены нормально работающие join-ы.

 

О, спасибо Александр! Не думал что достучусь на этот форум. laugh Завтра с утра буду пробовать. Спасибо ещё раз. Отпишу что да как. 

Не получилось записывать название таблицы в Join. Что за связь используется так и не получилось выяснить. Я пользуюсь редактором VBA непосредственно в EXCEL. За Visual Studio еще не садился.

Воспользовался oSubSelectColumn. Отличная штука. Всё что хотел, получилось.

    Set oSubSelectColumn = oSelectQuery.Items(0).Columns.CreateSubSelectColumn
    Set oTable = TSConnector.Services.GetNewItemByUSI(AccountBillingInfoTableName)
 
    Dim oSQLFilter As CustomSQLFilter
    Dim oSelect As TSObjectLibrary.Select
    Set oSelect = oSelectQuery.CreateItem
    oSelect.FromTable = oTable
    oSelect.FromTableAlias = "AccountBillingInfo"
    Set oGeneralColumn = oSelectQuery.Columns.CreateGeneralColumn
    oGeneralColumn.Field = oTable.Fields.ItemsByName("Name")
    oGeneralColumn.ColumnAlias = "Name"
    oSelect.Columns.Add oGeneralColumn
    Set oSQLFilter = oSelect.Filters.CreateCustomSQLFilter
    oSQLFilter.SQLText = "[AccountBillingInfo].[ID] = [OfferingMovement].[InfoItelonID]"
    oSelect.Filters.Add oSQLFilter
    oSubSelectColumn.ColumnSelectQuery.Add oSelect
    oSubSelectColumn.ColumnAlias = "AccountBillingInfoItelon"
    oSelectQuery.Items(0).Columns.Add oSubSelectColumn
    oSelectQuery.Items(0).Columns.Items(oSelectQuery.Items(0).Columns.Count - 1).IsEnabled = True

 

В редакторе VBA тоже есть отладка. Не знаю, показывает ли структуру объектов, но вроде бы должен.

Можно и через SubSelect. Когда-то в базе на Orcacle упирались в лимит Join-ов и приходилось в дизайнере переделывать в sq часть колонок на колонки-подзапросы.

Зверев Александр, есть, да, структура объектов.

Если видно структуру, можно посмотреть, как устроен нормальный join. Ну, или использовать подзапросы.

Зверев Александр, Пользовался более года подзапросами. Сегодня столкнулся с проблемой фильтрации по столбцам от подзапросов. Долго не стал вникать в суть проблемы.

Решил вернуться к Join'ам. smiley

Решение к проблеме в самом низу этого сообщения.

Пошёл по вашему совету, Александр. Решил сравнивать структуры созданного мною нового Join'а и уже готового Join'а из другого готового SelectQuery.

Задача была поставлена аналогична упомянутой выше: прикрепить таблицу(создать join) к запросу(SelectQuery). При этом из прикрепленной таблицы(join'а) должны выбираться те строки, значения ключевых полей которых равны значениям полей не из исходной таблицы, на основе которой сформирован запрос(SelectQuery), а из таблицы, прикрепленной ранее (уже прикрепленного join'а). Всё тоже самое, что и в предыдущей задаче. Выражаясь кратким языком, присоединить join к уже имеющемся join'у в SelectQuery.

На следующей картинке представлен SQL текст уже (упомянутого в первом сообщения топика) готового запроса "sq_OfferingInMovement", сформированный на основе таблицы "tbl_OfferingInMovement" и нескольких прикрепленных таблиц к нему. В этом тексте можно увидеть, что к запросу прикреплена таблица(join), к заранее прикрепленному (join'у). В этом запросе всё правильно.

Синим выделены строки, означающие что к запросу прикреплено представление(если не понятно что такое представление, можно прочесть как "таблица") "[vw_Offering], у которого значение ключевого поля "[ID]" равняется значению поля исходной таблицы  "[tbl_OfferingInMovement].[OfferingID]". Этому присоединенному представлению присвоен псевдоним(alias) "[Offering]".

Красным выделены строки, означающие что к запросу прикреплена таблица "[tbl_Unit]", у которого значение ключевого поля "[ID]" равняется значению поля не исходной таблицы, а значению поля прикрепленного (синим выделено) представления [Offering].[DefaultUnitID].

В этом примере всё отлично.

Проблема заключалась в том, что при создании нового собственного прикрепления (join'а), которое должно было крепиться к уже имеющемуся прикреплению (join'у), в SQL тексте отображалось что новое прикрепление (join) крепиться не к уже имеющемуся прикреплению, а к исходной таблице. То есть, если взять пример выше, и прикрепление, которое выделено красным, было бы создано мною, то там вместо [Offering].[DefaultUnitID] было бы [tbl_OfferingInMovement][DefaultUnitID], что было бы неверным и, естественно, выдавало бы ошибку. Так как в [tbl_OfferingInMovement] нет поля [DefaultUnitID] и вообще это не то что мне нужно.

В ходе сравнения join'ов выяснилось, что у объекта Join, оказывается, есть параметр ParentJoin, которому необходимо присваивать тот Join, к которому будет необходимо прикреплять новый join.

Вот код правильно созданного Join'а и необходимого нам столбца из него.

Создание нового прикрепления (join'а) к исходной таблице запроса.

 
//Таблица, строки которой будем крепить. 
   Set objTSTable = objTSConnector.GetService(strAssemblingInTaskTableName)
 
   Set objTSAssemblingInTaskJoin = objTSSelect.Joins.CreateItem
   objTSAssemblingInTaskJoin.ParentJoins = objTSSelect.Joins
   objTSAssemblingInTaskJoin.LeftField = objTSTable.Fields.ItemsByName("ID")
   objTSAssemblingInTaskJoin.RightField = 
   objTSSelect.FromTable.Fields.ItemsByName("AssemblingInTaskID")
   objTSSelect.Joins.Add objTSAssemblingInTaskJoin

 Добавляем необходимый нам столбец из этого прикрепления. Не знаю, нужен ли он для другого прикрепления, но это не трудно выяснить. Просто не надо его создавать и добавлять. Вот код.

    Set objTSColumn = objTSSelect.Columns.CreateGeneralColumn
    objTSColumn.ParentColumns = objTSSelect.Columns
    objTSColumn.ParentJoin = objTSAssemblingInTaskJoin
    objTSColumn.ParentParametriticQuery = objTSSelectQuery
    objTSColumn.Field = objTSTable.Fields.ItemsByName("TaskID")
    objTSColumn.DependsOn.CommaText = objTSTable.SQLName
    objTSSelect.Columns.Add objTSColumn
    objTSColumn.IsEnabled = True

Теперь добавляем ещё одно прикрепление (join) к уже созданному выше прикреплению.

    'Таблица нового прикрепления.
    Set objTSTable = objTSConnector.GetService(strTaskTableName)
 
    Set objTSTaskJoin = objTSSelect.Joins.CreateItem
 
    'Вот здесь обязательно указать то прикрепление, к которому будем крепить это.
    objTSTaskJoin.ParentJoin = objTSAssemblingInTaskJoin
 
    objTSTaskJoin.ParentJoins = objTSSelect.Joins
    objTSTaskJoin.LeftField = objTSTable.Fields.ItemsByName("ID")
    objTSTaskJoin.RightField = objTSAssemblingInTaskJoin.LeftField.ParentFields.ItemsByName("TaskID")
    objTSSelect.Joins.Add objTSTaskJoin

Ну и добавляем обязательно столбец из этого прикрепления. Без него join не включиться, если не указать параметр у Join'а CanDisable = False. Без особой причины CanDisable можно не трогать. Достаточно просто добавить столбец из этого join'а, как его параметр IsEnabled сразу же станет True.

    Set objTSColumn = objTSSelect.Columns.CreateGeneralColumn
    objTSColumn.ParentColumns = objTSSelect.Columns
    objTSColumn.ParentJoin = objTSTaskJoin
    objTSColumn.ParentParametriticQuery = objTSSelectQuery
    objTSColumn.Field = objTSTable.Fields.ItemsByName("StatusID")
    objTSColumn.DependsOn.CommaText = objTSTable.SQLName
    objTSSelect.Columns.Add objTSColumn
    objTSColumn.IsEnabled = True

Всё.

Извините за столь жидкий текст. Думаю, что кто столкнётся или уже столкнулся с данной проблемой, то всё поймёт.

Если есть какие-то замечания, то обязательно напишите.

Можно и так. Всё ли правильно, по коду сказать сложно. Если в результате в профайлере идёт нужный SQL-запрос, то нормально.

По поводу свойства ParentJoin, в SDK о нём есть, но очень кратко. Лучше смотреть примеры использования поиском по скриптам конфигурации.

Показать все комментарии

Хочу DataSet, создаваемый SelectQuery, вывести в Excel через VBA. С этим проблем нет. SelectQuery является готовым сервисом, созданным администратором. К админке доступа нету. Всё прекрасно работает. Проблема в одном. Не могу Join по нормальному прикрепить.

Вот SQLText это SelectQuery:

SELECT
    [Offering].[Name] AS [OfferingName],
    [Offering].[Code] AS [OfferingCode],
    [tbl_OfferingInMovement].[Quantity] AS [Quantity],
    [OfferingMovementType].[Name] AS [OfferingMovementType],
    [OfferingMovement].[StatusID] AS [OfferingMovementStatusID],
    [tbl_OfferingInMovement].[InvoiceID] AS [InvoiceID],
    [tbl_Invoice].[InvoiceNumber] AS [InvoiceNumber],
    [tbl_OfferingInMovement].[Date] AS [Date]
FROM
    [dbo].[tbl_OfferingInMovement] AS [tbl_OfferingInMovement]
LEFT OUTER JOIN
    [dbo].[vw_Offering] AS [Offering] ON [Offering].[ID] = [tbl_OfferingInMovement].[OfferingID]
LEFT OUTER JOIN
    [dbo].[vw_OfferingMovement] AS [OfferingMovement] ON [OfferingMovement].[ID] = [tbl_OfferingInMovement].[OfferingMovementID]
LEFT OUTER JOIN
    [dbo].[tbl_OfferingMovementType] AS [OfferingMovementType] ON [OfferingMovementType].[ID] = [OfferingMovement].[TypeID]
LEFT OUTER JOIN
    [dbo].[vw_Invoice] AS [tbl_Invoice] ON [tbl_Invoice].[ID] = [tbl_OfferingInMovement].[InvoiceID]
LEFT OUTER JOIN
    [dbo].[tbl_AccountBillingInfo] AS [AccountBillingInfoItelon] ON [AccountBillingInfoItelon].[ID] = [OfferingMovement].[InfoItelonID]

Последняя строка, добавленный мной JOIN. Он работает на отличненько.

Его код(кстати, скажите насколько он крив):

    Set oTable = TSConnector.Services.GetNewItemByUSI(AccountBillingInfoItelonTableName)
    Set oJoin = oSelectQuery.Items(0).Joins.Items(4).CreateCopy ' создание копии join'а LEFT OUTER JOIN [dbo].[tbl_OfferingMovementType] AS [OfferingMovementType] ON [OfferingMovementType].[ID] = [OfferingMovement].[TypeID]
    oJoin.UID = TSConnector.GenGUID
    oJoin.RightField = oJoin.RightField.ParentFields.Items(40) 'нужное поле из OfferingMovement
    oJoin.LeftField = oTable.Fields.ItemsByName("ID")
    oJoin.LeftTableAlias = "AccountBillingInfoItelon"
    oJoin.JoinType = jtLeftOuter
    oSelectQuery.Items(0).Joins.AddItem oJoin
    oSelectQuery.Items(0).Joins.Items(oSelectQuery.Items(0).Joins.Count - 1).CanDisable = False

А вот этот не хочет:

    Set oTable = TSConnector.Services.GetNewItemByUSI(InvoiceTableName)
    Set oJoin = oSelectQuery.Items(0).Joins.Items(10).CreateCopy
    oJoin.UID = TSConnector.GenGUID
    oJoin.RightField = oSelectQuery.Items(0).Joins.Items(9).LeftField.ParentFields.ItemsByName("PayerID") 'проблема именно здесь. Крепиться Field от tbl_Invoice. Но в SQL_Text ошибка. Крепит Field от не того Table.
    oJoin.LeftField = oTable.Fields.ItemsByName("ID")
    oJoin.LeftTableAlias = "AccountBillingPayerInfo"
    oJoin.JoinType = jtLeftOuter
    oSelectQuery.Items(0).Joins.AddItem oJoin
    oSelectQuery.Items(0).Joins.Items(oSelectQuery.Items(0).Joins.Count - 1).CanDisable = False

После него в SQLText в конце появляется:

LEFT OUTER JOIN
    [dbo].[vw_Invoice] AS [AccountBillingPayerInfo] ON [AccountBillingPayerInfo].[ID] = [tbl_OfferingInMovement].[PayerID]

В результате ошибка. В конце должно быть не [tbl_OfferingInMovement].[PayerID], а [tbl_Invoice].[PayerID]. Ведь креплю то я PayerID именно от tbl_Invoice. А в SQLText он зачем-то подставляет PayerID из tbl_OfferingInMovement, которого там нету. Именно в этом ошибка. Где вообще в SelectQuery эти прикрепленные таблицы?

Я может быть как-то не правильно Field добавляю в Join'ы. И до конца не понимаю суть этих Field'ов. 

Может быть как-то с нуля можно создать этот join. Есть ли какие нибудь статьи?

 

Нравится

1 комментарий

Вопрос обсудили в соседней Вашей теме.

Показать все комментарии