Trouble with the Lookup...

[#1]

This just ain't working ... I'm getting NULLs in the result.

/*

CREATE TABLE dbo.HadoopIngestionTableCounts

(TableName  VARCHAR(128) NOT NULL,

CountRecs  BIGINT NOT NULL,

DateIngestion  Datetime NOT NULL,

InsertTS    Datetime NOT NULL

);

*/

LET vTodayStr = Date(Today());

LET vYesterdayStr = Date(Today() - 1);

LastRunStatistics:

SQL SELECT CountRecs FROM dbo.HadoopIngestionTableCounts WHERE TableName IN ('customers', 'suppliers') AND DateIngestion = '$(vYesterdayStr)';

LET vNumRowsMemDemoLast = Lookup('CountRecs', 'TableName', 'customers', 'LastRunStatistics');

LET vNumRowsMembershipLast = Lookup('CountRecs', 'TableName', 'suppliers', 'LastRunStatistics');

[Anil_Babu_Samineni]

Variables should placed before Load / Select statement only..

[antoniotiman · Accepted solution]

Hi Romi,

where is 'TableName' Field in SQL Select Statement ?

[#4]

Ha ... Thanks so much for pointing out the silly error. It's working all good now. Changed script is as follows...

/*

CREATE TABLE dbo.HadoopIngestionTableCounts

(TableName  VARCHAR(128) NOT NULL,

CountRecs  BIGINT NOT NULL,

DateIngestion  Datetime NOT NULL,

InsertTS    Datetime NOT NULL

);

*/

LET vTodayStr = Date(Today());

LET vYesterdayStr = Date(Today() - 1);

LastRunStatistics:

SQL SELECT TableName, CountRecs FROM dbo.HadoopIngestionTableCounts WHERE TableName IN ('customers', 'suppliers') AND DateIngestion = '$(vYesterdayStr)';

LET vNumRowsMemDemoLast = Lookup('CountRecs', 'TableName', 'customers', 'LastRunStatistics');

LET vNumRowsMembershipLast = Lookup('CountRecs', 'TableName', 'suppliers', 'LastRunStatistics');