Solved

Rewrite IsNull BQL in FBQL

  • 21 February 2023
  • 4 replies
  • 195 views

Userlevel 7
Badge +9

Hello Everyone,

I am working on enhancing some of my older projects and simultaneously I am updating my codes from BQL to FBQL for better readability and using some new functions of FBQL. I have come across a piece that I have used IsNull<,>. I am wondering how can I use IsNull in FBQL.

Below is the piece of code I have converted to FBQL (the BQL version was working fine) I tried using “Use<>” before “IsNull<>” to mix use BQL with FBQL but didn’t work either.

                HCLPMCostProjectionView costProjection = SelectFrom<HCLPMCostProjectionView>
.Where<Brackets<HCLPMCostProjectionView.projectID.IsEqual<@P.AsInt>>
.And<Brackets<HCLPMCostProjectionView.taskID.IsNull.Or<HCLPMCostProjectionView.taskID.IsEqual<IsNull<@P.AsInt, HCLPMCostProjectionView.taskID>>>>
.And<Brackets<HCLPMCostProjectionView.costCodeID.IsNull.Or<HCLPMCostProjectionView.costCodeID.IsEqual<IsNull<@P.AsInt, HCLPMCostProjectionView.costCodeID>>>>
.And<Brackets<HCLPMCostProjectionView.accountGroupID.IsNull.Or<HCLPMCostProjectionView.accountGroupID.IsEqual<IsNull<@P.AsInt, HCLPMCostProjectionView.accountGroupID>>>>
.And<Brackets<HCLPMCostProjectionView.inventoryID.IsNull.Or<HCLPMCostProjectionView.inventoryID.IsEqual<IsNull<@P.AsInt, HCLPMCostProjectionView.inventoryID>>>>
.And<Brackets<HCLPMCostProjectionView.tranPeriodID.IsLessEqual<@P.AsString>>
.And<Brackets<HCLPMCostProjectionView.tranDate.IsLessEqual<@P.AsDateTime>>>>>>>>>
.View.Select(Base, line.ProjectID, taskID, costCodeID, accountGroupID, inventoryID, documentExt.UsrHCLTranPeriodID, document.Date).FirstOrDefault();

 

and error snip

 

 

 

icon

Best answer by aaghaei 21 February 2023, 09:25

View original

4 replies

Userlevel 7
Badge +9

UPDATE

Instead of “IsNull<@P.AsInt, HCLPMCostProjectionView.taskID>”

I used “@P.AsInt.IfNullThen<HCLPMCostProjectionView.taskID>”

And worked but I am not sure if it is the best practice or if there is an alternative to IsNull other than what I did. SQL Query seems fine though.

Userlevel 5
Badge +3

Just a suggestion to keep in mind.

In T-SQL, as well as in BQL and FBQL, Null is synonym to uknown.

It means, that if you compare zero with NULL, or zero with unknown, result of comparison will be unknown.

If you compare string with unknown, result will be unknown.

Userlevel 7
Badge +9

@Yuriy Zaletskyy 

Noted. Thanks. So, what do we have as IsNull<> equivalent in FBQL?

Userlevel 7
Badge +5

The Framework Development Guide mentions the following:

Op1.IfNullThen<Op2>

Op1.NullIf<Op2>

Edit: I see that you’re already using IfNullThen.  :)

 

Reply


About Acumatica ERP system
Acumatica Cloud ERP provides the best business management solution for transforming your company to thrive in the new digital economy. Built on a future-proof platform with open architecture for rapid integrations, scalability, and ease of use, Acumatica delivers unparalleled value to small and midmarket organizations. Connected Business. Delivered.
© 2008 — 2024  Acumatica, Inc. All rights reserved