Запрос к базе данных SQL из 1С, ошибка при использовании в 1С

1. gerg 08.12.18 20:34 Сейчас в теме
При запуске запроса в SQL выполняется без ошибок, а при сохранении в процедуре 1С выдает ошибку:
|Restaurants00."<<?>>NAME" AS "Restaurants",
{D:\Разработки 1С\Кафе Тутака\R_KEEPER\SQL.ert(55)}: Ожидается ключевое слово 'КонецПроцедуры' ('EndProcedure')
При проверке модуля обнаружены синтаксические ошибки!

Вот сама процедура:
Процедура ПриОткрытии()
ТекстЗапроса="
|/*
|define:
|Restaurants = Restaurants:Name;
|*/

|sel ect *
|fr om (/*блюда*/
|(SEL ECT
|--PAYBINDINGS.visit,
|Restaurants00."NAME" AS "Restaurants",
|CASHES00."SIFR" AS "CLOSESTATION",
|GlobalShifts00."ShiftDate" AS "SHIFTDATE",
|GlobalShifts00."SHIFTNUM" AS "SHIFTNUM",
|SaleObjects00."Code" AS "CODE",
|SaleObjects00."NAME" AS "DISH",
|MENUITEMS00."GUIDSTRING" AS "GUIDSTRING",
|NULL AS "MODCODE",
|NULL AS "MODNAME",
|PayBindings."Quantity" AS "QUANTITY",
|PayBindings."PriceSum" AS "PRLISTSUM",
|PayBindings."PAYSUM" AS "PAYSUM",
|PayBindings."TAXESADDED" AS "TAXESADDED",
|PayBindings."PaySum"-PayBindings."PriceSum"-PayBindings."TaxesAdded" AS "DiscSum",
|0 AS "QUANTITYMOD",
|0 AS "PRLISTSUMMOD",
|0 AS "COMBODISHUNI",
|CURRENCIES00."NAME" AS "CURRENCY",
|CURRENCIES00."CODE" AS "CURRENCYCODE",
|CashGroups00."NETNAME" AS "NETNAME",
|(select top 1 P.ISPREPAY fr om payments P where (P.Visit = CurrLines00.Visit) AND (P.MidServer = CurrLines00.MidServer) AND (P.CurrLineUNI = CurrLines00.UNI) and (P.CHANGEFORCURR=0)) as "ISPREPAY"
|FROM PAYBINDINGS
|LEFT JOIN SaleObjects SaleObjects00
|ON (SaleObjects00.Visit = PayBindings.Visit) AND (SaleObjects00.MidServer = PayBindings.MidServer) AND (SaleObjects00.DishUNI = PayBindings.DishUNI) AND (SaleObjects00.ChargeUNI = PayBindings.ChargeUNI)
|LEFT JOIN CurrLines CurrLines00
|ON (CurrLines00.Visit = PayBindings.Visit) AND (CurrLines00.MidServer = PayBindings.MidServer) AND (CurrLines00.UNI = PayBindings.CurrUNI)
|LEFT JOIN PrintChecks PrintChecks00
|ON (PrintChecks00.Visit = CurrLines00.Visit) AND (PrintChecks00.MidServer = CurrLines00.MidServer) AND (PrintChecks00.UNI = CurrLines00.CheckUNI)
|LEFT JOIN CashGroups CashGroups00
|ON (CashGroups00.SIFR = PayBindings.Midserver)
|LEFT JOIN Orders Orders00
|ON (Orders00.Visit = PrintChecks00.Visit) AND (Orders00.MidServer = PrintChecks00.MidServer) AND (Orders00.IdentInVisit = PrintChecks00.OrderIdent)
|LEFT JOIN GlobalShifts GlobalShifts00
|ON (GlobalShifts00.MidServer = Orders00.MidServer) AND (GlobalShifts00.ShiftNum = Orders00.iCommonShift)
|LEFT JOIN Restaurants Restaurants00
|ON (Restaurants00.SIFR = CashGroups00.Restaurant)
|LEFT JOIN SessionDishes SessionDishes00
|ON (SessionDishes00.Visit = SaleObjects00.Visit) AND (SessionDishes00.MidServer = SaleObjects00.MidServer) AND (SessionDishes00.UNI = SaleObjects00.DishUNI)
|LEFT JOIN MENUITEMS MENUITEMS00
|ON (MENUITEMS00.SIFR = SessionDishes00.Sifr)
|LEFT JOIN CASHES CASHES00
|ON (CASHES00.SIFR = PrintChecks00.iCloseStation)
|LEFT JOIN CURRENCIES CURRENCIES00
|ON (CURRENCIES00.SIFR = CurrLines00.Sifr)
|where (PrintChecks00."STATE" = 6)
|)

|uni on all
|(/*модификаторы*/
|SELECT
|--DISHMODIFIERS.visit,
|Restaurants00."NAME" AS "Restaurants",
|CASHES00."SIFR" AS "CLOSESTATION",
|GlobalShifts00."ShiftDate" AS "SHIFTDATE",
|GlobalShifts00."SHIFTNUM" AS "SHIFTNUM",
|MenuItems00."Code" AS "CODE",
|MenuItems00."Name" AS "DISH",
|Modifiers00."GUIDSTRING" AS "GUIDSTRING",
|Modifiers00."Code" AS "MODCODE",
|Modifiers00."Name" AS "MODNAME",
|0 AS "QUANTITY",
|0 AS "PRLISTSUM",
|0 AS "PAYSUM",
|0 AS "TAXESADDED",
|0 AS "DiscSum",
|DishModifiers."Pieces" AS "QUANTITYMOD",
|DishModifiers."PRListSum" AS "PRLISTSUMMOD",
|DishModifiers.combodishuni AS "COMBODISHUNI",
|CURRENCIES00."NAME" AS "CURRENCY",
|CURRENCIES00."CODE" AS "CURRENCYCODE",
|CashGroups00."NETNAME" AS "NETNAME",
|(sel ect top 1 P.ISPREPAY fr om payments P wh ere (P.Visit = CurrLines00.Visit) AND (P.MidServer = CurrLines00.MidServer) AND (P.CurrLineUNI = CurrLines00.UNI) and (P.CHANGEFORCURR=0)) as "ISPREPAY"

|FROM DISHMODIFIERS
|LEFT JOIN Modifiers Modifiers00
|ON (Modifiers00.SIFR = DishModifiers.Sifr)
|LEFT JOIN SessionDishes SessionDishes00
|ON (SessionDishes00.Visit = DishModifiers.Visit) AND (SessionDishes00.MidServer = DishModifiers.MidServer) AND (SessionDishes00.UNI = DishModifiers.DishUNI)
|LEFT JOIN MenuItems MenuItems00
|ON (MenuItems00.SIFR = SessionDishes00.Sifr)
|LEFT JOIN OrderSessions OrderSessions00
|ON (OrderSessions00.Visit = SessionDishes00.Visit) AND (OrderSessions00.MidServer = SessionDishes00.MidServer) AND (OrderSessions00.UNI = SessionDishes00.SessionUNI)
|LEFT JOIN Orders Orders00
|ON (Orders00.Visit = OrderSessions00.Visit) AND (Orders00.MidServer = OrderSessions00.MidServer) AND (Orders00.IdentInVisit = OrderSessions00.OrderIdent)
|LEFT JOIN GlobalShifts GlobalShifts00
|ON (GlobalShifts00.MidServer = Orders00.MidServer) AND (GlobalShifts00.ShiftNum = Orders00.iCommonShift)
|LEFT JOIN CashGroups CashGroups00
|ON (CashGroups00.SIFR = DishModifiers.Midserver)
|LEFT JOIN PrintChecks PrintChecks00
|ON (PrintChecks00.Visit = Orders00.Visit) AND (PrintChecks00.MidServer = Orders00.MidServer) AND (PrintChecks00.UNI = Orders00.LastCheckUNI)
|LEFT JOIN CurrLines CurrLines00
|ON (PrintChecks00.Visit = CurrLines00.Visit) AND (PrintChecks00.MidServer = CurrLines00.MidServer) AND (PrintChecks00.UNI = CurrLines00.CheckUNI)
|LEFT JOIN CurrencyTypes CurrencyTypes00
|ON (CurrencyTypes00.SIFR = CurrLines00.iHighLevelType)
|LEFT JOIN Restaurants Restaurants00
|ON (Restaurants00.SIFR = CashGroups00.Restaurant)
|LEFT JOIN CASHES CASHES00
|ON (CASHES00.SIFR = PrintChecks00.iCloseStation)
|LEFT JOIN CURRENCIES CURRENCIES00
|ON (CURRENCIES00.SIFR = CurrLines00.Sifr)
|WHERE
|(DishModifiers."Pieces" BETWEEN 0.001 AND 999991)
|AND (DishModifiers.combodishuni = 0)
|)
|) as U
|WHERE U.SHIFTDATE='ДатаЗапроса'
|";
КонецПроцедуры
По теме из базы знаний
Найденные решения
5. gerg 08.12.18 22:31 Сейчас в теме
Спасибо за подсказку.
Нашел также еще одно решение, формат должен быть типа :yyyymmddhhmmss
а для 1с следующий порядок:
Стр = стрЗаменить(ТекстЗапроса, "CHECK_DATE", "'" + Формат(ДатаЗапроса, "ДЗ='yyyymmddhhmmss'") + "'");
Cmd.CommandText = Стр;

в запросе "ДатаЗапроса" заменил на "CHECK_DATE". "ДатаЗапроса" - поле в форме типа дата, для ввода даты

Спасибо всем
Остальные ответы
В избранное Подписаться на ответы Сортировка: Древо развёрнутое
Свернуть все
2. gerg 08.12.18 21:08 Сейчас в теме
Указанная ошибка устранена.
Как правильно использовать переменную из 1С - ДатаЗапроса
3. gerg 08.12.18 21:09 Сейчас в теме
Подскажите, не сочтите за труд. СП.
4. acanta 08.12.18 21:12 Сейчас в теме
|WHERE U.SHIFTDATE='"+Формат(ДатаЗапроса,"ДФ=dd.MM.yyyy")+"'
5. gerg 08.12.18 22:31 Сейчас в теме
Спасибо за подсказку.
Нашел также еще одно решение, формат должен быть типа :yyyymmddhhmmss
а для 1с следующий порядок:
Стр = стрЗаменить(ТекстЗапроса, "CHECK_DATE", "'" + Формат(ДатаЗапроса, "ДЗ='yyyymmddhhmmss'") + "'");
Cmd.CommandText = Стр;

в запросе "ДатаЗапроса" заменил на "CHECK_DATE". "ДатаЗапроса" - поле в форме типа дата, для ввода даты

Спасибо всем
Оставьте свое сообщение
Вакансии
Разработчик 1С
Москва
зарплата от 200 000 руб.
Полный день

Ведущий программист 1С
Москва
зарплата от 180 000 руб.
Полный день

Консультант 1С ERP
Москва
зарплата от 200 000 руб. до 250 000 руб.
Полный день

Разработчик 1С
Москва
зарплата от 220 000 руб. до 300 000 руб.
Полный день

Аналитик 1C
Москва
зарплата от 200 000 руб. до 240 000 руб.
Полный день