No subject

SELECT splits.*, accounttree.*
FROM splits INNER JOIN accounttree ON splits.account_guid =
accounttree.acct_guid

NOTE: Nz is a function that's available in Microsoft Access that lets you
return a value when otherwise you would get a null value; I don't know if a
similar function is available in other applications/languages. [perhaps
COALESCE(arg1, arg2, arg3, argN...) would work instead?]

Robin

On Fri, Apr 1, 2011 at 4:42 PM, Martin Cunningham <
martin.cunningham at gmail.com> wrote:

> Hi -
>
> I contributed to this thread about a year ago hoping to generate interest
> in
> starting a library of SELECT queries; when executed against the GnuCash
> database, they could serve as ODBC data sources for Excel pivot tables or
> to
> generate reports in other tools like Access, Crystal Reports, etc. There
> doesn't seem to be much interest, or am I looking in the wrong place, or
> not
> explaining things properly? Im using MySQL as my back end for GnuCash,
> incidentally.
>
> To date, I have been able to produce a functioning query that I'll post
> later today that returns every journal entry in the database including
> date,
> description, debit account, credit account and amount (can't recall how it
> handles multiline splits). One key thing that's missing from my query: the
> FULL name of the account being debited (or credited), e.g., "Assets:Current
> Assets:...:My Checking Account" instead of just "My Checking Account",
> which
> is what i have today. Or assign "Assets" to a column called "AcctLev1",
> "Current Assets" to "AcctLev2", etc.
>
> Has anyone else tried tackling this problem?
>
> Martin Cunningham
> T: (415) 448-6854
>
>
> On Sun, Jul 18, 2010 at 3:53 AM, Geert Janssens
> <janssens-geert at telenet.be>wrote:
>
> > On Saturday 17 July 2010, Martin Cunningham wrote:
> > > Ah - got it. Thank you!
> > >
> > > Is anyone posting SQL statements they have used to generate reports?
> > seems
> > >  a repository of those would be a useful thing.
> > >
> > I think it's a little early for this. The sql backend is currently only
> > available for the unstable development series. Most users will wait for
> the
> > stable 2.4 release before updating their software, so most users haven't
> > played with the sql backend yet.
> >
> > But if you like you are very welcome to add your results to the GnuCash
> > wiki.
> > I recently started a "Using GnuCash" section [1], which would be a
> suitable
> > place to add useful SQL statements.
> >
> > Geert
> >
> > [1] http://wiki.gnucash.org/wiki/Using_GnuCash
> >
> > > Martin Cunningham
> > >
> > >
> > > On Sat, Jul 17, 2010 at 3:34 AM, Geert Janssens
> > >
> > > <janssens-geert at telenet.be>wrote:
> > > > On Friday 16 July 2010, Martin Cunningham wrote:
> > > > > Well I found an answer to one of my questions: the schema is posted
> > > > > here<http://wiki.gnucash.org/wiki/SQL>.
> > > > > I have downloaded/installed a copy of MySQL and the 'Workbench'
> GUI.
> > > > > Now what - can someone tell me how to connect to the GnuCash data
> > > > > source?
> > > > >
> > > > > Martin Cunningham
> > > >
> > > > Did you save your gnucash data into a mysql database already ?
> > > >
> > > > You can do so by opening your existing data file and use "Save as"
> > > > to save it in mysql.
> > > >
> > > > From there on, you should be able to simply run your mysql queries in
> > any
> > > > mysql aware tool (like MySQL workbench).
> > > >
> > > > Geert
> > >
> >
> >
> _______________________________________________
> gnucash-user mailing list
> gnucash-user at gnucash.org
> https://lists.gnucash.org/mailman/listinfo/gnucash-user
> -----
> Please remember to CC this list on all your replies.
> You can do this by using Reply-To-List or Reply-All.
>