Problem with Exists()

[#1]

I'm loading a dimension (GL account) with the following script:

PostingAccounts:
LOAD AcctCode AS SysCode,
AcctName,
FormatCode,
applymap('Stmt_SectionMap', left("FormatCode",1),'blank section') AS "Fin Stmt Section",
applymap('StmtMap', left("FormatCode",1),'blank') AS "Fin Stmt",
"Segment_0" AS GLCode,
"Segment_1" AS ParkCode,
"Segment_2" AS StateCode,
applymap('ParkMap',"Segment_1") As ParkName,
applymap('StateMap',"Segment_2") As StateName,
Fathernum AS ParentCode;
SQL SELECT AcctCode,
AcctName,
FormatCode,
"Segment_0",
"Segment_1",
"Segment_2",
Fathernum
FROM "DHP_SAP".dbo.OACT WHERE "DHP_SAP".dbo.OACT.Postable = 'Y' AND "DHP_SAP".dbo.OACT.FormatCode < '900' ;

I'm then loading GL transactions in the following script, but I only want to load them where the GL account (ie SysCode) already exists.

FinTrans:
LOAD Account AS SysCode,
BatchNum,
Debit - Credit AS Actual$,
"Line_ID",
LineMemo,
Project,
Ref1 ,
Ref2,
'Actuals' AS Set,
RefDate AS PostingDate,
MonthStart(RefDate)AS PeriodLookupDate,
ShortName,
Applymap('TransTypeMap',"TransType",'No Trans Type') AS TransType,
TransId,
ContraAct;
SQL SELECT *
FROM "DHP_SAP".dbo.JDT1 WHERE Exists(SysCode)
AND ("DHP_SAP".dbo.JDT1.RefDate >= '2009-07-01') AND ("DHP_SAP".dbo.JDT1.TransType > 0);

When I run the script I get the following error:

SQL Error:[Microsoft][ODBC SQL Server Driver][SQL Server]Incorrect syntax near 'SysCode'.
SQL Scriptline:
SQL State:37000
SQL SELECT *
FROM "DHP_SAP".dbo.JDT1 WHERE Exists(SysCode)
AND ("DHP_SAP".dbo.JDT1.RefDate >= '2009-07-01') AND ("DHP_SAP".dbo.JDT1.TransType > 0)

What am I doing wrong?

Thanks

Derek

[disqr_rm]

Try:

FinTrans:
LOAD Account AS SysCode,
BatchNum,
Debit - Credit AS Actual$,
"Line_ID",
LineMemo,
Project,
Ref1 ,
Ref2,
'Actuals' AS Set,
RefDate AS PostingDate,
MonthStart(RefDate)AS PeriodLookupDate,
ShortName,
Applymap('TransTypeMap',"TransType",'No Trans Type') AS TransType,
TransId,
ContraAct
WHERE EXISTS (Account, SysCode);
SQL SELECT *
FROM "DHP_SAP".dbo.JDT1
WHERE ("DHP_SAP".dbo.JDT1.RefDate >= '2009-07-01') AND ("DHP_SAP".dbo.JDT1.TransType > 0);

Reason: EXISTS is not an SQL command so it cannot be specified with the SQL portion in LOAD statement.

By the way, did you try SAP Connector? The new version is pretty nice. Connecting at SQL level is fine, but I have experienced many problems in long run. Just a thought for you.

[#3]

Hi Derek,

Use Exists(SysCode,Account) instead of Exists(SysCode) and try.

Regards,

Sridhar Chitikela