gbn-ws2
Goto Top

Array in Scalarfunktion in SQL (UDF)

Die nachfolgende Funktion soll als als Scalarfunktion (UDF) im SQL Managmentstudio eingebaut werden, um diese gleich in einem SQL-Statement für eine Berechnung zu nutzen. Leider komme ich schon am Beginn der Funktion zu Problemen, da ich kein Array definieren kann. Weiß hier jemand weiter? Oder gibt es dafür keine Lösung?

Public Function Farbtemperatur(x, y) As Decimal
Dim rt(30) As Double
Dim u(30) As Double
Dim v(30) As Double
Dim T(30) As Double
Dim us As Double
Dim vs As Double
Dim p As Double
Dim di As Double
Dim dm As Double
Dim Z As Double
Dim i As Integer

rt(0) = 1.0E-20
rt(1) = 0.00001
rt(2) = 0.00002
rt(3) = 0.00003
rt(4) = 0.00004
rt(5) = 0.00005
rt(6) = 0.00006
rt(7) = 0.00007
rt(8) = 0.00008
rt(9) = 0.00009
rt(10) = 0.0001
rt(11) = 0.000125
rt(12) = 0.00015
rt(13) = 0.000175
rt(14) = 0.0002
rt(15) = 0.000225
rt(16) = 0.00025
rt(17) = 0.000275
rt(18) = 0.0003
rt(19) = 0.000325
rt(20) = 0.00035
rt(21) = 0.000375
rt(22) = 0.0004
rt(23) = 0.000425
rt(24) = 0.00045
rt(25) = 0.000475
rt(26) = 0.0005
rt(27) = 0.000525
rt(28) = 0.00055
rt(29) = 0.000575
rt(30) = 0.0006

u(0) = 0.18006
u(1) = 0.18066
u(2) = 0.18133
u(3) = 0.18208
u(4) = 0.18293
u(5) = 0.18388
u(6) = 0.18494
u(7) = 0.18611
u(8) = 0.1874
u(9) = 0.1888
u(10) = 0.19032
u(11) = 0.19462
u(12) = 0.19962
u(13) = 0.20525
u(14) = 0.21142
u(15) = 0.21807
u(16) = 0.22511
u(17) = 0.23247
u(18) = 0.2401
u(19) = 0.24792
u(20) = 0.25591
u(21) = 0.264
u(22) = 0.27218
u(23) = 0.28039
u(24) = 0.28863
u(25) = 0.29685
u(26) = 0.30505
u(27) = 0.3132
u(28) = 0.32129
u(29) = 0.32931
u(30) = 0.33724

v(0) = 0.26352
v(1) = 0.26589
v(2) = 0.26846
v(3) = 0.27119
v(4) = 0.27407
v(5) = 0.27709
v(6) = 0.28021
v(7) = 0.28342
v(8) = 0.28668
v(9) = 0.28997
v(10) = 0.29326
v(11) = 0.30141
v(12) = 0.30921
v(13) = 0.31647
v(14) = 0.32312
v(15) = 0.32909
v(16) = 0.33439
v(17) = 0.33904
v(18) = 0.34308
v(19) = 0.34655
v(20) = 0.34951
v(21) = 0.352
v(22) = 0.35407
v(23) = 0.35577
v(24) = 0.35714
v(25) = 0.35823
v(26) = 0.35907
v(27) = 0.35968
v(28) = 0.36011
v(29) = 0.36038
v(30) = 0.36051

T(0) = -0.24341
T(1) = -0.25479
T(2) = -0.26876
T(3) = -0.28539
T(4) = -0.3047
T(5) = -0.32675
T(6) = -0.35156
T(7) = -0.37915
T(8) = -0.40955
T(9) = -0.44278
T(10) = -0.47888
T(11) = -0.58204
T(12) = -0.70471
T(13) = -0.84901
T(14) = -1.0182
T(15) = -1.2168
T(16) = -1.4512
T(17) = -1.7298
T(18) = -2.0637
T(19) = -2.4681
T(20) = -2.9641
T(21) = -3.5814
T(22) = -4.3633
T(23) = -5.3762
T(24) = -6.7262
T(25) = -8.5955
T(26) = -11.324
T(27) = -15.628
T(28) = -23.325
T(29) = -40.77
T(30) = -116.45

Z = 1 - x - y
If (x < 1.0E-20) And (y < 1.0E-20) And (Z < 1.0E-20) = True Then
Farbtemperatur = -1
Else
us = (4.0# * x) / (x + 15.0# * y + 3.0# * Z)
vs = (6.0# * y) / (x + 15.0# * y + 3.0# * Z)
dm = 0#
For i = 0 To 30
di = (vs - v(i)) - T(i) * (us - u(i))
If ((i > 0) And (((di < 0#) And (dm >= 0#)) Or ((di >= 0#) And (dm < 0#)))) Then
Exit For ' found lines bounding (us, vs) : i-1 and i
End If
dm = di
Next i
If (i = 31) Then
Farbtemperatur = -1
Else
di = di / Math.Sqrt(1.0# + T(i) * T(i))
dm = dm / Math.Sqrt(1.0# + T(i - 1) * T(i - 1))
'di = di / Math.Sqr(1.0# + T(i) * T(i))
'dm = dm / Sqr(1.0# + T(i - 1) * T(i - 1))
p = dm / (dm - di) ' p = interpolation parameter, 0.0 : i-1, 1.0 : i
p = 1 / ((rt(i) - rt(i - 1)) * p + rt(i - 1))
Farbtemperatur = Math.Round(CDec((p)), 1)
End If
End If
End Function

Content-Key: 667583

Url: https://administrator.de/contentid/667583

Ausgedruckt am: 28.03.2024 um 21:03 Uhr

Mitglied: em-pie
em-pie 14.06.2021 um 15:39:33 Uhr
Goto Top
Moin,

Punkt 1: NIMM DIE CODE-TAGS ZUR HAND. Das kann doch keiner hier ordentlich lesen. So schwer ist das doch nicht!

Punkt 2: erstelle dir für jedes Array eine kleine Tabelle:
https://www.sqlshack.com/implement-array-like-functionality-sql-server/
Oder baue das als Spalten ein:
ROW | rt      | u       | v       | T
1   | 1.0E-20 | 0.18006 | 0.26352 | -0.24341
... |     ... |     ... |     ... |     ... 
30  |  0.0006 | 0.33724 | 0.36051 | -116.45

Gruß
em-pie