Помогите сконвертировать SQL-запрос

Работа VB и СУБД (Access, MSSQL, MySQL, Oracle и пр.)
Правила форума
При создании новой темы не забывайте указывать используемую СУБД.
alibek
Большой Человек
Большой Человек
 
Сообщения: 14205
Зарегистрирован: 19.04.2002 (Пт) 11:40
Откуда: Russia

Помогите сконвертировать SQL-запрос

Сообщение alibek » 30.01.2005 (Вс) 21:16

Народ, помогите с таким вопросом. Есть SQL-запрос и его надо преобразовать к такому виду, который понимается Access (т.е. с использованием left join).
Сам запрос такой (в формате Oracle, т.е. вместо left join используется (+) ):
Код: Выделить всё
select {c}.{CLNT_KEY}                  as {CLIENT_KEY}
       {c}.{CLIT_CLIT_KEY}             as {CLIENT_TYPE}
       {ct}.{CLIT_KEY}                 as {C_TYPE_KEY}
       {ct}.{NAME}                     as {C_TYPE_NAME}
       {c}.{ACCOUNT}                   as {CLIENT_ACCOUNT}
       {c}.{NAME}                      as {CLIENT_NAME}
       {c}.{DOC_TYPE}                  as {CLIENT_DOCTYPE}
       {c}.{DOC_SERIES}                as {CLIENT_DOCSERIES}
       {c}.{DOC_NUMBER}                as {CLIENT_DOCNUMBER}
       {c}.{DOC_DATE}                  as {CLIENT_DOCDATE}
       {c}.{DOC_WHO}                   as {CLIENT_DOCWHO}
       {c}.{LANGUAGE}                  as {CLIENT_LANGUAGE}
       {c}.{COUNTRY}                   as {CLIENT_COUNTRY}
       {c}.{AREA}                      as {CLIENT_AREA}
       {c}.{CITY}                      as {CLIENT_CITY}
       {c}.{ADDRESS}                   as {CLIENT_ADDRESS}
       {c}.{PHONE}                     as {CLIENT_PHONE}
       {c}.{FAX}                       as {CLIENT_FAX}
       {c}.{NOTES}                     as {CLIENT_NOTES}
       {cd}.{CLDT_KEY}                 as {C_DETAIL_KEY}
       {cd}.{ADR_COU_COU_KEY}          as {C_DETAIL_COUNTRY}
       {cd}.{ADR_CIT_CIT_KEY}          as {C_DETAIL_CITY}
       {cd}.{ADR_STR_STR_KEY}          as {C_DETAIL_STREET}
       {cd}.{ADR_BUILDING}             as {C_DETAIL_BUILDING}
       {cd}.{ADR_BUILDING_TYPE}        as {C_DETAIL_BUILDINGTYPE}
       {cd}.{ADR_APPARTAMENT}          as {C_DETAIL_APPARTAMENT}
       {cd}.{ADR_APPARTAMENT_TYPE}     as {C_DETAIL_APPARTAMENTYPE}
       {cd}.{ADR_ROOM}                 as {C_DETAIL_ROOM}
       {cd}.{ADR_ROOM_TYPE}            as {C_DETAIL_ROOMTYPE}
       {cd}.{DOC_TYPE}                 as {C_DETAIL_DOCTYPE}
       {cd}.{DOC_SERIES}               as {C_DETAIL_DOCSERIES}
       {cd}.{DOC_NUMBER}               as {C_DETAIL_DOCNUMBER}
       {cd}.{DOC_DATE}                 as {C_DETAIL_DOCDATE}
       {cd}.{DOC_WHO}                  as {C_DETAIL_DOCWHO}
       {cp}.{PHON_KEY}                 as {C_PHONE_KEY}
       {cp}.{PHONE_COUNTRY}            as {C_PHONE_COUNTRY}
       {cp}.{PHONE_ZONE}               as {C_PHONE_ZONE}
       {cp}.{PHONE_NUMBER}             as {C_PHONE_NUMBER}
       {cp}.{PHONE_TYPE}               as {C_PHONE_TYPE}
       {acou}.{COU_KEY}                as {C_ADR_COU_KEY}
       {acou}.{CODE}                   as {C_ADR_COU_CODE}
       {acou}.{NAME}                   as {C_ADR_COU_NAME}
       {acou}.{NAME_RUS}               as {C_ADR_COU_NAMERUS}
       {acou}.{NAME_ENG}               as {C_ADR_COU_NAMEENG}
       {aare}.{ARE_KEY}                as {C_ADR_ARE_KEY}
       {aare}.{NAME}                   as {C_ADR_ARE_NAME}
       {aare}.{TYPE}                   as {C_ADR_ARE_TYPE}
       {aare}.{POSTINDEX}              as {C_ADR_ARE_POSTINDEX}
       {aare}.{NAME_RUS}               as {C_ADR_ARE_NAMERUS}
       {aare}.{NAME_ENG}               as {C_ADR_ARE_NAMEENG}
       {adst}.{DST_KEY}                as {C_ADR_DST_KEY}
       {adst}.{NAME}                   as {C_ADR_DST_NAME}
       {adst}.{TYPE}                   as {C_ADR_DST_TYPE}
       {adst}.{POSTINDEX}              as {C_ADR_DST_POSTINDEX}
       {adst}.{NAME_RUS}               as {C_ADR_DST_NAMERUS}
       {adst}.{NAME_ENG}               as {C_ADR_DST_NAMEENG}
       {acit}.{CIT_KEY}                as {C_ADR_CIT_KEY}
       {acit}.{NAME}                   as {C_ADR_CIT_NAME}
       {acit}.{TYPE}                   as {C_ADR_CIT_TYPE}
       {acit}.{POSTINDEX}              as {C_ADR_CIT_POSTINDEX}
       {acit}.{NAME_RUS}               as {C_ADR_CIT_NAMERUS}
       {acit}.{NAME_ENG}               as {C_ADR_CIT_NAMEENG}
       {astr}.{STR_KEY}                as {C_ADR_STR_KEY}
       {astr}.{NAME}                   as {C_ADR_STR_NAME}
       {astr}.{TYPE}                   as {C_ADR_STR_TYPE}
       {astr}.{NAME_RUS}               as {C_ADR_STR_NAMERUS}
       {astr}.{NAME_ENG}               as {C_ADR_STR_NAMEENG}
from {CLIENTS} c, {CLIENT_TYPES} ct, {CLIENT_DETAILS} cd, {CLIENT_PHONES} cp
   , {REF_ADR_COUNTRY}   acou
   , {REF_ADR_AREA}      aare
   , {REF_ADR_DISTRICT}  adst
   , {REF_ADR_CITY}      acit
   , {REF_ADR_STREET}    astr
where {c}.{CLIT_CLIT_KEY} = {ct}.{CLIT_KEY} and {c}.{CLNT_KEY} = {cd}.{CLNT_CLNT_KEY} and {c}.{CLNT_KEY} = {cp}.{CLNT_CLNT_KEY}
  and {astr}.{CIT_CIT_KEY}(+) = {acit}.{CIT_KEY}
  and {acit}.{DST_DST_KEY}(+) = {adst}.{DST_KEY}
  and {acit}.{ARE_ARE_KEY}(+) = {aare}.{ARE_KEY}
  and {acit}.{COU_COU_KEY}(+) = {acou}.{COU_KEY}
  and {adst}.{ARE_ARE_KEY}(+) = {aare}.{ARE_KEY}
  and {adst}.{COU_COU_KEY}(+) = {acou}.{COU_KEY}
  and {aare}.{COU_COU_KEY}(+) = {acou}.{COU_KEY}
order by #ORDER#


т.е. запрос должен минимально зависеть (вернее, вообще не должен зависеть) от заполненности REF_ADR_* (их могут вообще не заполнять, но клиенты должны быть показаны все).

Результат должен быть примерно такой:
Код: Выделить всё
from ((({client_detail} as cd
  left join
   {ref_adr_country} as acou
  on {cd}.{COU_COU_KEY} = {acou}.{COU_KEY})
  left join
   {ref_adr_area} as aare
  on {cd}.{ARE_ARE_KEY} = {aare}.{ARE_KEY})
  left join
   {ref_adr_district} as adst
  on {cd}.{DST_DST_KEY} = {adst}.{DST_KEY})
  left join
   {ref_adr_city} as acit
  on {cd}.{CIT_CIT_KEY} = {acit}.{CIT_KEY}

Но он будет куда сложнее, т.к. REF_ADR_* связаны между собой (хотя тут связи избыточные, можно убрать некоторые из них без каких-либо изменений на результате). А также нужно учесть, что помимо cd есть еще c и cp, которые не связаны с REF_ADR_*.

Просто под рукой нет ни Access, ничего другого, а рисовать его в блокноте - слишком много вложений, где-нибудь ошибусь. В Access это сделать легко - нарисовать в макете таблички, связать их, а затем откорректировать связи на left join; сделать полученную строку читаемой уже моя забота.
Lasciate ogni speranza, voi ch'entrate.

Andrey Fedorov
Член-корреспондент академии VBStreets
Член-корреспондент академии VBStreets
 
Сообщения: 3287
Зарегистрирован: 21.05.2004 (Пт) 9:28
Откуда: Москва

Сообщение Andrey Fedorov » 31.01.2005 (Пн) 8:39

1.
Непонятно как у тебя таблички c, ct, cd, cp связаны с остальными.
По крайней мере в приведенном WHERE они никак не связаны.

2.
and {astr}.{CIT_CIT_KEY}(+) = {acit}.{CIT_KEY}

Если влом разбираться и хочется менять поминиуму, то аналог левой связки во WHERE это:

and {acit}.{DST_DST_KEY} *= {adst}.{DST_KEY}

Ну и правой, соответственно:

and {acit}.{ARE_ARE_KEY} =* {aare}.{ARE_KEY}

Так что нет проблем все откорректировать просто ручками... ;)
Фиг Вам! - Сказал Чебурашка, обгладывая Крокодила Гену...

alibek
Большой Человек
Большой Человек
 
Сообщения: 14205
Зарегистрирован: 19.04.2002 (Пт) 11:40
Откуда: Russia

Сообщение alibek » 31.01.2005 (Пн) 9:46

1. С адресными табличками связаны только cd, я забыл это указать.

2 и дальше. Т.е. можно использовать * ? Не знал :)
Lasciate ogni speranza, voi ch'entrate.

Andrey Fedorov
Член-корреспондент академии VBStreets
Член-корреспондент академии VBStreets
 
Сообщения: 3287
Зарегистрирован: 21.05.2004 (Пт) 9:28
Откуда: Москва

Сообщение Andrey Fedorov » 31.01.2005 (Пн) 9:52

Использовать *= можно, но особо не рекомендуется, ибо это оставлено для совместимости (сколько она продлится неизвестно, хотя, скорей всего, до смены диалекта запросов ;)
Фиг Вам! - Сказал Чебурашка, обгладывая Крокодила Гену...

alibek
Большой Человек
Большой Человек
 
Сообщения: 14205
Зарегистрирован: 19.04.2002 (Пт) 11:40
Откуда: Russia

Сообщение alibek » 31.01.2005 (Пн) 15:40

Так, с запросами разобрался.
Сконвертировать все-равно не вышло, слишком неоднозначные внешние связи для Access, пришлось разбивать запрос на несколько подзапросов.

Теперь еще один вопрос.
Есть таблица CLIENTS, есть CLIENT_PHONES. У одного клиента может быть несколько телефонов.
Как лучше всего (средствами запросов, естественно) все строки CLIENT_PHONES свести в одну? Т.е. все телефоны клиента будут перечислены в одной строке через запятую.
На мой взгляд, правда, это невозможно без функции/хранимой процедуры, но вдруг кто-то умеет?
Lasciate ogni speranza, voi ch'entrate.

skiperski
Идеолог
Идеолог
Аватара пользователя
 
Сообщения: 1386
Зарегистрирован: 25.06.2002 (Вт) 15:52

Сообщение skiperski » 31.01.2005 (Пн) 15:54

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

Konst_One
Член-корреспондент академии VBStreets
Член-корреспондент академии VBStreets
Аватара пользователя
 
Сообщения: 3041
Зарегистрирован: 09.04.2004 (Пт) 13:47
Откуда: Химки

Сообщение Konst_One » 31.01.2005 (Пн) 16:03

это только через PIVOT-инг

Andrey Fedorov
Член-корреспондент академии VBStreets
Член-корреспондент академии VBStreets
 
Сообщения: 3287
Зарегистрирован: 21.05.2004 (Пт) 9:28
Откуда: Москва

Сообщение Andrey Fedorov » 31.01.2005 (Пн) 16:07

alibek писал(а):На мой взгляд, правда, это невозможно без функции/хранимой процедуры, но вдруг кто-то умеет?


Да нет в запросе - нельзя.
Фиг Вам! - Сказал Чебурашка, обгладывая Крокодила Гену...

alibek
Большой Человек
Большой Человек
 
Сообщения: 14205
Зарегистрирован: 19.04.2002 (Пт) 11:40
Откуда: Russia

Сообщение alibek » 31.01.2005 (Пн) 16:19

Увы, подозревал я это :)
Просто думал, может есть такая фича, о чем я просто не в курсе.

В таком случае, что на ваш взгляд будет быстрее.

Вариант 1.
Делается избыточный запрос; т.е. в случае нескольких телефонов отображается несколько строк с одним ID клиента. При загрузке результата в ListView производится анализ (прежний клиент или уже новый) и соответственно добавляется новая строка или же добавляется только номер телефона к столбцу.

Вариант 2.
Делается обычный запрос, загружается в ListView, затем циклом прохожусь по всем строкам и на каждого клиента делаю подзапрос, извлекающий все номера телефонов.

Вариант 3.
Что-то вроде варианта 2, но с модификациями. Делается первый запрос, загружается в ListView. Затем выбираются все строки из CLIENT_PHONES и в цикле находится соответствие строкам в ListView.

Пока я склоняюсь ко второму.
Lasciate ogni speranza, voi ch'entrate.

alibek
Большой Человек
Большой Человек
 
Сообщения: 14205
Зарегистрирован: 19.04.2002 (Пт) 11:40
Откуда: Russia

Сообщение alibek » 31.01.2005 (Пн) 16:20

Konst_One писал(а):это только через PIVOT-инг

Хм... А нельзя ли пример? Мне казалось, что в данном случае Pivot не подойдет.
Lasciate ogni speranza, voi ch'entrate.

Konst_One
Член-корреспондент академии VBStreets
Член-корреспондент академии VBStreets
Аватара пользователя
 
Сообщения: 3041
Зарегистрирован: 09.04.2004 (Пт) 13:47
Откуда: Химки

Сообщение Konst_One » 31.01.2005 (Пн) 16:55

с наскоку не напишу, но должно получится, в твоем случае в качестве столбцов будут номера телефонов клиента

для теста попробуй прямо в excel это сделать или заюзай office web component

Konst_One
Член-корреспондент академии VBStreets
Член-корреспондент академии VBStreets
Аватара пользователя
 
Сообщения: 3041
Зарегистрирован: 09.04.2004 (Пт) 13:47
Откуда: Химки

Сообщение Konst_One » 31.01.2005 (Пн) 17:13

проверил, сделать можно, но тебе это ничего не даст, так что лучше юзай вариант 2

skiperski
Идеолог
Идеолог
Аватара пользователя
 
Сообщения: 1386
Зарегистрирован: 25.06.2002 (Вт) 15:52

Сообщение skiperski » 31.01.2005 (Пн) 17:59

Хе, хе :)

Если в таблицу CLIENT_PHONES ввести доп. поле CLIENT_PHONE_NR, которое для каждого клиента будет пронумеровано от 1 до N, где N кол-во телефонов у данного клиента, то можно написать запрос для ограниченного максимального числа телефонов.

Вот пример для максимум 3-х телефонов для клиента.
Код: Выделить всё
SELECT CLIENTS.CLIENT_NAME,
   cp_1.CLIENT_PHONE &
   IIf(cp_2.CLIENT_PHONE Is Null, "", ", " & cp_2.CLIENT_PHONE) &
   IIf(cp_3.CLIENT_PHONE Is Null, "", ", " & cp_3.CLIENT_PHONE)

FROM (((CLIENTS

   LEFT JOIN (SELECT * FROM CLIENT_PHONES WHERE CLIENT_PHONE_NR = 1) AS cp_1
   ON cp_1.CLIENT_ID = CLIENTS.CLIENT_ID)

   LEFT JOIN (SELECT * FROM CLIENT_PHONES WHERE CLIENT_PHONE_NR = 2) AS cp_2
   ON cp_2.CLIENT_ID = CLIENTS.CLIENT_ID)

   LEFT JOIN (SELECT * FROM CLIENT_PHONES WHERE CLIENT_PHONE_NR = 3) AS cp_3
   ON cp_3.CLIENT_ID = CLIENTS.CLIENT_ID)


Или просто без IIf() выбрать только значения, а уже позже их собирать в строку. Можно запрос расширить до 10-ти, больше врядли у кого есть.

alibek
Большой Человек
Большой Человек
 
Сообщения: 14205
Зарегистрирован: 19.04.2002 (Пт) 11:40
Откуда: Russia

Сообщение alibek » 01.02.2005 (Вт) 8:26

У меня есть другая идея :)
Вернее, появилась этой ночью. Вот что значит здоровый сон - сразу мысли путные появляются.

В таблицу CLIENTS я добавляю текстовое поле CLIENTS.PHONES, в котором через запятую будут перечислены телефоны. Обновляться поле будет при изменениях в CLIENT_PHONES (все редактирование происходит через интерфейс проги). Дублирование информации - это конечно свидетельство не очень толковой разработки, но зато дешево и сердито.
Lasciate ogni speranza, voi ch'entrate.

Andrey Fedorov
Член-корреспондент академии VBStreets
Член-корреспондент академии VBStreets
 
Сообщения: 3287
Зарегистрирован: 21.05.2004 (Пт) 9:28
Откуда: Москва

Сообщение Andrey Fedorov » 01.02.2005 (Вт) 16:53

Вообще как бы делал я (с MDB-шкой, то бишь без серверных функций/процедур) если бы мне было нужно отобразить в Grid-e (я пользую GridEx) пользователей с их номерами телефонов:

Одним запросом выдернул бы всех пользователей которые надо показать в Grid-e, а вторым - номера их телефонов, то есть получил бы два Recordset-a. На первый Recordset повесил бы Grid, колонка Phones которого была бы выставлена в FetсhData. Тогда при событии FetchData записи юзера мне было бы достаточно поставить фильтр на второй Recordset по UserID и сформировать строку с номерами телефонов. Чтобы фильтр работал побыстрей (если записей много) - выставить Optimize для поля UserID второго Recordset-a.

Вот и все, собственно...

Подобным образом, в принципе, можно запихнуть куда угодно...
Фиг Вам! - Сказал Чебурашка, обгладывая Крокодила Гену...

alibek
Большой Человек
Большой Человек
 
Сообщения: 14205
Зарегистрирован: 19.04.2002 (Пт) 11:40
Откуда: Russia

Сообщение alibek » 01.02.2005 (Вт) 17:22

Andrey Fedorov, если подключу грид, то наверное так и сделаю. Но пока обхожусь ListView и думаю его хватит.
Эта проблема уже неактуально, но мне просто интересно, какой вариант из трех более эффективен. Количество клиентов ожидается порядка тысяч (3-4 тысячи), у клиента будет в среднем 1-2 телефона (у некоторых будет 5-6 номеров). Как мне кажется, в этом случае лучше всего второй вариант; если бы число клиентов было под 50-100 тысяч, то я бы выбрал вариант 3.
Lasciate ogni speranza, voi ch'entrate.

Andrey Fedorov
Член-корреспондент академии VBStreets
Член-корреспондент академии VBStreets
 
Сообщения: 3287
Зарегистрирован: 21.05.2004 (Пт) 9:28
Откуда: Москва

Сообщение Andrey Fedorov » 01.02.2005 (Вт) 17:48

Но пока обхожусь ListView и думаю его хватит.


ListView будет тормозить на большом количестве записей (ибо он нагружаемый контрол). Grid-нет. В принципе, ListView в режиме таблицы я вообще не пользую - он к тому-же еще и не удобен в работе.

Как мне кажется, в этом случае лучше всего второй вариант


Да ну - там выполняется куча подзапросов (по одному на каждую запись клиента)... А у меня всего два. Быстрей будет однозначно (особенно заметно на загруженной сетке).
Фиг Вам! - Сказал Чебурашка, обгладывая Крокодила Гену...

alibek
Большой Человек
Большой Человек
 
Сообщения: 14205
Зарегистрирован: 19.04.2002 (Пт) 11:40
Откуда: Russia

Сообщение alibek » 02.02.2005 (Ср) 8:43

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

А загрузка будет протекать так: выполнился первый запрос, заполнились строки ListView, а потом, в фоновом режиме, будут заполняться столбцы телефонов.
Я так понял, что ты за третий вариант?
Lasciate ogni speranza, voi ch'entrate.

Andrey Fedorov
Член-корреспондент академии VBStreets
Член-корреспондент академии VBStreets
 
Сообщения: 3287
Зарегистрирован: 21.05.2004 (Пт) 9:28
Откуда: Москва

Сообщение Andrey Fedorov » 02.02.2005 (Ср) 8:46

Да - за третий. Ибо это всего два довольно простых запроса и минимальный трафик по сетке.
Фиг Вам! - Сказал Чебурашка, обгладывая Крокодила Гену...


Вернуться в Базы данных

Кто сейчас на конференции

Сейчас этот форум просматривают: AhrefsBot и гости: 4

    TopList