At 12:15 AM 16/02/2007, you wrote:

>Hi
>
>Version 4.6Bc
>
>Odd thing has occured
>
>Have a TIB_Query with nothing set or connected except the IB_Connection and
>this SQL
>
>select h.invoice_number,h.customer_ac_no,h.status,
>l.quantity_delivered,l.catalogue_code,l.description
>from ((icc_header h join iccline l on h.icc_no=l.icc_no)join
>product p on p.product_number=l.product_id)join
>customer c on h.customer_ac_no=c.account_no and c.co=h.company
>order by h.Invoice_number
>
>In the Designer prepare gives the error 'field ICCLINE.QUANTITY_DELIVERED
>not found'
>If I put this SQL into IBExpert it works fine - If I remove the select
>columns from icc_header, just leaving those from iccline it works fine.
>
>If I change the SQL to the other join syntax
>
>select h.invoice_number,h.customer_ac_no,h.status,
>l.quantity_delivered,l.catalogue_code,l.description
>from icc_header h,iccline l,product p,customer c
>where h.icc_no=l.icc_no and p.product_number=l.product_id
>and h.customer_ac_no=c.account_no and c.co=h.company
>order by h.Invoice_number
>
>it works fine!
>
>When running the app with SQL monitor it lists the fields including
>ICCLINE.QUANTITY_DELIVERED then shows the error.
>
>Is this a new bug or am I doing something daft?

well, those brackets don't belong in that syntax, that's for sure. I
think the dsql parser on the server side will just ignore them but
they'll sure be a challenge to the IBO parser.

The parser in IBO 4.6Bc is seriously broken anyway, particularly for
joins. You really shouldn't be trying to do anything serious with
that version.

Still, for the record (and for when you update to IBO 4.7), the
proper syntax for the explicit join is:

select
h.invoice_number,
h.customer_ac_no,
h.status,
l.quantity_delivered,
l.catalogue_code,
l.description
from icc_header h
join iccline l on h.icc_no=l.icc_no
join product p on p.product_number=l.product_id
join customer c on h.customer_ac_no=c.account_no
and c.co=h.company
order by h.Invoice_number

Helen