Counter: 2640
Published: 2007-05-11 06:03:25

Question: Why in FIBPlus 6.5 do I get an error "COLUMN The column ... was specified multiple times for derived table unnamed." in some queries?

An example of such a query:

SELECT PAYMENTS.ID,PAYMENTS.DATE_ORDER,PAYMENTS.SERVICE_STATUS,MAIN.ID,
MAIN.CLNT,CLIENTS.ID,CLIENTS.R FROM PAYMENTS
LEFT JOIN MAIN ON PAYMENTS.ID_COMPANY=MAIN.ID
LEFT JOIN CLIENTS ON MAIN.clnt=CLIENTS.ID WHERE PAYMENTS.
SERVICE_STATUS=0 AND PAYMENTS.DATE_ORDER IS NOT NULL

This example works well everywhere (in IBExpert, console, etc).

Answer: This happens because you try to execute this query from pFIBDataset with an active poAskRecordCount option for Firebird 2.x.

If you use Firebird 2.x, poAskRecordCount uses Select Count(*) from (Select...)

In your case the query will be:

Select Count(*) from (SELECT PAYMENTS.ID,PAYMENTS.DATE_ORDER,PAYMENTS.
SERVICE_STATUS,MAIN.ID,MAIN.CLNT,CLIENTS.ID,CLIENTS.R FROM PAYMENTS
LEFT JOIN MAIN ON PAYMENTS.ID_COMPANY=MAIN.ID
LEFT JOIN CLIENTS ON MAIN.clnt=CLIENTS.ID WHERE PAYMENTS.
SERVICE_STATUS=0 AND PAYMENTS.DATE_ORDER IS NOT NULL )

As Firebird 2.x cannot execute this query, it shows the error "COLUMN The column ... was specified multiple times for derived table unnamed." In other words, this happens because the error is in the query which realises the poAskRecordCount function.

If you write the query as:

SELECT PAYMENTS.ID,PAYMENTS.DATE_ORDER,PAYMENTS.
SERVICE_STATUS,MAIN.ID ID1 ,MAIN.CLNT,CLIENTS.ID ID2,CLIENTS.R FROM PAYMENTS
LEFT JOIN MAIN ON PAYMENTS.ID_COMPANY=MAIN.ID
LEFT JOIN CLIENTS ON MAIN.clnt=CLIENTS.ID WHERE PAYMENTS.
SERVICE_STATUS=0 AND PAYMENTS.DATE_ORDER IS NOT NULL

everything will work correctly. So you should either deactivate the poAskRecordCount option in the dataset or rewrite the query.

Prices in Euro:

235 (1 copy)
1250 (unlimited)
Volume discounts are available...

What a good job you did! Keep coding great pieces of fast and efficient software:) We started using FIBPlus back in 2001. Since then, every project we offer uses FIBPlus to connect to FirebirdSQL. You library has always been stable, flexible and rocket fast ! Our major project, an helpdesk/data mining application, concurrently used daily by more that a hundred technicians and analysts, uses FIBPlus. Now that FirebirdSQL 2.0 is available, we have been able to upgrade our application quickly and painlessly - that's another reason you can be proud of your work ! >>

Benoit Le Bourhis, Adhoc Innovations
FOR CUSTOMERS
Download full versions and updates in our Customer's Center