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

← →
Max Zyuzin © (2005-08-31 12:40) [0]

Если конечно такой вообще можно составить.
Есть 2 таблицы master-detail (Отношение один к многим)
MasterTable имеет поля к примеру MId (PK), MField1, MField2 ...
DetailTable имеет поля - MasterId (FK), DField1, DField2 ...

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

|MId | MField1 | Mfield2 | "DField1, DField1" | ....
То, есть через запятую, к примеру, перечислены все значения из таблицы Detail у которых MasterId = Mid...
Вот как это реализовать можно?

Что то мне подсказывает что такое реализовать можно только через SP ???

← →
stone © (2005-08-31 12:49) [1]

> Что то мне подсказывает что такое реализовать можно только
> через SP ???

Интуиция? Правильно подсказывает, т.к. без курсоров тут не обойтись.

← →
Ega23 © (2005-08-31 13:00) [2]

Set NoCount ON Declare @MId int, @ss varchar(8000); Create Table #tmp ( MId int, MField1 int, MField2, DetailFldStr varchar(8000) null ); Insert into #tmp (MId, MField1, MField2) Select MId, MField1, MField2 from MasterTable; Declare CurMaster cursor local static for Select MId from MasterTable; Open Cur; While 0 = 0 begin fetch next from CurMaster into @MId; if @@fetch_status <> 0 BREAK; Select @ss=DField1+","+DField2+","+... from DetailTable where MasterID=@Mid; Update #tmp Set DetailFldStr=@ss where MId=@Mid; end; Close CurMaster; Deallocate CurMaster; Set NoCount OFF; Select * from #tmp; Drop Table #tmp;

Вроде так...

← →
Max Zyuzin © (2005-08-31 14:12) [3]

>Ega23 © (31.08.05 13:00) [2]
Спасибо за направление, но это не совсем так, как мне надо, надо в одно поле было собрать все значения одного и того же поля в подчиненной таблице... Еще раз спасибо, буду читать книги умные как курсорами пользоваться...

>stone © (31.08.05 12:49) [1]
Спасибо, пошел читать

← →
Ega23 © (2005-08-31 14:34) [4]

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

А, ну это фигня. Надо ещё один курсор ввести, только и всего.
После
if @@fetch_status <> 0 BREAK;
вставляешь следующий код:
Declare CurDetail cursor local static for Select DField from DetailTable where MasterID=@MID; Open CurDetails; While 0 = 0 begin fetch next from CurMaster into @ss2; if @@fetch_status <> 0 BREAK; Set @ss=@ss + @ss2; end; Close CurDetails; Deallocate CurDetails;

Ну и @ss2 надо объявить, конечно...

← →
Max Zyuzin © (2005-08-31 15:48) [5]

Проба пера...
create procedure GetFiltersValue @FieldValue varchar(8000) = NULL output as declare @CurVal varchar(1000) declare curShipFilter cursor local static read_only for Select distinct DispatchStation from ShpRequestFilterView open curShipFilter; while @@FETCH_STATUS = 0 begin fetch next from curShipFilter into @CurVal Set @FieldValue = @FieldValue + @CurVal end go declare @s varchar(8000) Exec GetFiltersValue @s select @s drop procedure GetFiltersValue
Решил пока все в кучу свалить, но результатом получаю NULL
Хотя запрос
Select distinct DispatchStation from ShpRequestFilterView
Возвращает некоторый набор данных, правда в нем есть и NULL может здесь закопана собака?

← →
ANB © (2005-08-31 15:52) [6]

> Max Zyuzin © (31.08.05 15:48) [5]
строка + Null = Null. IsNull поможет.

← →
ANB © (2005-08-31 15:53) [7]

> Max Zyuzin © (31.08.05 15:48) [5]
- кстати, лучше напиши функцию, ее можно потом будет просто в запрос запихать. Я так и делал.

← →
Max Zyuzin © (2005-08-31 15:56) [8]

>ANB © (31.08.05 15:52) [6]
Поменял в ХП строку с текстом запроса на вот такую
Select distinct coalesce(DispatchStation," ") from ShpRequestFilterView
Результат тот же... NULL

← →
Ega23 © (2005-08-31 15:58) [9]

2 Max Zyuzin © (31.08.05 15:48) [5]

кстати, а нафига ты SP создаёшь, а потом грохаешь? Я тебе пример написал, который сам по сеье будет работать. В Query можно запихать и через Open открыть...

2 ANB © (31.08.05 15:53) [7]

На MS SQL 7.0 не прокатит.

← →
Ega23 © (2005-08-31 16:00) [10]

2 Max Zyuzin © (31.08.05 15:56) [8]
Не то поменял. Измени
Set @FieldValue = @FieldValue + @CurVal
на
Set @FieldValue = @FieldValue + IsNull(@CurVal,"")

← →
Max Zyuzin © (2005-08-31 16:01) [11]

>ANB © (31.08.05 15:53) [7]
Ага... я вообще то как раз и хотел написать функцию.... ща поправим...

← →
Max Zyuzin © (2005-08-31 16:19) [12]

Поправил на функцию... пока тоже в кучу все свалил
create function GetFiltersValue() returns varchar(8000) as begin declare @FieldValue varchar(8000) declare @CurVal varchar(1000) declare curShipFilter cursor local static read_only for Select distinct DispatchStation from ShpRequestFilterView open curShipFilter; while @@fetch_status = 0 begin fetch next from curShipFilter into @CurVal Set @FieldValue = @FieldValue + IsNULL(@CurVal,"") end return @FieldValue end
Получаю на select GetFiltersValue() сообщение что
"GetFiltersValue" is not a recognized function name.
К чему бы это?

← →
Max Zyuzin © (2005-08-31 16:27) [13]

Доправился вот до такого кода

create function GetFiltersValue(@RequestNumber bigint, @RequestDate Datetime) returns varchar(8000) as begin declare @FieldValue varchar(8000) declare @CurVal varchar(1000) declare curShipFilter cursor local static read_only for Select distinct SRF.DispatchStation from ShpRequestFilterView SRF where SRF.srfRequestNumber = @RequestNumber and SRF.srfRequestDate = @RequestDate open curShipFilter; while @@fetch_status = 0 begin fetch next from curShipFilter into @CurVal Set @FieldValue = @FieldValue + IsNULL(@CurVal,"") end return @FieldValue end select GetFiltersValue(srRequestNumber,srCrDate), * from ShipmentRequestView

Все то же выдает что функции такой нету :(

← →
ANB © (2005-08-31 16:27) [14]

> Ega23 © (31.08.05 15:58) [9]
- а разве 7 не ест функции ?

← →
Ega23 © (2005-08-31 16:32) [15]

AFAIK, функции только с 2000 появились.
По крайней мере, у нас существенная часть заказчиков ещё семёркой пользуется. Поэтому при написании скриптов от функций приходится отказываться...

← →
Max Zyuzin © (2005-08-31 16:36) [16]

В общем сделал вот что

create function GetFiltersValue(@RequestNumber bigint, @RequestDate Datetime) returns varchar(8000) as begin declare @FieldValue varchar(8000) set @FieldValue = "" declare @CurVal varchar(1000) declare curShipFilter cursor local static read_only for Select distinct SRF.DispatchStation from ShpRequestFilterView SRF where SRF.srfRequestNumber = @RequestNumber and SRF.srfRequestDate = @RequestDate open curShipFilter; while @@fetch_status = 0 begin fetch next from curShipFilter into @CurVal Set @FieldValue = @FieldValue + IsNULL(@CurVal,"") end return @FieldValue end select dbo.GetFiltersValue(srRequestNumber,srCrDate), * from ShipmentRequestView
Все запускается и если раньше стабильно NULL приезжал то сейчас приезжает стабильно "" Типа не NULL уже, но тоже не много...
Мало того результат тот же если убрать строку
where SRF.srfRequestNumber = @RequestNumber and SRF.srfRequestDate = @RequestDate
Т.е. по идее должно вообще все возвращаться... возвващается фиг... почему?

← →
ANB © (2005-08-31 16:39) [17]

> Ega23 © (31.08.05 16:32) [15]
- странно. Давненько я MS SQL не юзал. Да и последний раз 2000.

> Max Zyuzin © (31.08.05 16:27) [13]
- нарывался я на похожую проблему, только с процедурой. Уже не помню, чего было. То ли ошибка в синтаксисе, то ли как то функцию похитрее надо было объявлять. А она в списке функций появляется ? Может Ega23 чего подскажет, у него сервер под рукой и знает больше.

← →
Ega23 © (2005-08-31 16:40) [18]

2 Max Zyuzin © (31.08.05 16:36) [16]

Перед return @FieldValue надо вставить
Close curShipFilter
Deallocate curShipFilter

← →
Ega23 © (2005-08-31 16:42) [19]

2 ANB © (31.08.05 16:39) [17]
Может Ega23 чего подскажет, у него сервер под рукой и знает больше.

Не, по функциям - не скажу. Не делал никогда. Еднственное, что могу посоветовать - BOL.

← →
ANB © (2005-08-31 16:43) [20]

> open curShipFilter;
- а зачем тут точка с запятой ?

Жмуткий язык T-SQL. PL/SQL намного удобнее.

← →
ANB © (2005-08-31 16:46) [21]

> Ega23 © (31.08.05 16:40) [18]
- очень желательно, но ошибка тут не из-за этого.

> Max Zyuzin © (31.08.05 16:36) [16]
- сконопать пример цикла по курсору из BOL, отладь его отдельно с принтом, потом засунь в функцию. В функции тоже оставь принты в цикле. Так потихоньку и найдешь ошибку.

← →
Max Zyuzin © (2005-08-31 16:48) [22]

Дабавил вот такую штуку... короче "1" никогда не возвращается типа в цикл не заходит оно никогда
open curShipFilter; while @@fetch_status = 0 begin fetch next from curShipFilter into @CurVal Set @FieldValue = @FieldValue + "1" + IsNULL(@CurVal,"") end

>Ega23 © (31.08.05 16:40) [18]
Результат тот же.

← →
Ega23 © (2005-08-31 16:53) [23]

2 ANB © (31.08.05 16:46) [21]
- очень желательно, но ошибка тут не из-за этого.
2 Max Zyuzin © (31.08.05 16:48) [22]
Результат тот же.

Да это и не может быть решением проблемы. Просто коль выделил память под курсор и открыл его, то будь любезен закрыть и освободить память. Ошибка, конечно, не из-за этого...

2 ANB © (31.08.05 16:43) [20]
- а зачем тут точка с запятой ?

Логическое разграничение операций. В принципе, на TSQL - не требуется. С учётом того, что кроме TSQL ещё пишу на delphi, иногда на С++ и в свободное время играюсь с FireBird, где разделитель обязателен - просто привычка. Смотреть текст удобнее.

← →
Max Zyuzin © (2005-08-31 17:00) [24]

В обчем почитал BOL и в общем проблемма решалась путем добавления строки
fetch first from curShipFilter into @CurVal
После открытия курсора...
И усе забегало.

← →
Ega23 © (2005-08-31 17:04) [25]

2 Max Zyuzin © (31.08.05 17:00) [24]
В обчем почитал BOL и в общем проблемма решалась путем добавления строки
fetch first from curShipFilter into @CurVal
После открытия курсора...
И усе забегало.

Только хотел написать...
Обрати внимание, как я первый раз тебе пример с курсором показал:
while (0=0) begin Fetch Next .... if @@FETCH_STATUS=0 then Break end

>Ega23 © (31.08.05 17:04) [25]
Я и так пробовал... только я сначала видать проверку ставил, а потом фетчил... спасибо, с меня пиво :) обоим

2 Max Zyuzin © (31.08.05 17:07) [26]

Ещё одно замечание. Даже не замечание, а, скорее, ремарка:
Select distinct SRF.DispatchStation from ShpRequestFilterView SRF where SRF.srfRequestNumber = @RequestNumber and SRF.srfRequestDate = @RequestDate

В случае обращения к одной таблице префикс SRF можно не использовать.

>Ega23 © (31.08.05 17:11) [27]
Это понятно... написал ибо думал что там запрос будет сложнее. у меня вообще есть привычка именовать поля таким образом, что бы они не повторялись вообще во все базе данных, обычно добавляя сокращения названия таблицы в начало типа srfRequestNumber (первые 3-и маленьких буквы- сокращение ShipmentRequestFilter)

возникло у меня еще одно безумное желание :)
Вот так у меня теперь выглядит функция

create function GetFiltersValue(@RequestNumber bigint, @RequestDate Datetime) returns varchar(8000) as begin declare @FieldValue varchar(8000) set @FieldValue = "" declare @CurVal varchar(1000) declare curShipFilter cursor local static read_only for Select distinct SRF.DispatchStation from ShpRequestFilterView SRF where SRF.srfRequestNumber = @RequestNumber and SRF.srfRequestDate = @RequestDate open curShipFilter fetch first from curShipFilter into @CurVal while @@fetch_status = 0 begin Set @FieldValue = @FieldValue + IsNULL(@CurVal,"") fetch next from curShipFilter into @CurVal if @@fetch_status = 0 Set @FieldValue = @FieldValue + "," end Close curShipFilter Deallocate curShipFilter return @FieldValue end

Хочется значение столбца, по которому будем вести отбор в данном случае DispatchStation передавать в качестве параметра, ибо таких столбцов мне надо несколько... а для каждого ваять такую же функцию не хоцца... почитал пример вот тут...
http://www.sql.ru/faq/faq_topic.aspx?fid=104
Но пока нифига не понял, как это равлизовать.

> Ega23 © (31.08.05 16:53) [23]
- халтурщики. Требуется, не требуется. Оракл рулит.

> Max Zyuzin © (31.08.05 17:20) [28]
- если у тебя ограниченный состав полей, то лучше написать case в запросе для курсора. Динамический SQL в T-SQL по сравнению с PL/SQL отстойный, хотя и в PL/SQL им лучше не злоупотреблять без причины.

Так с ходу не придумывается...
А тебе всенепременно надо функцией?

Пока ходил кофе пить сам додумался до Case :) ща буду реализовывать. Сенкс

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

2 ANB © (31.08.05 17:27) [29]
- халтурщики. Требуется, не требуется. Оракл рулит.

А я и не спорю, что TSQL - "птичий" язык. Но другого-то пока нет...

>Ega23 © (31.08.05 17:35) [32]
Мне в общем то надо вполне конечное число полей... к тому же в том View из которого я выбираю их тоже конечно число... вполне устраивает, ИМХО лучше чем цать отдельных функций...

>ANB © (31.08.05 17:55) [35]
Дык а чего писать добавил еще один параметр к функции и по разным условиям прописал разное объявление курсора (разные точнее запросы) и все заработало.

Помогите составить SQL запрос Найти похожие ветки