GnuCash bulk operations

GnuCash.md

Gnu Cash Bulk Operations

This can be used from the sqlite backend.

List placeholder accounts with transactions in them

SELECT accounts.name AS Ledger, transactions.description AS Record,
	CAST(splits.value_num AS FLOAT)/splits.value_denom AS Amount
FROM accounts
LEFT JOIN splits ON accounts.guid=splits.account_guid
LEFT JOIN transactions ON splits.tx_guid=transactions.guid
WHERE placeholder=1 AND splits.guid notnull

List all the splits of account <FROM>

SELECT * FROM splits WHERE account_guid='<FROM>'

Same, with jump accounts

SELECT * FROM splits WHERE tx_guid IN (
	SELECT tx_guid FROM splits
	WHERE account_guid='<FROM>'
)

Same, but only with jump account <JUMP>

SELECT * FROM splits S
WHERE account_guid='<FROM>'
AND EXISTS (
	SELECT NULL FROM splits T
	WHERE T.tx_guid = S.tx_guid
	AND account_guid='<JUMP>'
)

Same, but change <FROM> to <TO>

UPDATE splits SET account_guid='<TO>'
WHERE guid IN (
	SELECT guid FROM splits S
	WHERE account_guid='<FROM>'
	AND EXISTS (
		SELECT NULL FROM splits T
		WHERE T.tx_guid = S.tx_guid
		AND account_guid='<JUMP>'
	)
)

eof