ReportSmith: Linking Tables

[twincalicos]

When attempting to link tables in the ADP company level jobcode table to the employee job table via jobcode by "edit(ing) the SQL" query in the report. When we added our linking to the from clause of the query, as you can do in true SQL (client and I are SQL people, not Oracle), it failed with an invalid field name msg.

Another time, we got a msg that the query was not properly ended.

I know the ADP system they have is actually Oracle based (via web access), but Report Smith uses SQL as its terminology on the buttons. Perhaps there is an Oracle format to the query that we were missing?
I think we had also tried to link in the where clause and had an issue there too.

Additionally, is there a cosmetic place to link tables in Report Smith (not in the query itself) whereby it will link them for you so you do not have to always edit the query?

 

[CharlesCook]

Do your linking at the bottom of the form where you add the tables. ReportSmith will use the where clause to do the linking.

CharlesCook.com
ADP - PeopleSoft - SAP
ReportSmith - Crystal Reports - SQR - Query - Access
Reporting - Interfaces - Data Mining

 

[RSHelp]

“When attempting to link tables in the ADP company level jobcode table to the employee job table via jobcode by "edit(ing) the SQL" query in the report. When we added our linking to the from clause of the query, as you can do in true SQL (client and I are SQL people, not Oracle), it failed with an invalid field name msg.”

I thought all SQL DQL did it’s linking in the Where clause, but maybe not; anyway, you are correct Oracle does require linking in the Where clause.

"I know the ADP system they have is actually Oracle based (via web access), but Report Smith uses SQL as its terminology on the buttons."

ReportSmith is referring to Structured Query Language when it says SQL, not Microsoft SQL Server. MS did the world a disservice by calling their database SQL Server. I think they just did that for name recognition since they were so late to the RDBMS market; kind of like when they named their word processor Word.

“Additionally, is there a cosmetic place to link tables in Report Smith (not in the query itself) whereby it will link them for you so you do not have to always edit the query?”

There is a place in the GUI to Auto Link tables if you are ReportSmith v3.1(?) or higher. It is at the bottom of the Report Query – Tables screen. However, the Auto Link button may be hidden with the following parameter in your RPTSMITH.INI (located in your Windows directory) HideAutoLinkButton=1 parameter. Change the 1 to a 0 to get it back. Which tables are linked to which other table and by what column can be controlled by the rs_link.ini (also in your Windows directory).

 

[twincalicos]

Hi, RSHelp!

True, you CAN also link in MS SQL in the WHERE clause as in Oracle, but for very advanced joins, you must do it in the FROM (you are very limited in the WHERE on linking with MS SQL). As a result, I am accustomed to doing all my MS SQL linking in the FROM clause.

I forgot that the term "SQL" is not owned by Microsoft!
Being an MS SQL person, I tend to have tunnel vision I guess.

Thanks.