Analytics Software

Hi,

I want query SQL Period Column for the month based on the FDM PoV, can or has anyone a sample script of how this is can be achieved?

My script when run comes back with "No records to load" - msg in my script. Let me know if you can spot anything obvious that's causing this in my script.

SQL table

EVENT    YEAR     Period      Entity       Ccy         Acc          ICP         Value     Product

Dim objSS   'ADODB.Connection
Dim strSQL    'SQL string
Dim strSelectPer 'FDM Period
Dim strCurFDMYear'FDM Year
Dim rs        'SQL Recordset
Dim rsAppend   'FDM tTB table append rs object
Dim recordCount
Dim sWhere
Dim sSelect

'Initialize objects
Set cnSS = CreateObject("ADODB.Connection")
Set rs = CreateObject("ADODB.Recordset")
Set rsAppend = DW.DataAccess.farsTable(strWorkTableName)

'Get Current POV Period And Year And determine HFM Select Year
strSelectPer = Left((RES.PstrPer), 3)
strCurFDMYear = Right(RES.PstrPer, 4)
Select Case UCase(strSelectPer)
Case "APR", "MAY", "JUN", "JUL", "AUG", "SEP", "OCT", "NOV", "DEC"
  strSelectYear = strCurFDMYear + 1
Case "JAN", "FEB", "MAR"
     strSelectYear = strCurFDMYear
    
End Select

'***************************************
'Watch with this as it can cause looping
'***************************************
On Error Resume Next
Err.clear

'Connect to SQL database
'cnSS.Open "Driver=SQL Server;Server=EHEINTRADCG\EHEINTRADCG;Database=dw_foundation;UID=hypdb"
cnSS.Open "Driver=SQL Server;Server=EHEINTRADCG\EHEINTRADCG;Database=ODI_WORK_MARS;UID=hypdb"
'Connect to SQL Server database
cnSS.CommandTimeout = 1200

'Keep the error message handling in for testing but will probably need  to write
'to a log if running in an overnight batch
'*******************************************************************************   
'Error Handling
If  Err.Number <> 0  Then  
'  An exception occurred
RES.PlngActionType = 2
RES.PstrActionValue =  Err.Description & Chr(10) & "Unable to connect to SQL Data Source"
Err.clear
Set cnSS = Nothing
FinAllLocs_Conv = False
Exit Function
End If

'Create query String

strSQL = strSQL & "From ODI_WORK_MARS.dbo.TMP_HFM_DATA_EXTRACT_TIN1 "
strSQL = sWhere & " And YearID =  '" & strSelectYear & "'  And PeriodID = '" & strSelectPer & "'"

'Get data
rs.Open strSQL, cnSS

'    Check For data
If rs.bof And rs.eof Then
  RES.PlngActionType = 2
         RES.PstrActionValue = "No Records to load!"

  Exit Function
End If
'   RecordCount = 0
    'Loop through records and append to FDM tTB table in location's DB
If Not rs.bof And Not rs.eof Then
  Do While Not rs.eof

'Create the record 
  rsAppend.AddNew
   rsAppend.Fields("PartitionKey") = RES.PlngLocKey         ' Location ID
   rsAppend.Fields("CatKey") = RES.PlngCatKey          'Current Category ID
   rsAppend.Fields("PeriodKey") = RES.PdtePerKey         'Current Period ID
      rsAppend.Fields("DataView") = "YTD"            'Data View ID
   rsAppend.Fields("CalcAcctType") = 9            'Input data indicator
   rsAppend.Fields("Entity") = rs.fields("Entity").Value        ' Entity/Genpo ID
   rsAppend.Fields("Account")= rs.fields("Account").Value        'Account ID
      rsAppend.Fields("ICP") = rs.fields("Inter_Company_Entity_HFM").Value   ' Inter-Co/Destination
   rsAppend.Fields("Amount") = rs.fields("Value").Value        ' Data Value ID
                          
  rsAppend.Update
  RecordCount = Recordcount + 1
  rs.movenext
  Loop
End If

'Records loaded
RES.PlngActionType = 2
    RES.PstrActionValue = "SQL Import successful!   "  & RecordCount

'Assign Return value
SAP_HFM = True

End Function