SQL запрос #401191

Надо мне написать простенький отчет. решил, что пора осваивать sql-запросы. почитал и . Начал пробовать выбирать документы и их реквизиты. С реквизитами шапки все получилось, а вот с общими почему-то не хочет. Если в строке "|$ДокОбщ.Фирма as Фирма," ставить $ - ошибка "Meta name parser error: неизвестное метаимя или алиас "$ДокОбщ"" если не ставить - ошибка "State 42S22, native 207, message [Microsoft][ODBC SQL Server Driver][SQL Server]Invalid column name 'Фирма'." если типизировать [Фирма $Справочник.Контрагенты] - то же самое. в чем ошибка?

ДокОбщ.$ОбщийРеквизит.Фирма as Фирма

Грац!!

Странно, автор, фирма выбрались нормально, а комментарий не хочет. В чем ошибка?

комментарий - это общий реквизит?

С чем ты его сейчас поздравил-то?

Случаем не строка неограниченной длины?

Да, общий реквизит неограниченной длины

это я так спасибо говорю. Большое

тогда CAST(ДокОбщ.$ОбщийРеквизит.Комментарий AS String) а чего - нада указывать ДокОбщ.$ОбщийРеквизит? вроде работает и $ОбщийРеквизит...

у меня ДокОбщ.$ОбщийРеквизит работает, ща попробую и без него

Да, пофиг можно указывать, можно нет. а вот с комментарием не прошло. Добавил строку |CAST($ОбщийРеквизит.Комментарий AS String) as Коммент, Правильно? ошибка: State 42000, native 243, message [Microsoft][ODBC SQL Server Driver][SQL Server]Type String is not a defined system type.

либо State 42S22, native 207, message [Microsoft][ODBC SQL Server Driver][SQL Server]Invalid column name 'sp660'. при строке |CAST(ДокОбщ.$ОбщийРеквизит.Комментарий AS String) as Коммент,

varchar

Ага, сработало. причем только вариант |CAST($ОбщийРеквизит.Комментарий AS varchar) as Коммент, Этот: |CAST(ДокОбщ.$ОбщийРеквизит.Комментарий AS varchar) as Коммент, выдал ошибку. Спасибо!

отбор не стоит, отсюда и ошибка.

Продолжим. За сутки я проапгрейдился. В результате родился такой вот работающий запрос по одному типу документов Работает замечательно, но по одному виду документов, а мне надо по 5ти.

В результате модернизации родилось вот это: Работать не хочет. State 42000, native 170, message [Microsoft][ODBC SQL Server Driver][SQL Server]Line 22: Incorrect syntax near '_1SJOURN'. Ошибки не вижу. Какие предложения??

торможу. удалил строку щас выдало State 42S22, native 207, message [Microsoft][ODBC SQL Server Driver][SQL Server]Invalid column name 'sp4056'. копаю дальше

From Зы.Ну и хинты nolock добавь

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

это раз. и второе у таблицы ДокументСтроки всегда есть Ссылка на документ, которому они пренадлежат.

| _1SJOURN as ДокОбщ (nolock) нет, та же ошибка. а для чего вообще (nolock) нужен и в каких случаях применим?? только начал копать эту область.

nolock - так называемое "грязное чтение". упускает заблокированные записи.

т.е. не упускает, а читает уже имеющиеся зафиксированные данные

а при отсутствии (nolock) и попытке прочитать заблокированные что произойдет?

Ждать будет.

wait wait WAIT smoking :)))

| _1SJOURN as ДокОбщ (NoSmoking) =) ммм... не понял присоединю я таблицы, а нужные колонки как потом выбрать?? я же это задаю в теле Select ...

а у тебя контрагент есть. тогда все равно две таблицы нужны. Но все равно так быстрей будет.

Не надо цеплять к журналу шапки, а к шапкам строки. Цепляй строки прямо к журналу. И не надо Коалеске для общих реквизитов.

а вообще что за отчет и конечная цель

Автор, вот глянь еще, может пригодится:

отчет по документам (5 видов) за период по контрагенту (элемент, группа), по ТМЦ (элемент, группа). по данным номенклатурам выводить кол-во, цену. ну и также фирма, контрагент, комментарий

+1. А еще можно попробовать, сначала выбрать строки с Контрагентом, а пото цепануть к общим. Надо короче мерить как быстрей будет. Но 2-ой вариант должен быстрей работать.

то есть блоки    |LEFT JOIN    |    $Документ.Реализация as ДокРеал ON ДокРеал.IDDoc = ДокОбщ.IDDoc заменить на: ??

Ага.

то 36 UNION Вам поможет а вообще cast, coalese , case  , left и прочее отвечают за представление данных , не за выборку .Closed & 1 = 1 уменьшает селективность индекса раз в 10

И COALESCE (ДокРеал.$ОбщийРеквизит.Фирма, ДокВозвр.$ОбщийРеквизит.Фирма, ДокПост.$ОбщийРеквизит.Фирма,

из отладки: SELECT ДокОбщ.IDDOC as [Код $Документ], ДокОбщ.DocNo as НомерДок, ДокОбщ.IDDOCDEF as ДокВид, CAST(LEFT(ДокОбщ.Date_Time_IDDoc, 8) as DateTime) as ДатаДок, sp4056 as [Фирма $Справочник.Фирмы], CAST(sp660 AS varchar) as Коммент, COALESCE (ДокРеал.sp1583, ДокВозвр.sp1629, ДокПост.sp1555, FROM    _1SJOURN as ДокОбщ (nolock) LEFT JOIN    dh1611 as ДокРеал ON ДокРеал.IDDoc = ДокОбщ.IDDoc LEFT JOIN    dt1611 as ДокРеалС ON ДокРеалС.IDDoc = ДокОбщ.IDDoc LEFT JOIN    dh1656 as ДокВозвр ON ДокВозвр.IDDoc = ДокОбщ.IDDoc LEFT JOIN    dt1656 as ДокВозврС ON ДокВозврС.IDDoc = ДокОбщ.IDDoc LEFT JOIN    dh1582 as ДокПост ON ДокПост.IDDoc = ДокОбщ.IDDoc LEFT JOIN    dt1582 as ДокПостС ON ДокПостС.IDDoc = ДокОбщ.IDDoc LEFT JOIN    dh1684 as ДокВозПост ON ДокВозПост.IDDoc = ДокОбщ.IDDoc LEFT JOIN    dt1684 as ДокВозПостС ON ДокВозПостС.IDDoc = ДокОбщ.IDDoc LEFT JOIN    dh1628 as ДокПерем ON ДокПерем.IDDoc = ДокОбщ.IDDoc LEFT JOIN    dt1628 as ДокПеремС ON ДокПеремС.IDDoc = ДокОбщ.IDDoc WHERE На этот раз что-то новое :) Ambiguous column name 'SP660'.

"CAST(sp660 AS varchar) as Коммент" раньше отрабатывало нормально

CAST($ОбщийРеквизит.Комментарий AS varchar) as Коммент,

вот так может быть. А так ты вводишь не известную таблицу sp660

да, вру в коде у меня CAST($ОбщийРеквизит.Комментарий AS varchar) as Коммент, это сейчас сюда скопировал из строчек выше

сюда в окно сообщений скопировал из . в коде отчета нормально

в sp660 разрешается имя "Комментарий". Т.к. запрос идет к _1sJournal, а там такого нет (нет отбора по Комментарию, поэтому не в журнале лежит, а в каждой шапке) - оно и ругается.

sp660 - это справочник. но не как не общий реквизит. Посмотри в DDS файле за что отвечает 660 таблица

ну значит и берет из шапки. Какая проблема.

F=SP660                 |(P)Комментарий      |M   |0     |0 это общий реквизит, в шапке каждого документа

Значит все-таки через COALESCE надо к ним

Выпил? объясняю еще раз: CAST($ОбщийРеквизит.Комментарий AS varchar) as Коммент, ... FROM   _1SJOURN as ДокОбщ (nolock) разрешается в ... CAST(sp660 AS varchar) as Коммент, ... FROM   _1SJOURN as ДокОбщ (nolock) т.е. в выборку поля sp660 из таблицы _1SJOURN, а там такого поля  нет - оно лежит в dhxxx. И с чего ты взял, что это справочник? Обычный реквизит...

и я гипотезу двину, авось угадаю :) Комментарий - общий реквизит без отбора, следовательно поле располагается в шапках документов. Шапок документов у тебя много, а алиас ты не указал - sql-сервер и говорит, что поле он видит, но не знает из какой именно таблицы DHxxx брать.

через COALESCE тоже не получается - Invalid column name 'sp4056' нет, все-таки мне кажется, что CAST(sp660 AS varchar) as Коммент это правильно. тут ведь ошибка другая - Ambiguous column name 'SP660'. что значит Ambiguous??

и как его указать??

я может не по-русски написал?

у тебя в было правильно

+56 эта, у тебя там поле Комментарий было правильно описано, а за все остальное я не ручаюсь. Тем более, что там журн в воздухе висит :)

Все клево, все получилось! Респект товарищу Кириллка!!! Действительно ввиду отбора единственно верно так: Еще добавил фильтрацию по купля-продажа / комиссия. Вообще сказка. Спасибо всем большое. И возник еще вопросец. Как из IDDOCDEF получить вид как он задан в конфигураторе? Типизацией не получается.

$ВидДокумента

имхо, указывая общий реквизит Фирма без алиаса, закладываешь себе мину замедленного действия.

???? Он же из журнала выбирает...

там же по-русски написано "замедленного действия".

Эмм.. что значит алиас? "as Фирма" - это не алиас?

да, что такое алиас я понял. но при указании алиаса у фирмы у меня вообще работать отказывался. т.е. если общий реквизит с отбором,то он в _1SJOURN и указывать алиас не нужно, если без отбора - то в DHxxxx и алиас нужен

ДокОбщ.$ОбщийРеквизит.Фирма as [Фирма $Справочник.Фирмы]

понял :)

Продолжим. По совету МихаилаМ пробую сделать через UNION. т.е. в цикле прицепляю UNION в зависимости от выбранных типов доков на форме. но вот какая фигня получается: Вот этот запрос: Док.IDDOC as [Док $Документ.Реализация], ДокОбщ.DocNo as НомерДок, ДокОбщ.IDDOCDEF as Док_вид, Cast(Left(ДокОбщ.Date_Time_IDDoc, 8) as DateTime) as ДатаДок, ДокОбщ.$ОбщийРеквизит.Фирма as [Фирма $Справочник.Фирмы],   Cast(Док.$ОбщийРеквизит.Комментарий AS varchar) as Коммент, $Док.Контрагент as [Контрагент $Справочник.Контрагенты], $Док.КодОперации as [КодОперации $Перечисление.КодыОпераций], $ДокСтр.Количество as Количество, $ДокСтр.Цена as Цена,   $ДокСтр.Номенклатура as [Номенклатура $Справочник.Номенклатура]   FROM $Документ.Реализация as Док INNER JOIN _1SJOURN As ДокОбщ ON (Док.IDDOC = ДокОбщ.IDDOC) INNER JOIN $ДокументСтроки.Реализация as ДокСтр ON (Док.IDDOC = ДокСтр.IDDOC) WHERE    ДокОбщ.Date_Time_IDDoc BETWEEN :НачДата AND :КонДата~ Док.IDDOC as [Док $Документ.ВозвратОтПокупателя], ДокОбщ.DocNo as НомерДок, ДокОбщ.IDDOCDEF as Док_вид, Cast(Left(ДокОбщ.Date_Time_IDDoc, 8) as DateTime) as ДатаДок, ДокОбщ.$ОбщийРеквизит.Фирма as [Фирма $Справочник.Фирмы],   Cast(Док.$ОбщийРеквизит.Комментарий AS varchar) as Коммент, $Док.Контрагент as [Контрагент $Справочник.Контрагенты], $Док.КодОперации as [КодОперации $Перечисление.КодыОпераций], $ДокСтр.Количество as Количество, $ДокСтр.Цена as Цена,   $ДокСтр.Номенклатура as [Номенклатура $Справочник.Номенклатура]   FROM $Документ.ВозвратОтПокупателя as Док INNER JOIN _1SJOURN As ДокОбщ ON (Док.IDDOC = ДокОбщ.IDDOC) INNER JOIN $ДокументСтроки.ВозвратОтПокупателя as ДокСтр ON (Док.IDDOC = ДокСтр.IDDOC) WHERE    ДокОбщ.Date_Time_IDDoc BETWEEN :НачДата AND :КонДата~ Док.IDDOC as [Док $Документ.Реализация], ДокОбщ.DocNo as НомерДок, ДокОбщ.IDDOCDEF as Док_вид, Cast(Left(ДокОбщ.Date_Time_IDDoc, 8) as DateTime) as ДатаДок, ДокОбщ.sp4056 as [Фирма $Справочник.Фирмы],   Cast(Док.sp660 AS varchar) as Коммент, Док.sp1629 as [Контрагент $Справочник.Контрагенты], Док.sp3340 as [КодОперации $Перечисление.КодыОпераций], ДокСтр.sp1645 as Количество, ДокСтр.sp1648 as Цена,   ДокСтр.sp1644 as [Номенклатура $Справочник.Номенклатура]   FROM dh1611 as Док INNER JOIN _1SJOURN As ДокОбщ ON (Док.IDDOC = ДокОбщ.IDDOC) INNER JOIN dt1611 as ДокСтр ON (Док.IDDOC = ДокСтр.IDDOC) WHERE Док.IDDOC as [Док $Документ.ВозвратОтПокупателя], ДокОбщ.DocNo as НомерДок, ДокОбщ.IDDOCDEF as Док_вид, Cast(Left(ДокОбщ.Date_Time_IDDoc, 8) as DateTime) as ДатаДок, ДокОбщ.sp4056 as [Фирма $Справочник.Фирмы],   Cast(Док.sp660 AS varchar) as Коммент, Док.sp1629 as [Контрагент $Справочник.Контрагенты], Док.sp3340 as [КодОперации $Перечисление.КодыОпераций], ДокСтр.sp1645 as Количество, ДокСтр.sp1648 as Цена,   ДокСтр.sp1644 as [Номенклатура $Справочник.Номенклатура]   FROM dh1656 as Док INNER JOIN _1SJOURN As ДокОбщ ON (Док.IDDOC = ДокОбщ.IDDOC) INNER JOIN dt1656 as ДокСтр ON (Док.IDDOC = ДокСтр.IDDOC) WHERE Обратите внимание на Док.sp1629 as [Контрагент $Справочник.Контрагенты]и т.д... в обоих UNION/ sp1629 - относится к Возврату, у реализации - sp1583 Т.е. почему-то берет не те таблицы. В чем причина?

как тут текст форматировать??? по тексту не пропустите "Разрешается в"

ни[пилятскийматофильтр]acилил[пилятскийматофильтр]многa[пилятскийматофильтр]букaв

дык отож. я так и подумал, что народу просто впадлу читать будет:) здесь в сообщении можно текст форматировать? Проблема если коротко: $Док.Контрагент as [Контрагент $Справочник.Контрагенты], FROM $Документ.Реализация as Док UNION SELECT $Док.Контрагент as [Контрагент $Справочник.Контрагенты], FROM $Документ.ВозвратОтПокупателя as Док РАСПОЗНАЕТСЯ В ИТОГЕ ТАК: SELECT sp1629 as [Контрагент $Справочник.Контрагенты], FROM dh1611 as Док UNION SELECT sp1629 as [Контрагент $Справочник.Контрагенты], FROM dh1656 as Док А ДОЛЖНО БЫТЬ ТАК: SELECT sp1583 as [Контрагент $Справочник.Контрагенты], FROM dh1611 as Док UNION SELECT sp1629 as [Контрагент $Справочник.Контрагенты], FROM dh1656 as Док Почему??

Мне так кажется алиасы разные надо дать для документов.

используй разные алиасы, это "особенность" парсера.

но тогда это будут разные колонки в таблице. как их потом правильно слепить чтобы на выходе была таблица с 10ю а не 50ю колонками?

coalesce

С чего бы это им быть разными колонками при UNION. От алиаса это не зависит, это зависит от того что у тебя в Select.

как ты расположишь в выборе полей данные, так они и состыковываться будут

Мда... Я конечно еще только осваиваю SQL, но в умной книжке только что вычитал, что при использовании UNION набор столбцов в таблицах должен совпадать, иначе UNION не работает. Т.е. алиасы должны быть одинаковыми.

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

Алиасы в Select, то есть SELECT $ДокР.Контрагент as [Контрагент $Справочник.Контрагенты], FROM $Документ.Реализация as ДокР UNION SELECT $ДокВ.Контрагент as [Контрагент $Справочник.Контрагенты], FROM $Документ.ВозвратОтПокупателя as ДокВ

+ Должны быть одинаковые

Причем тут алиасы. На выходе в каждом запросе выборки у тебя должны быть одни и те же "данные".

или в общем смысле +

дА,ПОНЯЛ. Пока что выдало ORDER BY items must appear in the select list if the statement contains a UNION operator

Запрос такой: SELECT ВозвратОтПокупателя.IDDOC as [Док $Документ.ВозвратОтПокупателя], ДокОбщ.DocNo as НомерДок, ДокОбщ.IDDOCDEF as Док_вид, Cast(Left(ДокОбщ.Date_Time_IDDoc, 8) as DateTime) as ДатаДок, ДокОбщ.$ОбщийРеквизит.Фирма as [Фирма $Справочник.Фирмы],   Cast(ВозвратОтПокупателя.$ОбщийРеквизит.Комментарий AS varchar) as Коммент, $ВозвратОтПокупателя.Контрагент as [Контрагент $Справочник.Контрагенты], $ВозвратОтПокупателя.КодОперации as [КодОперации $Перечисление.КодыОпераций], $ВозвратОтПокупателяСтр.Количество as Количество, $ВозвратОтПокупателяСтр.Цена as Цена,   $ВозвратОтПокупателяСтр.Номенклатура as [Номенклатура $Справочник.Номенклатура]   FROM $Документ.ВозвратОтПокупателя as ВозвратОтПокупателя INNER JOIN _1SJOURN As ДокОбщ ON (ВозвратОтПокупателя.IDDOC = ДокОбщ.IDDOC) INNER JOIN $ДокументСтроки.ВозвратОтПокупателя as ВозвратОтПокупателяСтр ON (ВозвратОтПокупателя.IDDOC = ВозвратОтПокупателяСтр.IDDOC) WHERE    ДокОбщ.Date_Time_IDDoc BETWEEN :НачДата AND :КонДата~ ПоступлениеТМЦ.IDDOC as [Док $Документ.ПоступлениеТМЦ], ДокОбщ.DocNo as НомерДок, ДокОбщ.IDDOCDEF as Док_вид, Cast(Left(ДокОбщ.Date_Time_IDDoc, 8) as DateTime) as ДатаДок, ДокОбщ.$ОбщийРеквизит.Фирма as [Фирма $Справочник.Фирмы],   Cast(ПоступлениеТМЦ.$ОбщийРеквизит.Комментарий AS varchar) as Коммент, $ПоступлениеТМЦ.Контрагент as [Контрагент $Справочник.Контрагенты], $ПоступлениеТМЦ.КодОперации as [КодОперации $Перечисление.КодыОпераций], $ПоступлениеТМЦСтр.Количество as Количество, $ПоступлениеТМЦСтр.Цена as Цена,   $ПоступлениеТМЦСтр.Номенклатура as [Номенклатура $Справочник.Номенклатура]   FROM $Документ.ПоступлениеТМЦ as ПоступлениеТМЦ INNER JOIN _1SJOURN As ДокОбщ ON (ПоступлениеТМЦ.IDDOC = ДокОбщ.IDDOC) INNER JOIN $ДокументСтроки.ПоступлениеТМЦ as ПоступлениеТМЦСтр ON (ПоступлениеТМЦ.IDDOC = ПоступлениеТМЦСтр.IDDOC) WHERE    ДокОбщ.Date_Time_IDDoc BETWEEN :НачДата AND :КонДата~

Убери ORDER BY из UNION, сделай этот свой запрос подзапросом, а потом уже сортируй результат.

Соответственно в подзапросе убери типизацию, а типизируй уже результат.

Имелось ввиду: Select    Результат.Док as [Док $Документ],    Результат.Док_вид as Док_вид,    Результат.ДатаВремя as ДатаВремя,    ..... From (ТекстТвоегоЗапросаБезORDERBYИТипизации) as Результат ORDER BY Результат.ДатаВремя Ну и добаввь в свой ДокОбщ.Date_Time_IDDoc as ДатаВремя