SQL Declarations in Data Adapter

Michel_Sabourin
Contributor II

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

1 ACCEPTED SOLUTION

Michel_Sabourin
Contributor II

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,CXEFT

The 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 If

Return 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

 

 

View solution in original post

7 REPLIES 7

TerryLedet
New Contributor III

 

Michael

Copied and pasted your quote to SSMS which worked after fixing the quotes.

TerryLedet_1-1637781870401.png

 

Copied and pasted the SSMS code to a Data Adapter which returned 121181

TerryLedet_2-1637781870543.png

 

TerryLedet_3-1637781870527.png

 

I suspect the code has a syntax error with one or more quotes which I cannot identify due to HTML changing the quote syntax.

Terry

Curiouser and curiouser…

When I run it as an application SQL, it works. the minute I add it to the external query, it fails. must have something to do with the way it’s set up for the connector. I’ll keep digging. Thanks for the help!

Definitely curious. I tested with an external application after your reply and the execution was the same result i.e. returned 121181. I have had an occurrence where external database connections with ODBC did not work but with OLE DB it did. The code I wrote was similar to what you posted and what we ended up with was one external db connection configured as ODBC and another external db connection configured as OLE DB with OLE DB in the name.

scottr
New Contributor III

I’m assuming the application database is SQL Server. Is the JDE database also SQL Server? different versions of SQL Server?

also, I’ve experienced a SQL syntax issue in an external data adapter that went away once I put the SQL in a business rule for the Method Type. Just an idea for troubleshooting…

Yeah. I know I can get it to work for my business rule, but for my purposes, i’m trying to build a review dashboard before doing the actual import. I’ve put a ticket into OneStream to see if they have any insights. The client’s application is hosted by them, and they have the keys to the SQL connection

i’ve experienced JDE on SQL Server, Oracle database, and DB2, which is why I was asking. while your SQL works for SQL Server, it isn’t valid for Oracle.

Michel_Sabourin
Contributor II

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&gt;= 'XFBR(MFC_FX_Date_Converter, MonthStart,TimeMe=[|!MFC_FX_ML_Select_Time!|])'
AND CXEFT&lt;= 'XFBR(MFC_FX_Date_Converter, MonthEnd,TimeMe=[|!MFC_FX_ML_Select_Time!|])'
ORDER BY
CXCRCD,CXEFT

The 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 If

Return 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