Набор часто используемых SQL-запросов

От /

На момент написания статьи сервером IIKO заявлена поддержка двух СУБД: Microsoft SQL Server и PostgreSQL . По части конвертации данных из xml запросы к различным СУБД несколько отличаются.

Например, запрос полей номенклатуры:

MS SQL

SELECT
	convert(xml, [xml]).value('(/r/name/customValue)[1]', 'nvarchar(100)') as name
FROM
	Entity 
WHERE
	type = 'Product'

PostgreSQL

SELECT
	(xpath('/r/name/customValue/text()',xml::xml))[1] as name
FROM
	Entity 
WHERE
	type = 'Product'

Таким образом, на каждую СУБД необходим свой комплект запросов. Все часто используемые SQL-запросы написаны с использованием промежуточных функций, которые позволяют абстрагироваться от конкретной СУБД.

Функция MSXML(Путь, ДлинаСтроки=100)
	Возврат "convert(xml, [xml]).value('("+Путь+")[1]', 'nvarchar("+ДлинаСтроки+")')";
КонецФункции

Функция PGXML(Путь)
	Возврат "(xpath('"+Путь+"/text()',xml::xml))[1]";	
КонецФункции

Функция PGXMLC(Путь)
	Возврат "cast((xpath('"+Путь+"/text()',xml::xml)) AS text[])";  // {строка}
КонецФункции

Функция XML(ТипБД, Путь, ДлинаСтроки=100, Условие=Ложь)
	Если ТипБД = "MS" Тогда
		Возврат MSXML(Путь, ДлинаСтроки);
	ИначеЕсли ТипБД = "PG" Тогда
		Если Условие Тогда
			Возврат PGXMLC(Путь);
		Иначе
			Возврат PGXML(Путь);
		КонецЕсли;
	Иначе
		Возврат Неопределено;
	КонецЕсли;
КонецФункции

Функция CV(ТипБД, Значение)
	Если ТипБД = "MS" Тогда
		Возврат Значение;
	ИначеЕсли ТипБД = "PG" Тогда
		Возврат "{" + Значение + "}";
	Иначе
		Возврат Неопределено;
	КонецЕсли;
КонецФункции

В запросах используются вставки в квадратных скобках. В момент формирования запросов такие вставки замещаются на конкретные значения.

Некоторые знаки синтаксиса опущены для простоты.

Запросы

Общие

Номер объекта

select max(revision)+1 as rev from Entity

Тип СУБД

select dbVendor from DBVersion

Справочники

select id,type,xml from Entity where type in ([entity_types]) and deleted = '0'

Подразделения

select 
  id, 
  XML(ТипБД, "/r/departmentId") as code,
  XML(ТипБД, "/r/name") as name 
from 
  entity 
where 
  type = 'Department'

Номенклатура

select 
  id, 
  XML(ТипБД, "/r/type")             as type,
  XML(ТипБД, "/r/num")              as vcode, 
  XML(ТипБД, "/r/name/customValue") as name,
  XML(ТипБД, "/r/parent")	    as parent,
  XML(ТипБД, "/r/mainUnit")	    as unit
from 
  entity 
where 
  type = 'Product' 
  and deleted = '0'

Склады

select 
	id, 
	XML(ТипБД, "/r/code") as code,
	XML(ТипБД, "/r/name/customValue") as name 
from 
	entity 
where 
	type = 'Store'

GUID склада по коду

select 
	id 
from 
	entity 
where 
	type='Store' 
	and XML(ТипБД, "/r/code",,Истина)='CV(ТипБД, "[store_code]")'

Поставщики

select 
	id, 
	XML(ТипБД, "/r/code") as vendor_code, 
	XML(ТипБД, "/r/name/customValue") as name
from 
	entity 
where 
	type='User' 
	and XML(ТипБД, "/r/supplier", "5", Истина)='CV(ТипБД, "true")'

Технологические карты

Срез последних с представлением даты для 1С

select 
  Product, 
  MAX(dateFrom) as maxDate
into
  #tmpAC
from 
  AssemblyChart 
group by
  Product

select
  AC.id,
  replace(convert(varchar, AC.dateFrom,23),'-','') + replace(convert(varchar, AC.dateFrom,8),':','') as dateFrom,
  AC.Product as productId
from
  #tmpAC
  left join AssemblyChart as AC
  on AC.dateFrom = #tmpAC.maxDate
  and AC.Product = #tmpAC.Product

Счета затрат

select 
	id,
	XML(ТипБД, "/r/name/customValue") as name
from 
	entity 
where 
	type = 'Account' 
	and	XML(ТипБД, "/r/type",,Истина) in ('CV(ТипБД, "OTHER_EXPENSES")', 
										  'CV(ТипБД, "COST_OF_GOODS_SOLD")'
										  'CV(ТипБД, "EXPENSES")')

Документ по номеру и году

MS SQL

select 
	id 
from 
	[doc_type] 
where 
	documentNumber='[doc_number]'
	and (dateCreated >= convert(datetime,'[year]-01-01 00:00:00.000', 120)
	and  dateCreated <= convert(datetime,'[year]-12-31 23:59:59.999', 120))

PostgreSQL

select 
	id 
from 
	[doc_type] 
where 
	documentNumber='[doc_number]'
	and dateCreated >= '[year]-01-01 00:00:00.000' 
	and  dateCreated <= '[year]-12-31 23:59:59.999'
Прокрутить вверх