August 11, 2022, 04:28:39

Neuigkeiten:

Ist euer Problem gelöst, dann bitte den Knopf "Thema gelöst" drücken!


Aufruf eines Moduls in einer Stored Procedure oder Sicht

Begonnen von Klaus S. aus B, März 26, 2018, 16:47:50

⏪ vorheriges - nächstes ⏩

Klaus S. aus B

Hallo zusammen,

ich versuche mal mein Problem zu erklären.
Ich habe als eine Spalte in einer Abfrage den Aufruf einer Prozedur drin:
SELECT tbl_PN.Proj_ID, zPN([Proj_ID]) AS zeile
FROM tbl_PN
GROUP BY tbl_PN.Proj_ID;


Die Prozedur sieht wie folgt aus:
Public Function zPN(Proj_ID As Integer) As String
    strSQL = "Select PN, Proj_ID from tbl_PN where Proj_ID =" & Proj_ID & "  Order by PN"
   
    Set rS = CurrentDb.OpenRecordset(strSQL)
       
    Do While rS.EOF = False
        zPN = zPN & "PN" & rS!PN
        zPN = zPN & Chr(13) & Chr(10)
        rS.MoveNext
    Loop
End Function


Funktioniert super. Jetzt bin ich dabei im Zuge des Umbaus der  Datenbank auf SQL zu versuchen die Abfragen im Frontend umzubauen in Sichten und stored Procedures (zur Performance Steigerung).

Jetzt meine Fragen:

  • kann man so etwas überhaupt in Sichten oder stored procedures umbauen?

  • was ist der richte Ansatz?



Gruß Klaus

Lachtaube

Mit Microsofts SQL-Server würde man eine Funktion wie STRING_AGG in einer View einsetzen. Andere SQL-Server haben ähnliche Funktionen.
Grüße von der (⌒▽⌒)

Klaus S. aus B

Super!

Vielen Dank! Genau das habe ich gebraucht.

Gruß
Klaus

...ich liebe SQL Server jetzt schon. Da gehen Sachen, die man in Access umständlich bauen muss.

Klaus S. aus B


Klaus S. aus B

Zitat von: Lachtaube am März 26, 2018, 17:18:34
Mit Microsofts SQL-Server würde man eine Funktion wie STRING_AGG in einer View einsetzen. Andere SQL-Server haben ähnliche Funktionen.


Hallo Lachtaube,

jetzt habe ich aber doch ein Problem. String_Agg gibt es wohl erst ab SQL Server 2017. Ich muss meine Datenbank aber jetzt auch MS SQL 2014 implementieren.

Wie bekomme ich das dort hin?

Gruß
Klaus

Lachtaube

Es iist mmer eine gute Idee, die Version der verwendeten Software bei Fragen anzugeben.

In älteren Versionen des SQL-Servers muss man sich selbst etwas zusammenschustern. Dazu kann die STUFF-Funktion von T-SQL verwendet werden. Die darin erzeugten Datensätze lassen sich mit der FOR XML Anweisung im PATH-Modus so transformieren, dass deren Datensätze verkettet werden. Hier ist ein DBFiddle, der aufzeigt, wie Du in Deinem Fall vorgehen kannst.
Grüße von der (⌒▽⌒)

Klaus S. aus B

Hallo Lachtaube,

super vielen Dank! Nach etwas basteln habe ich es hinbekommen.
Das mit dem path-Modus habe ich noch nicht verstanden, aber ich konnte meine Views entsprechend umbauen, dass sie funktionieren.

Gruß
Klaus

Klaus S. aus B

... ich bin doch zu doof.  :(

Zwei Views habe ich hinbekommen, bei der dritten scheitere ich völlig. Ich bekomme zwar ein Ergebnis, das ist aber leider falsch.

Identische Ausgangslage: Eine Abfrage mit Aufruf einer Prozedur:

Abfrage:
SELECT tbl_DocMap.DN_ID, PNmap([DN_ID]) AS mapping
FROM tbl_DocMap
GROUP BY tbl_DocMap.DN_ID;


Prozedur:
Public Function PNmap(ByVal DN_ID As Variant) As String
    Dim strSQL As String
    On Error GoTo err
   
    If IsNull(DN_ID) Then
        PNmap = "  no PN assigned"
    Else
   
   
     strSQL = "SELECT tbl_PN.Proj_ID, [tbl_PN]![PN] & ""("" & [tbl_maschtyp]![Typ] & [tbl_maschtyp]![Baugr]" _
            & " & "")"" AS unit, tbl_PN.PN, tbl_DocMap.DN_ID FROM (tbl_maschtyp INNER JOIN tbl_PN ON" _
            & " tbl_maschtyp.ID_Mtyp = tbl_PN.Mtyp_ID) INNER JOIN tbl_DocMap ON tbl_PN.ID_PN = tbl_DocMap.PN_ID" _
            & " WHERE (((tbl_DocMap.DN_ID) = " & DN_ID & ")) ORDER BY tbl_PN.PN;"

   
        Set rS = DBEngine(0)(0).OpenRecordset(strSQL)
       
        Do Until rS.EOF
            PNmap = PNmap & "; PN" & rS!unit
            rS.MoveNext
           
        Loop
           
           PNmap = Mid(PNmap, 3)
   
    End If

ExitPN:
Exit Function
           
err:

Resume ExitPN

                   
End Function



Ich habe es wie folgt versucht umzusetzen, aber leider kommen da falsche Ergebnisse raus.
SELECT        dbo.tbl_DocMap.DN_ID, stuff
                             ((SELECT DISTINCT '; PN' + cast(PN AS varchar(10)) + ' (', dbo.tbl_maschtyp.Typ, dbo.tbl_maschtyp.Baugr, ')'
                                 FROM            dbo.tbl_PN inner join dbo.tbl_maschtyp on dbo.tbl_maschtyp.ID_Mtyp = dbo.tbl_PN.Mtyp_ID INNER JOIN
                         dbo.tbl_DocMap ON dbo.tbl_PN.ID_PN = dbo.tbl_DocMap.PN_ID
                                 WHERE        proj_id = t.proj_id
                                 ORDER BY 1 FOR xml path(''), type ).value('.', 'varchar( max )'), 1,2, '') AS unit

FROM            dbo.tbl_PN t inner join dbo.tbl_DocMap on t.ID_PN = dbo.tbl_DocMap.PN_ID


Also habe ich im dbfiddle versucht das ganze vereinfacht nachzubauen https://dbfiddle.uk/?rdbms=sqlserver_2014&fiddle=5bfaf38b9577c56c36fe6251a5034259, da kommt aber natürlich auch nur das falsche Ergebnis raus.

Kann mir jemand auf die Sprünge helfen?

Gruß
Klaus

Lachtaube

Eine andere, vermutlich auch leichter zu verstehende Möglichkeit besteht in der Verwendung einer CTE, in der rekursiv die Ausdrücke verkettet werden und am Ende über die Anzahl gefiltert wird: Siehe dbfiddle.

PS: einmal ein Schlüsselfeld mtyp_id und dann id_mtyp zu nennen, ist ganz schön verwirrend. :)
Grüße von der (⌒▽⌒)

Klaus S. aus B

ZitatPS: einmal ein Schlüsselfeld mtyp_id und dann id_mtyp zu nennen, ist ganz schön verwirrend


...mir hilft es: ID_xxx ist das Primärschlüsselfeld einer Tabelle und xxx_ID ist in einer Tabelle ein verknüpfter Wert hierzu.

ich weiß, ist nicht umgekehrt polnische Notation, aber mit der komme ich nicht klar. ;)

datekk

dbfiddle... danke Lachtaube, wieder was gelernt :)

@Klaus S.: Ich würde auf _ "Unterstriche" in SQL Datenbanken aus heutiger Sicht verzichten. Sie machen es erforderlich, dass Spalten geklammert [ID_Spalte] werden müssen.
Access 2016 mit SQL Server Backend. Bereits umgesetzt: Access mit MS SQL Backend,  ADODB Formularbindung, Streamen von Dateien zum SQL Server und zurück (Filestream), Drag&Drop Dateiupload zum Server, CTI / TAPI Integrierung in Access Anwendung - Nutzung auch über Remote Desktop, selbst aktualisierendes Access Frontend auf entfernten Rechnern (Upgrade). Berichte / Kreuztabellen mit SQL Server Backend, Mail Tagging, Outlook Steuerung über Access und umgekehrt // Grundwissen in .Net Core & Blazor Apps

Klaus S. aus B

Hi Datekk,

...wieder was gelernt.

Danke für den Tipp!

Gruß
Klaus

PhilS

Zitat von: datekk am Mai 16, 2018, 12:13:18Ich würde auf _ "Unterstriche" in SQL Datenbanken aus heutiger Sicht verzichten. Sie machen es erforderlich, dass Spalten geklammert [ID_Spalte] werden müssen.

Das ist nicht korrekt. Unterstriche sind im Gegensatz zu anderen Sonderzeichen völlig unproblematisch.
Neue Videoserie: Windows API in VBA

Klassische CommandBars visuell bearbeiten: Access DevTools CommandBar Editor

Klaus S. aus B

Zitat von: Lachtaube am Mai 11, 2018, 17:29:28
Eine andere, vermutlich auch leichter zu verstehende Möglichkeit besteht in der Verwendung einer CTE, in der rekursiv die Ausdrücke verkettet werden und am Ende über die Anzahl gefiltert wird


Hallo Lachtaube,

bin jetzt erst dazu gekommen, mir die Sache wieder anzuschauen.

Jetzt habe ich aber eine Frage, die mir auch Googlen nicht gelöst hat:

Wo "kommt diese CTE" hin? Ist sie Teil der View Definition (wenn ja, ist mir die Syntax nicht klar), oder ist das etwas, das unter "Programmierbarkeit" gehört, oder ganz woanders hin?

Gruß
Klaus

PhilS

Zitat von: Klaus S. aus B am Juni 25, 2018, 04:11:52Wo "kommt diese CTE" hin? Ist sie Teil der View Definition (wenn ja, ist mir die Syntax nicht klar), oder ist das etwas, das unter "Programmierbarkeit" gehört, oder ganz woanders hin?

Eine Common Table Expression (CTE) ist Teil des jeweiligen SQL-Statements und gehört somit bei einer View mit in die View Definition.
Neue Videoserie: Windows API in VBA

Klassische CommandBars visuell bearbeiten: Access DevTools CommandBar Editor