Seven Stars

Getting Null for dates

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

FinDashNullDate.PNG

The date elements are defined in tMAP as date

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

 

Thank you

1 ACCEPTED SOLUTION

Accepted Solutions
Fifteen Stars

Re: Getting Null for dates

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.

Rilhia Solutions
8 REPLIES
Fifteen Stars

Re: Getting Null for dates

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?

Rilhia Solutions
Seven Stars

Re: Getting Null for dates

Here is the job

extractJob.PNG

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

 

Seven Stars

Re: Getting Null for dates

tMap

schema.PNG

 

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


Seven Stars

Re: Getting Null for dates

tMap

schema.PNG

 

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


Seven Stars

Re: Getting Null for dates

Even after recreating the tMap I still get null values on the dates. tMap See attached
Fifteen Stars

Re: Getting Null for dates

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

Rilhia Solutions
Fifteen Stars

Re: Getting Null for dates

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.

Rilhia Solutions
Seven Stars

Re: Getting Null for dates

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