[SQL] waarde uit tabel doorgeven naar UDF

[StarLite]

Ik zit met een SQL probleem waar ik niet uitkom.

Ik heb 2 queries
De eerste haalt een lijst met objecten op en de 2e selecteerd alle foto's die bij dat object horen. het probleem is dat de 1e query 1 row teruggeeft met x rows met de waardes, dus ik kan er niet gewoon een subquery van maken.

Query 1: [haalt alle objecten op]

SQL:

SELECT  
    r.rls_id ,
    r.rls_rw_residential_id as id, 
[...]
    'o' as cv_indicator,
    f.*
FROM tbl_realestate r, 
    enm_postalcode p, 
    enm_status s, 
[...]
    fn_fotos(192) f
WHERE p.psc_id = r.psc_id 
    AND s.sts_id = r.sts_id 
    [...]

Query 2: [haalt alle foto's op]]

SQL:

SELECT 
    f.img_file as foto1, f1.img_file as foto2 , f2.img_file as foto3
FROM 
    tbl_image f, tbl_image f1, tbl_image f2
WHERE
    f.img_id = 
        (SELECT 
            MIN(img_id) 
        FROM 
            tbl_rls_img_con 
        WHERE 
            rls_id = @Id
        )
AND
    f1.img_id =
        (SELECT 
            MIN(img_id)
        FROM
            tbl_rls_img_con
        WHERE
            rls_id = @Id
        AND 
            img_id > f.img_id
        )
and
f2.img_id =
        (SELECT 
            MIN(img_id)
        FROM
            tbl_rls_img_con
        WHERE
            rls_id = @Id
        AND 
            img_id > f1.img_id
        )

Nou heb ik van de 2e query een UDF gemaakt en die roep ik aan in de 1e query. Dit werkt perfect zolang ik een vaste varibele meegeef [in dit vooebeeld fn_fotos(192) f ], maar ik wil dat hij de rls_id van elke row meegeeft. Maar op de een of andere manier kan ik daar alleen maar 1 var meegeven en niet een veldnaam. Is dat uberhaupt wel mogelijk met een UDF of moet ik het anders aanpakken?

De complete UDF:

SQL:

CREATE FUNCTION fn_fotos (@Id int)
RETURNS TABLE
AS

RETURN (SELECT 
    f.img_file as foto1, f1.img_file as foto2 , f2.img_file as foto3
FROM 
    tbl_image f, tbl_image f1, tbl_image f2
WHERE
    f.img_id = 
        (SELECT 
            MIN(img_id) 
        FROM 
            tbl_rls_img_con 
        WHERE 
            rls_id = @Id
        )
AND
    f1.img_id =
        (SELECT 
            MIN(img_id)
        FROM
            tbl_rls_img_con
        WHERE
            rls_id = @Id
        AND 
            img_id > f.img_id
        )
and
f2.img_id =
        (SELECT 
            MIN(img_id)
        FROM
            tbl_rls_img_con
        WHERE
            rls_id = @Id
        AND 
            img_id > f1.img_id
        )
)

[P_de_B]

Je kunt in een UDF gewoon een waarde uit een tabel in je query meegeven.

Je queries zijn redelijk complex, kun je misschien je tabel definitie plaatsen, met een voorbeeldje van de gewenste output. Ik heb het gevoel dat het misschien wel iets eenvoudiger kan.

[StarLite]

Output query 1:

70	AM10508	24	Achtertuin	o	Geen garage	o	o
77	AM10506	24	Geen tuin	o	Geen garage	o	o
78	AM10498	24	Achtertuin	o	Geen garage	o	o

Output query 2:

AM10226001.jpg	AM10226002.jpg	AM10226003.jpg

Gewenste output

70	AM10508	24	Achtertuin	o	Geen garage	o	o	AM10226001.jpg	AM10226002.jpg	AM10226003.jpg
77	AM10508	24	Geen tuin	o	Geen garage	o	o	AM10236001.jpg	AM10236002.jpg	AM10236003.jpg
78	AM10498	24	Achtertuin	o	Geen garage	o	o	AM10246001.jpg	AM10246002.jpg	AM10246003.jpg

[ Voor 73% gewijzigd door StarLite op 06-07-2005 10:01 ]

[P_de_B]

Zeur ik erg als ik ook nog een tabellayout vraag?

[whoami]

Mja, eigenlijk is het niet de taak van SQL om jouw 'output' te verzorgen. SQL is er om gegevens te manipuleren / selecteren / etc....
Het is de taak van jouw applicatie om de gegevens die je dmv SQL opgehaald hebt, in een gewenst output-formaat te gieten.

Als je het toch perse via SQL wil doen, dan zal je idd een UDF moeten maken die de output van query 2 in één veld zet.
Dat zou ik echter niet doen op de manier zoals jij het al gedaan hebt, maar ik zou gewoon een simpele query in m'n UDF steken, die de foto's ophaalt. Nadien loop je dmv een CURSOR door je recordset, en bouw je een string op (stop alle velden in die string). Op die manier ben je ook niet gelimiteerd tot 3 'foto's'.

[StarLite]

Het probleem is dat ik geen controle heb over het script wat de input verlangt, het script vereist 1 SQL-query die alle data ophaalt, ik zou het zelf ook niet in 1 zo'n megaquery gooien...

Tabel layout: [de belangrijkste tabellen dan]

[ Voor 36% gewijzigd door StarLite op 06-07-2005 09:48 ]

[P_de_B]

StarLite schreef op woensdag 06 juli 2005 @ 09:41:
maar die kan je niet zomaar copy/pasten vanuit SQL-server

In Query Analyzer: F8, de tabel opzoeken, rechtermuisknop, script object to new window

[whoami]

Of gewoon een diagram maken in sql server, screen capturen, jpg uploaden, etc...

Als je geen controle hebt over het script wat de input verlangt, tja, dan kan je het idd misschien met een udf doen.
Echter, zoals ik al zei, ik zou die UDF iets anders schrijven.

[StarLite]

Het input script verwerkt automatisch de kolomnamen, dus de output moet helaas wel exact zijn zoals ik heb beschreven

Maar het probleem is niet dat de UDF het niet doet oid, de UDF doet het prima, alleen ik kan er geen waarde uit de query heen sturen, alleen maar een vaste variabele. Is het uberhaupt wel mogelijk om dat te doen?

Op het moment dat ik in mijn 1e query
"Select r.rls_id as rlsid, [...] from fn_fotos(rlsid) f [...]"
zet krijg ik de melding :
"'rlsid' is not a recognized OPTIMIZER LOCK HINTS option."

Ik heb het gevoel dat ik iets heel simpels over het hoofd zie, zoiets exotisch doe ik toch niet?

[ Voor 114% gewijzigd door StarLite op 06-07-2005 09:59 ]

[whoami]

StarLite schreef op woensdag 06 juli 2005 @ 09:52:
Het input script verwerkt automatisch de kolomnamen, dus de output moet helaas wel exact zijn zoals ik heb beschreven

Dat zou geen probleem zijn.

Maar het probleem is niet dat de UDF het niet doet oid, de UDF doet het prima, alleen ik kan er geen waarde uit de query heen sturen, alleen maar een vaste variabele. Is het uberhaupt wel mogelijk om dat te doen?

Ja.

Op het moment dat ik in mijn 1e query
"Select r.rls_id as rlsid, [...] from fn_fotos(rlsid) f [...]"
zet krijg ik de melding :
"'rlsid' is not a recognized OPTIMIZER LOCK HINTS option."

Gebruik eens geen alias, en gebruik gewoon r.rls_id

[ Voor 95% gewijzigd door whoami op 06-07-2005 10:05 ]

[P_de_B]

Als het probleem alleen is om een waarde uit je resultset aan je query door te geven:

SQL:

SELECT  
    r.rls_id ,
    r.rls_rw_residential_id as id, 
[...]
    'o' as cv_indicator,
    f.*
FROM tbl_realestate r, 
    enm_postalcode p, 
    enm_status s, 
[...]
    fn_fotos(r.rls_id) f
WHERE p.psc_id = r.psc_id 
    AND s.sts_id = r.sts_id 
    [...]

Je gebruikt gewoon r.rls_id als parameter voor je UDF

edit:
/traag ....

[ Voor 4% gewijzigd door P_de_B op 06-07-2005 10:08 ]

[StarLite]

Als ik r.rls_id gebruik krijg ik:
Line 37: Incorrect syntax near '.'.
Zelfde bij tbl_realestate.rls_id

[P_de_B]

StarLite schreef op woensdag 06 juli 2005 @ 10:10:
Als ik r.rls_id gebruik krijg ik:
Line 37: Incorrect syntax near '.'.
Zelfde bij tbl_realestate.rls_id

gebruik eens dbo.fn_fotos

Of een andere eigenaar, als dbo de funtie niet gemaakt heeft natuurlijk

[StarLite]

Nope, nog steeds dezelfde error. Maar de syntax lijkt me juist, aangezien het met fn_fotos(192) wel werkt

[P_de_B]

copy/paste de exacte query eens. Niet een verkorte versie, niet een testversie, de exacte query.

[whoami]

Ah....
Waarom heb je van die UDF een TABLE - udf gemaakt (ik weet de juiste naam niet meer voor UDF-en die tables returnen)?
Waarom maak je geen UDF die een string returned , en die je dan in je SELECT list kunt opnemen ?

code:

CREATE FUNCTION  fn_fotos( @id INT )
AS
BEGIN

   DECLARE curr CURSOR FOR
   SELECT .....

    OPEN curr
     
    FETCH NEXT .....
   
    WHILE @@FETCH_STATUS = 0 
    ....
         SET str = str + ' ' + ....
    END

    CLOSE curr
    DEALLOCATE curr

     RETURN str

END

Zo iets ongeveer.

Dan kan je het zo doen:

code:

select blaa, fn_foto(id) from tabel

[StarLite]

SQL:

SELECT  
    r.rls_id,
    r.rls_rw_residential_id as id, 
    '24' as eigenaar, 
    p.psc_postalcode as postcode, 
    r.rls_number as huisnr, 
    r.rls_number_extra as toevoeging, 
    convert(varchar, GETDATE(), 105) as publicatiedatum, 
    s.sts_name as status, 
    type.ptt_name as woningtype, 
    pcc.pcc_name as financiering, 
    '2' as oplevercode, 
    '' as opleverdatum, 
    r.rls_buildyear as bouwjaar, 
    r.rls_rooms as aantal_kamers, 
    r.rls_livingspace as oppervlakte, 
    r.rls_surface as oppervlakte_kavel, 
    r.rls_volume as inhoud, 
    pc.rls_price as prijs, 
    r.rls_description as tekst, 
    tuin.ptt_name as tuinindicator, 
    'o' as balkonindicator, 
    garage.ptt_name as garage_indicator, 
    schuur.ptt_name as bergingsindicator, 
    'o' as liftindicator, 
    'o' as cv_indicator,
    f.*
FROM tbl_realestate r, 
    enm_postalcode p, 
    enm_status s, 
    tbl_price pc, 
    enm_pricecondition pcc, 
    enm_propertiestype type, 
    enm_propertiestype tuin, 
    enm_propertiestype garage, 
    enm_propertiestype schuur,
    dbo.fn_fotos(r.rls_id) f
WHERE p.psc_id = r.psc_id 
    AND s.sts_id = r.sts_id 
    AND pc.rls_id = r.rls_id 
    AND ( pc.pct_id = 5 OR pc.pct_id = 6 ) 
    AND pcc.pcc_id = pc.pcc_id 
    AND type.ptt_id = (
        select MIN(pt.ptt_id) 
        from tbl_property p, enm_propertiestype pt 
        where rls_id = r.rls_id 
        and p.ptt_id = pt.ptt_id 
        and pt.ptts_id = 1) 
    AND tuin.ptt_id = (
        select MIN(pt.ptt_id) 
        from tbl_property p, enm_propertiestype pt 
        where rls_id = r.rls_id 
        and p.ptt_id = pt.ptt_id 
        and pt.ptts_id = 2) 
    AND garage.ptt_id = (
        select MIN(pt.ptt_id) 
        from tbl_property p, enm_propertiestype pt 
        where rls_id = r.rls_id 
        and p.ptt_id = pt.ptt_id 
        and pt.ptts_id = 3) 
    AND schuur.ptt_id = (
        select MIN(pt.ptt_id) 
        from tbl_property p, enm_propertiestype pt 
        where rls_id = r.rls_id 
        and p.ptt_id = pt.ptt_id 
        and pt.ptts_id = 4)

[P_de_B]

whoami schreef op woensdag 06 juli 2005 @ 10:17:
Ah....
Waarom heb je van die UDF een TABLE - udf gemaakt (ik weet de juiste naam niet meer voor UDF-en die tables returnen)?
Waarom maak je geen UDF die een string returned , en die je dan in je SELECT list kunt opnemen ?

Dacht ik ook al aan, dan zou het ook wel inline kunnen, maar het ontvangende script wil waarschijlijk 3 kolommen terug, althans dat vermoed ik.

[StarLite]

klopt, het moet 1 kolom per foto zijn, niet 1 kolom met alle foto's.

[whoami]

Probeer het eens door geen gebruik te maken van aliassen (ok, wat meer type-werk, maar ik denk dat het daar toch iets mee te maken zal hebben)

[StarLite]

Ook het vervangen van de aliasses heeft niks geholpen

zelfs:

code:

SELECT  
    tbl_realestate.rls_id,
    f.*
FROM tbl_realestate r, 
    fn_fotos(tbl_realestate.rls_id) f

geeft:
Server: Msg 170, Level 15, State 1, Line 4
Line 4: Incorrect syntax near '.'.

[ Voor 66% gewijzigd door StarLite op 06-07-2005 10:32 ]

[P_de_B]

Ik denk dat het komt omdat je een table valued functie gebruikt, en je joined hem niet goed. Ik heb je query even omgeschreven zodat de join criteria niet meer in de where staan (vind ik overzichterlijker) en dan ziet het er zo uit:

SQL:

SELECT  
  [...]
FROM tbl_realestate r 
INNER JOIN enm_postalcode p ON  p.psc_id = r.psc_id 
INNER JOIN enm_status s ON s.sts_id = r.sts_id 
INNER JOIN tbl_price pc ON pc.rls_id = r.rls_id 
INNER JOIN enm_pricecondition pcc ON pcc.pcc_id = pc.pcc_id 
INNER JOIN enm_propertiestype type ON type.ptt_id = (
                                select MIN(pt.ptt_id) 
                                from tbl_property p, enm_propertiestype pt 
                                where rls_id = r.rls_id 
                                and p.ptt_id = pt.ptt_id 
                                and pt.ptts_id = 1) 
INNER JOIN enm_propertiestype tuin ON tuin.ptt_id = (
                                select MIN(pt.ptt_id) 
                                from tbl_property p, enm_propertiestype pt 
                                where rls_id = r.rls_id 
                                and p.ptt_id = pt.ptt_id 
                                and pt.ptts_id = 2) 
INNER JOIN enm_propertiestype garage ON garage.ptt_id = (
                                select MIN(pt.ptt_id) 
                                from tbl_property p, enm_propertiestype pt 
                                where rls_id = r.rls_id 
                                and p.ptt_id = pt.ptt_id 
                                and pt.ptts_id = 3) 
INNER JOIN enm_propertiestype schuur ON schuur.ptt_id = (
                                select MIN(pt.ptt_id) 
                                from tbl_property p, enm_propertiestype pt 
                                where rls_id = r.rls_id 
                                and p.ptt_id = pt.ptt_id 
                                and pt.ptts_id = 4)
INNER JOIN dbo.fn_fotos(r.rls_id) f ON f.rls_id = r.rls_id
WHERE 
    ( pc.pct_id = 5 OR pc.pct_id = 6 ) 
    

Je ziet dat de functie nu ook gejoined wordt, je moet alleen r.rls_id nog wel opnemen in de output van je UDF.

Als dit nog niet werkt, haal dan eerst even alle overbodige tabellen (tuin, garage etc) even weg zodat het debuggen wat makkelijker gaat, zonder de subselects in de join criteria.

[ Voor 5% gewijzigd door P_de_B op 06-07-2005 10:32 ]

[StarLite]

helaas, ik krijg nog steeds diezelfde error
Server: Msg 170, Level 15, State 1, Line 56
Line 56: Incorrect syntax near '.'.

Zou het een configuratieerror ergens kunnen zijn?

[ Voor 11% gewijzigd door StarLite op 06-07-2005 10:50 ]

[P_de_B]

Hmm, snap het niet echt meer

SQL:

SELECT  * 
FROM tbl_realestate r 
INNER JOIN fn_fotos(r.rls_id) f ON f.rls_id = r.rls_id 

Dit dan?

[StarLite]

Server: Msg 170, Level 15, State 1, Line 3
Line 3: Incorrect syntax near '.'.

[P_de_B]

aaaaarrghhhh, ik ben gek

Natuuuurlijk werkt dit niet. Je wilt helemaal geen parameter meegeven. Je moet je UDF aanpassen zodat hij zonder parameter werkt, ook een rls_id terug geeft en dan gewoon joinen.

SQL:

    
SELECT  * 
FROM tbl_realestate r 
INNER JOIN fn_fotos() f ON f.rls_id = r.rls_id

[ Voor 89% gewijzigd door P_de_B op 06-07-2005 11:19 ]

[StarLite]

Tnx, dat was inderdaad de juiste oplossing.
De SQL in mijn UDF ziet er nu zo uit:

SQL:

SELECT 
    tr.rls_id, f.img_file as foto1, f1.img_file as foto2 , f2.img_file as foto3
FROM 
    tbl_rls_img_con tr, tbl_image f, tbl_image f1, tbl_image f2
WHERE
    f.img_id = 
        (SELECT MIN(img_id) FROM tbl_rls_img_con where rls_id = tr.rls_id)
AND
    f1.img_id =
        (SELECT MIN(img_id) FROM tbl_rls_img_con where img_id > f.img_id 
            AND  rls_id = tr.rls_id )
and
    f2.img_id =
        (SELECT MIN(img_id) FROM tbl_rls_img_con where img_id > f1.img_id 
            AND  rls_id = tr.rls_id )

Het enige probleem is nu dat hij alleen alle objecten weergeeft waar 3 of meer foto's aan zitten, wat is de beste manier op dit op te lossen?
Als er geen foto is moet het veld dus leeg blijven.

[P_de_B]

Je moet je JOIN structuur aanpassen. Zoals jij het doet met implicit joins, met de join criteria in de WHERE clausule, heb je altijd een INNER JOIN. Je hebt juist een LEFT OUTER JOIN nodig.

SQL:

SELECT  * 
FROM tbl_realestate r 
LEFT OUTER JOIN fn_fotos() f ON f.rls_id = r.rls_id

[StarLite]

Inderdaad, als ik in de UDF ook left joins gebruik pakt ie automatisch de NULL's
Nu alleen nog zorgen dat ook alle objecten zonder foto's weergegeven worden

edit:

Dat doet ie dus al door de left outer join

Iedereen bedankt voor de hulp en tips

[ Voor 84% gewijzigd door StarLite op 06-07-2005 15:18 ]