To Verify All Account Code which is not Parent Account but unable to select From Transaction

Note : this gl account is not a parent account but not able to select in report filter or transaction as below.

Solution:

Use Below Script to find all glaccount:

select * from glaccounts where id not in (Select parentid from glaccounts) and issubaccount = '1'

If above Script can find result.

then Run Below Script to update all problem account.

---temp Table to hold records

declare @Table1 table (id uniqueidentifier, code varchar(100))

-- insert some records into the temp table

insert into @Table1

select Id,GLAccountCode from glaccounts where id not in (Select parentid from glaccounts) and issubaccount = '1'

-- display all 

select * from @Table1

---Display Table Before Update

select * from glaccounts where Id in (Select id from @Table1)

---update isSubAccount

update GLaccounts set issubAccount = '0' where id in(Select id from @Table1)

** Learn more about Azure Cloud

** Learn more about QNE Hybrid Cloud Software