Source language: Translate to:

Print report made with INNER JOIN

Questions about our Advanced Database plug-in

Moderator: Neosoft Support

Print report made with INNER JOIN

Postby smartmedia » Fri Sep 12, 2008 3:21 am

Hi...

I use an dbpExecSQL to execute an inner join query. The problems are multiple.
The query running is this:
dbpExecSQL "Data" "SELECT Ergazomenoi.id, Ergazomenoi.Name, Ergazomenoi.Fathername, Ergazomenoi.Eidikotita, Adies.a2_ypol FROM Ergazomenoi INNER JOIN Adies ON Ergazomenoi.id=Adies.id WHERE Adies.a2_ypol >= [TextEntry94] AND Adies.etos=[Etos];" "3453453"
The data are taken from 2 tables. Ergazomenoi and Adies.
I send the data into table 3453453 which is not exist, i only used to present the data into a grid. So far is working.
The problem after is when i create the report. Because the table is not exist, the data are not show at all. If i try to create a table view i must set the source table. What table from 2 i put there....??? As result i can't print a report came from that INNER JOIN...
User avatar
smartmedia
 
Posts: 889
Joined: Fri Apr 01, 2005 6:50 am
Location: Hellas

Postby Gaev » Fri Sep 12, 2008 5:59 am

smartmedia:
The problem after is when i create the report. Because the table is not exist, the data are not show at all. If i try to create a table view i must set the source table. What table from 2 i put there....???
The resulting Table you have created is 3453453 ... so isn't this the name you need to specify for the Report ?

From the Help File ... for dbpExecSQL ...
Code: Select all
results table (optional)

This parameter can be used to enter the name of the table where you want the results of the query to be displayed. This may be the name of an existing database table or it can be a temporary table. If the table specified does not exist in the database, it is assumed to be a temporary table. Leave this parameter blank to auto detect the table name or if the query does not return any results.
... note that you can specify an existing Table ... so it does NOT have to be a temporary Table ... so you can then specify this same Table Name in the Report Designer.
User avatar
Gaev
 
Posts: 3718
Joined: Fri Apr 01, 2005 7:48 am
Location: Toronto, Canada

Re: Print report made with INNER JOIN

Postby dpayer » Fri Sep 12, 2008 6:04 am

smartmedia wrote:Hi...

I use an dbpExecSQL to execute an inner join query. The problems are multiple.
The query running is this:
dbpExecSQL "Data" "SELECT Ergazomenoi.id, Ergazomenoi.Name, Ergazomenoi.Fathername, Ergazomenoi.Eidikotita, Adies.a2_ypol FROM Ergazomenoi INNER JOIN Adies ON Ergazomenoi.id=Adies.id WHERE Adies.a2_ypol >= [TextEntry94] AND Adies.etos=[Etos];" "3453453"
The data are taken from 2 tables. Ergazomenoi and Adies.
I send the data into table 3453453 which is not exist, i only used to present the data into a grid. So far is working.


I don't know if this helps or not but it has been my experience to use a SELECT *** (AS *** ) FROM *** WHERE ***

where the AS gives you an opportunity to name the resulting table that is generated. Depending on your data engine, I believe this is stored only temporarily and is useful for further query or display.

I am forgetting the precise syntax though. The AS statement may come at the end of the select statement.

David P.
User avatar
dpayer
 
Posts: 1380
Joined: Mon Apr 11, 2005 5:55 am
Location: Iowa - USA

Postby smartmedia » Fri Sep 12, 2008 8:38 am

Gaev wrote:The resulting Table you have created is 3453453 ... so isn't this the name you need to specify for the Report ?

Yes this is the table. I set the table name in report. I wonder if the report designer has trouble to recognize the table because it's name is with numbers. I will try to change it and see.

dpayer wrote:I don't know if this helps or not but it has been my experience to use a SELECT *** (AS *** ) FROM *** WHERE ***

where the AS gives you an opportunity to name the resulting table that is generated. Depending on your data engine, I believe this is stored only temporarily and is useful for further query or display.

I am forgetting the precise syntax though. The AS statement may come at the end of the select statement.

I think that, the statement AS is used to take the data under a different name. So if i use SELECT name AS nam1.... the result will be a var nam1 contain the name data.

Thanks both of you. I will try it Monday morning at office.
User avatar
smartmedia
 
Posts: 889
Joined: Fri Apr 01, 2005 6:50 am
Location: Hellas

Postby Gaev » Fri Sep 12, 2008 9:26 am

smartmedia:
Yes this is the table. I set the table name in report.
I can see that you specify the Table Name in your dbpExecSQL command ... but the crucial point is ... did this Table exist before you invoked the command ? ... otherwise, it will be a Temporary table ... that will not exist after you exit the program where you invoked this command.

I wonder if the report designer has trouble to recognize the table because it's name is with numbers.
If you are trying to Preview this Table's Report in the Report Designer ... and the Table is a temporary one created in your pub/exe ... the Report Designer will not see it ... make sure you try and generate the report inside your pub/exe using dbpPreviewReport/dbpPrintReport.

I think that, the statement AS is used to take the data under a different name. So if i use SELECT name AS nam1.... the result will be a var nam1 contain the name data.
When used in reference to a column/field (instead of a Table), this gives you an opportunity to define (an ALIAS) name for the column/field rendered in the resulting Table ... instead of being at the mercy of the SQL engine ... expanding on the idea of "pre-creating a Results Table" ... you can give it any column/field names you like ... then use the AS clause (with each column/field) to match your given names.

I have never tried INNER JOINS, OUTER JOINS ... or any other positions in the Kama Sutra ... but I would think that the best approach would be to populate a "Results Table" ... using whatever methods NeoBookDBPro and your SQL engine provide ... and then have the Report Designer view it as any other (simple, permanent) Table.
User avatar
Gaev
 
Posts: 3718
Joined: Fri Apr 01, 2005 7:48 am
Location: Toronto, Canada

Postby smartmedia » Sun Sep 14, 2008 11:35 pm

Hi...
Gaev wrote:I can see that you specify the Table Name in your dbpExecSQL command ... but the crucial point is ... did this Table exist before you invoked the command ? ... otherwise, it will be a Temporary table ... that will not exist after you exit the program where you invoked this command.

The table is not exist before i run the SQLQuery, is only temporary table. Actually is not a table, i only redirect the result data from SQLQuery into this temp table i be able to present it in grid. Once again, so far is working 100% when i present the data in grid.

If you are trying to Preview this Table's Report in the Report Designer ... and the Table is a temporary one created in your pub/exe ... the Report Designer will not see it ... make sure you try and generate the report inside your pub/exe using dbpPreviewReport/dbpPrintReport.

I am familiar with this, i have run in from application in both Design and compile mode.
I have also change the table name from numbers into letters and still is not playing the report.
I can't think something else. I suppose that the problem is in Report Designer which is not recognize the temp table.
User avatar
smartmedia
 
Posts: 889
Joined: Fri Apr 01, 2005 6:50 am
Location: Hellas

Postby Neosoft Support » Mon Sep 15, 2008 10:16 am

A temporary table should work with the report designer, but the preview will not work and you will have to manually enter all of the field names. To preview the report you will actually have to run your pub, execute the query then view the report.

If all else fails, you could always create a real table to hold the queried data and use that for the report instead. Just remember to clear the table before executing a new query.
NeoSoft Support
Neosoft Support
NeoSoft Team
 
Posts: 5593
Joined: Thu Mar 31, 2005 10:48 pm
Location: Oregon, USA

Postby smartmedia » Mon Sep 15, 2008 12:00 pm

Neosoft Support wrote:A temporary table should work with the report designer, but the preview will not work and you will have to manually enter all of the field names. To preview the report you will actually have to run your pub, execute the query then view the report.

I have tried that over and over again... IS NOT WORKING...

Neosoft Support wrote:If all else fails, you could always create a real table to hold the queried data and use that for the report instead. Just remember to clear the table before executing a new query.

This is not an option for me. Imagine what will happen when i must run 10 different inner join queries...
Thanks any way...
User avatar
smartmedia
 
Posts: 889
Joined: Fri Apr 01, 2005 6:50 am
Location: Hellas

Postby smartmedia » Tue Sep 16, 2008 12:39 am

Hi...

After testing i came up with the conclusion that the problem is 100% at the Report Designer. I tried to print the contain of the grid with dbpListPrint and is working.
I will prepare a demo pub shows what i am say so you can understand.
Thanks
User avatar
smartmedia
 
Posts: 889
Joined: Fri Apr 01, 2005 6:50 am
Location: Hellas


Return to NeoBookDBPro

Who is online

Users browsing this forum: No registered users and 1 guest