Join problem with ImageSql

[PAWEL_5]

From some days I've problem with sql to ImageSql. Easy question (like agregate function, group by, select)are ok,
but with Join :


/Sql

select consignment.* , transport.*
from
consignment LEFT JOIN transport
ON consignment.transport = transport.transport
WHERE
(consignment.transport LIKE 'ty%') OR (consignment.transport LIKE '____ty%')

if I aplly this I've BDE error:
Bde error = 13059
" [MiniSoft] [3kodbc.dll] Syntax error in
table list."

/Sql

MS Axcess doing this sql with no problems to
/Sql

SELECT DBCONS_CONSIGNMENT.COLLECT_DATE, DBCONS_CONSIGNMENT.DELIVER_DATE, DBCONS_TRANSPORT.TRAFFIC, DBCONS_TRANSPORT.TRANSPORT, DBCONS_CONSIGNMENT.CONSIGNMENT, DBCONS_CONSIGNMENT.TRAFFIC_FROM, DBCONS_CONSIGNMENT.TRAFFIC_TO, DBCONS_CONSIGNMENT.DEPARTMENT, DBCONS_CONSIGNMENT.STATUSTEXT, DBCONS_CONSIGNMENT.[NO], DBCONS_CONSIGNMENT.CLL, DBCONS_CONSIGNMENT.G_WEIGHT, DBCONS_CONSIGNMENT.N_WEIGHT, DBCONS_INVOICING.AMOUNT, DBCONS_CONSIGNMENT.SHIP_NO, DBCONS_CONSIGNMENT.SHIP_ADDRESS_1, DBCONS_CONSIGNMENT.SHIP_ADDRESS_2, DBCONS_CONSIGNMENT.DELV_NO, DBCONS_CONSIGNMENT.DELV_ADDRESS_1, DBCONS_CONSIGNMENT.DELV_ADDRESS_2
FROM DBCONS_INVOICING RIGHT JOIN (DBCONS_CONSIGNMENT LEFT JOIN DBCONS_TRANSPORT ON DBCONS_CONSIGNMENT.TRANSPORT = DBCONS_TRANSPORT.TRANSPORT) ON DBCONS_INVOICING.CONSIGNMENT = DBCONS_CONSIGNMENT.CONSIGNMENT
GROUP BY DBCONS_CONSIGNMENT.COLLECT_DATE, DBCONS_CONSIGNMENT.DELIVER_DATE, DBCONS_TRANSPORT.TRAFFIC, DBCONS_TRANSPORT.TRANSPORT, DBCONS_CONSIGNMENT.CONSIGNMENT, DBCONS_CONSIGNMENT.TRAFFIC_FROM, DBCONS_CONSIGNMENT.TRAFFIC_TO, DBCONS_CONSIGNMENT.DEPARTMENT, DBCONS_CONSIGNMENT.STATUSTEXT, DBCONS_CONSIGNMENT.[NO], DBCONS_CONSIGNMENT.CLL, DBCONS_CONSIGNMENT.G_WEIGHT, DBCONS_CONSIGNMENT.N_WEIGHT, DBCONS_INVOICING.AMOUNT, DBCONS_CONSIGNMENT.SHIP_NO, DBCONS_CONSIGNMENT.SHIP_ADDRESS_1, DBCONS_CONSIGNMENT.SHIP_ADDRESS_2, DBCONS_CONSIGNMENT.DELV_NO, DBCONS_CONSIGNMENT.DELV_ADDRESS_1, DBCONS_CONSIGNMENT.DELV_ADDRESS_2
HAVING (((DBCONS_CONSIGNMENT.COLLECT_DATE)=20630) AND ((DBCONS_CONSIGNMENT.DEPARTMENT)="PST24"))
ORDER BY DBCONS_CONSIGNMENT.COLLECT_DATE, DBCONS_TRANSPORT.TRANSPORT;

/Sql


I've Delphi 6 programmer, I connect
with DAtaBAse by ODBC HP 3000 Data Axcess Driver.
Operating system with DB: MPE/iX C.70.01
DB ver allbase/sql: A.G3.22

Operating system from I doing sql:Win98
I connect with DB by ODBC HP 3000 Data Axcess Driver.
My ODBC Driver settings

lock retries = 1
Century Split year = 0
Disable HP3000 transactions = not checked
Enable item-level locking = checked
Use standard sql join processing = checked
Auto-Commit ON is the default = checked
all warrnings are trated as errors = not checked
Database Open mode = 5. read concurrent modify


So what it's wrong in my SELECT ?

[Reinhard Burger]

Hi Pawel

I do not know exactly the syntax of imagesql but assuming that it is not very different from pure sql i would say the problem is the usage of LEFT JOIN

If you just copy sql syntax from MSACCESS this will not run with pure sql because of LEFT JOIN or RIGHT JOIN is not known by sql

try instead :

select table1.*,table2.*
from table1,table2
where table1.field1 = table2.field1 (+)
<< this will be a right join in MSACCESS (if i'm not totally wrong)>>

A left join you get by :

where table1.field1 (+) = table2.field1

Hope this is a help for you