ReSQL.ru
     
powered by simpleCommunicator - 2.0.61     © 2026 Programmizd 02
Мобильная версия    Контакт    Правила    FAQ    Помощь
Целевая тема:
Создать новую тему:
Автор:
Закрыть
Цитировать
Доб. в избранное | Игнор. тему | Прикреп. тему | Пометить прочит. / непрочит. | Фильтр
Форумы / Microsoft Access [игнор отключен] [закрыт для гостей] / Пользовательская функция в базе ACCESS
27 сообщений из 27, показаны все 2 страниц
  все  
Пользовательская функция в базе ACCESS
    #38950850
Ghost Writer
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Ghost Writer
Участник
В таблице есть поля, содержащие текст в формате "параметр=значение". Например:
ALTNAME Параметр1=Значение1Параметр2=Значение2Параметр3=Значение3
Я хотел сделать в ACCESS так называемый ЗАПРОС, разбивающий эти списки и возвращающий значения параметров как отдельные поля.

Сделал на VBA функцию:
Код: vbnet
1.
2.
3.
4.
5.
6.
7.
8.
Public Function ValueFromList(StrList As String, StrParam As String) As String
    ValueFromList = ""
    Dim P1, P2 As Long
    P1 = InStr(1, StrList, StrParam, vbTextCompare)
    P2 = InStr(P1 + 1, StrList, vbCrLf, vbTextCompare)
    If P1 <= 0 Then Exit Function
    ValueFromList = Mid(StrList, P1 + Len(StrParam) + 1, P2 - 1 - P1 - Len(StrParam))
End Function



В ACCESS использовал так:
Код: plsql
1.
2.
3.
4.
5.
SELECT 
ValueFromList(CERTIFICATS.ALTNAME,"1.2.643.3.61.502710.1.7") AS KPP,
ValueFromList(CERTIFICATS.ALTNAME,"1.2.643.3.61.502710.1.5") AS SPZ,
ValueFromList(CERTIFICATS.ALTNAME,"1.2.643.3.61.502710.1.8") AS GMU
FROM CERTIFICATS


НО ЖЕСТКО ОБЛОМАЛСЯ ((( когда вызвал запрос из программы на Delphi.
Получил ошибку: Неопределенная функция 'ValueFromList' в выражении. Ясно, что извне не доступен VBA.

Что можно придумать, чтобы не писать для каждого параметра порнуху типа этой:
Код: plsql
1.
2.
3.
4.
5.
6.
7.
SELECT 
SWITCH(InStr(1, CERTIFICATS.ALTNAME, "1.2.643.3.61.502710.1.7=", 1), 
  MID(CERTIFICATS.ALTNAME, InStr(1, CERTIFICATS.ALTNAME, "1.2.643.3.61.502710.1.7=", 1)+Len("1.2.643.3.61.502710.1.7="), 
  InStr(InStr(1, CERTIFICATS.ALTNAME, "1.2.643.3.61.502710.1.7=", 1)+Len("1.2.643.3.61.502710.1.7="), 
  CERTIFICATS.ALTNAME, Chr(13) + Chr(10), 1)+Len(Chr(13) + Chr(10))-InStr(1, CERTIFICATS.ALTNAME, "1.2.643.3.61.502710.1.7=",1)
  +Len("1.2.643.3.61.502710.1.7="))) AS KPP
FROM CERTIFICATS;
...
Рейтинг: 0 / 0
03.05.2015, 21:15:57
| Ответить | Цитировать | Написать  
Пользовательская функция в базе ACCESS
    #38950869
Фотография Akina
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Akina
Участник
Тривиальным запросом преобразуй таблицу в вид
Pname PvalueПараметр1Значение1 Параметр2Значение2 Параметр3Значение3
А потом PIVOT (TRANSFORM).
...
Рейтинг: 0 / 0
03.05.2015, 22:31:48
| Ответить | Цитировать | Написать  
Пользовательская функция в базе ACCESS
    #38950873
uux
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
uux
Гость
Еще можно оформить ValueFromList в виде dll.
...
Рейтинг: 0 / 0
03.05.2015, 22:51:30
| Ответить | Цитировать | Написать  
Пользовательская функция в базе ACCESS
    #38950876
Ghost Writer
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Ghost Writer
Участник
товарищи, не могли бы Вы о предложенном рассказать подробнее?

Akina,

такой тривиальный запрос не приходит в голову, т.к. это не таблица. Я наверно недостатчно ясно описал:
Вся табличка, которую я нарисовал в 17596478 - это значение одного поля MEMO.


uux,

Помню для SQL Server делал процедуру в виде DLL. Но для ACCESS что-то сомневаюсь теперь.
Разве эта функция из DLL будет доступна вне ACCESS ? Где почитать подробнее ?
...
Рейтинг: 0 / 0
03.05.2015, 23:06:49
| Ответить | Цитировать | Написать  
Пользовательская функция в базе ACCESS
    #38950899
Фотография __Michelle
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
__Michelle
Участник
А если в два запроса?

1-ый. Называется Cross_Query
Код: sql
1.
2.
3.
4.
5.
6.
TRANSFORM First(ZZ.SVal) AS [First-SVal]
SELECT SS, Left(SS,1) AS LL
FROM [SELECT Left(ALTNAME,Instr(ALTNAME,"=")-1) AS SS, Right(SS,1) AS RR, Mid(ALTNAME,Instr(ALTNAME,"=")+1) AS SVal
FROM CERTIFICATS]. AS ZZ
GROUP BY ZZ.SS
PIVOT IIf(RR="5","SPZ",IIf(RR="7","KPP","GMU"));

2-ой.
Код: sql
1.
2.
3.
SELECT Q1.KPP, Q2.SPZ, Q3.GMU
FROM (Cross_Query AS Q1 INNER JOIN Cross_Query AS Q2 ON Q1.LL = Q2.LL) INNER JOIN Cross_Query AS Q3 ON Q2.LL = Q3.LL
WHERE Not Q1.KPP Is Null AND Not Q2.SPZ Is Null AND Not Q3.GMU Is Null;
...
Рейтинг: 0 / 0
04.05.2015, 00:45:36
| Ответить | Цитировать | Написать  
Пользовательская функция в базе ACCESS
    #38950905
Ghost Writer
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Ghost Writer
Участник
__Michelle,
мало что понял. скопировал первый запрос - access ругнулся Недопустимый вызов процедуры
...
Рейтинг: 0 / 0
04.05.2015, 00:59:40
| Ответить | Цитировать | Написать  
Пользовательская функция в базе ACCESS
    #38950908
Фотография __Michelle
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
__Michelle
Участник
Ghost Writer,
Сейчас сделаю БД, выложу.
Должно работать, думаю.
...
Рейтинг: 0 / 0
04.05.2015, 01:07:32
| Ответить | Цитировать | Написать  
Пользовательская функция в базе ACCESS
    #38950911
8871
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
8871
Гость
Ghost Writer,

Можно сделать доп.табличку со списком параметров и тогда попроще будет чем через строковые функции парсить
(Если вдруг доп.таблицу создавать нельзя, то её можно заменить на подзапрос, по типу Digits-а)

Осн.таблица - Tab
Код: plaintext
1.
2.
3.
4.
5.
6.
7.
8.
id	ALTNAME
--------------------------------
1	Параметр1=Значение1
	Параметр2=Значение2
	Параметр3=Значение3
--------------------------------
2	Параметр1=Значение1
	Параметр5=Значение5
	Параметр6=Значение6

Доп.таблица - Param
NAMEПараметр1Параметр2Параметр3Параметр4Параметр5Параметр6
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
select id, NAME, [value]
from

(SELECT Tab.id, Tab.ALTNAME, Param.NAME,
    InStr(1,Tab.ALTNAME,Param.NAME)+len(Param.NAME)+1 as pos_start_value,
    InStr(pos_start_value,Tab.ALTNAME,Chr(13) + Chr(10)) as pos_CrLf,
    Mid(Tab.ALTNAME, pos_start_value, iif(pos_CrLf>0,pos_CrLf-pos_start_value, 100500)) as [value]
FROM Tab, Param
WHERE Tab.ALTNAME like "*" & Param.NAME & "*") t

order by id, NAME


результат
idNAMEvalue1Параметр1Значение11Параметр2Значение21Параметр3Значение32Параметр1Значение12Параметр5Значение52Параметр6Значение6
и нужно будет ещё добавить во внешний запрос TRANSFORM / PIVOT
...
Рейтинг: 0 / 0
04.05.2015, 01:17:52
| Ответить | Цитировать | Написать  
Пользовательская функция в базе ACCESS
    #38950912
Фотография __Michelle
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
__Michelle
Участник
Ну вот база. Попробуйте.
У меня работает.

Нужно открыть запрос Запрос2.

Сделала в Access 2000, другого сейчас под рукой нет.
...
Рейтинг: 0 / 0
04.05.2015, 01:18:45
| Ответить | Цитировать | Написать  
Пользовательская функция в базе ACCESS
    #38950913
Фотография __Michelle
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
__Michelle
Участник
Вот результат запроса Запрос2.
...
Рейтинг: 0 / 0
04.05.2015, 01:19:51
| Ответить | Цитировать | Написать  
Пользовательская функция в базе ACCESS
    #38950983
uux
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
uux
Гость
Ghost Writeruux,

Помню для SQL Server делал процедуру в виде DLL. Но для ACCESS что-то сомневаюсь теперь.
Разве эта функция из DLL будет доступна вне ACCESS ? Где почитать подробнее ?

Ох... Я в этом вопросе, к сожалению, гораздо больше теоретик, чем практик - знаю, что в принципе обращаться из VBA к dll-кам можно, но в жизни с такими задачами почти не сталкивался.

Тем не менее, вы совершенно точно можете сделать нужную dll при помощи Delphi, а потом обратиться к ней из Access (да и вообще из практически любого приложения с VBA).

Почитать можно в Аксовском (точнее, вба-шном) хелпе по ключевому слову Declare.

Ну или вот ссылка нвскидку .
...
Рейтинг: 0 / 0
04.05.2015, 08:11:41
| Ответить | Цитировать | Написать  
Пользовательская функция в базе ACCESS
    #38951056
Ghost Writer
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Ghost Writer
Участник
uux,

мне как раз НЕ из Access обращаться нужно, а из программы на Delphi и web странички на ASP.
...
Рейтинг: 0 / 0
04.05.2015, 12:08:15
| Ответить | Цитировать | Написать  
Пользовательская функция в базе ACCESS
    #38951059
Ghost Writer
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Ghost Writer
Участник
__Michelle,

большое спасибо за такой подробный ответ. Но, это не совсем мой случай. Как я писал выше:
Ghost WriterВся табличка, которую я нарисовал в 17596478 - это значение одного поля MEMO.
...
Рейтинг: 0 / 0
04.05.2015, 12:14:09
| Ответить | Цитировать | Написать  
Пользовательская функция в базе ACCESS
    #38951081
Ghost Writer
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Ghost Writer
Участник
8871,

хмм. доп.табличку создать можно.
Попробовал ваш запрос... Работает, но дублирует записи по кол-ву параметров в доп. табличке.
Понятно, что по вашей задумке еще нужно TRANSFORM / PIVOT, но я затрудняюсь понять как их использовать. Не могли бы вы в ваш код еще TRANSFORM / PIVOT воткнуть ?

Еще попутно вопрос. Эти TRANSFORM / PIVOT должны работать в Windows Server 2003, офис 2003 ?
...
Рейтинг: 0 / 0
04.05.2015, 12:56:15
| Ответить | Цитировать | Написать  
Пользовательская функция в базе ACCESS
    #38951113
5032
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
5032
Гость
Ghost WriterЕще попутно вопрос. Эти TRANSFORM / PIVOT должны работать в Windows Server 2003, офис 2003 ?да

Ghost Writerхмм. доп.табличку создать можно.
Попробовал ваш запрос... Работает, но дублирует записи по кол-ву параметров в доп. табличке.
Понятно, что по вашей задумке еще нужно TRANSFORM / PIVOT, но я затрудняюсь понять как их использовать. Не могли бы вы в ваш код еще TRANSFORM / PIVOT воткнуть ?
В эту доп.табличку ещё можно алиасы для полей сразу вписать, что бы потом их в запросе не "выписывать" каждый раз
NAMEAliasNAMEПараметр1KPPПараметр2SPZПараметр3GMUПараметр4ASDПараметр5FGHПараметр6JKL
и такой запрос тогда
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
TRANSFORM Max(t.value) AS [Max-value]
SELECT t.id
FROM (SELECT Tab.id, Tab.ALTNAME, Param.NAME, Param.AliasNAME,
    InStr(1,Tab.ALTNAME,Param.NAME)+len(Param.NAME)+1 as pos_start_value,
    InStr(pos_start_value,Tab.ALTNAME,Chr(13) + Chr(10)) as pos_CrLf,
    Mid(Tab.ALTNAME, pos_start_value, iif(pos_CrLf>0,pos_CrLf-pos_start_value, 100500)) as [value]
FROM Tab, Param
WHERE Tab.ALTNAME like "*" & Param.NAME & "*") t
GROUP BY t.id
ORDER BY t.id
PIVOT t.AliasNAME;


idFGHGMUJKLKPPSPZ1Значение3Значение1Значение22Значение5Значение6Значение1
Если нужно выводить не все параметры, или важен их порядок в результате запроса, то нужно перечислить их в PIVOT
Код: sql
1.
PIVOT t.AliasNAME IN ("KPP", "SPZ", "GMU");


idKPPSPZGMU1Значение1Значение2Значение32Значение1
...
Рейтинг: 0 / 0
04.05.2015, 13:43:17
| Ответить | Цитировать | Написать  
Пользовательская функция в базе ACCESS
    #38951118
Ghost Writer
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Ghost Writer
Участник
5032,

круто, спасибо большое. Но одна загвоздка:
запрос возвращает не все записи, а только те, которые, я так полагаю, отвечают условию WHERE Tab.ALTNAME like "*" & Param.NAME & "*") t.
А надо бы все записи, и если нет Param.NAME, то вывести пустое значение.
...
Рейтинг: 0 / 0
04.05.2015, 14:00:19
| Ответить | Цитировать | Написать  
Пользовательская функция в базе ACCESS
    #38951123
8630
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
8630
Гость
Ghost Writer5032,

круто, спасибо большое. Но одна загвоздка:
запрос возвращает не все записи, а только те, которые, я так полагаю, отвечают условию WHERE Tab.ALTNAME like "*" & Param.NAME & "*") t.
А надо бы все записи, и если нет Param.NAME, то вывести пустое значение.т.е. там вообще ни одного параметра в Мемо поле нет, так?
Тогда так: WHERE - убрать вообще, а условие перенести в IIf
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
TRANSFORM Max(t.value) AS [Max-value]
SELECT t.id
FROM (SELECT Tab.id, Tab.ALTNAME, Param.NAME, Param.AliasNAME,
    InStr(1,Tab.ALTNAME,Param.NAME)+len(Param.NAME)+1 as pos_start_value,
    InStr(pos_start_value,Tab.ALTNAME,Chr(13) + Chr(10)) as pos_CrLf,
    IIf(Tab.ALTNAME like "*" & Param.NAME & "*",Mid(Tab.ALTNAME, pos_start_value, iif(pos_CrLf>0,pos_CrLf-pos_start_value, 100500))) as [value]
FROM Tab, Param
) t
GROUP BY t.id
ORDER BY t.id
PIVOT t.AliasNAME;
...
Рейтинг: 0 / 0
04.05.2015, 14:22:36
| Ответить | Цитировать | Написать  
Пользовательская функция в базе ACCESS
    #38951187
Ghost Writer
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Ghost Writer
Участник
8630т.е. там вообще ни одного параметра в Мемо поле нет, так?просто у разных записей может быть разный набор параметров. А также, может быть параметр с пустым значением.
...
Рейтинг: 0 / 0
04.05.2015, 17:54:13
| Ответить | Цитировать | Написать  
Пользовательская функция в базе ACCESS
    #38951208
Ghost Writer
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Ghost Writer
Участник
8630
Код: sql
1.
    IIf(Tab.ALTNAME like "*" & Param.NAME & "*",Mid(Tab.ALTNAME, pos_start_value, iif(pos_CrLf>0,pos_CrLf-pos_start_value, 100500))) as [value]

всё равно результат не полный
...
Рейтинг: 0 / 0
04.05.2015, 18:53:07
| Ответить | Цитировать | Написать  
Пользовательская функция в базе ACCESS
    #38951213
непоймучка
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
непоймучка
Гость
Ghost Writer8630т.е. там вообще ни одного параметра в Мемо поле нет, так?просто у разных записей может быть разный набор параметров. А также, может быть параметр с пустым значением.
тогда
Код: vbnet
1.
2.
3.
4.
5.
6.
7.
'вместо
FROM Tab, Param
WHERE Tab.ALTNAME like "*" & Param.NAME & "*"

'попробуйте так
FROM Tab LEFT JOIN Param 
ON Tab.ALTNAME like "*" & Param.NAME & "*"
...
Рейтинг: 0 / 0
04.05.2015, 19:12:50
| Ответить | Цитировать | Написать  
Пользовательская функция в базе ACCESS
    #38951218
4977
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
4977
Гость
непоймучкаGhost Writerпропущено...
просто у разных записей может быть разный набор параметров. А также, может быть параметр с пустым значением.
тогда
Код: vbnet
1.
2.
3.
4.
5.
6.
7.
'вместо
FROM Tab, Param
WHERE Tab.ALTNAME like "*" & Param.NAME & "*"

'попробуйте так
FROM Tab LEFT JOIN Param 
ON Tab.ALTNAME like "*" & Param.NAME & "*"

Я так пробовал, Акс ошибку давал про "невозможна связь по полю Мемо/ОЛЕ", а у ТС поле ALTNAME - Мемо.
Вообще, я не знаю что там у него за "результат не полный" ... Последняя версия запроса это кросс джойн таблиц, там - "лишнего" много должно быть, но никак не "не полный" ...

Ждём-с, может ТС таки "созреет" на пример ))
...
Рейтинг: 0 / 0
04.05.2015, 19:31:01
| Ответить | Цитировать | Написать  
Пользовательская функция в базе ACCESS
    #38951228
Ghost Writer
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Ghost Writer
Участник
4977,

прошу прощения. Действительно, последний вариант даёт полный результат.
Просто умудрился я накосячить, когда копировал выделенное желтым
...
Рейтинг: 0 / 0
04.05.2015, 19:45:22
| Ответить | Цитировать | Написать  
Пользовательская функция в базе ACCESS
    #38951236
Ghost Writer
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Ghost Writer
Участник
Опять фиаско. В Access всё красиво, а когда выполняю запрос из своей программы, то все эти поля из AliasNAME - пустые . Остальные, которые существуют в таблице, нормально.
...
Рейтинг: 0 / 0
04.05.2015, 20:16:11
| Ответить | Цитировать | Написать  
Пользовательская функция в базе ACCESS
    #38951242
1005
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
1005
Гость
[quot Ghost Writer]Опять фиаско. В Access всё красиво, а когда выполняю запрос из своей программы, то все эти поля из AliasNAME - пустые . Остальные, которые существуют в таблице, нормально.[/quot
Если программа использует АДО, то вот это
Код: sql
1.
IIf(Tab.ALTNAME like "*" & Param.NAME & "*",


измени на
Код: sql
1.
IIf(Tab.ALTNAME like "%" & Param.NAME & "%",
...
Рейтинг: 0 / 0
04.05.2015, 20:29:04
| Ответить | Цитировать | Написать  
Пользовательская функция в базе ACCESS
    #38951247
Ghost Writer
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Ghost Writer
Участник
Да, ADO. Как же я сам не сообразил. Сколько раз вчера менял * на % и обратно.
Спасибо, всё пучком )
...
Рейтинг: 0 / 0
04.05.2015, 20:38:17
| Ответить | Цитировать | Написать  
Пользовательская функция в базе ACCESS
    #38951250
Ghost Writer
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Ghost Writer
Участник
Ghost WriterКак же я сам не сообразил. Сэто всё хоккеисты виноваты
...
Рейтинг: 0 / 0
04.05.2015, 20:40:11
| Ответить | Цитировать | Написать  
Пользовательская функция в базе ACCESS
    #38951302
Ghost Writer
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Ghost Writer
Участник
По мере добавления параметров в доп. таблицу запрос выполняется заметно медленнее.
...
Рейтинг: 0 / 0
04.05.2015, 22:55:32
| Ответить | Цитировать | Написать  
27 сообщений из 27, показаны все 2 страниц
  все  
Форумы / Microsoft Access [игнор отключен] [закрыт для гостей] / Пользовательская функция в базе ACCESS
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
Польз. соглашение   Полит. конфиден.  
NoSQL.ru       Кролег: Проекты, Новости, Чат       РЕД ФОРУМ       Фотоальбом: Участники, Встречи
Закрыть
start [/forum/topic.php?all=1&fid=45&tid=1615096]:
 0ms
get settings:
 11ms
get forum list:
 18ms
check forum access:
 3ms
check topic access:
 3ms
track hit:
 83ms
get topic data:
 12ms
get forum data:
 3ms
get page messages:
 75ms
get tp. blocked users:
 1ms
others: 232ms
total:  441ms
созд. / загр.: 441ms
x
x
Закрыть


Просмотр
0 / 0
Close
Debug Console [Select Text]