BLR error

[Ertan Küçükoglu]

Hello,

I am getting a BLR error for a specific SQL on WI-V2.5.9.27139 Firebird 2.5 (Firebird/x86-64/Windows NT).

SQL is as follows:

SELECT
  1 INTERNALORDER,
  ORDERDETAIL.ORDERDETAILID,
  ORDERS.*,
  ORDERDETAIL.PRODUCTID,
  ORDERDETAIL.PRICE,
  (ORDERDETAIL.QTY - ORDERDETAIL.PAIDQTY) AS QTY,
  ORDERDETAIL.ADDITIONPRINTEDQTY,
  ORDERDETAIL.UNIT,
  ORDERDETAIL.PRODUCTNOTES,
  ORDERDETAIL.ENTRYUSER AS USERID,
  ORDERDETAIL.VAT,
  FLOOR.FLOORNAME,
  TABLES.TABLENAME,
  case when orders.customerid < 0 then orders.customerid2 else orders.customerid end as customerid,
  case when orders.customerid < 0 then c2.customername else c.customername end as customername,
  case when orders.customerid < 0 then c2.taxnumber else c.taxnumber end as taxnumber,
  case when orders.customerid < 0 then c2.taxoffice else c.taxoffice end as taxoffice,
  case when orders.customerid < 0 then c2.town else c.town end as town,
  case when orders.customerid < 0 then c2.discountrate else c.discountrate end as discountrate,
  case when orders.customerid < 0 then c2.telefon1 else c.telefon1 end as telefon1,
  case when orders.customerid < 0 then c2.telefon2 else c.telefon2 end as telefon2,
  case when orders.customerid < 0 then c2.mobilephone else c.mobilephone end as mobilephone,
  case when orders.customerid < 0 then c2.emailadress else c.emailadress end as emailadress,
  case when orders.customerid < 0 then c2.debt else c.debt end as debt,
  case when orders.customerid < 0 then c2.credit else c.credit end as credit,
  case when orders.customerid < 0 then c2.street else c.street end as street,
  case when orders.customerid < 0 then c2.quarter else c.quarter end as quarter,
  COURIER.COURIERNAME,
  COURIER.COURIERTEL,
  COURIER.COURIERMOBILE,
  COURIER.COURIERPLATE,
  ORDERSPAYMENTMETHOD.ORDERSPAYMENTMETHOD,
  YEMEKSEPETI_ORDERS.*,
  YEMEKSEPETI_PAYMENTMETHOD.*,
  GETIRORDERS.*,
  TYORDERS.*,
  PRODUCT.TICKETCOUNT,
  YSV3ORDERS.*
FROM
  ORDERS
  INNER JOIN ORDERDETAIL ON (ORDERS.ORDERID = ORDERDETAIL.ORDERID)
  LEFT JOIN PRODUCT ON (PRODUCT.PRODUCTID = ORDERDETAIL.PRODUCTID)
  LEFT OUTER JOIN FLOOR ON (ORDERS.FLOORID = FLOOR.FLOORID)
  LEFT OUTER JOIN TABLES ON (ORDERS.TABLEID = TABLES.TABLEID)
  left outer join customer c on (orders.customerid = c.customerid)
  left outer join customer c2 on (orders.customerid2 = c2.customerid)
  LEFT OUTER JOIN COURIER ON (ORDERS.COURIERID = COURIER.COURIERID)
  LEFT OUTER JOIN ORDERSPAYMENTMETHOD ON (ORDERS.CUSTOMERPAYMENTMETHODID = ORDERSPAYMENTMETHOD.ORDERSPAYMENTMETHODID)
  LEFT OUTER JOIN YEMEKSEPETI_ORDERS ON (YEMEKSEPETI_ORDERS.ID = ORDERS.YEMEKSEPETIID)
  LEFT OUTER JOIN YEMEKSEPETI_PAYMENTMETHOD ON (YEMEKSEPETI_PAYMENTMETHOD.YSEPETIORDERSPAYMENTMETHODID = YEMEKSEPETI_ORDERS.PAYMENTMETHODID)
  LEFT OUTER JOIN GETIRORDERS ON (GETIRORDERS.ORDERID = ORDERS.GETIRYEMEKID)
  LEFT OUTER JOIN TYORDERS ON (TYORDERS.TYID = ORDERS.TRENDYOLYEMEKID)
  LEFT OUTER JOIN YSV3ORDERS ON (YSV3ORDERS.middlewareorderid = ORDERS.YSV3ORDERID)
WHERE
  (ORDERDETAIL.QTY - ORDERDETAIL.PAIDQTY >  0) AND
  ORDERDETAIL.PRICE <> 0 AND
  ORDERS.ORDERID = 1234

UNION

SELECT
  2 INTERNALORDER,
  ORDERDETAIL.ORDERDETAILID,
  ORDERS.*,
  ORDERDETAIL.PRODUCTID,
  ORDERDETAIL.PRICE,
  (ORDERDETAIL.QTY - ORDERDETAIL.PAIDQTY) AS QTY,
  ORDERDETAIL.ADDITIONPRINTEDQTY,
  ORDERDETAIL.UNIT,
  ORDERDETAIL.PRODUCTNOTES,
  ORDERDETAIL.ENTRYUSER AS USERID,
  ORDERDETAIL.VAT,
  FLOOR.FLOORNAME,
  TABLES.TABLENAME,
  case when orders.customerid < 0 then orders.customerid2 else orders.customerid end as customerid,
  case when orders.customerid < 0 then c2.customername else c.customername end as customername,
  case when orders.customerid < 0 then c2.taxnumber else c.taxnumber end as taxnumber,
  case when orders.customerid < 0 then c2.taxoffice else c.taxoffice end as taxoffice,
  case when orders.customerid < 0 then c2.town else c.town end as town,
  case when orders.customerid < 0 then c2.discountrate else c.discountrate end as discountrate,
  case when orders.customerid < 0 then c2.telefon1 else c.telefon1 end as telefon1,
  case when orders.customerid < 0 then c2.telefon2 else c.telefon2 end as telefon2,
  case when orders.customerid < 0 then c2.mobilephone else c.mobilephone end as mobilephone,
  case when orders.customerid < 0 then c2.emailadress else c.emailadress end as emailadress,
  case when orders.customerid < 0 then c2.debt else c.debt end as debt,
  case when orders.customerid < 0 then c2.credit else c.credit end as credit,
  case when orders.customerid < 0 then c2.street else c.street end as street,
  case when orders.customerid < 0 then c2.quarter else c.quarter end as quarter,
  COURIER.COURIERNAME,
  COURIER.COURIERTEL,
  COURIER.COURIERMOBILE,
  COURIER.COURIERPLATE,
  ORDERSPAYMENTMETHOD.ORDERSPAYMENTMETHOD,
  YEMEKSEPETI_ORDERS.*,
  YEMEKSEPETI_PAYMENTMETHOD.*,
  GETIRORDERS.*,
  TYORDERS.*,
  PRODUCT.TICKETCOUNT,
  YSV3ORDERS.*
FROM
  ORDERS
  INNER JOIN ORDERDETAIL ON (ORDERS.ORDERID = ORDERDETAIL.ORDERID)
  LEFT JOIN PRODUCT ON (PRODUCT.PRODUCTID = ORDERDETAIL.PRODUCTID)
  LEFT OUTER JOIN FLOOR ON (ORDERS.FLOORID = FLOOR.FLOORID)
  LEFT OUTER JOIN TABLES ON (ORDERS.TABLEID = TABLES.TABLEID)
  left outer join customer c on (orders.customerid = c.customerid)
  left outer join customer c2 on (orders.customerid2 = c2.customerid)
  LEFT OUTER JOIN COURIER ON (ORDERS.COURIERID = COURIER.COURIERID)
  LEFT OUTER JOIN ORDERSPAYMENTMETHOD ON (ORDERS.CUSTOMERPAYMENTMETHODID = ORDERSPAYMENTMETHOD.ORDERSPAYMENTMETHODID)
  LEFT OUTER JOIN YEMEKSEPETI_ORDERS ON (YEMEKSEPETI_ORDERS.ID = ORDERS.YEMEKSEPETIID)
  LEFT OUTER JOIN YEMEKSEPETI_PAYMENTMETHOD ON (YEMEKSEPETI_PAYMENTMETHOD.YSEPETIORDERSPAYMENTMETHODID = YEMEKSEPETI_ORDERS.PAYMENTMETHODID)
  LEFT OUTER JOIN GETIRORDERS ON (GETIRORDERS.ORDERID = ORDERS.GETIRYEMEKID)
  LEFT OUTER JOIN TYORDERS ON (TYORDERS.TYID = ORDERS.TRENDYOLYEMEKID)
  LEFT OUTER JOIN YSV3ORDERS ON (YSV3ORDERS.middlewareorderid = ORDERS.YSV3ORDERID)
WHERE
  (ORDERDETAIL.QTY - ORDERDETAIL.PAIDQTY >  0) AND
  ORDERDETAIL.PRICE = 0 AND
  ORDERS.ORDERID = 1234

ORDER BY
  1,2

Error is as follows:

invalid request BLR at offset 6231
BLR syntax error: expected record selection expression clause at offset 6232, encountered 24

There are similar SQLs being used on this same application. They do not get such an error.

I am trying to list ORDERDETAIL.PRICE=0 records at the bottom steel keeping ORDERDETAIL.ORDERDETAILID ordering using union.

Running individual select statements separately works fine. Error is only when using union.

Is there anything that I can do to get this SQL running?

Thanks & Regards,

Ertan

[Mark Rotteveel]

On 06/12/2025 07:05, Ertan Küçükoglu wrote:
> I am getting a BLR error for a specific SQL on WI-V2.5.9.27139 Firebird
> 2.5 (Firebird/x86-64/Windows NT).
>
> SQL is as follows:

[..]

> Error is as follows:
> invalid request BLR at offset 6231
> BLR syntax error: expected record selection expression clause at offset
> 6232, encountered 24
>
> There are similar SQLs being used on this same application. They do not
> get such an error.
>
> I am trying to list ORDERDETAIL.PRICE=0 records at the bottom steel
> keeping ORDERDETAIL.ORDERDETAILID ordering using union.
> Running individual select statements separately works fine. Error is
> only when using union.
>
> Is there anything that I can do to get this SQL running?

Could you try reducing the query so it doesn't have so many parts, and
provide the necessary DDL for that minimal reproduction?

Mark
--
Mark Rotteveel