SQL запрос, Регистр ОстаткиТоваров не отбирает остатки по :ТекущаяДата~.

Форум Программирование Общие вопросы программирования (Dev)

Платформа 1С v7.7

1. succub1_5 89 21.05.15 08:48 Сейчас в теме

Добрый день, имеется самописная конфа под 7.7, регистр ОстаткиТоваров, база SQL. У Регистра: Измерения - Фирма, Товар, Склад и Ресурсы - ОстатокТовара.

	Запрос = СоздатьОбъект("ODBCRecordset");
	
	ТекстЗапроса = "
	|SEL ECT
	|	ОТ.Товар AS [Товар $Справочник.Номенклатура],
	|	ОТ.Склад AS [Склад $Справочник.МестаХранения],
	|	ОТ.ОстатокТовара AS Количество
	|FROM
	|	$Регистр.ОстаткиТоваров (:ТекущаяДата~,,Склад = :Склад,Товар, Количество) AS ОТ
	//|	$Регистр.ОстаткиТоваров AS ОТ
	|WHERE
	|ОТ.Товар IN (SELECT VAL FR OM #СписокТоваров)
	|";
	
	СпрСклад = СоздатьОбъект("Справочник.МестаХранения");
	СпрСклад.НайтиПоКоду(1);;
	Запрос.УстановитьТекстовыйПараметр("Склад", СпрСклад.ТекущийЭлемент());
	Запрос.УстановитьТекстовыйПараметр("ТекущаяДата", ТекущаяДата());
	Запрос.УложитьСписокОбъектов(СписокТоваров, "#СписокТоваров", "Номенклатура");

Показать

Выдает ошибку: ... Неправильный синтаксис около , - почему?

Ответы

Подписаться на ответы Инфостарт бот

Свернуть все

2. ditp 93 21.05.15 10:01 Сейчас в теме

(1) succub1_5, нет возможности проверить, но я б попробовал так:

 ТекстЗапроса = "
    |SEL ECT
    |    ОТ.Товар AS [Товар $Справочник.Номенклатура],
    |    ОТ.Склад AS [Склад $Справочник.МестаХранения],
    |    ОТ.ОстатокТовара AS Количество
    |FROM
    |    $Регистр.ОстаткиТоваров (:ТекущаяДата~,,Склад = :Склад AND Товар IN (SELECT VAL FR OM #СписокТоваров),(Товар), (Количество)) AS ОТ
    |";

9. succub1_5 89 21.05.15 11:58 Сейчас в теме

(2) ditp, тоже не работает - таже ошибка.

11. ditp 93 21.05.15 11:59 Сейчас в теме

(9) succub1_5, текст ошибки приведен дословно?

13. succub1_5 89 21.05.15 12:04 Сейчас в теме

(11) ditp,
$Регистр.ОстаткиТоваров (:ТекущаяДата~,,Склад = :Склад AND Товар IN (SELECT VAL FR OM #СписокТоваров),(Товар), (Количество)) AS ОТ
... Неправильный синтаксис около конструкции ",".

14. ditp 93 21.05.15 12:18 Сейчас в теме

(13) succub1_5, а глянул доку, освежил память, вот так попробуете?

ТекстЗапроса = "
    |SELECT
    |    ОТ.Товар AS [Товар $Справочник.Номенклатура],
    |    ОТ.Склад AS [Склад $Справочник.МестаХранения],
    |    ОТ.ОстатокТовара AS Количество
    |FROM
    |    $РегистрОстатки.ОстаткиТоваров(:ТекущаяДата,,Склад = :Склад AND Товар IN (SELECT VAL FR OM #СписокТоваров),(Товар), (Количество)) AS ОТ
    |";

В результате если честно не уверен, проходил это все изрядно давно.

15. ture 608 21.05.15 12:41 Сейчас в теме

(14) ditp, тильды у даты нету, значит границы времени тю-тю. Я кстати тоже давно все наглухо забыл

21. succub1_5 89 21.05.15 13:06 Сейчас в теме

(14) ditp, что с тильдой что без - таже ошибка. Если же РегистрОстатки - то ругается на синтакисис около даты.
Если использую РегистрИтоги - то не правильная информация. В общем в php напрямую из SQL базы да еще и 7-чной остатки не получить?

22. ditp 93 21.05.15 13:28 Сейчас в теме

(21) succub1_5, получить, но без 1cpp муторней.
нужно писать sql запрос по типу

sel ect 
reg.ид_товара,
sc_Номенклатура.descr,
sum(reg.Количество) fr om
(sel ect fr om 
rg_нужный_регистр.ид_товара,
rg_нужный_регистр.количество,
where 
period = дата1
uni on
sel ect fr om 
ra_нужный_регистр.ид_товара,
ra_нужный_регистр.количество * case when ra_нужный_регистр.debcred=1 then -1 else 1 end,
wh ere 
ra_нужный_регистр.date_time_iddoc > дата1 and ra_нужный_регистр.date_time_iddoc < дата2) reg
inner join sc_Номенклатура on sc_Номенклатура.id = reg.ид_товара
group by reg.ид_товара,
sc_Номенклатура.descr

Показать

где
дата2 = нужная дата,
дата1 = дата хранения итогов а период, предшествующий дата2, обычно начало месяца,
ra*, rg* и sc* заменить на названия нужных табиц, см. 1cv7.dd[s], представления остальных полей берем оттуда же.

51. succub1_5 89 21.05.15 16:11 Сейчас в теме

(22) ditp, вот (в PHP) что получилось:

	$date1 = date("Y-m-01");
	$date2 = date("Y-m-d");
       
	$query = "SEL ECT reg.sp101,
		  sc33.descr,
		  sum(reg.sp102)
		  FR OM 
		  (SEL ECT
		  rg99.sp101,
		  rg99.sp102
		  FR OM rg99
		  WHERE rg99.period = ".$date1."
		  UNI ON
		  SEL ECT ra99.sp101,
		  ra99.sp102 * CASE WHEN ra99.debkred=1 THEN -1 ELSE 1 END
		  FR OM ra99
		  WH ERE ra99.date_time_iddoc > ".$date1." and ra99.date_time_iddoc < ".$date2.") reg
		  INNER JOIN sc33 on sc33.id = reg.sp101
		  GROUP BY reg.sp101, sc33.descr";

Показать

Только вот у ra99 (Регистр.ОстаткиТоваров) - нет date_time_iddoc.
P.S. Rg99 - РегистрИтоги.ОстаткиТоваров, Sc31 - склады, Sc33 - товары.

52. ture 608 21.05.15 16:18 Сейчас в теме

(51)

SEL ECT 
	t.sp101,
        n.descr,
    sum(t.sp102) as [sum]
FR OM 
   (SEL ECT
       r.sp101,
       r.sp102
    FR OM 
		rg99 as r (nolock)
    WHERE 
		r.period = ".$date1."
    UNI ON all
    SEL ECT 
		r.sp101,
        r.sp102*(1-2*r.debkred)
    FR OM 
		ra99 as r (nolock)
    WH ERE 
		r.date_time_iddoc > ".$date1." 
		and ra99.date_time_iddoc < ".$date2."
	) t
    
	INNER JOIN sc33 as n(nolock) on(n.id = t.sp101)
 GROUP BY 
	t.sp101, 
	n.descr

Показать

я, конечно, не мог проверить

53. ture 608 21.05.15 16:22 Сейчас в теме

(52)я все думаю, что за "sel ect" ? теперь сам столкнулся

58. succub1_5 89 21.05.15 16:47 Сейчас в теме

(52) ture, у ra99.date_time_iddoc < ".$date2." - нет поля date_time_iddoc

54. ture 608 21.05.15 16:24 Сейчас в теме

(51)если запрос заработает, то надо фильтры влепить по складам и номенклатуре. Чтоб быстрее было.

55. ture 608 21.05.15 16:26 Сейчас в теме

(51) union я дополнил all
хинты поставил
дата должна встать, я обычно ставлю строку типа '20150301' c одинарными

56. ture 608 21.05.15 16:28 Сейчас в теме

(51)параметризованные запрос можно подготовить и тогда много раз его шарашить быстрее будет

57. ditp 93 21.05.15 16:39 Сейчас в теме

(51) succub1_5, семерки под рукой нет, все по памяти пишу.

если в ra* нет date_time_iddoc, то
1. там возможно тоже есть поле период, либо
2. нужно ra* inner join _1sjourn on ra*.iddoc = _1sjourn.iddoc and _1sjourn.date_time_iddoc > дата1 and _1sjourn.date_time_iddoc < дата2

59. ture 608 21.05.15 16:47 Сейчас в теме

(57)точно к журналу надо цеплять тогда сразу. а вот от чего зависит это поле не помню уже.

3. gimalaj 17 21.05.15 10:38 Сейчас в теме

Ты уверен, что "УложитьСписокОбъектов" и "УстановитьТекстовыйПараметр" - это методы класса "ODBCRecordset"?

10. succub1_5 89 21.05.15 11:58 Сейчас в теме

(3) gimalaj, конечно - работает же.

4. ture 608 21.05.15 10:46 Сейчас в теме

$РегистрОстатки

12. succub1_5 89 21.05.15 12:01 Сейчас в теме

(4) ture, я по тем примерам и учусь, только что-то не работает =)
Переделал на | $РегистрОстатки.ОстаткиТоваров (:ТекущаяДата~,,Склад = :Склад,Товар, Количество) AS ОТ
Теперь ошибка на дату типа неправильный синтаксис около (ДАТА).

По идее мне нужен готовый MSSQL запрос для вставки в php. Поэтому и мучаюсь =). Если сделать РегистрИтоги - то на текущую дату выводит 1 из товаров в количестве = 0, но по факту его 2 шт.

16. ture 608 21.05.15 12:43 Сейчас в теме

(12) пхп? У ты дал парень! Это ж для 1С++ запросы то, а тебе надо их будет ловить там в профайлере и смотреть, что да как получается.

5. ture 608 21.05.15 10:47 Сейчас в теме

$Регистр.ХХХ – таблица движений регистра ХХХ
$РегистрИтоги.ХХХ – таблица итогов регистра ХХХ

6. ture 608 21.05.15 10:48 Сейчас в теме

Виртуальные таблицы
$РегистрОстатки.ХХХ
$РегистрОстаткиОбороты

7. ture 608 21.05.15 10:49 Сейчас в теме

во смотрите тут http://www.script-coding.com/Direct_queries.html

8. ture 608 21.05.15 10:51 Сейчас в теме

так не будет работать
$Регистр.ОстаткиТоваров (:ТекущаяДата~,,Склад = :Склад,Товар, Количество) AS ОТ

надо эту таблицу спрашивать так
$Регистр.ОстаткиТоваров AS ОТ

17. ture 608 21.05.15 12:44 Сейчас в теме

Если б только профайлер помог... так ведь сказано же виртаульная таблица, значит конечный запрос SQL будет меняться.
От позиции даты и времени зависит

18. ture 608 21.05.15 12:46 Сейчас в теме

Короче, есть правильный подход:
1) взять за уши одинесника
2) ткнуть его моськой в эти запросы и спросить - "это я должен в этом г...не копаться или ты?"
3) пусть вьюхи читабельные сделает к своим таблицам
4) ты по вьюхам построишь запрос как нормальный программист

19. ture 608 21.05.15 12:48 Сейчас в теме

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

23. m-serg74 46 21.05.15 13:44 Сейчас в теме

(19) ture, не поверишь - но именно так и хранится

24. ture 608 21.05.15 14:07 Сейчас в теме

(23) я б вообще нагнул этого семерышного программера со стороны php - собрал бы остатки на основе оборотов и без хинтов на таблицы, чтоб взвесить его вотчину по самый не горюй.

25. m-serg74 46 21.05.15 14:14 Сейчас в теме

(24) ture, а не допускаешь мысли что ТС в одном лице на все руки?

26. succub1_5 89 21.05.15 14:36 Сейчас в теме

(25) m-serg74, (ОФФТОП) не, не в 1 лице, 7-чный 1С-ник ушел и я 7 вообще не знал до апреля =), а сейчас по мере необходимости приходится кодить, а так я просто и php знаю вот и поднапрягли для сайта остатки из 7 выгружать, вот.

28. ture 608 21.05.15 14:40 Сейчас в теме

(26) упс...
Значит поглумиться не получится. Ну ты что ль не забывай ставить (nolock).
Это позволит не накладывать блокировки на таблицы семерошной базы, но читать Вы остатки прям из транзакций будите.

39. AlexO 135 21.05.15 15:13 Сейчас в теме

(28) ture,

но читать Вы остатки прям из транзакций будите.

чего-чего, из каких транзакций он читать будет?
Вам даже отвечают уже в 37) "Тут уже надо решать ТС, что ему важнее "чистые" данные или возможные транзакции в системе."

41. ture 608 21.05.15 15:24 Сейчас в теме

(39) это сленг такой "из транзакций"
дело не хитрое - один пишет, а другой сразу читает, не дожидаясь фиксации или отката.

Нечего автору решать... чистые, грязные. Он их блокировать не умеет. Значит будет грязные читать и резервы делать через сайт с задержкой появления в 1С.
Мы тут сами можем ему на решать, еще и правильней может оказаться.
Вам ли не знать?

44. AlexO 135 21.05.15 15:35 Сейчас в теме

(41) ture,

дело не хитрое - один пишет, а другой сразу читает, не дожидаясь фиксации или отката.

Ну так ни из каких "транзакций" он ничего не получит. Все запросы SQL - исключительно к базе, и никак иначе.
(1) succub1_5, что-то я не понял с примером:
|SEL ECT
СпрСклад.НайтиПоКоду(1);;
Вообще, сделай самый примитивный запрос (например, без параметров), а потом добавляй параметры. От простого - к сложному.
А так - что тут гадать, данных все равно не видно.
Ругается на синтаксис - закомменть проблемную строку, смотри, что получится.

46. ture 608 21.05.15 15:44 Сейчас в теме

(44) Транзакция переводит базу из одного "правильного" состояния в другое "правильное" состояние. Я об этом понятие говорю.
SQL сервер сразу пишет в два места - лог транзакции и конечную таблицу (в конец какой-то секции кластеризованного индекса). Запись в таблице остается помечена той транзакцией, в которой пишутся данные. Это позволит потом такие записи убрать, если откатят транзакцию. Хинт/указатель nolock читает по таблице не парясь есть пометка, что транзакция не завершена или нет. Это принято называть грязным чтением.
Я это так понимаю.

60. AlexO 135 21.05.15 16:48 Сейчас в теме

(46) ture,

Хинт/указатель nolock читает по таблице не парясь есть пометка, что транзакция не завершена или нет.

Так читает-то таблицу базы, а не лог транзакций ))

62. ture 608 21.05.15 16:51 Сейчас в теме

(60) это верно. Спорить не буду.

38. AlexO 135 21.05.15 15:10 Сейчас в теме

(24) ture,

я б вообще нагнул этого семерышного программера со стороны php - собрал бы остатки на основе оборотов и без хинтов на таблицы, чтоб взвесить его вотчину по самый не горюй.

ничего не понял из фраз:
"я б вообще нагнул этого семерышного программера со стороны php"
" - собрал бы остатки на основе оборотов и без хинтов на таблицы,"
" чтоб взвесить его вотчину по самый не горюй."

20. ture 608 21.05.15 12:49 Сейчас в теме

может кто и кинет вам пример запроса SQL, но как он угадает имена ваших таблиц и полей?
вьюхи надо.

27. ivsher 21.05.15 14:38 Сейчас в теме

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

Код
SEL ECT $ОТ.Товар AS Товар, $ОТ.Склад AS Склад, SUM($ОТ.Количество) AS Остаток FR OM $Регистр.ОстаткиТовара AS ОТ (NOLOCK) WHERE ОТ.Period = :ДатаТА AND $ОТ.Товар IN (SELECT VAL FR OM #СписокТоваров) AND $ОТ.Склад = :Склад GROUP BY $ОТ.Товар, $ОТ.Склад Показать полностью

Код

SEL ECT 
   $ОТ.Товар AS Товар,
   $ОТ.Склад AS Склад,
   SUM($ОТ.Количество) AS Остаток
FR OM
   $Регистр.ОстаткиТовара AS ОТ (NOLOCK)
WHERE
   ОТ.Period = :ДатаТА AND
   $ОТ.Товар IN (SELECT VAL FR OM #СписокТоваров) AND
   $ОТ.Склад = :Склад
GROUP BY
   $ОТ.Товар, $ОТ.Склад

Показать полностью

Агрегат можно и не ставить если вы уверены, что у вас у регистра только 2 измерения товар и склад.
Отмечу что переменная ДатаТА - это дата начала периода хранения итогов. Обычно это начало месяца текущего периода.

31. ivsher 21.05.15 14:45 Сейчас в теме

(27) ivsher, Извините чутка нужно подправить :)
вот это $Регистр.ОстаткиТовара AS ОТ (NOLOCK)
замените на это : $РегистрИтоги.ОстаткиТовара AS ОТ (NOLOCK)

47. succub1_5 89 21.05.15 15:54 Сейчас в теме

(31) ivsher, да я так пробовал ч/з РегистрИтоги - неправильно данные выводит.

48. ture 608 21.05.15 16:07 Сейчас в теме

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

49. ture 608 21.05.15 16:08 Сейчас в теме

(47) итоги помесячно хранятся?

1С обновляет итоги по границу расчета итогов, но не далее

50. ture 608 21.05.15 16:09 Сейчас в теме

(47) а! скорее всего в регистре хранятся остатки, а еще есть резервы, которые учтены в том "другом месте", которому Вы доверяете.

63. ivsher 21.05.15 17:04 Сейчас в теме

(47) succub1_5, В таблице итогов хранятся итоги на конец периода. Если у вас в 1с-ке для регистров остатков установлен период месяц, то итоги будут хранится на 1-ое число каждого месяца. А то что у вас не правильно показывает в запросе, то это либо вы не тот период указали, либо агрегат забыли поставить. Для получения актуальных итогов не нужно использовать таблицу движений.
А нужно взять остаток из таблицы итогов на нужный период с нужными значениями отборов по измерениям и не забудьте про SUM.
И вообще неплохо бы описание регистра увидеть. Какие там измерения в нем.
Ну и в догонку. Если хотите увидеть поле Date_Time_IDDoc в таблице движений, то нужно зайти в конфигуратор, и установить для регистра признак, "Быстрая обработка движений". Когда это сделаете то увидите поле Date_Time_IDDoc в таблице RA.

32. ture 608 21.05.15 14:47 Сейчас в теме

(27) (NOLOCK) разве сам не ставится?

37. ivsher 21.05.15 14:59 Сейчас в теме

(32) ture, нет не ставиться. если не указать NOLOCK на таблицу во время чтения будет наложена блокировка. Тут уже надо решать ТС, что ему важнее "чистые" данные или возможные транзакции в системе.

35. ture 608 21.05.15 14:52 Сейчас в теме

(27) а группировка потому что другие измерения у регистра есть? некондиция там..., резерв...

36. ivsher 21.05.15 14:55 Сейчас в теме

(35) ture, да, ну обычно третье измерение это партия, возможно еще фирма... надо смотреть на конфу. А так с группировкой 100 процентов не будет в результирующей таблице дублей товаров.

29. ture 608 21.05.15 14:44 Сейчас в теме

Таблички join ить надо с названиями товаров и складов.

30. m-serg74 46 21.05.15 14:45 Сейчас в теме

(29) ture, опередил:)

33. ivsher 21.05.15 14:47 Сейчас в теме

(29) ture, здесь ничего джоинть не надо, у него есть значение склада и товаров которые надо отобрать из таблицы итогов просто по условию.

34. ture 608 21.05.15 14:50 Сейчас в теме

(33) согласен, если id товаров и складов уже есть в базе сайта.

40. jj_mail 21.05.15 15:22 Сейчас в теме

Там нужно просто двойную тильду поставить. я ведь так понимаю, что остатки нужны на конец периода ?

42. ture 608 21.05.15 15:26 Сейчас в теме

(40) jj_mail, двойная тильда это разве не позиция+id документа?

43. jj_mail 21.05.15 15:30 Сейчас в теме

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

45. AlexO 135 21.05.15 15:36 Сейчас в теме

(43) jj_mail,

что выдает ошибку с одной тильдой

Это же 1cpp-шные запросы, и надо их на форуме 1Cpp задавать.
Вполне вероятно, автор использует "нестабильный" релиз ))

61. ditp 93 21.05.15 16:50 Сейчас в теме

да, поле "date_time_iddoc" это строка, так что для проверки его на меньше, лучше брать left(date_time_iddoc, 8), и сравнивать по "<=".
При проверке на ">" несущественно.

64. succub1_5 89 22.05.15 09:20 Сейчас в теме

(61) ditp, огромное спасибо =)

65. succub1_5 89 22.05.15 09:22 Сейчас в теме

Всем спасибо за помощь, в общем сделал так:

	$query1 = "SEL ECT rg99.sp102
		  FR OM rg99
		  INNER JOIN sc31
		  ON sc31.ID = rg99.sp100
		  INNER JOIN sc33
		  ON sc33.ID = rg99.sp101
	          WHERE sc31.CODE=".$mag."
		  AND sc33.CODE=".$kod."
		  AND rg99.period = '".$date0."'";

Показать

то что ниже, так было сперва, потом оказалось что запрос равносилен последнему упрощенному варианту =)

         
	//$query = "SEL ECT reg.sp100, 
	//	  reg.sp101,
	//	  sum(reg.sp102)
	//	  FR OM 
	//	  (
	//	  SELECT rg99.sp100, rg99.sp101, rg99.sp102
	//	  FR OM rg99 (NOLOCK)		  
	//	  WH ERE rg99.period = '".$date0."'".
	//	  
	//	  UNI ON ALL
	//	  
	//	  SEL ECT ra99.sp100, ra99.sp101,
	//	  ra99.sp102 * CASE WHEN ra99.debkred=1 THEN -1 ELSE 1 END
	//	  FR OM ra99 (NOLOCK)
	//	  INNER JOIN _1sjourn ON ra99.iddoc = _1sjourn.iddoc"."
	//	  AND left(_1sjourn.date_time_iddoc,8) > ".$date1."
	//	  AND left(_1sjourn.date_time_iddoc,8) <= ".$date2."
	//	  ") reg
	//	  
	//	  INNER JOIN sc31 (NOLOCK) on sc31.id = reg.sp100
	//	  INNER JOIN sc33 (NOLOCK) on sc33.id = reg.sp101
	//	  
	//	  WH ERE sc31.CODE=".$mag." AND sc33.CODE =".$kod." 
	//	  GROUP BY reg.sp100, reg.sp101";

Показать

66. AlexO 135 22.05.15 09:29 Сейчас в теме

(65) succub1_5, ну наконец-то ))
А обычный запрос к SQL теперь сам построишь?
Кстати, с твоей "тильдой" так и непонятно, что было.

67. ditp 93 22.05.15 09:38 Сейчас в теме

(65) succub1_5, если уж упрощать, то так:

$query1 = "SEL ECT rg99.sp102
    FR OM rg99
        WHERE rg99.sp100 in (sel ect id fr om sc31 wh ere CODE=".$mag.")
        AND 
        rg99.sp101 in (select id fr om sc33 wh ere CODE=".$kod.")
        AND 
        rg99.period = '".$date0."'";

что бы избавиться от наложения условий уже после джойнов.
Хотя, возможно, скуль сам оптимизирует.
И да, если нужны текущие остатки, то их действительно можно брать просто по последнему period из rg*.

69. succub1_5 89 25.05.15 08:32 Сейчас в теме

(67) ditp, да так вообще красиво =)

68. ture 608 23.05.15 20:31 Сейчас в теме

Inner можно не писать , и так поймет, что Вы от нее хотите.
А вот строк много ожидаете? Группировок то нет.
Если полю басу одно тогда еще top 1 влепите, SQL к этому не равнодушен.

Оставьте свое сообщение

E-mail: