Any Sage50 ODBC/SQL guru's in here?

[ArsE82]

Hi All

I'm trying to solve what should really be a quite simple problem. I have a report designed in Sage 50 which works well, but I want to run it on a schedule which you can't do with the application out of the box.

I can connect to the Sage database via ODBC no problem, and run simple queries. But the report uses aliases and inner joins and I'm starting to get out of my depth. If I can get the SQL query right then the scheduling bit I'm fine with.

I'm starting to think it's not possible at this level and we might need to use another reporting tool.

Any guidance appreciated!

[dmsims]

Try Microsoft Access or some other ODBC reporting tool

[RUSTILLDOWN]

Simple SQL, is erm, simple

If ur struggling, often it’s the formatting (eg indents) that help u understand the necessary tweaks.

Was gonna suggest u take a look at RPA if u need the report run regularly but sounds like u have a plan

[98elise]

ArsE82 said:

Hi All

I'm trying to solve what should really be a quite simple problem. I have a report designed in Sage 50 which works well, but I want to run it on a schedule which you can't do with the application out of the box.

I can connect to the Sage database via ODBC no problem, and run simple queries. But the report uses aliases and inner joins and I'm starting to get out of my depth. If I can get the SQL query right then the scheduling bit I'm fine with.

I'm starting to think it's not possible at this level and we might need to use another reporting tool.

Any guidance appreciated!

Is it that you don't understand the aliases and how to form inner joins?

The SQL should be relatively simple. A different tool wouldn't make it easier.

[ArsE82]

dmsims said:

Try Microsoft Access or some other ODBC reporting tool

I'll have a look at this. Essentially my problem is that I can't recreate what the Sage 50 Report is doing, in an SQL query environment. Maybe doing it in Access/Excel might help me understand things a bit better.

RUSTILLDOWN said:

Simple SQL, is erm, simple

If ur struggling, often it’s the formatting (eg indents) that help u understand the necessary tweaks.

Was gonna suggest u take a look at RPA if u need the report run regularly but sounds like u have a plan

That's exactly it. We have the RPA side tied up, I just need to feed this query in!

98elise said:

Is it that you don't understand the aliases and how to form inner joins?

The SQL should be relatively simple. A different tool wouldn't make it easier.

Exactly that. I'm not a DBA but I understand the basic principals. However, inner joins and aliases just blow my mind!

There are tools out there that use the SDK to manipulate the Sage data. I've found a couple that will potentially help with this problem but I'd really like to get this working using an SQL query, as on the face of it it doesn't appear that complex. Also, if we need to make a slighty tweak in the future it's easier if we do it this way.

[98elise]

ArsE82 said:

dmsims said:

Try Microsoft Access or some other ODBC reporting tool

I'll have a look at this. Essentially my problem is that I can't recreate what the Sage 50 Report is doing, in an SQL query environment. Maybe doing it in Access/Excel might help me understand things a bit better.

RUSTILLDOWN said:

Simple SQL, is erm, simple

If ur struggling, often it’s the formatting (eg indents) that help u understand the necessary tweaks.

Was gonna suggest u take a look at RPA if u need the report run regularly but sounds like u have a plan

That's exactly it. We have the RPA side tied up, I just need to feed this query in!

98elise said:

Is it that you don't understand the aliases and how to form inner joins?

The SQL should be relatively simple. A different tool wouldn't make it easier.

Exactly that. I'm not a DBA but I understand the basic principals. However, inner joins and aliases just blow my mind!

There are tools out there that use the SDK to manipulate the Sage data. I've found a couple that will potentially help with this problem but I'd really like to get this working using an SQL query, as on the face of it it doesn't appear that complex. Also, if we need to make a slighty tweak in the future it's easier if we do it this way.

What database is it on (i.e. Oracle or SQL Server)?

Aliases are just temporary names for tabes or columns. As an example if you were selecting from a table called "employees" you could do this

SELECT emp.name
FROM employees emp

Within the SQL you can now reference employees table as emp, or whatever you chose as the alias. I tend to use a, b, c etc so my query would be

SELECT a.name
FROM employees a

To give the column an alias for the output

SELECT a.name AS employee
FROM employees a

Now the column will appear in the output as "employee" instead of "name"

An inner join is actually very simple as it's only where there is a match in both tables.

It might be easier if you post the SQL

Edited by 98elise on Monday 1st February 11:33