SQL Declarations in Data Adapter
Source: Migrated from Champions
Hello all,
I’m creating a Data Adapter that is using SQL to poll an external database. I can get it to work when I’m just supplying the strings for dates. However, I wanted to get fancy and be able to parse the effective dates using variables. When I go to an online SQL tester (https://www.w3schools.com/sql/trysqlserver.asp?filename=trysql_func_sqlserver_left), I can get the below code to return ‘121181’, which is the Julian date I need in querying JDE. But the OS SQL Query returns an error saying ‘int’ is an invalid token. does anyone know if there’s a way to use variables in this way?
Declare @YearRun int
Declare @YearPart int
Declare @MonthRun int
Declare @Global varchar(100)
Declare @GetTime datetime
Declare @RunTime varchar(100)
Set @Global = ‘2021M6’
set @YearRun = LEFT(@Global, 4)
Set @YearPart = Right(@YearRun,2)
set @MonthRun = Right(@Global, Len(@Global)-CHARINDEX(‘M’,@Global))
Set @GetTime = EOMONTH(CONCAT(@MonthRun, ‘/’, 1, ‘/’, @YearRun))
Set @RunTime = CONCAT(‘1’,@yearpart, DATENAME(dayofyear , @gettime))
Select @RunTime
In the end, I had to do some research about JDE's SQL in particular. I was able to get it to work with a few minor adjustments. In the end, I decided to handle the date conversion using a couple of XFBR rules, which greatly simplified the SQL.
SELECT
CXCRCD as "CURR FROM",
CXCRDC as "CURR TO",
Round(CXCRR,5) as Multiplier,
Round(CXCRRD,5) as Divisor
from
PRODDTA.F0015
where
CXCRDC = 'USD'
and CXEFT>= 'XFBR(MFC_FX_Date_Converter, MonthStart,TimeMe=[|!MFC_FX_ML_Select_Time!|])'
AND CXEFT<= 'XFBR(MFC_FX_Date_Converter, MonthEnd,TimeMe=[|!MFC_FX_ML_Select_Time!|])'
ORDER BY
CXCRCD,CXEFTThe XFBR looks like this:
Public Function Main(ByVal si As SessionInfo, ByVal globals As BRGlobals, ByVal api As Object, ByVal args As DashboardStringFunctionArgs) As Object
Try
'Parameter Example: XFBR(MFC_FX_Date_Converter, MonthStart,TimeMe=[TimeSelector])
If args.FunctionName.XFEqualsIgnoreCase("MonthStart") Then
Dim TimeMe As String = args.NameValuePairs("TimeMe")
Dim StartTime As String = Me.Month_Start(si,TimeMe)
Return StartTime
End If
'Parameter Example: XFBR(MFC_FX_Date_Converter, MonthEnd,TimeMe=[TimeSelector])
If args.FunctionName.XFEqualsIgnoreCase("MonthEnd") Then
Dim TimeMe As String = args.NameValuePairs("TimeMe")
Dim EndTime As String = Me.Month_End(si,TimeMe)
Return EndTime
End IfReturn Nothing
Catch ex As Exception
Throw ErrorHandler.LogWrite(si, New XFException(si, ex))
End Try
End Function
Public Function Month_Start(ByVal si As SessionInfo, ByVal TimeMe As String )
Try
Dim TimeLength As Integer = TimeMe.Length
Dim MosCount As Integer
If TimeLength = 7 Then MosCount = 2 Else MosCount = 1
Dim YearRun As Integer = LEFT(TimeMe, 4)
Dim YearPart As Integer = YearRun.ToString.Substring(YearRun.ToString.Length - 2)
Dim MonthRun As Integer = TimeMe.Substring(5,MosCount)
Dim EndTime As String = Nothing
Dim Dayz As Integer = Nothing
Dim iDayNumber As Integer = Nothing
Dim booIsLeapYear As Boolean
booIsLeapYear = ((YearRun Mod 4 = 0) And (YearRun Mod 100 <> 0)) Or (YearRun Mod 400 = 0)
Dim dt As Date = MonthRun & "/1/" & YearRun
If debugSwitch = True Then brapi.ErrorLog.LogMessage(si, "TimeMe= " & TimeMe & " YearRun= " & YearRun & " YearPart= " & YearPart & " dt= " & dt & " iDayNumber= " & iDayNumber)
iDayNumber = DateDiff("d", CDate("1/1/" & Year(dt)), dt) + 1
EndTime = "1" & YearPart & iDayNumber
Return EndTime
Return Nothing
Catch ex As Exception
Throw ErrorHandler.LogWrite(si, New XFException(si, ex))
End Try
End Function
Public Function Month_End(ByVal si As SessionInfo, ByVal TimeMe As String )
Try
Dim TimeLength As Integer = TimeMe.Length
Dim MosCount As Integer
If TimeLength = 7 Then MosCount = 2 Else MosCount = 1
Dim YearRun As Integer = LEFT(TimeMe, 4)
Dim YearPart As Integer = YearRun.ToString.Substring(YearRun.ToString.Length - 2)
Dim MonthRun As Integer = TimeMe.Substring(5,MosCount)
Dim EndTime As String = Nothing
Dim Dayz As Integer = Nothing
Dim iDayNumber As Integer = Nothing
Dim booIsLeapYear As Boolean
booIsLeapYear = ((YearRun Mod 4 = 0) And (YearRun Mod 100 <> 0)) Or (YearRun Mod 400 = 0)
Select Case MonthRun
Case 1, 3, 5, 7, 8, 10, 12
Dayz = 31
Case 2
If booIsLeapYear = True Then
Dayz = 29
Else
Dayz = 28
End If
Case Else
Dayz = 30
End Select
Dim dt As Date = MonthRun & "/" & Dayz & "/" & YearRun
If debugSwitch = True Then brapi.ErrorLog.LogMessage(si, "TimeMe= " & TimeMe & " YearRun= " & YearRun & " YearPart= " & YearPart & " dt= " & dt & " iDayNumber= " & iDayNumber)
iDayNumber = DateDiff("d", CDate("1/1/" & Year(dt)), dt) + 1
EndTime = "1" & YearPart & iDayNumber
Return EndTime
Return Nothing
Catch ex As Exception
Throw ErrorHandler.LogWrite(si, New XFException(si, ex))
End Try
End Function
