[SQL] Functie aanroepen met parameter, terug naar query

[Urk]

Ik heb onderstaande SQL Query:

SQL:

DECLARE @doctypes varchar(100)
SELECT @doctypes = COALESCE (@doctypes + ', ', '') + CAST(doctype_name AS varchar(25))
FROM lsidoctype
INNER JOIN doctypes ON lsidoctype.lsidoctype_doctypeid = doctypes.doctype_id
WHERE lsidoctype_docid = 1009

SELECT lsidoc_id, lsidoc_title, lsidoc_authorpublisher, @doctypes AS lsidoc_doctypes
FROM lsidocs
WHERE (lsidoc_title LIKE '%human%')
ORDER BY lsidoc_title, lsidoc_submitted DESC

In de 2e SELECT query vraag ik de variabele @doctypes op uit het SELECT statement daarboven. Echter wordt deze eerste SELECT query en daarmee dus die variabele maar 1 keer gevuld.
Voor ieder record is @doctypes dus hetzelfde.

Hoe kan ik van de eerste SELECT query een SQL functie maken die ik aan kan roepen vanuit de 2e SELECT query en daarmee een ID mee kan geven die in de functie wordt gebruikt?

Heb de MS SQL2005 handleiding op functies nageslagen maar kom er niet uit

Heb zoiets geprobeerd:

SQL:

CREATE FUNCTION lsidoctype.tester(@docuid int)
RETURN 
(DECLARE @doctypes varchar(100) SELECT @doctypes = COALESCE (@doctypes + ', ', '') + CAST(doctype_name AS varchar(25))
FROM lsidoctype INNER JOIN doctypes ON  lsidoctype.lsidoctype_doctypeid = doctypes.doctype_id
WHERE lsidoctype_docid = @docuid);

SELECT lsidoc_id, lsidoc_title, lsidoc_authorpublisher,  tester(lsidoc_id) AS lsidoc_doctypes
FROM lsidocs
WHERE (lsidoc_title LIKE '%human%') AND (lsidoc_title LIKE '%rights%')
ORDER BY lsidoc_title, lsidoc_submitted DESC

[ Voor 23% gewijzigd door Urk op 20-06-2007 16:45 ]

[remco_k]

Waarom wil je dat in een functie?
Je kan toch ook gewoon joinen, zoiets als:

SQL:

SELECT lsidoc_id, lsidoc_title, lsidoc_authorpublisher, COALESCE (@doctypes + ', ', '') + CAST(doctype_name AS varchar(25)) AS lsidoc_doctypes 
FROM lsidocs 
INNER JOIN lsidoctype ON lsidoctype_docid = 1009 
INNER JOIN doctypes ON lsidoctype.lsidoctype_doctypeid = doctypes.doctype_id 
WHERE (lsidoc_title LIKE '%human%') 
ORDER BY lsidoc_title, lsidoc_submitted DESC

[ Voor 18% gewijzigd door remco_k op 20-06-2007 16:51 . Reden: Sql fixje ]

[Urk]

remco_k schreef op woensdag 20 juni 2007 @ 16:48:
Waarom wil je dat in een functie?
Je kan toch ook gewoon joinen, zoiets als:

SQL:

SELECT lsidoc_id, lsidoc_title, lsidoc_authorpublisher, @doctypes AS lsidoc_doctypes 
FROM lsidocs 
INNER JOIN lsidoctype ON lsidoctype_docid = 1009 
INNER JOIN doctypes ON lsidoctype.lsidoctype_doctypeid = doctypes.doctype_id 
WHERE (lsidoc_title LIKE '%human%') 
ORDER BY lsidoc_title, lsidoc_submitted DESC

Een join kan volgens mij niet, ook een subquery heb ik al geprobeerd maar dan krijg ik vage foutmeldingen.
Ik heb namelijk het eerste deel nodig:

Het gaat om documenten die kunnen behoren bij meerdere documenttypen, omdat ik later hierin moet doorzoeken en resultaten moet weergeven dien ik alle documenttype die in een aparte tabel staan komma delimited in 1 kolom terug te geven in de query.

Stel ik heb dus een document wat behoort tot 3 document types: Handleiding, Brochure en Flyer. Dan wil ik die in de query in 1 kolom weergeven als Handleinding, Brochure, Flyer i.p.v. 3 aparte records (in rijen) weer te geven.

[RobIII]

Urk schreef op woensdag 20 juni 2007 @ 16:59:
[...]
Stel ik heb dus een document wat behoort tot 3 document types: Handleiding, Brochure en Flyer. Dan wil ik die in de query in 1 kolom weergeven als Handleinding, Brochure, Flyer i.p.v. 3 aparte records (in rijen) weer te geven.

Dan zou ik persoonlijk liever 3 rijen hebben en de output (komma separated dus) genereren in mijn "GUI laag". Dit soort dingen hoor je (IMHO) niet te laten doen door je RDBMS

[ Voor 6% gewijzigd door RobIII op 20-06-2007 17:04 ]

[Urk]

RobIII schreef op woensdag 20 juni 2007 @ 17:03:
[...]

Dan zou ik persoonlijk liever 3 rijen hebben en de output (komma separated dus) genereren in mijn "GUI laag". Dit soort dingen hoor je (IMHO) niet te laten doen door je RDBMS

Toch wil ik dit doen binnen de query, en het samenvoegen van de records in 1 kolom record met komma seperatie gaat prima losstaand, echter niet i.c.m. de rest van de query.

Iemand een idee?

[RobIII]

Maak er een UDF van Die kun je namelijk inline aanroepen. Again; of dit gunstig is voor je performance is een tweede... ik zou het écht overlaten aan de presentatielaag.
Dat had je al

[ Voor 83% gewijzigd door RobIII op 20-06-2007 18:20 ]

[remco_k]

Urk schreef op woensdag 20 juni 2007 @ 16:59:
[...]

Een join kan volgens mij niet, ook een subquery heb ik al geprobeerd maar dan krijg ik vage foutmeldingen.
Ik heb namelijk het eerste deel nodig:

Het gaat om documenten die kunnen behoren bij meerdere documenttypen, omdat ik later hierin moet doorzoeken en resultaten moet weergeven dien ik alle documenttype die in een aparte tabel staan komma delimited in 1 kolom terug te geven in de query.

Stel ik heb dus een document wat behoort tot 3 document types: Handleiding, Brochure en Flyer. Dan wil ik die in de query in 1 kolom weergeven als Handleinding, Brochure, Flyer i.p.v. 3 aparte records (in rijen) weer te geven.

Waarom zou een join niet kunnen? Wil je daarmee zeggen dat er helemaal geen relatie is tussen lsidoctype en lsidocs?
In dat geval kan het natuurlijk simpel niet wat je wilt, niet met een losse functie en niet met een inner join.
Als er wel een relatie is (en die is er natuurlijk) dan kan beide weer wel. Noteer je tabel structuur hier even, want alleen met je queries kan ik niet zoveel. Want, afleidend uit die queries - zie ik geen relatie tussen lsidoctype en lsidocs.

[bigbeng]

De TS wil dus alle doctypes van een document als 1 kolom weergeven. Dit kan inderdaad uitstekend met een UDF.

@TS: Gezien je code en je halsstarrig vasthouden aan het vullen van een veld met csv's heb je nog niet veel ervaring met transact sql. Coalesce geeft van een verzameling expressions de eerste waarde terug die niet null is. Dat is dus niet wat jij wil.

edit:
eerst lezen dan blaten... sowwy

Even ervan uitgaande dat het samenstellen van de doctypes tot 1 kolom een must is, dan is het definieren van een UDF inderdaad de way to go. Zoek eerst de syntax van user defined functions eens op en implementeer dan de volgende pseudo code:

code:

... definieer een return value variabele (@doctypes in jouw code)
...gegeven het doc id, haal alle doctypes erbij en plaats dit in een cursor
...doorloop de cursor en voeg het doctype toe aan de return variabele
...sluit de cursor
...return return variabele

[Urk]

bigbeng schreef op woensdag 20 juni 2007 @ 19:27:
De TS wil dus alle doctypes van een document als 1 kolom weergeven. Dit kan inderdaad uitstekend met een UDF.

@TS: Gezien je code en je halsstarrig vasthouden aan het vullen van een veld met csv's heb je nog niet veel ervaring met transact sql. Coalesce geeft van een verzameling expressions de eerste waarde terug die niet null is. Dat is dus niet wat jij wil.

edit:
eerst lezen dan blaten... sowwy

Even ervan uitgaande dat het samenstellen van de doctypes tot 1 kolom een must is, dan is het definieren van een UDF inderdaad de way to go. Zoek eerst de syntax van user defined functions eens op en implementeer dan de volgende pseudo code:

code:

... definieer een return value variabele (@doctypes in jouw code)
...gegeven het doc id, haal alle doctypes erbij en plaats dit in een cursor
...doorloop de cursor en voeg het doctype toe aan de return variabele
...sluit de cursor
...return return variabele

Ik heb inderdaad nauwelijks ervaring met SQL User Definied Functions, probeer me een beetje in te lezen en mijn query te bouwen, maar blijf een error krijgen "Incorrect syntax near the keyword 'SELECT'" op regel 12:

SQL:

CREATE FUNCTION dbo.testerr(@docuid int)
RETURNS varchar
AS
BEGIN
 DECLARE @doctypes varchar(100)
 SELECT @doctypes = COALESCE(@doctypes + ', ', '')+CAST(doctype_name AS varchar(25))
 FROM lsidoctype INNER JOIN doctypes ON lsidoctype.lsidoctype_doctypeid = doctypes.doctype_id
 WHERE lsidoctype_docid = @docuid
 RETURN @doctypes
END

SELECT lsidoc_id, lsidoc_title, lsidoc_authorpublisher, dbo.testerr(1009) AS lsidoc_doctypes
FROM lsidocs
WHERE (lsidoc_title LIKE '%human%') AND (lsidoc_title LIKE '%rights%')
ORDER BY lsidoc_title, lsidoc_submitted DESC

Zit ik in de buurt?

[Urk]

YES YES YES!! Het is gelukt! haha
Ik heb de volgende functie gemaakt:

SQL:

CREATE FUNCTION dbo.getdoctypes(@docuid int)
RETURNS varchar
AS
BEGIN
 DECLARE @doctypes varchar(100)
 SELECT @doctypes = COALESCE(@doctypes + ', ', '')+CAST(doctype_name AS varchar(25))
 FROM lsidoctype INNER JOIN doctypes ON lsidoctype.lsidoctype_doctypeid = doctypes.doctype_id
 WHERE lsidoctype_docid = @docuid
 RETURN @doctypes
END
GO

Ik zie onder MS SQL 2005 dat er dan een nieuwe functie is aangemaakt onder:
Database --> Programmability --> Functions --> Scalar-valued Functions
met als inhoud:

SQL:

USE [lsi_develop]
GO
/****** Object:  UserDefinedFunction [dbo].[getdoctypes]    Script Date: 06/20/2007 21:07:33 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER FUNCTION [dbo].[getdoctypes](@docuid int)
RETURNS varchar(1000)
AS
BEGIN
 DECLARE @doctypes varchar(1000)
 SELECT @doctypes = COALESCE(@doctypes + ', ', '')+CAST(doctype_name AS varchar(25))
 FROM lsidoctype INNER JOIN doctypes ON lsidoctype.lsidoctype_doctypeid = doctypes.doctype_id
 WHERE lsidoctype_docid = @docuid
 RETURN @doctypes
END

Vervolgens run ik deze query:

SQL:

SELECT lsidoc_id, lsidoc_title, lsidoc_authorpublisher, dbo.getdoctypes(lsidoc_id) AS lsidoc_doctypes
FROM lsidocs
WHERE (lsidoc_title LIKE '%human%') AND (lsidoc_title LIKE '%rights%')
ORDER BY lsidoc_title, lsidoc_submitted DESC

En krijg ik netjes het juiste resultaat!
Bedankt voor al jullie hulp op de goede richting!