Getting Null for dates

[Anonymous]

Hi 

I have a job to extract data from MS SQL Server.

The source for my tMSSqlInput:

declare @AgeDate date
set @AgeDate =  DATEADD(day, DATEDIFF(day, 0, GETDATE()), 0) 

SELECT      'EDGECS' as Company, 'Edge Consulting' as CompanyDescrp, Client.Account AS Account, 
                         Client.Name as Company,
   _efnAgedPostARBalancesSumExtra_1.AccountLink,
  _efnAgedPostARBalancesSumExtra_1.Age1 as '>180 Days',
  _efnAgedPostARBalancesSumExtra_1.Age2 as '150 Days',
  _efnAgedPostARBalancesSumExtra_1.Age3 as '120 Days', 
                         _efnAgedPostARBalancesSumExtra_1.Age4  as '90 Days',
  _efnAgedPostARBalancesSumExtra_1.Age5 as '60 Days',
  _efnAgedPostARBalancesSumExtra_1.Age6 as '30 Days',
  _efnAgedPostARBalancesSumExtra_1.Age7 as 'Current', 
                         _efnAgedPostARBalancesSumExtra_1.AccountBalance,
  _efnAgedPostARBalancesSumExtra_1.UADebits, _efnAgedPostARBalancesSumExtra_1.UACredits,
  _efnAgedPostARBalancesSumExtra_1.FCAge1, 
                         _efnAgedPostARBalancesSumExtra_1.FCAge2,
  _efnAgedPostARBalancesSumExtra_1.FCAge3, _efnAgedPostARBalancesSumExtra_1.FCAge4,
  _efnAgedPostARBalancesSumExtra_1.FCAge5, 
                         _efnAgedPostARBalancesSumExtra_1.FCAge6,
  _efnAgedPostARBalancesSumExtra_1.FCAge7, _efnAgedPostARBalancesSumExtra_1.AccountBalanceForeign,
  
                         _efnAgedPostARBalancesSumExtra_1.UADebitsForeign,
  _efnAgedPostARBalancesSumExtra_1.UACreditsForeign,  
                         _efnAgedPostARBalancesSumExtra_1.LastDebitDate,
  _efnAgedPostARBalancesSumExtra_1.LastDebitAmount,
  _efnAgedPostARBalancesSumExtra_1.LastDebitAmountForeign, 
                         _efnAgedPostARBalancesSumExtra_1.LastCreditDate,
  _efnAgedPostARBalancesSumExtra_1.LastCreditAmount,
  _efnAgedPostARBalancesSumExtra_1.LastCreditAmountForeign, @AgeDate as AgeDate
                                           
FROM            dbo._efnAgedPostARBalancesSumExtra(@AgeDate,
  NULL) AS _efnAgedPostARBalancesSumExtra_1 INNER JOIN
                         Client ON _efnAgedPostARBalancesSumExtra_1.AccountLink = Client.DCLink

However when running the job I get null in the date values. Running the above SQL on the SQL studio does return dates

The date elements are defined in tMAP as date

Is it because I am using a declare in my SQL? Any ideas?

Thank you

[Anonymous]

The Declare shouldn't have done that. I just double checked and it hasn't caused me any problems in 6.2.1. Can you show us your job design and can you confirm what your schema looks like?

[Anonymous]

Here is the job

Could it be due to to using tXMLMap and not tMap?

I am going to try and re-create the Map and see then only on the dates

On run only the date fields are null

[Anonymous]

tMap

DB Table script

USE [FinReporting]
GO

/****** Object:  Table [dbo].[AgeAnalysis]    Script Date: 4/5/2018 10:03:26 AM ******/
DROP TABLE [dbo].[AgeAnalysis]
GO

/****** Object:  Table [dbo].[AgeAnalysis]    Script Date: 4/5/2018 10:03:26 AM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[AgeAnalysis](
	[Company] [char](10) NOT NULL,
	[CompanyDescrp] [char](50) NOT NULL,
	[Account] [varchar](10) NOT NULL,
	[AccountName] [varchar](100) NOT NULL,
	[OneEightyDays] [numeric](18, 0) NOT NULL,
	[OneFiftyDays] [numeric](18, 0) NOT NULL,
	[OneTwentyDays] [numeric](18, 0) NOT NULL,
	[NinetyDays] [numeric](18, 0) NOT NULL,
	[SixtyDays] [numeric](18, 0) NOT NULL,
	[ThirtyDays] [numeric](18, 0) NOT NULL,
	[Current] [numeric](18, 0) NOT NULL,
	[AccountBalance] [numeric](18, 0) NOT NULL,
	[UADebits] [numeric](18, 0) NOT NULL,
	[UACredits] [numeric](18, 0) NOT NULL,
	[AccountBalanceForeign] [numeric](18, 0) NOT NULL,
	[UADebitsForeign] [numeric](18, 0) NOT NULL,
	[UACreditsForeign] [numeric](18, 0) NOT NULL,
	[LastDebitDate] [date] NULL,
	[LastDebitAmount] [numeric](18, 0) NOT NULL,
	[LastDebitAmountForeign] [numeric](18, 0) NOT NULL,
	[LastCreditDate] [date] NULL,
	[LastCreditAmount] [numeric](18, 0) NOT NULL,
	[LastCreditAmountForeign] [numeric](18, 0) NOT NULL,
	[AgeDate] [date] NULL
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

[Anonymous]

tMap

DB Script

USE [FinReporting]
GO

/****** Object:  Table [dbo].[AgeAnalysis]    Script Date: 4/5/2018 10:03:26 AM ******/
DROP TABLE [dbo].[AgeAnalysis]
GO

/****** Object:  Table [dbo].[AgeAnalysis]    Script Date: 4/5/2018 10:03:26 AM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[AgeAnalysis](
	[Company] [char](10) NOT NULL,
	[CompanyDescrp] [char](50) NOT NULL,
	[Account] [varchar](10) NOT NULL,
	[AccountName] [varchar](100) NOT NULL,
	[OneEightyDays] [numeric](18, 0) NOT NULL,
	[OneFiftyDays] [numeric](18, 0) NOT NULL,
	[OneTwentyDays] [numeric](18, 0) NOT NULL,
	[NinetyDays] [numeric](18, 0) NOT NULL,
	[SixtyDays] [numeric](18, 0) NOT NULL,
	[ThirtyDays] [numeric](18, 0) NOT NULL,
	[Current] [numeric](18, 0) NOT NULL,
	[AccountBalance] [numeric](18, 0) NOT NULL,
	[UADebits] [numeric](18, 0) NOT NULL,
	[UACredits] [numeric](18, 0) NOT NULL,
	[AccountBalanceForeign] [numeric](18, 0) NOT NULL,
	[UADebitsForeign] [numeric](18, 0) NOT NULL,
	[UACreditsForeign] [numeric](18, 0) NOT NULL,
	[LastDebitDate] [date] NULL,
	[LastDebitAmount] [numeric](18, 0) NOT NULL,
	[LastDebitAmountForeign] [numeric](18, 0) NOT NULL,
	[LastCreditDate] [date] NULL,
	[LastCreditAmount] [numeric](18, 0) NOT NULL,
	[LastCreditAmountForeign] [numeric](18, 0) NOT NULL,
	[AgeDate] [date] NULL
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

[Anonymous]

Even after recreating the tMap I still get null values on the dates. tMap See attached
schema.PNG

[Anonymous]

Just connect your Input to a tlogrow component and run it. See what you get

[Anonymous · Accepted solution]

I've just noticed that your query does not match your schema. The column names are either different or out of order. You must ensure that your column names match and are in the same order to be sure that it is not an issue here.

[Anonymous]

Thanks, yes it was a naming convention issue. I changed/added the alias in the sql to reflect the input name in the tMSSqlInput and it worked - thanks allot. Sequence is not enough only naming needs to match