I have a situation where I need to test for a “blank” date field and cannot get a returned value.
It seems easier to explain in this < 2 min video than typing a long winded explanation.
Essentially if my max(Trans Date) from the subquery is ____ then I want to use this other date where “____” from the subquery is not returning any of the following: ‘’, ’ ', or Null.
(note I also tested for ‘1/1/1900’)
Thanks in advance for any advice.
Can we see the BAQ?
Sure:
select
[Part].[PartNum] as [Part_PartNum],
[Part].[PartDescription] as [Part_PartDescription],
[Part].[LastSigV8_c] as [Part_LastSigV8_c],
[LastSigTrans].[Calculated_LastTranDate] as [Calculated_LastTranDate],
(case LastSigTrans.Calculated_LastTranDate
when ‘1/1/1900’ then 1
else 0
end) as [Calculated_booleancheck]
from Erp.PartBin as PartBin
inner join Erp.Part as Part on
Part.Company = PartBin.Company
and Part.PartNum = PartBin.PartNum
and ( Part.InActive = 0 )
left outer join (select
[PartTran].[PartNum] as [PartTran_PartNum],
(max(PartTran.TranDate)) as [Calculated_LastTranDate]
from Erp.PartTran as PartTran
where (PartTran.TranType in (‘MFG-CUS’, ‘MFG-STK’, ‘MFG-WIP’, ‘PUR-INS’, ‘PUR-MTL’, ‘PUR-STK’, ‘PUR-SUB’, ‘STK-ASM’, ‘STK-CUS’, ‘STK-MTL’, ‘WIP-MFG’))
group by [PartTran].[PartNum]) as LastSigTrans on
Part.PartNum = LastSigTrans.PartTran_PartNum
where (PartBin.OnhandQty > 0 and PartBin.PartNum = ‘81DCDR100DA’)
group by [Part].[PartNum],
[Part].[PartDescription],
[Part].[LastSigV8_c],
[LastSigTrans].[Calculated_LastTranDate]
Note, the BAQ sample was the last check of ‘1/1/1900’
Something isn’t right with that case statement. Does that pass syntax check?
I see that PartTran.TranDate is stored with a 2 digit year. Try using the PartTran.SysDate to filter instead. I tried to duplicate the issue on my end, but all of our part transactions have dates in that field. You could also try casting your PartTran.TranDate as another kind of variable and then output it directly to see what value might be stored there.
It does.
Unfortunately, doesn’t recognize the “blank” field as null.
Where do you take date “blank” field? it is usually null in db
Huh, learned something new. I didn’t know you could organize a case statement that way.
Agreed. Here is an example showing the BAQ test for null and the output not recognizing the null (Boolean should be true).
look at the link I posted above. You cannot write When Null then…
SQL uses a special IS NULL syntax to compare values to null. You will need to change your Case statement to:
case when LastSigTrans.Calculated_LastTranDate is null then 1 else 0 end
I think I see the issue. The subquery isn’t pulling any records. You’d think that the “LastTranDate” field would be Null, but it is at another level of nonexistence.
Ok. I reread what you shared and understand the “non-error” generating issue with syntax. Thank you.
Started with the IS NULL, but the BAQ Editor doesn’t accept it.
I find it rarely useful in practice, though. Can’t tell you how many times I start with one like that, get my hopes up for some clean code, and then end up with some horrific hierarchy of and clauses instead.
Like the one use case I have is to do a day(dateadd(...)) and see if it falls on a weekend and then adjust accordingly.
No:
Yes:
First one always returns 0.
And this is another reason the cool case syntax is not usually useful.
Excellent! This worked. Thank you.