Skip to main content
Solved

Rewrite IsNull BQL in FBQL


aaghaei
Captain II
Forum|alt.badge.img+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

 

 

 

Best answer by aaghaei

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.

View original
Did this topic help you find an answer to your question?

4 replies

aaghaei
Captain II
Forum|alt.badge.img+9
  • Author
  • Captain II
  • 1169 replies
  • Answer
  • February 21, 2023

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.


Yuriy Zaletskyy
Jr Varsity I
Forum|alt.badge.img+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.


aaghaei
Captain II
Forum|alt.badge.img+9
  • Author
  • Captain II
  • 1169 replies
  • February 21, 2023

@Yuriy Zaletskyy 

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


Forum|alt.badge.img+5
  • Captain II
  • 460 replies
  • February 21, 2023

The Framework Development Guide mentions the following:

Op1.IfNullThen<Op2>

Op1.NullIf<Op2>

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

 


Reply


Cookie policy

We use cookies to enhance and personalize your experience. If you accept you agree to our full cookie policy. Learn more about our cookies.

 
Cookie settings