Access-o-Mania

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

Mit Microsofts SQL-Server würde man eine Funktion wie STRING_AGG (https://docs.microsoft.com/de-de/sql/t-sql/functions/string-agg-transact-sql) in einer View einsetzen. Andere SQL-Server haben ähnliche Funktionen.

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.

... wo ist der "Thema gelöst Button" hin ???

Zitat von: Lachtaube am März 26, 2018, 17:18:34
Mit Microsofts SQL-Server würde man eine Funktion wie STRING_AGG (https://docs.microsoft.com/de-de/sql/t-sql/functions/string-agg-transact-sql) 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

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 (https://msdn.microsoft.com/de-de/library/ms188043(v=sql.120).aspx)-Funktion von T-SQL verwendet werden. Die darin erzeugten Datensätze lassen sich mit der FOR XML (https://docs.microsoft.com/de-de/sql/relational-databases/xml/for-xml-sql-server?view=sql-server-2017) Anweisung im PATH-Modus so transformieren, dass deren Datensätze verkettet werden. Hier (https://dbfiddle.uk/?rdbms=sqlserver_2014&fiddle=1a55e156d0297a829dbba588c513498d) ist ein DBFiddle, der aufzeigt, wie Du in Deinem Fall vorgehen kannst.

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

... 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 (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

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 (https://dbfiddle.uk/?rdbms=sqlserver_2014&fiddle=aef85db6cc87f054c0a1610f085703bd).

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

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. ;)

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.

Hi Datekk,

...wieder was gelernt.

Danke für den Tipp!

Gruß
Klaus

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.

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

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.

Hallo zusammen,

ich  habe jetzt ein etwas seltsames Phänomen.
Ich habe das CTE hinbekommen und im SQL Management Studio bekomme ich auch ein vernünftiges Ergebnis angezeigt.
Aber wenn ich in einer Access DB die Sicht einbinde und dort anzeige, dauert es zum einen sehr lange bis sie angezeigt wird und es sind keine zusammengesetzten Werte (Mehr als eine PN) zu finden. Pro Zeile nur eine PN mit Typ, das war's. ???

Was mache ich falsch?

Gruß
Klaus

Kannst du dafür ein reproduzierbares Minimalbeispiel posten?

Wie kann ich von einem SQL Server ein Beispiel posten?

Zitat von: Klaus S. aus B am Juni 25, 2018, 17:40:14Wie kann ich von einem SQL Server ein Beispiel posten?

In diesem Fall wäre das CREATE VIEW Statement ein Anfang. Ideal wäre es, wenn du ein vollständiges Script, das den Fehler reproduziert, posten würdest.

Ich freue mich, wenn ich sowas bekomme, um ein Problem zu reproduzieren:
CREATE TABLE dbo.foo (
   ID int NOT NULL PRIMARY KEY,
   Dummy varchar(20) NULL
);
GO

INSERT INTO dbo.foo (ID, Dummy) VALUES (1,'Blablabla'), (2,'Abrakadabra');
GO

CREATE VIEW dbo.bar
AS
WITH cte_bar (ID, Dummy)
AS (SELECT ID,
Dummy
FROM dbo.foo
)
SELECT f.ID,
f.Dummy,
cb.Dummy AS CTE_Dummy
FROM dbo.foo f
INNER JOIN cte_bar cb
ON f.ID = cb.ID
;
GO

SELECT * FROM  dbo.bar ;

/*
DROP VIEW dbo.bar;
DROP TABLE dbo.foo;
*/
Nebenbei: Wenn man ein Problem auf seinen Kern reduziert, findet man dabei nicht selten selbst den Fehler bzw. die Ursache des Problems.

Hallo Phils,

:-[  #-(
...ich bin echt ein Trottel!

Wenn man auch den Zeilenumbruch " + char(13) + char(10) +" im Management Studio nicht sieht, sollten man trotzdem mal ein Shift+F2 im Access machen.
Siehe da, die Zeile ist komplett vorhanden.

Sorry, Problem gelöst!

Gruß
Klaus