AND BAL.Last_Update_Date >= '')

Report Inappropriate Content · ‎2016-06-29

Hi,

Implemented the incremental load. Data is not fetching the updated records in report.

Fact Table:

LET vSQLSection = '
SELECT
BAL.CODE_COMBINATION_ID,
LED.LEDGER_ID,
LED.NAME LEDGER_NAME,
BAL.Last_Update_Date,
(CASE WHEN led.ledger_category_code = ' & chr(39) & 'PRIMARY' & chr(39) & ' then ' & chr(39) & 'Primary' & chr(39) & ' else ' & chr(39) & 'Reporting' & chr(39) & ' end) Ledger_type,
PERIOD_NAME,
NVL (bal.begin_balance_dr, 0) - NVL (bal.begin_balance_cr, 0) Begin_USD,
(nvl(bal.period_net_dr,0) - nvl(bal.period_net_cr,0)) NET_USD,
NVL (bal.begin_balance_dr, 0) - NVL (bal.begin_balance_cr, 0)
+ NVL(bal.period_net_dr, 0) - NVL (bal.period_net_cr, 0) END_USD
FROM
GL_BALANCES BAL,
GL_LEDGERS LED
where 1=1
AND LED.LEDGER_ID = BAL.LEDGER_ID
AND LED.LEDGER_CATEGORY_CODE IN(' & chr(39) & 'PRIMARY' & chr(39) & ',' & chr(39) & 'ALC' & chr(39) & ')
AND BAL.TRANSLATED_FLAG IS NULL
--and bal.period_name =' & chr(39) & 'JAN-16' & chr(39) & '
--AND BAL.LEDGER_ID =2235 --consolidate ledger
and BAL.CURRENCY_CODE =' & chr(39) & 'USD' & chr(39) & '
and BAL.ACTUAL_FLAG =' & chr(39) & 'A' & chr(39) & '
';
SET vTableName = 'GL_Balances';

LET vUniqueKey = 'LEDGER_ID & CODE_COMBINATION_ID & PERIOD_NAME';
SET vOutputTableName = 'Fact - GL Balances';
SET vLastUpdateFieldName = 'BAL.Last_Update_Date';

Call IncrementalLoad ('$(vTableName)', '$(vSQLSection)', '$(vUniqueKey)','$(vOutputTableName)','$(vLastUpdateFieldName)') ;

Below is the logic implemented

sub IncrementalLoad (vTableName, vSQLSection, vUniqueKey, vOutputTableName, vLastUpdateField)

Trace('$(vDataFolder)$(vOutputTableName).qvd');

Let v_Incremental_qvd = isNull(QvdCreateTime('$(vDataFolder)$(vOutputTableName).qvd'));

If (v_Incremental_qvd = -1) then

Trace(Beginning Full Load for $(vTableName));

LET v_SQL = '$(vSQLSection)';

$(vTableName):

Load

*,

$(vUniqueKey) as key_Unique;

SQL $(v_SQL);

RENAME field LAST_UPDATE_DATE to Last_Update_Date;

ELSE

Trace(Beginning Incremental Load for $(vTableName) on $(vSite));

// get dates

Dates:

Load distinct Last_Update_Date

from $(vDataFolder)$(vOutputTableName).qvd (QVD);

//exit script;

GetMaxDate:

Load Max(Last_Update_Date) as MaxRecordDate

Resident Dates;

LET v_MaxRecordDate = date(Peek('MaxRecordDate', 0, 'GetMaxDate'));

Drop Tables GetMaxDate, Dates;

LET v_SQL = '$(vSQLSection) AND $(vLastUpdateField) >= '& chr(39) & v_MaxRecordDate & chr(39);

Trace($(v_SQL));

temp:

NoConcatenate

Load

*,

$(vUniqueKey) as key_Unique;

SQL $(v_SQL);

$(vTableName):

NoConcatenate

LOAD *

from $(vDataFolder)$(vOutputTableName).qvd (QVD)

where Not Exists(key_Unique)

;

Concatenate

Load * RESIDENT temp;

Drop Table temp;

ENDIF

Store $(vTableName) into $(vDataFolder)$(vOutputTableName).qvd;

Drop Table $(vTableName);

end sub

After reload we observed in where class last_update_date checking ' ' instead of latest date

SET vTableName = 'GL_Balances'

LET vUniqueKey = 'LEDGER_ID & CODE_COMBINATION_ID & PERIOD_NAME'

SET vOutputTableName = 'Fact - GL Balances'

SET vLastUpdateFieldName = 'BAL.Last_Update_Date'

Call IncrementalLoad ('GL_Balances', '

SELECT

BAL.CODE_COMBINATION_ID,

LED.LEDGER_ID,

LED.NAME LEDGER_NAME,

BAL.Last_Update_Date,

(CASE WHEN led.ledger_category_code = ''PRIMARY'' then ''Primary'' else ''Reporting'' end) Ledger_type,

PERIOD_NAME,

NVL (bal.begin_balance_dr, 0) - NVL (bal.begin_balance_cr, 0) Begin_USD,

(nvl(bal.period_net_dr,0) - nvl(bal.period_net_cr,0)) NET_USD,

NVL (bal.begin_balance_dr, 0) - NVL (bal.begin_balance_cr, 0)

+ NVL(bal.period_net_dr, 0) - NVL (bal.period_net_cr, 0) END_USD

FROM

GL.GL_BALANCES BAL,

GL.GL_LEDGERS LED

where 1=1

AND LED.LEDGER_ID = BAL.LEDGER_ID

AND LED.LEDGER_CATEGORY_CODE IN(''PRIMARY'',''ALC'')

AND BAL.TRANSLATED_FLAG IS NULL

--and bal.period_name =''JAN-16''

--AND BAL.LEDGER_ID =2235 --consolidate ledger

and BAL.CURRENCY_CODE =''USD''

and BAL.ACTUAL_FLAG =''A''

', 'LEDGER_ID & CODE_COMBINATION_ID & PERIOD_NAME','Fact - GL Balances','BAL.Last_Update_Date')

Trace('D:\QVServer\FinancePOC\QVD\Fact - GL Balances.qvd')

('D:\QVServer\FinancePOC\QVD\Fact - GL Balances.qvd')

Let v_Incremental_qvd = isNull(QvdCreateTime('D:\QVServer\FinancePOC\QVD\Fact - GL Balances.qvd'))

If (v_Incremental_qvd = -1) then

Trace(Beginning Incremental Load for GL_Balances on )

(Beginning Incremental Load for GL_Balances on )

Dates:

Load distinct Last_Update_Date

from D:\QVServer\FinancePOC\QVD\Fact - GL Balances.qvd (QVD)

1 fields found: Last_Update_Date,

1 lines fetched

GetMaxDate:

Load Max(Last_Update_Date) as MaxRecordDate

Resident Dates

1 fields found: MaxRecordDate,

1 lines fetched

LET v_MaxRecordDate = date(Peek('MaxRecordDate', 0, 'GetMaxDate'),'DD-MMM-YYYY')

Drop Tables GetMaxDate, Dates

LET v_SQL = '

SELECT

BAL.CODE_COMBINATION_ID,

LED.LEDGER_ID,

LED.NAME LEDGER_NAME,

BAL.Last_Update_Date,

(CASE WHEN led.ledger_category_code = ''PRIMARY'' then ''Primary'' else ''Reporting'' end) Ledger_type,

PERIOD_NAME,

NVL (bal.begin_balance_dr, 0) - NVL (bal.begin_balance_cr, 0) Begin_USD,

(nvl(bal.period_net_dr,0) - nvl(bal.period_net_cr,0)) NET_USD,

NVL (bal.begin_balance_dr, 0) - NVL (bal.begin_balance_cr, 0)

+ NVL(bal.period_net_dr, 0) - NVL (bal.period_net_cr, 0) END_USD

FROM

GL.GL_BALANCES BAL,

GL.GL_LEDGERS LED

where 1=1

AND LED.LEDGER_ID = BAL.LEDGER_ID

AND LED.LEDGER_CATEGORY_CODE IN(''PRIMARY'',''ALC'')

AND BAL.TRANSLATED_FLAG IS NULL

--and bal.period_name =''JAN-16''

--AND BAL.LEDGER_ID =2235 --consolidate ledger

and BAL.CURRENCY_CODE =''USD''

and BAL.ACTUAL_FLAG =''A''

AND BAL.Last_Update_Date >= '& chr(39) & v_MaxRecordDate & chr(39)

Trace(

SELECT

BAL.CODE_COMBINATION_ID,

LED.LEDGER_ID,

LED.NAME LEDGER_NAME,

BAL.Last_Update_Date,

(CASE WHEN led.ledger_category_code = 'PRIMARY' then 'Primary' else 'Reporting' end) Ledger_type,

PERIOD_NAME,

NVL (bal.begin_balance_dr, 0) - NVL (bal.begin_balance_cr, 0) Begin_USD,

(nvl(bal.period_net_dr,0) - nvl(bal.period_net_cr,0)) NET_USD,

NVL (bal.begin_balance_dr, 0) - NVL (bal.begin_balance_cr, 0)

+ NVL(bal.period_net_dr, 0) - NVL (bal.period_net_cr, 0) END_USD

FROM

GL.GL_BALANCES BAL,

GL.GL_LEDGERS LED

where 1=1

AND LED.LEDGER_ID = BAL.LEDGER_ID

AND LED.LEDGER_CATEGORY_CODE IN('PRIMARY','ALC')

AND BAL.TRANSLATED_FLAG IS NULL

--and bal.period_name ='JAN-16'

--AND BAL.LEDGER_ID =2235 --consolidate ledger

and BAL.CURRENCY_CODE ='USD'

and BAL.ACTUAL_FLAG ='A'

AND BAL.Last_Update_Date >= '')

(

SELECT

BAL.CODE_COMBINATION_ID,

LED.LEDGER_ID,

LED.NAME LEDGER_NAME,

BAL.Last_Update_Date,

(CASE WHEN led.ledger_category_code = 'PRIMARY' then 'Primary' else 'Reporting' end) Ledger_type,

PERIOD_NAME,

NVL (bal.begin_balance_dr, 0) - NVL (bal.begin_balance_cr, 0) Begin_USD,

(nvl(bal.period_net_dr,0) - nvl(bal.period_net_cr,0)) NET_USD,

NVL (bal.begin_balance_dr, 0) - NVL (bal.begin_balance_cr, 0)

+ NVL(bal.period_net_dr, 0) - NVL (bal.period_net_cr, 0) END_USD

FROM

GL.GL_BALANCES BAL,

GL.GL_LEDGERS LED

where 1=1

AND LED.LEDGER_ID = BAL.LEDGER_ID

AND LED.LEDGER_CATEGORY_CODE IN('PRIMARY','ALC')

AND BAL.TRANSLATED_FLAG IS NULL

--and bal.period_name ='JAN-16'

--AND BAL.LEDGER_ID =2235 --consolidate ledger

and BAL.CURRENCY_CODE ='USD'

and BAL.ACTUAL_FLAG ='A'

AND BAL.Last_Update_Date >= '')

temp:

NoConcatenate

Load

*,

LEDGER_ID & CODE_COMBINATION_ID & PERIOD_NAME as key_Unique

SQL

SELECT

BAL.CODE_COMBINATION_ID,

LED.LEDGER_ID,

LED.NAME LEDGER_NAME,

BAL.Last_Update_Date,

(CASE WHEN led.ledger_category_code = 'PRIMARY' then 'Primary' else 'Reporting' end) Ledger_type,

PERIOD_NAME,

NVL (bal.begin_balance_dr, 0) - NVL (bal.begin_balance_cr, 0) Begin_USD,

(nvl(bal.period_net_dr,0) - nvl(bal.period_net_cr,0)) NET_USD,

NVL (bal.begin_balance_dr, 0) - NVL (bal.begin_balance_cr, 0)

+ NVL(bal.period_net_dr, 0) - NVL (bal.period_net_cr, 0) END_USD

FROM

GL.GL_BALANCES BAL,

GL.GL_LEDGERS LED

where 1=1

AND LED.LEDGER_ID = BAL.LEDGER_ID

AND LED.LEDGER_CATEGORY_CODE IN('PRIMARY','ALC')

AND BAL.TRANSLATED_FLAG IS NULL

--and bal.period_name ='JAN-16'

--AND BAL.LEDGER_ID =2235 --consolidate ledger

and BAL.CURRENCY_CODE ='USD'

and BAL.ACTUAL_FLAG ='A'

AND BAL.Last_Update_Date >= ''

10 fields found: CODE_COMBINATION_ID, LEDGER_ID, LEDGER_NAME, LAST_UPDATE_DATE, LEDGER_TYPE, PERIOD_NAME, BEGIN_USD, NET_USD, END_USD, key_Unique,

0 lines fetched

GL_Balances:

NoConcatenate

LOAD *

from D:\QVServer\FinancePOC\QVD\Fact - GL Balances.qvd (QVD)

where Not Exists(key_Unique)

11 fields found: CODE_COMBINATION_ID, LEDGER_ID, LEDGER_NAME, Last_Update_Date, LEDGER_TYPE, PERIOD_NAME, BEGIN_USD, NET_USD, END_USD, key_Unique, LAST_UPDATE_DATE,

10 lines fetched

Concatenate

Load * RESIDENT temp

10 fields found: CODE_COMBINATION_ID, LEDGER_ID, LEDGER_NAME, LAST_UPDATE_DATE, LEDGER_TYPE, PERIOD_NAME, BEGIN_USD, NET_USD, END_USD, key_Unique,

10 lines fetched

Drop Table temp

ENDIF

Store GL_Balances into D:\QVServer\FinancePOC\QVD\Fact - GL Balances.qvd

Drop Table GL_Balances

end sub

Could you please give an idea how to fix the incremental load.

Incrementaal load not success

AND BAL.Last_Update_Date >= '')

AND BAL.Last_Update_Date >= '')