complex DATE query ax2012

Finance and Operations/AX Developer Forum
1

hi all

the below query is compiling with out any error but during the execution its affecting the result.

is my query correct??

qbds.addRange(fieldNum(VendPackingSlipJour,DeliveryDate)).value(strFmt("<= %1 - %2 ",systemDateGet(),fieldStr(VendTable, UCAWaitingPeriodforInvoicing)));

suggestion pls.

2

That it compiles correctly means nothing - X++ compiler isn’t able to check your SQL snippets in strings.

I don’t believe that your code constructs valid SQL statement - see the resulting query by calling toString(). Even if it worked as you intended, comparing time span with DeliveryDate makes little sense to me.

3

hi martin,

thanks for your reply,

i called toString(), the query executing upto <=%1 , its not performing the subtraction(-) process.

is there any way to perform the subtraction ???

fieldStr(VendTable, UCAWaitingPeriodforInvoicing) is integer field → eg 30

i want to subtract the value from today date → 09-12-2014 -30 = 09-11-2014

so i want to select the delivery date which all are lesser than the 09-11-2014.

thanks in advance.

4

Aha, I didn’t realize that UCAWaitingPeriodforInvoicing is an integer field. Now the comparison makes sense.
Before going into implementation details (such as how to correctly refer to multiple data sources), could you tell us more about your requirements?

I see you’re trying to filter VendPackingSlipJour by a value calculated from data in another table, VendTable. Can’t you calculated the value before the query for VendPackingSlipJour? Or how should the whole query look like?

5

hi martin,

thanks for ur reply,

classes → purchjournalselectinvoice → buildquery method.

my exact query is

qbds = this.journalHeader_ds().query().dataSourceTable(tablenum(VendPackingSlipJour));

this.buildRangesSelectedOrders(qbds);

qbds = qbds.addDataSource(tablenum(VendPackingSlipTrans));

qbds.relations(true);

qbds.joinMode(JoinMode::ExistsJoin);

qbds.addRange(fieldnum(VendPackingSlipTrans,FullyMatched)).value(queryValue(NoYes::No));

qbds = this.qbdsAll();

if (qbds)

{

qbds = qbds.addDataSource(tablenum(VendPackingSlipTrans));

qbds.relations(true);

qbds.joinMode(JoinMode::ExistsJoin);

qbds.addRange(fieldnum(VendPackingSlipTrans,FullyMatched)).value(queryValue(NoYes::No));

//start: to filter the datas based on UCAApcode → Arun

if( formRun.args().menuItemName() == menuitemDisplayStr(UCAPurchJournalSelectPOR_PackingSlip))

{

qbds = qbds.addDataSource(tablenum(PurchTable));

//qbds = qbds.addDataSource(tablenum(VendPackingSlipJour));

qbds.relations(true);

qbds.joinMode(JoinMode::ExistsJoin);

qbds = qbds.addDataSource(tablenum(VendTable));

qbds.relations(true);

qbds.joinMode(JoinMode::ExistsJoin);

qbds.addRange(fieldnum(VendTable,UCAApcode)).value(queryValue(NoYes::Yes));

//qbds.addRange(fieldNum(VendPackingSlipJour,DeliveryDate)).value(queryValue(strFmt("< %1 - %2",systemDateGet(),fieldStr(VendTable, UCAWaitingPeriodforInvoicing))));

//queryBuildRange.value(strFmt(’((%1 == %2) || ((%1 == %3) && (%4 == “%5”)))’,fieldStr(InventTable, ItemType),

//any2int(ItemType::Service),any2int(ItemType::Item),fieldStr(InventTable, ProjCategoryId),queryValue(“Spares”)));

qbds = qbds.addDataSource(tablenum(VendPackingSlipJour));

qbds.relations(true);

qbds.joinMode(JoinMode::ExistsJoin);

qbds.addRange(fieldNum(VendPackingSlipJour,DeliveryDate)).value(strFmt(" > %1 - %2", systemDateGet(),fieldNum(VendTable, UCAWaitingPeriodforInvoicing)));

6

Isn’t this a sufficient self-containing example demonstrating your problem (without unnecessary clutter)?

Query query = new Query();
QueryBuildDataSource vendTableDs = query.addDataSource(tablenum(VendTable));
QueryBuildDataSource jourDs = vendTableDs.addDataSource(tablenum(VendPackingSlipJour));
QueryBuildRange range;
    
jourDs.relations(true);
jourDs.joinMode(JoinMode::ExistsJoin);
    
range = jourDs.addRange(fieldNum(VendPackingSlipJour, DeliveryDate));
range.value(strFmt(" > %1 - %2", systemDateGet(), fieldNum(VendTable, UCAWaitingPeriodforInvoicing)));
    
info(vendTableDs.toString());

Then you can easily debug it - and others may make some sense of your code.

This is the result so far:

SELECT * FROM VendTable(VendTable_1)
EXISTS JOIN * FROM VendPackingSlipJour(VendPackingSlipJour_1)
WHERE VendTable.AccountNum = VendPackingSlipJour.OrderAccount
AND ((DeliveryDate>{ts '2014-12-09 00:00:00.000'}))