Объединение запросов (UNION)

← →
Jaxtor (2003-05-13 12:49) [0]

У меня есть запрос:
select Stock from Stock where StockID=6
Есть еще другой запрос:
select st.*, gr.VenStGr, gr.Data from VENSTOCK st, VENSTGR gr where st.VenStGrId=gr.VenStGrId and (PN like "%HW0533B%") order by st.STOCK desc, st.TRANZIT1 desc, st.TRANZIT2 desc, st.TRANZIT3 desc, st.TRANZIT4 desc
Нужно сделать объединение этих запросов.

Пишу,значит :
select Stock from Stock where StockID=6 union select st.*, gr.VenStGr, gr.Data from VENSTOCK st, VENSTGR gr where st.VenStGrId=gr.VenStGrId and (PN like "%HW0533B%") order by st.STOCK desc, st.TRANZIT1 desc, st.TRANZIT2 desc, st.TRANZIT3 desc, st.TRANZIT4 desc
Ошибка появляется:

Invalid token.
Dynamic SQL Error.
SQL error code = -104.
Invalid command.
count of column list and variable list do not match.

Как сделать так, чтобы если у первой таблицы число полей меньше,
чем во второй, то недостающие в первой таблице поля имели значение NULL?

← →
Zacho (2003-05-13 12:58) [1]

Сделать так:
select Stock, CAST(NULL AS нужный_тип), CAST(NULL AS ..), ... from Stock where StockID=6

← →
Jaxtor (2003-05-13 13:09) [2]

>Zacho А как сделать соответствие полей:
поле VENSTRGR - 15-е по счету во втором запросе должно соответствовать первому полю в первом запросе?

← →
Соловьев (2003-05-13 13:19) [3]

SELECT snum, sname
FROM Salespeople
WHERE city = "London"

UNION

SELECT cnum, cname
FROM Customers
WHERE city = "London"

← →
Zacho (2003-05-13 13:20) [4]

> Jaxtor © (13.05.03 13:09)

Нет. Первое должно соответствовать первому, второе - второму и т.д. Да, еще одно: имена соответствующих полей должны совпадать

← →
Jaxtor (2003-05-13 13:21) [5]

ясненько.

← →
Johnmen (2003-05-13 15:50) [6]

>Zacho © (13.05.03 13:20)
>Да, еще одно: имена соответствующих полей должны совпадать

Не обязательно...:)

← →
Desdechado (2003-05-13 16:45) [7]

положение в таблице не важно, важно положение в запросе

← →
Jaxtor (2003-05-13 16:50) [8]

select gr.VenStGr, st.productid, st.venstgrid, st.PN,st.Product,st.comment, st.stock, st.forsale,st.tranzit1,st.tranzit2,st.tranzit3,st.tranzit4,st.price,st.oldprice,st.priceupdatedate,gr.data from VENSTOCK st,VENSTGR gr where st.VenStGrId=gr.VenStGrId and (PN like "%HW0533B%") order by st.STOCK desc, st.TRANZIT1 desc, st.TRANZIT2 desc, st.TRANZIT3 desc, st.TRANZIT4 desc union select cast (Stock as Varchar(63)), cast(NULL AS Integer), cast(NULL AS Integer), cast(NULL AS Varchar(63)), cast(NULL AS Varchar(255)), cast(NULL AS Varchar(63)), si.quantity, cast(NULL AS Integer), cast(NULL AS Integer), cast(NULL AS Integer), cast(NULL AS Integer), cast(NULL AS Integer), cast(NULL AS Double Precision), cast(NULL AS Double Precision), cast(NULL AS TimeStamp), cast(NULL AS TimeStamp) from Stock s,stockitem si where (s.stockid=6) and (si.stockitemid=3164)

Выдает ошибку:

Invalid token.
Dynamic SQL Error.
SQL error code = -104.
Token unknown - line 9, char -1.
union.

← →
Jaxtor (2003-05-13 16:52) [9]

Почему???

← →
Johnmen (2003-05-13 16:59) [10]

ORDER BY лишнее. И так будет упорядочено...

← →
Jaxtor (2003-05-13 17:00) [11]

>Johnmen
Ладно. Практика покажет:)

← →
NDeu (2003-05-13 17:03) [12]

Попробуй без order by

← →
NDeu (2003-05-13 17:06) [13]

Опоздал :(

← →
Johnmen (2003-05-13 17:10) [14]

Более того, если посмотреть соответствующую документацию по SQL, то можно узнать некоторые ограничения и особенности запросов в объединении...:)

← →
Jaxtor (2003-05-13 17:44) [15]

Теперь нужно чтобы последовательность была другой для запроса,
сначала должен выводиться запрос после UNION для исх. запроса.
Исх. запрос:
select gr.VenStGr, st.productid, st.venstgrid, st.PN,st.Product,st.comment, st.stock, st.forsale,st.tranzit1,st.tranzit2,st.tranzit3,st.tranzit4,st.price,st.oldprice,st.priceupdatedate,gr.data from VENSTOCK st,VENSTGR gr where st.VenStGrId=gr.VenStGrId and (PN like "%HW0533B%") union select cast (Stock as Varchar(63)), cast(NULL AS Integer), cast(NULL AS Integer), cast(NULL AS Varchar(63)), cast(NULL AS Varchar(255)), cast(NULL AS Varchar(63)), si.quantity, cast(NULL AS Integer), cast(NULL AS Integer), cast(NULL AS Integer), cast(NULL AS Integer), cast(NULL AS Integer), cast(NULL AS Double Precision), cast(NULL AS Double Precision), cast(NULL AS TimeStamp), cast(NULL AS TimeStamp) from Stock s,stockitem si where (s.stockid=6) and (si.stockitemid=3164)

Я изменил его на:
select cast (Stock as Varchar(63)), cast(NULL AS Integer), cast(NULL AS Integer), cast(NULL AS Varchar(63)), cast(NULL AS Varchar(255)), cast(NULL AS Varchar(63)), si.quantity, cast(NULL AS Integer), cast(NULL AS Integer), cast(NULL AS Integer), cast(NULL AS Integer), cast(NULL AS Integer), cast(NULL AS Double Precision), cast(NULL AS Double Precision), cast(NULL AS TimeStamp), cast(NULL AS TimeStamp) from Stock s,stockitem si where (s.stockid=6) and (si.stockitemid=3164) union select gr.VenStGr, st.productid, st.venstgrid, st.PN,st.Product,st.comment, st.stock, st.forsale,st.tranzit1,st.tranzit2,st.tranzit3,st.tranzit4,st.price,st.oldprice,st.priceupdatedate,gr.data from VENSTOCK st,VENSTGR gr where st.VenStGrId=gr.VenStGrId and (PN like "%HW0533B%") - не помогает.

← →
Johnmen (2003-05-13 21:25) [16]

См. Johnmen © (13.05.03 17:10)

← →
kaif (2003-05-14 02:01) [17]

Кстати, сегодня на работе на Yaffils build-821 у меня на ХП с запросом с UNION сервер прерывал соединение. Хотя тот же запрос пару дней назад работал. Причем дома, когда я попробовал его из под Delphi отладчик успел сказать, что невозможно извлечь план. Затем запрос 1 раз сработал. Затем (на повторный запрос) сервер разорвал соединение. Советую с UNION не связываться. Делайте ХП и возвращайте каждый набор с помощью своего SUSPEND, расположив их друг под другом.
Кстати, еще учтите, что если не применять UNION ALL, то совпадающие строки будут превращаться в одну. Хотя в данном случае это, видимо, неважно, так как большинство полей одного из запросов NULL.
У меня такое ощущение, что на днях на форуме мелькал вопрос о странном разрыве соединения. Я не первый раз сталкиваюсь с глюками при UNION. Особенно глючат планировщики запросов.
Хотя иногда UNION удобен безусловно...

← →
Sergey13 (2003-05-14 08:48) [18]

2kaif © (14.05.03 02:01)
>...Советую с UNION не связываться...
Тут на днях мужика машина сбила, когда он на красный свет дорогу перебегал. Так вот - советую дорогу вообще не пересекать. 8-)
ИМХО, более чем странный совет. Не ожидал... 8-(

← →
Alexandr (2003-05-14 09:48) [19]

в таком случае с select лучше тоже не связываться.
А вот у одного чувака был селект из процедуры join таблица, и у него сервак падал...
Да и вообще. программированием лучше не заниматься, а то был один программист он однажды программировал, сидел за компьютером, и ему на голову кирпич упал. И все...

:)

← →
Jaxtor (2003-05-14 10:34) [20]

select cast (Stock as Varchar(63)) as VENSTGR, cast(NULL AS Integer) as PRODUCTID, cast(NULL AS Integer) as VENSTGRID, cast(NULL AS Varchar(63)) as PN, cast(NULL AS Varchar(255)) as PRODUCT, cast(NULL AS Varchar(63)) as COMMENT, si.quantity as STOCK, cast(NULL AS Integer) as FORSALE, cast(NULL AS Integer) as TRANZIT1, cast(NULL AS Integer) as TRANZIT2, cast(NULL AS Integer) as TRANZIT3, cast(NULL AS Integer) as TRANZIT4, cast(NULL AS Double Precision) as PRICE, cast(NULL AS Double Precision) as OLDPRICE, cast(NULL AS TimeStamp) as PRICEUPDATEDATE, cast(NULL AS TimeStamp) as DATA from Stock s,stockitem si where (s.stockid=6) and (si.stockitemid=3164) union select gr.VenStGr, st.productid, st.venstgrid, st.PN,st.Product,st.comment, st.stock, st.forsale,st.tranzit1,st.tranzit2,st.tranzit3,st.tranzit4,st.price,st.oldprice,st.priceupdatedate,gr.data from VENSTOCK st,VENSTGR gr where st.VenStGrId=gr.VenStGrId and (PN like "%HW0533B%") Ну почему же все-таки сначала выводится запрос после оператора UNION? Или это тайна за семью печатями?

← →
kaif (2003-05-14 10:58) [21]

:))
Согласен, что в "советую не связываться с UNION" спорол глупость. Но меня можно понять (хотя бы эмоционально), так как я именно эти пару дней задолбался с разрывом соединения, иногда (!) происходившем в банальном запросе с UNION. Прошу уважаемую публику меня простить. Беру свои слова назад.

>Ну почему же все-таки сначала выводится запрос после оператора >UNION? Или это тайна за семью печатями?

Что значит сначала выводится? Если имеется в виду выходной набор, то вероятно дело в том, что при упорядочивании значения NULL оказываются в конце.
Кстати, упорядочиванием можно управлять, насколько я помню и в запросах с UNION, если использовать в самом конце запроса выражение вроде ORDER BY 1,2 (упорядочить по первой и второй колонке, к примеру)

Я часто применял следующий прием, если мне нужно было в запросе с UNION выводить четко последовательно 3 результирующих набора. Я заводил поле INTEGER, в котором возвращал
SELECT CAST((1) AS INTEGER), ...
FROM ...
WHERE...
UNION ALL
SELECT CAST((2) AS INTEGER), ...
FROM ...
WHERE...
UNION ALL
SELECT CAST((3) AS INTEGER), ...
FROM ...
WHERE...
ORDER BY 1

← →
Alexandr (2003-05-14 11:01) [22]

2 Jaxtor напиши union all
1) тебе так и надо
2) Будет быстрее
3) Будет так. как тебе надо

2 kaif
1)что, простой запрос с union приводит к падению сервера? Сервер-то какой? Сторанно как-то...
2) По-моему union all попорядку веведет, это для union нужны такие хитрости

← →
Jaxtor (2003-05-14 11:06) [23]

>Alexandr Есть! UNION ALL помог!! Спасибо!!!

← →
Zacho (2003-05-14 11:06) [24]

← →
kaif (2003-05-14 16:50) [25]

Мой запрос с UNION:

CREATE PROCEDURE "BED_COMPLECTS_CAN_BE_RESTORED"(
"STOCK" INTEGER,
"ADATE" DATE
)
RETURNS(
"ID" INTEGER,
"QUANTITY" FLOAT
)
AS
BEGIN
FOR SELECT
BC.ID,
GIS.QUANTITY/KS.DETAIL_QUANTITY
FROM
KIND_SET KS,
PILLOW_CASE PC,
BED_COMPLECTS BC,
BED_CLOTHES BC1,
BED_CLOTHES BC2,
GOODS_IN_STOCK(:STOCK, :ADATE) GIS
WHERE
PC.ID = GIS.GOODS AND
BC.COMPLECT_KIND = KS.COMPLEX_KIND AND
BC1.ID = PC.ID AND BC2.ID = BC.ID AND
BC1.BED_DECOR = BC2.BED_DECOR AND
PC.PILLOW_CASE_KIND = KS.DETAILS
/* UNION */
INTO :ID, :QUANTITY
DO SUSPEND; /*вставлено вместо UNION*/

FOR SELECT
BC.ID,
GIS.QUANTITY/KS.DETAIL_QUANTITY
FROM
KIND_SET KS,
BLANKET_COVER PC,
BED_COMPLECTS BC,
BED_CLOTHES BC1,
BED_CLOTHES BC2,
GOODS_IN_STOCK(:STOCK, :ADATE) GIS
WHERE
PC.ID = GIS.GOODS AND
BC.COMPLECT_KIND = KS.COMPLEX_KIND AND
BC1.ID = PC.ID AND BC2.ID = BC.ID AND
BC1.BED_DECOR = BC2.BED_DECOR AND
PC.DUVET_COVER_KIND = KS.DETAILS
/* UNION */
INTO :ID, :QUANTITY
DO SUSPEND; /*вставлено вместо UNION*/

FOR SELECT
BC.ID,
GIS.QUANTITY/KS.DETAIL_QUANTITY
FROM
KIND_SET KS,
BED_SHEET PC,
BED_COMPLECTS BC,
BED_CLOTHES BC1,
BED_CLOTHES BC2,
GOODS_IN_STOCK(:STOCK, :ADATE) GIS
WHERE
PC.ID = GIS.GOODS AND
BC.COMPLECT_KIND = KS.COMPLEX_KIND AND
BC1.ID = PC.ID AND BC2.ID = BC.ID AND
BC1.BED_DECOR = BC2.BED_DECOR AND
PC.BED_SHEET_KIND = KS.DETAILS
INTO :ID, :QUANTITY
DO SUSPEND;
END

Раньше вместо всех этих SUSPEND был UNION, и лишь в конце один SUSPEND.
Сервер рушился, когда делался запрос из этой ХП

SELECT
ID,
O1.SHORT_NAME ITEM_NAME,
MIN(QUANTITY) QUANTITY
FROM
BED_COMPLECTS_CAN_BE_RESTORED(:STOCK, :ADATE),
OBJECT_NAMES O1
WHERE
O1.OBJECT_ID = ID
GROUP BY
ID, O1.SHORT_NAME
ORDER BY
O1.SHORT_NAME

STOCK = 1023
ADATE = 14.05.2003

=======================
Задача состояла в том, чтобы одним запросом выяснить все варианты комплектов постельного белья, которые можно восстановить из раскомплектованных единиц (наволочек, простыней и пододеяльников) с учетом общих дизайнов. На самом деле запрос по сути сложный (долго объяснять, что к чему) но по форме - простой и работает мгновенно.

Процедура, запрашивающая остатки по складу выглядит так:

CREATE PROCEDURE "GOODS_IN_STOCK"
(
"STOCK" INTEGER,
"ADATE" DATE
)
RETURNS(
"GOODS" INTEGER,
"QUANTITY" INTEGER,
"AMOUNT" DECIMAL(18, 2)
)
AS
BEGIN
FOR SELECT
OBJECT_ID GOODS,
SUM(QUANTITY_DEBIT - QUANTITY_CREDIT) QUANTITY,
SUM(DEBIT-CREDIT) AMOUNT
FROM ACC_TURN /*таблица оборотов*/
WHERE ACC_ID = :STOCK AND OP_DATE <= :ADATE
GROUP BY OBJECT_ID
HAVING SUM(QUANTITY_DEBIT - QUANTITY_CREDIT) <> 0
INTO :GOODS, QUANTITY, AMOUNT
DO SUSPEND;
END

Никаких делений на 0 или рекурсивных вызовов, как видите нет.
Тем не менее Yaffils 821 рушился. А менять сервер опять не хочется - вдруг еще какой баг вылезет. Вот я и придерживаюсь минимализма...
И вообще дятел платным стал, я скорее всего перейду на Firebird.

← →
Johnmen (2003-05-14 17:47) [26]

Удивительно, что компилятор вообще это пропускал...:)
Принципиальная ошибка в синтаксисе применения SELECT в цикле FOR (по поводу UNION).

← →
kaif (2003-05-14 22:38) [27]

2 Johnmen © (14.05.03 17:47)
Там не было ошибки. Ты видимо понял так, что я писал
FOR SELECT...
UNION
FOR SELECT...

Нет. Я писал FOR один раз в начале.
Вот точный исходный текст:

CREATE PROCEDURE "BED_COMPLECTS_CAN_BE_RESTORED"(
"STOCK" INTEGER,
"ADATE" DATE
)
RETURNS(
"ID" INTEGER,
"QUANTITY" FLOAT
)
AS
BEGIN
FOR SELECT
BC.ID,
GIS.QUANTITY/KS.DETAIL_QUANTITY
FROM
KIND_SET KS,
PILLOW_CASE PC,
BED_COMPLECTS BC,
BED_CLOTHES BC1,
BED_CLOTHES BC2,
GOODS_IN_STOCK(:STOCK, :ADATE) GIS
WHERE
PC.ID = GIS.GOODS AND
BC.COMPLECT_KIND = KS.COMPLEX_KIND AND
BC1.ID = PC.ID AND BC2.ID = BC.ID AND
BC1.BED_DECOR = BC2.BED_DECOR AND
PC.PILLOW_CASE_KIND = KS.DETAILS
UNION
SELECT
BC.ID,
GIS.QUANTITY/KS.DETAIL_QUANTITY
FROM
KIND_SET KS,
BLANKET_COVER PC,
BED_COMPLECTS BC,
BED_CLOTHES BC1,
BED_CLOTHES BC2,
GOODS_IN_STOCK(:STOCK, :ADATE) GIS
WHERE
PC.ID = GIS.GOODS AND
BC.COMPLECT_KIND = KS.COMPLEX_KIND AND
BC1.ID = PC.ID AND BC2.ID = BC.ID AND
BC1.BED_DECOR = BC2.BED_DECOR AND
PC.DUVET_COVER_KIND = KS.DETAILS
UNION
SELECT
BC.ID,
GIS.QUANTITY/KS.DETAIL_QUANTITY
FROM
KIND_SET KS,
BED_SHEET PC,
BED_COMPLECTS BC,
BED_CLOTHES BC1,
BED_CLOTHES BC2,
GOODS_IN_STOCK(:STOCK, :ADATE) GIS
WHERE
PC.ID = GIS.GOODS AND
BC.COMPLECT_KIND = KS.COMPLEX_KIND AND
BC1.ID = PC.ID AND BC2.ID = BC.ID AND
BC1.BED_DECOR = BC2.BED_DECOR AND
PC.BED_SHEET_KIND = KS.DETAILS
INTO :ID, :QUANTITY
DO SUSPEND;
END

Просто я неточно оговорил, что было изменено.

← →
Alexandr (2003-05-15 08:39) [28]

этож join процедуры и таблицы, если я правильно взлянул на код...
да?

← →
Zacho (2003-05-15 08:44) [29]

← →
kaif (2003-05-15 13:34) [30]

Да, я постоянно перемешиваю в SELECT-ах таблицы и результирющие наборы хранимых процедур. В этой возможности - огромное преимущество IB, из-за которого я предпочитаю юзать именно этот сервер. Иногда проблемы бывают, но крайне редко. Я замечал, что объединение таблицы с результатами рекурсивных процедур часто неверно отрабатывают ORDER BY. Но не более того. Не исключено, что глюк проявляется в UNION, если его сочетать с такими объединениями, причем это видимо глюк планировщика запросов. Обычно IB каким-то фантастическим образом умудряется оптимально строить планы запросов даже если перемешаны таблицы и хранимые процедуры, если использовать преимущественно INNER JOIN. Однако в случае с UNION видимо бывает и глюкс. В обычных запросах по таблицам с UNION у меня проблем не бывало.
Если же отказаться от перемешивания ХП и таблиц в запросах, то 90% уникальных возможностей IB пропадает. Несмотря ни на что я рекомендую использовать этот механизм, так как он чрезвычайно гибкий и мощный.

← →
Alexandr (2003-05-15 13:49) [31]

да. именно так.
Но все-таки, что мешает весь запрос в процедуру загнать, вместе с объединяемой таблицей...
А то вон оно как...

Объединение запросов (UNION) Найти похожие ветки