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

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

Спасибо всем
+
Внимание! Тема сдана в архив

Для получения уведомлений об ответах подключите телеграм бот:
Инфостарт бот