The above sql statement is only returning false. There are a few results where this should return true and doesn’t. What am I doing wrong?
1 Like
The kit flag isn’t P or the shipCmpl isn’t 0…
Can you show those field in the BAQ next to the calculated field to verify that’s what it “Should be”.
I don’t see anything wrong with your field, so it’s probably something in the data.
1 Like
I was thinking the same thing. I just double checked and kit parents do get marked ship complete… I was thinking maybe they don’t get the checkmark because they are not the parts that shipped. But I can confirm that it does.
1 Like
I should also note that it is definitely the Ship Cmpl = 0 that is causing the problem because removing that part does make it work. Ship Cmpl is a boolean type. It doesn’t let me type “false” so 0 is supposed to represent false.
This the result row that is still returning false
So for those lines, Kit = “C” and Complete = 1. So that doesn’t satisfy the condition.
1 Like
Sorry, I didn’t mean to make my circle that big. There is one row where it equals P and ShipCmpl is false that the calculated field should return true but doesn’t
Ok, so false in a check box could also mean Null. So you’ll have to check for that too.
2 Likes
I didn’t know that, but changing from 0 to is null worked.
Thank you @Banderson and @dr_dan for your help!
1 Like
Don’t forget, You’ll have to do an “Or” in there so you can both of the possible cases.
when OderDtl1.KitFlag = 'P' and (ShipDtl.ShipCmpl = 0 or ShipDtl.ShipCmpl = null) then 1
4 Likes
On that being SQL I believe it should read:
when OderDtl1.KitFlag = 'P' and (ShipDtl.ShipCmpl = 0 or ShipDtl.ShipCmpl IS NULL) then 1
3 Likes
ISNULL is a favorite of mine any time I don’t know for certain a field isn’t specified as NOT NULL or DEFAULT at the database.
ISNULL(ShipDtl.ShipCmpl, 0) will fail over to 0 if the field is null, and it’s a quick CYA boilerplate if there’s any question about the possibility of null values.
1 Like
if i remember correctly, you could put this into your calculated field instead of the WHEN/Else)… using an in-line if statement and combining the isnull statement.
(OderDtl1.KitFlag = 'P' and isnull(ShipDtl.ShipCmpl,0) , 1,0)
1 Like