Hi,
Would any one have a recommendation on the best way to write a BAQ to compare lines within a journal?
Trying to see where within a journal a credit amount = debit amount, but the segment value is different?
see below example - Thank you!!
What I typically do in this case is make a calculated field to hold the sum of the credit and debit. Since one will always be zero its just the value of the line. Then sort by that field.
Granted This just makes it easier to see what’s what, it doesn’t actually return “Lines 11 and 12 are offsetting”
Are you trying reverse engineer the posting rules?
Edit
after a re-read of your post, I see what you want now. You’ll need to use Group By, and might need a subquery.
Thanks - I’ll give that a shot. Our segment value 2 represents a location, and they should always match. I’m trying to pull out journal entries that are coded incorrectly.
still stumped on the best way to write it - any ideas? Everytime I do a group by it says bad SQL. Is there some rule to Group BY that always needs to be followed? Thx in advance for any assistance
The app server log will give you a more specific SQL error
Sorry in advanced if there is more to this than what I assume, but I’ll answer this the way I am reading it.
Grouping in a BAQ needs a calculated field that uses an aggregate function (Max, average, etc.). And then all non-calculated fields are grouped. Like this:
1 Like
You need to get those 2 lines onto a single line.
so instead of this.
| record | data | data2 |
|---|---|---|
| 1 | a | b |
| 2 | a | b |
| 3 | a | b |
| 4 | a | b |
You need this
| record | data | data2 | record | data | data2 |
|---|---|---|---|---|---|
| 1 | a | b | 3 | a | b |
| 2 | a | b | 4 | a | b |
You can do this by making 2 subqueries and then joining them, and adding the fields. Then you can do calculated fields to check for what you need to do.
You probably can filter one by 0 in the debit, and the other for 0 in the credit columns to be able to split them up.
1 Like
I’m wondering if doing the equivalent of the following would work for you:
select distinct case
when DisplayEntry.JournalLine < RelatedEntry.JournalLine then DisplayEntry.JournalLine10000000000+RelatedEntry.JournalLine1000000+RelatedEntry.CreditAmount
else RelatedEntry.JournalLine10000000000+DisplayEntry.JournalLine1000000+RelatedEntry.CreditAmount
end MatchID
, DisplayEntry.*
from erp.GLJrnDtl DisplayEntry
inner join erp.GLJrnDtl RelatedEntry
on RelatedEntry.Company = DisplayEntry.Company
and RelatedEntry.FiscalYear = DisplayEntry.FiscalYear
and RelatedEntry.JournalNum = DisplayEntry.JournalNum
and RelatedEntry.JournalLine <> DisplayEntry.JournalLine
and RelatedEntry.SegValue2<> DisplayEntry.SegValue2
and (RelatedEntry.CreditAmount = DisplayEntry.DebitAmount
or RelatedEntry.DebitAmount = DisplayEntry.CreditAmount)
order by DisplayEntry.Company
, DisplayEntry.FiscalYear
, DisplayEntry.JournalNum
, MatchID
, DisplayEntry.JournalLine
The difficulty is when there are multiple matches