Access SQL: Konvertierung Dezimalzahl zu Binärzahl

[hajott]

Hallo Wissende,

für die schnelle Antwort: Mit welchem SQL-Statement kann ich Dezimal in Binär umwandeln? Gegeben sei eine Tabelle mit hunderten Einträgen, in der im Feld [dec] eine Dezimalzahl steht (maximal 2^13) und im Feld [bin] soll mittels UPDATE ein linksbündiger Binärstring mit Länge 14 eingesetzt werden, also beispielsweise soll [dec]= 37 [bin] ="10100100000000" ergeben. Ich habe das Internet komplett durchgelesen und nur eine VBA-Funktion gefunden, die mir aber nicht hilft. Ich habe herausgefunden, dass ma mit einer Tabellendefinition über VBA auch einen Felddatentyp Binär erzeugen kann. Das klappt auch, aber wenn ich das Feld [bin] als Biär definiere sage "UPDATE tabelle SET [bin]= [dec]", führt das nicht ins Ziel. Wenn ich 37 übergebe, ist im Stringfeld ein % (weil 37 der ASCII-Code ist). Eine Konvertierungsfunktion, die hier hier ansetzen könnte, habe ich nicht gefunden.

FALLS DAS NICHT MÖGLICH IST, hier das zugrundeliegende Problem, vielleicht gibts ne andere Lösung.
Für eine Einsatzplanung gibt es Schichtmuster und dazugehörige Einsatztage (Zeitraum heute + 13 Tage.). Etwa so:

Code [Auswählen] Erweitern

Schichtmuster    Arbeitstage
Osternfrei       02.04.26
Osternfrei       07.04.26
Osternfrei       08.04.26
Osternfrei       09.04 26
Osternfrei       10.04.26
Osterhase        05.04.26
WoEnd            04.04.26
WoEnd            05.04.26
WoEnd            11.04.26
WoEnd            12.04.26
Ziel ist, mittels SQL für ein Schichtmuster einen String zu bekommen, dessen erster Wert heute, 2.4.26, ist (X=Arbeit, -=frei), also

Code [Auswählen] Erweitern

Osternfrei X----XXXX--
Osterhase  ---X-------
WoEnd      --XX-----XX
und so weiter.

Ich hatte schon die Idee, bei den Schichtmustern zu berechnen, wieviel Tage der Arbeitstag von heute entfernt ist (o bis 13), dann 2 hoch diese Zahl zu nehmen, so konnte ich die Schichtmuster gruppieren und die Exponentialzahlen addieren. Ich kriege aber keine Lösung, aus dieser summierten Dezimalzahl den dazugehörigen Binärstring zu machen

Vielen Dank fürs Lesen und Antworten und schonmal frohe Ostern

Hans-Jürgen

[hajott]

Hehe, manchmal muss man vielleicht das Problem auch nur aufschreiben, um selbst auf ne Idee zu kommen... ihr seid da sozusagen meine Therapiegruppe...

Also den Workaround könnte man (vereinfacht und nicht mathematisch ausgedrückt) so machen:

Code [Auswählen] Erweitern

BIN = IIF(DEC geprüft auf Teilerbedingung 2^0,'X','-') &
      IIF(DEC geprüft auf Teilerbedingung 2^1,'X','-') &
      IIF(DEC geprüft auf Teilerbedingung 2^2,'X','-') usw
Also man kriegts "hässlich" irgendwie hin. Bleibt die Frage: Gibt es auch eine elegante Lösung der Konvertierung?

[jens05]

Hallo,
schau mal ob dir dies weiterhilft

Code [Auswählen] Erweitern

Function DezimalNachBinaer(ByVal Zahl As Long) As String
    Dim Ergebnis As String

    If Zahl = 0 Then
        DezimalNachBinaer = "0"
        Exit Function
    End If

    Ergebnis = ""
    Do While Zahl > 0
        Ergebnis = (Zahl Mod 2) & Ergebnis
        Zahl = Zahl \ 2
    Loop

    DezimalNachBinaer = Ergebnis & String(14 - Len(Ergebnis), "0")

End Function

[hajott]

Hallo Jens,

vielen Dank, die Quelle hab ich auch gefunden. Es ist aber eine Funktion in VBA, die ich nicht in meine SQL-Abfrage implementieren kann (ich wüsste jedenfalls nicht, wie)

Viele Grüße

Hans-Jürgen

[jens05]

Hallo
wenn du in der Entwurfsansicht der Abfrage bist, gehe in eine neue Spalte und füge dort

BinCode: DezimalNachBinaer([NamedeinesZahlenfeldes])

Alternativ als SQL Sicht der Abfrage

Code [Auswählen] Erweitern

SELECT
    *,
    DezimalNachBinaer ([NamedeinesZahlenfeldes]) AS BinCode
FROM
    NameDeinerTabelle 

Die obigen Funktion "Function DezimalNachBinaer(ByVal Zahl As Long) As String" in ein Modul einfügen (Alt+F11), dann im Menü: Einfügen->Modul

[Knobbi38]

Hallo Hans-Jürgen,

du solltest dich dringend noch einmal mit den Datentypen beschäftigen. Dann sollte dir klar sein, dass der Datentyp Bit nur die Werte 0 und 1 aufnehmen kann, aber keinen Zahlenwert von 37.
Der Dezimaldatentyp ist ebenfalls für eine binäre Auswertung ungeeignet. Das sind Grundlagen!

Wenn du also binär etwas kodieren möchtest, bleibt dir der Datentyp Long, wobei das MSB das Vorzeichen repräsentiert, oder du kannst auch auf einen String ausweichen, dann können die Bits anders kodiert werden.

Eine Funktion für die Umwandlung einer Zahl in die Binärdarstellung gibt es in SQL nicht, das geht nur über den Umweg VBA. Was du machen kannst ist, deine Datenbank auf SQL-92 zu konvertieren. Dann werden binäre Operatoren wie BAND, BOR usw. unterstützt.

Beispiel:
Gegeben sei eine Tabelle mit dem Feld "Wert" (LONG INTEGER) mit den Datensätzen mit Wert := 0-15 .
Dann kannst du mit

Code [Auswählen] Erweitern

SELECT Wert, WERT BAND 2
FROM Tabelle1
WHERE (WERT BAND 2) = 2
alle Datensätze ermitteln, wo in WERT das BIT1 (BITS 0-15 von rechts nach links gezählt) gesetzt ist.

Jens hat dir ja schon aufgezeigt, wie man prinzipiell eine VBA Funktion in SQL verwenden kann, aber die angegebene  Funktion DezimalNachBinaer() ist in der Form nur bedingt zu gebrauchen, weil weder NULL noch das MSB-Bit eines Longs behandelt werden. Außerdem ist das nicht gerade die schnellste Methode, um so etwas in VBA zu implementieren. Aber es geht ja erstmal nur um das Prinzip - optimieren kann man später nochmal.

Unabhängig davon sollte man sich vielleicht mal überlegen, ob so für deine Problemstellung überhaupt eine Binärkodierung in der DB verwendet werden sollte, oder ob es da nicht doch noch eine andere Möglichkeit gibt. So ganz kann ich deiner Problemdarstellung noch nicht folgen.

Knobbi38

PS:
Hier ein Link für optimierte Umrechnungen von Long nach Binärdarstellung als String.  Die Versionen  LongToBit07() oder LongToBit08() von PeterNierop sind wahrscheinlich am besten geeignet. Für die Verwendung in SQL sollte aber noch die Behandlung von NULL Werten eingebaut werden:
http://www.xbeat.net/vbspeed/c_LongToBit.htm

[hajott]

Hallo Jens,

vielen Dank für deine Augen-öffnende Nachricht. Ich hab vor vielen Jahren mal gehört, dass man in VBA Funktionen definieren kann, die man dann aus SQL aufruft. Aber das hatte ich schlicht vergessen, weil ich bisher nie eine Funktion brauchte, die nicht schon serienmäßig eingebaut ist.

Dann: Sorry für meine Nachricht "Die Quelle hab ich auch gefunden". Ich dachte, es wäre der VBA-Code, den ich im Netz gesehen habe. Beim zweiten Hinsehen merkte ich dann - an der 14 - dass du den für mich schon angepasst hattest.

Falls es dich interessiert: Ich habe den Code an zweieinhalb Stellen angepasst:
- Durch den Abbruch bei Zahl = 0 ergeben sich unterschiedlich lange Strings, und das rechtsbündige Auffüllen ist da kontraproduktiv ;-) Ich zähle daher von 1 bis 14 und habe am Ende nur Nullen
- In der Zeile

Code [Auswählen] Erweitern

Ergebnis = (Zahl Mod 2) & Ergebnis habe ich die Reihenfolge der Operatoren getauscht, damit die kleinste Zahl links steht
- Den Teil mit Zahl = 0 konnte ich weglassen, dass ist durch die vorherigen Abfragen ausgeschlossen (=Schichten, die in den nächsten zwei Wochen keine Arbeitstage haben, sind gar nicht verzeichnet)

Ich habe noch meinen "Minimalismus-Filter" angewendet und die Funktion sieht jetzt so aus:

Code [Auswählen] Erweitern

Function DECTOBIN(dec As Long) As String
Dim count As Byte

    DECTOBIN = ""
    For count = 1 To 14
        DECTOBIN = DECTOBIN & dec MOD 2
        dec = dec \ 2
    Next
   
End Function
Ich hab das hier reinkopiert, falls jemand das anderweitig benötigt. Und das Beste: Es klappt einwandfrei!

Danke!

Hans-Jürgen

[hajott]

Hallo Knobbi,

die Grundlagen der Datentypen beherrsche ich eigentlich. Dass "Binär" nur eine 0 und 1 speichern kann, vergleichbar mit BOOLEAN, hätte ich mir bei längerem Nachdenken auch herleiten können, aber ich war verzweifelt auf Suche und griff danach als scheinbaren Strohhalm. Eigentlich logisch, denn wenn dieser Datentyp nicht über die Bedieneroberfläche definierbar ist sondern nur über VBA, wird das Gründe haben.

Die Funktion von Jens reicht für meine Zwecke mehr als aus, weil der übergebende Wert stabil eine Dezimalzahl zwischen 1 und 16.383 ist. Auch ein NULL muss nicht behandelt werden, die nicht zutreffenden Einträge wurden vorher schon herausgejoint.

Ob die Binärkodierung wirklich sonnvoll ist, ist eine wichtige Frage, die aber dahin gestellt bleiben kann, denn es soll genau so.

Dir auch herzlichen Dank

Hans-JÜrgen

[Bitsqueezer]

Hallo,

wie Ulrich schon sagte, die Funktion verwendet Long als Parameter, was für SQL-Aufrufe nicht gut ist, wenn ein Feld mal NULL ist. Daher am besten für solche Zwecke immer Variant (auch für Rückgabeparameter) verwenden, da hier auch NULL verwendet werden kann.

Wenn Du die Tage als Liste vorliegen hast (also jeweils ein Datensatz) und zusätzlich eine Kalendertabelle mit je Zeile ein Datum hast (und weiteren Spalten wie Jahr, Monat, Tag und weitere hilfreiche Datumswerte), dann kannst Du über MIN das kleinste vorhandene und MAX das größte vorhandene Datum aus Deinen Daten ermitteln und dann die Kalendertabelle verwenden, um alle Tage zwischen diesen Daten abzurufen.

Du hast dann also beispielsweise:

Code [Auswählen] Erweitern

Schichtmuster    Arbeitstage
Arbeitstag      01.04.26
Osternfrei      02.04.26
Osternfrei      03.04.26
Wochenende      04.04.26
Wochenende      05.04.26
Osternfrei      06.04.26
Osternfrei      07.04.26
Osternfrei      08.04.26
Arbeitstag      09.04.26
Arbeitstag      10.04.26
...
Wobei Dir die Kalendertabelle dazu auch gleich die Texte für "Schichtmuster" ermitteln kann.

Diese Tabelle kannst Du dann einfach für eine PIVOT-Abfrage benutzen, die aus Zeilen Spalten macht.
Wenn Du das variabel brauchst (weil Pivot-Abfragen normalerweise fest angegebene Spaltennamen brauchen), kannst Du das SQL in VBA zusammenstellen und an eine QueryDef übergeben und danach ausführen.
Mit einer weiteren Spalte, z.B. "IstFreierTag", die Du als Integer-Zahl der Größe Byte definierst, und in der Kalendertabelle angelegt wird, kannst Du diese als Wert für die Pivot-Abfrage verwenden. Da diese eine Aggregatabfrage braucht, kannst Du einfach MIN oder MAX verwenden, was keine Rolle spielt, welche, denn es gibt ohnehin immer nur einen Ergebniswert je Tag. Als Spalten kannst Du dann die Tage zwischen den Kalenderdaten verwenden, als Zeilen die Namen in "Schichtmuster" (gleichbedeutend mit "GROUP BY" quasi).

Theoretisch geht das so auch mit "X" und "-", wenn's sein muß und Du "IstFreierTag" als Text deklarierst. Allerdings hat die Zahl den Vorteil, daß Du sie auch in summierten Abfragen nutzen kannst, um etwa eine Abfrage zu machen, wieviele Arbeitstage zwischen Datum X und Y liegen.

Das Bitgefummel sieht für mich eher nach "Programmierer-Selbstbeschäftigung" aus und nicht nach einer sinnvollen Lösung.

Gruß

Christian

[hajott]

Hallo Christian,

auch dir vielen Dank. Das mit PIVOT werde ich mir mal ansehen.

Ich habe von euch echt noch viel gelernt!

Vielen Dank und frohe Ostern

Hans-Jürgem

[Knobbi38]

Hallo Hans-Jürgen,

dein Code-Minimalismus in allen Ehren, aber das führt zu nichts, denn Ziel ist es ja, erstmal eine Funktion lesbar und wiederverwendbar zu gestalten, damit man nach einiger Zeit mit Problemen konfrontiert wird. Hier ist es zunächst mal die Namenskonvention "dec" anstatt "lng...". "dec" wird sehr häufig als Präfix für Zahlen vom Datentyp Decimal verwendet, hier also mehr als irreführend. Dann fehlt die Angabe der Sichtbarkeit für die Funktion "Public". Schlimmer ist jedoch die Verwendung eines Bytewertes als Schleifenvariable, da dies in VBA nicht immer fehlerfrei funktioniert. Hier sollte man grundsätzlich einen Long Wert verwenden. Außerdem ist das Ergebnis falsch, was zumindest deutlich gekennzeichnet werden sollte!

Du bist auch schon mehrfach auf die Überprüfung von NULL hingewiesen worden – nicht ohne Grund. Die Helfer haben diesbezüglich sicherlich schon ihre Erfahrungen gemacht. Wenn die Abfrage dir "um die Ohren fliegt", erinnert sich später niemand mehr an die schlampig programmierte VBA-Funktion. Entschuldige, aber ich muss es einfach mal so deutlich sagen.

Knobbi38
 

[jens05]

Dann fehlt die Angabe der Sichtbarkeit für die Funktion "Public"
 

Wenn nicht explizit mit Public, Private oder Friend angegeben, lautet die Standardeinstellung für alle Function-Prozeduren ,,Public".

[Knobbi38]

@jens05

Sicherlich hast du damit recht, aber mir geht es um die Lesbarkeit des Codes. Da ist nicht sofort ersichtlich, dass eine Funktion ohne diese Deklaration öffentlich ist, eine mit DIM außerhalb einer Prozedur/Funktion deklarierte Variable jedoch nichtöffentlich. Die explizite Verwendung sollte hier für Klarheit und eine bessere Lesbarkeit sorgen.

Knobbi38