SageForum for UK versions of Sage 50- Help and support.

Hi there,

Newbie here, so tip of the hat to y'all.

Boring bits first:
Line 50 version 12
Running on Windows Server 2003

OK, my problem is this. I'm trying to extract various data from Sage into a Data Warehouse. I'm using ODBC with SQL Integration Services, which is working fine so far. Problem is my lack of knowledge of the Sage database structures, and accounting principals in general.

Problem 1 is I want to pull out the records relating to Time taken to Pay Sales Invoices. I see there is a report of this name, that looks at AUDIT_SPLIT and AUDIT_USEAGE. Unfortuantely, I can't see how those tables join.

In very basic terms, if I pull these two tables, and figure out how they join, then I should be able to see when payments were made against invoices, right? Is this a meaningful way of extracting this information, or should I be going to other tables?

Any help gratefully received.

There are three levels of hierarchy in the Audit trail

Header
Split
Usage

many usage records are linked to one split by the split_number to the tran_number

many splits are linked to one header by header_number to the tran_number

OK, got that. Thanks for your help

What does it mean

many usage records are linked to one split by the split_number to the tran_number

does it meane
Audit_Header is parent
Audit_Split is child with header_number as foreign key of Audit_Header table ??

And what is Tran_Number for ??

Audit_Header is parent
Audit_Split is child with a join on header_number in both tables.

The transaction number is the split number, the header number is the same as the first of its splits.

Thanks Bruce for reply.

But what iam seeing after join is that
For one record in Audit_Header there is no multiple records in Audit_Split table even we have entered multiple entries through UI.
For every record in Split table there is unique header_number.



Thanks

What type of join are you using (It needs to be an outer join) and in what context are you using ODBC (eg Excel)?

Im using ODBC and with inner join

Select a_h.Header_Number,a_s.Split_Number
From Audit_Header a_h JOIN Audit_Split a_s ON a_h.Header_Number=a_s.Header_Number

but still cannot get parent-child kind of data.

The sql you post is invalid, it does not have full table names and the case should be uppercase.

Can you post the actual sql you are using?

You have not said what you are using to access SQL. Some products such as the datapilot in open office have specific issues. Are you using MS Query with Excel or what?

The sql you post is invalid, it does not have full table names and the case should be uppercase.

The SQL is actually valid. The table names use an alias and do not need to be in upper case (upper case is just a commonly used convention.

Im using ODBC and with inner join

but still cannot get parent-child kind of data.

The SQL is OK and will give the header number from the AUDIT_HEADER table and the split number from any matching records in the AUDIT_SPLIT table, but only where at least one AUDIT_SPLIT record matches with an AUDIT_HEADER (which should always be the case anyway).

Not sure what your expectations are when you say parent-child kind of data but perhaps all you are entering in the UI is a single split for your header. What are entering as your test data?

The SQL could be valid, if there are alias's, but that is not the norm and there was no mention of an alias having been setup or anything else. Adding aliases just adds a layer of obfuscation when trying to debug so not a good idea.

It is not clear what the OP has or has not done in the way of creating aliases.

The SQL could be valid, if there are alias's, but that is not the norm and there was no mention of an alias having been setup or anything else. Adding aliases just adds a layer of obfuscation when trying to debug so not a good idea.

It is not clear what the OP has or has not done in the way of creating aliases.

The aliases are part of the SQL:

Select a_h.Header_Number,a_s.Split_Number
From Audit_Header a_h JOIN Audit_Split a_s ON a_h.Header_Number=a_s.Header_Number

This gives the AUDIT_HEADER table an alias of a_h and the AUDIT_SPLIT table an alias of a_s.

Use of an alias in SQL is common practice for anyone who writes SQL a lot as it can significantly cut down on the typing and can often aid understanding. It's all down to personal preference though.

The key point is that the SQL is valid and should return results. The question remains as to what data data has actually been entered in the UI as I'd hazard a guess that only 1 split has been entered.

So SQL is case insensitive as well.. you learn something everyday.

I just copied and pasted it in to MSquery and it is fine.

I get perfectly good data returned. I suspect you are right, the OP was looking at the beginning of the Audit Trail where it is all opening balances that have only 1 split for each header.