Finance | Project Operations, Human Resources, ...

Converting SQL query to SysDa Query in X++

(0) Share

Report

Posted on by Indra sena

Hi ,

Could someone please suggest how we can convert below SQL query to SysDa query.

select * from TAXTRANS_IN left join
HSNCODETABLE_IN on TAXTRANS_IN.HSNCODETABLE = HSNCODETABLE_IN.RECID
left join
SERVICEACCOUNTINGCODETABLE_IN on TAXTRANS_IN.SERVICEACCOUNTINGCODETABLE = SERVICEACCOUNTINGCODETABLE_IN.RECID

Thanks in advance.

All responses (2)

Answers (0)

Indra sena 12 on at

Like (0)

Report

Converting SQL query to SysDa Query in X++

Thanks for the reply Mohamed,

I've created the query as you've specified and found that there is an issue in it.

The second joinClause seems to be overwriting the previous joinclause , refer the below query string after executing it.

FROM TaxTrans_IN
JOIN FROM ServiceAccountingCodeTable_IN
WHERE (TaxTrans_IN.ServiceAccountingCodeTable == ServiceAccountingCodeTable_IN.RecId)

Could you suggest how we can overcome this ?
Suggested answer

Mohamed Amine Mahmoudi 21,751 Super User 2025 Season 1 on at

Like (0)

Report
Converting sql query to SysDa Query in x++

Hi,

try this code :

TAXTRANS_IN taxTans; HSNCODETABLE_IN hsnCodeTable; SERVICEACCOUNTINGCODETABLE_IN serviceAccCodeTable; SysDaQueryObject qTaxTrans = new SysDaQueryObject(taxTans); SysDaQueryObject qHsnCodeTable = new SysDaQueryObject(hsnCodeTable); SysDaQueryObject qserviceAccCodeTable = new SysDaQueryObject(serviceAccCodeTable); qHsnCodeTable.whereClause(new SysDaEqualsExpression (new SysDaFieldExpression(taxTans, fieldStr(TAXTRANS_IN, HSNCodeTable)), new SysDaFieldExpression(hsnCodeTable, fieldStr(HSNCODETABLE_IN, RecId)))); qserviceAccCodeTable.whereClause(new SysDaEqualsExpression (new SysDaFieldExpression(taxTans, fieldStr(TAXTRANS_IN, ServiceAccountingCodeTable)), new SysDaFieldExpression(serviceAccCodeTable, fieldStr(SERVICEACCOUNTINGCODETABLE_IN, RecId)))); qTaxTrans.joinClause(SysDaJoinKind::InnerJoin, qHsnCodeTable); qTaxTrans.joinClause(SysDaJoinKind::InnerJoin, qserviceAccCodeTable);

Best regards,

Mohamed Amine MAHMOUDI