CODEWITHSUNDEEP
t-sqlaxaptax++
neuser
neuser

Reputation: 107

Dynamics AX 2009: Or Exist Join in QueryBuildDataSource


I am want to use OR EXISTS clause in QueryBuildDataSource but i dont know how to use it.
I prepare sql query:

select * from LedgerJournalTable l
where
( 
   l.createdBy = '...' and l.WorkflowStatus != 99 
)
or exists 
( 
   select top 1 'x' from WorkflowWorkItemTable w 
   where l.RecId    = w.RefRecId 
   AND w.REFTABLEID = 211 
   and w.USERID     = '....'
)

and i try to build ax query 

    queryDS = LedgerJournalTable_ds.query().dataSourceTable(tableNum(LedgerJournalTable));

    ledgerStatusFilter  = queryDS.addRange( fieldnum(LedgerJournalTable,WorkflowStatus) );
    ledgerUserFilter    = queryDS.addRange( fieldnum(LedgerJournalTable,createdBy) );

    ledgerStatusFilter.value( "!=99" );
    ledgerUserFilter.value( "..." );

    qbdsWorkItemtTable  = queryDS.addDataSource( tablenum(WorkflowWorkItemTable) );
    qbdsWorkItemtTable.joinMode( JoinMode::NoExistsJoin );
    qbdsWorkItemtTable.addLink( fieldnum(LedgerJournalTable,RecId), fieldnum(WorkflowWorkItemTable,RefRecId) );
    qbdsWorkItemtTable.addRange( fieldnum(WorkflowWorkItemTable,RefTableId) ).value( strfmt("%1",tablenum(LedgerJournalTable)));

    workflowUser        = qbdsWorkItemtTable.addRange( fieldnum( WorkflowWorkItemTable, UserId ) );

    workflowUser.value("...");

    info( strfmt("%1", queryDS.toString()  ) );

sory for my bad English :c

Upvotes: 0

Views: 2059

Answers (1)

Jan B. Kjeldsen
Jan B. Kjeldsen

Reputation: 18051

Sorry, but AX queries do not support or exists.

Also see this similar question.

Upvotes: 2

Related Questions