how to query field date is null or not

(0) Share

Report

Posted on by Community Member

Hello guys,

In select query X++, how we filter date field which is empty ? Like for example, in CustTrans there is field Closed. and this is date type of field,right?

Thanks,

I have the same question (0)

All responses (15)

Answers (1)

Suggested answer

nmaenpaa 101,160 Moderator on at

Like (0)

Report
RE: how to query field date is null or not

if (!custTrans.Closed) { info("Date field is empty."); }

You can also use dateNull() on Date variables and utcDateTimeNull() on UTCDateTime variables.

Was this reply helpful? Yes No
Blue Wang on at

Like (0)

Report
RE: how to query field date is null or not

Hi WWwong,

Yes, the type of the Closed field of the CustTrans table is datetime.

If you want to set a date field to an empty date or need to compare if a date field is empty, you can use the dateNull() function. It is a method of the Global class and will simply return 01\01\1900.

Just write code like this:

if (custtrans.closed == datenull()) { }

In addition, if you use QueryBuildDataSource, then code like this:

dbds.addRange(fieldNum(CustTrans, Closed)).value(queryValue(dateNull());

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

RE: how to query field date is null or not

Hi Nikolaos and Blue,

Thanks,

As suggested, so I'm using it in my query, however there is an error saying the parameter is incorrect. Unfortunately I cannot find what's wrong, and the error is pointed to that dateNull() (there is a red wave line underneath "datenull()" word, at the last bracket)

Can I have an assist ?

This is the select query :

while select CustTable where CustTable.AccountNum == CustAccount

join CustInvJour where CustInvJour.InvoiceAccount == CustTable.AccountNum && DateTimeUtil::getSystemDate(DateTimeUtil::getUserPreferredTimeZone()) - CustInvJour.InvoiceDate >= (custCollectionLetterLine.NumOfDays)

&& CustInvJour.InvoiceAmountMST >= limitBalance

join CustTrans where CustTrans.Invoice == CustInvJour.InvoiceId

&& CustTrans.AccountNum == CustInvJour.InvoiceAccount

&& CustTrans.TransDate == CustInvJour.InvoiceDate

&& CustTrans.Voucher == CustInvJour.LedgerVoucher

&& CustTrans.Closed == dateNull()

All the necessary variable has been declared and the value is getting correctly. So is it select statement structure it self ? However I found nothing's wrong.

Thanks in advance.

rgds,

Was this reply helpful? Yes No
Suggested answer

nmaenpaa 101,160 Moderator on at

Like (0)

Report
RE: how to query field date is null or not

Well, first of all you should remove everything else from your statement to make sure that something else is not causing you this error.

So, let's start with the simplest possible statement:

select custTrans where custTrans.closed == dateNull();

For me this code compiles without errors. So your problem must be something else than this.

If you need help in spotting the error, please share your full code (at least this method) and a screenshot where we can see the error message.

Was this reply helpful? Yes No
Blue Wang on at

Like (0)

Report
RE: how to query field date is null or not

Hi WWwong,

Try this:

utcDateTime dt = DateTimeUtil::newDateTime(dateNull(), 0); while select custtrans where ...... && closed == dt { }

Was this reply helpful? Yes No
Suggested answer

nmaenpaa 101,160 Moderator on at

Like (0)

Report

RE: how to query field date is null or not

Ah, while I was typing, you shared a screenshot. So you get a runtime error, not a compiler error. The Exception just shows the statement that causes the exception, not specific part in that statement. So it can be anything in your statement.

I created a runnable class that contains only the code from my previous message, and it runs without errors.

So, your problem is somewhere else than "how to query field date is null or not".

Perhaps your problem is in the part that handles the nr of days in the beginning of your statement.

You can find it out easily by concentrating on one thing at a time and testing small pieces of code.

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

RE: how to query field date is null or not

Hi Nikolaos,

This "Select query" actually at the top of my code, not really the 1st, but before that is only the retrieval of the variables from Contract class, since it is a service class from a batch job, and a looping procedure.

However this is the screen shot :

The "black blocking" is just addition of condition by using my custom fields and I can assure you it is not the problem, the variable has values and data type is correct.

I changed the datenull to use !Closed as suggested, but the error persist.

I'm

Was this reply helpful? Yes No
Suggested answer

nmaenpaa 101,160 Moderator on at

Like (0)

Report

RE: how to query field date is null or not

I already said that it's not about the dateNull(), it's something else in your query. You can easily verify it yourself, just like I did and described in my earlier messages.

I can only repeat myself:

"Well, first of all you should remove everything else from your statement to make sure that something else is not causing you this error."

"You can find it out easily by concentrating on one thing at a time and testing small pieces of code."

Please let us know once you find what causes the error. Then we can try to figure out how to implement it in a way that doesn't cause errors.

Was this reply helpful? Yes No
Blue Wang on at

Like (0)

Report

RE: how to query field date is null or not

Hi WWwong,

Please remove function from where statements. DateTimeUtil.

Handle before while select.

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

RE: how to query field date is null or not

Hi Nikolaos,

Yes, it's only bcos you 're mentioned to share the method so I did, while on going search for the cause... :)

Anyway, yes, I found the cause, as Blue just mentioned as well, it is because the DateTimeUtil.

But I did change it just now, by preserved the GetSystemDate earlier like this :

TransDate CurrentDate = DateTimeUtil::date(DateTimeUtil::getSystemDate(DateTimeUtil::getUserPreferredTimeZone()));

while select CustTable where CustTable.AccountNum == CustAccount

join CustInvJour where CustInvJour.InvoiceAccount == CustTable.AccountNum

// && currentDate - CustInvJour.InvoiceDate >= (custCollectionLetterLine.NumOfDays)

&& CustInvJour.InvoiceAmountMST >= limitBalance

join CustTrans where CustTrans.Invoice == CustInvJour.InvoiceId

&& CustTrans.AccountNum == CustInvJour.InvoiceAccount

&& CustTrans.TransDate == CustInvJour.InvoiceDate

&& CustTrans.Voucher == CustInvJour.LedgerVoucher

&& !CustTrans.Closed

{

}

But unfortunately after using the variable, the error still persist. Currently I remark it, so the error gone. but of course I still need that date calculation.

Thanks.

Was this reply helpful? Yes No