Invalid transactions

Build and share your cool customized reports built w/ one sql, Chart.js and Lua (https://github.com/moneymanagerex/general-reports)

Moderator: Renato

Post Reply
Nikolay
MMEX Developer
Posts: 1403
Joined: Sat Dec 06, 2008 2:27 pm
Are you a spam bot?: No
Location: Sankt-Petersburg, Russia

Invalid transactions

Post by Nikolay »

This report should be always empty.
If not there are some problems in DB and it should be fixed.
Invalid Transactions.png
Invalid Transactions.png (11.49 KiB) Viewed 3948 times

Code: Select all

select c.transid, c.transdate, c.notes
    , 'ERROR' as Account
    , 'OK' as category
    ,  p.PayeeName as Payee
    from checkingaccount_v1 c
    inner join payee_v1 p on p.payeeid=c.payeeid
    where c.accountid not in (select accountid from accountlist_v1)
union all
select c.transid, c.transdate, c.notes
    , 'ERROR' as Account
    , 'OK' as category
    ,  '' as Payee
    from checkingaccount_v1 c
    where c.transcode ='Transfer' and c.toaccountid not in (select accountid from accountlist_v1)
union all
select c.transid, c.transdate, c.notes
    , a.AccountName
    , 'OK' 
    ,  'ERROR' 
    from checkingaccount_v1 c
      inner join accountlist_v1 a on a.accountid=c.accountid
    where c.payeeid not in (select payeeid from payee_v1) and transcode!='Transfer'
union all
select c.transid, c.transdate, c.notes
     , a.AccountName
    , 'ERROR'
    ,  p.PayeeName
    from checkingaccount_v1 c
      inner join payee_v1 p on p.payeeid=c.payeeid
      inner join accountlist_v1 a on a.accountid=c.accountid
    where c.categid=-1 and c.transid not in (select transid from splittransactions_v1)
union all
select c.transid, c.transdate, c.notes
     , a.AccountName
    , 'ERROR'
    ,  p.PayeeName
    from checkingaccount_v1 c
      inner join payee_v1 p on p.payeeid=c.payeeid
      inner join accountlist_v1 a on a.accountid=c.accountid
    where c.categid > 0 and c.categid not in (select categid from category_v1)
union all
select c.transid, c.transdate, c.notes
     , a.AccountName
    , 'ERROR'
    ,  p.PayeeName
    from checkingaccount_v1 c
      inner join payee_v1 p on p.payeeid=c.payeeid
      inner join accountlist_v1 a on a.accountid=c.accountid
    where c.categid > 0 and c.subcategid > 0 and c.subcategid not in (select subcategid from subcategory_v1)

Attachments
Invalid Transactions.grm
(1.91 KiB) Downloaded 738 times
Nikolay
MMEX Developer
Posts: 1403
Joined: Sat Dec 06, 2008 2:27 pm
Are you a spam bot?: No
Location: Sankt-Petersburg, Russia

Re: Invalid transactions

Post by Nikolay »

DB with invalid transactions sample
Attachments
test_invalid_transaction.mmb
(188 KiB) Downloaded 552 times
miki
New MMEX User
Posts: 11
Joined: Wed Jul 11, 2012 3:50 pm
Are you a spam bot?: No
Location: IT
Contact:

Re: Invalid transactions

Post by miki »

I have 3687 operations and 0 error ;) ... this report is empty :!:
MMEX v1.2.7 x64 [portable mode] / Windows 10 1703 Home x64
msjpham
New MMEX User
Posts: 1
Joined: Thu Oct 02, 2014 6:51 pm
Are you a spam bot?: No

Re: Invalid transactions

Post by msjpham »

I ran the report and the results were empty and I am still getting the error message.
Nikolay
MMEX Developer
Posts: 1403
Joined: Sat Dec 06, 2008 2:27 pm
Are you a spam bot?: No
Location: Sankt-Petersburg, Russia

Re: Invalid transactions

Post by Nikolay »

msjpham wrote:I ran the report and the results were empty and I am still getting the error message.
what kind of message?
guanlisheng
MMEX Developer
Posts: 218
Joined: Wed Dec 21, 2011 5:58 am
Are you a spam bot?: No
Location: China
Contact:

Re: Invalid transactions

Post by guanlisheng »

Nikolay, would we post this report to Github ?
Nikolay
MMEX Developer
Posts: 1403
Joined: Sat Dec 06, 2008 2:27 pm
Are you a spam bot?: No
Location: Sankt-Petersburg, Russia

Re: Invalid transactions

Post by Nikolay »

Post Reply