ORA-00936: missing expression
A customer has sent me a report that developed using Report Builder 5.53.
After upgrading to Report Builder 7.04, the report now produces the error:
ORA-00936: missing expression.
I have simplified the report and attached it. The error can be duplicated
by previewing the "AREA Linked to AREA_MAIN" query. I used SQL Monitor to
determine the sql fired is as follows (the error is caused by the AND AND
found six lines below the where keyword:
SELECT DISTINCT unit.unit_seqno unit_seqno_2,
area.area_id area_id_2,
unit.unit_id unit_id_2,
equip_type.name name_2,
area_2.area_seqno,
area_2.area_id, unit_2.unit_id,
unit_2.unit_seqno,
equip_type_2.name,
equip_2.equip_seqno,
equip_2.equip_id
FROM area area, unit_area unit_area, unit unit
, equip equip, equip_type equip_type
, area area_2, unit_area unit_area_2
, unit unit_2, equip equip_2
, equip_type equip_type_2
WHERE (unit_area.area_seqno = area.area_seqno)
AND
(unit.unit_seqno = unit_area.unit_seqno)
AND (equip.unit_seqno = unit.unit_seqno)
AND
(equip_type.equip_type_seqno = equip.equip_type_seqno)
AND AND
(unit_area_2.area_seqno = area_2.area_seqno)
AND
(unit_2.unit_seqno = unit_area_2.unit_seqno)
AND (unit_2.unit_seqno = unit.unit_seqno)
AND
(equip_2.unit_seqno = unit_2.unit_seqno)
AND
(equip_type_2.equip_type_seqno = equip_2.equip_type_seqno)
AND (( unit.shop_yn = 'N' )
AND ( unit.warehouse_yn = 'N' )
AND ( unit.unit_id NOT LIKE 'Z%' )
AND ( equip_type.name IN
('DRUM','EXCHANGER','TOWER','HEATER','REACTOR','FIN FAN') )
AND ( equip.history_no = 0 )
AND ( equip_type_2.name IN
('DRUM','EXCHANGER','TOWER','HEATER','REACTOR','FIN FAN') )
)
ORDER BY area.area_id, unit.unit_id,
equip_type.name, unit_2.unit_seqno,
equip_2.equip_id
Any idea what is causing the bad sql?
Thanks for your time,
Bob
After upgrading to Report Builder 7.04, the report now produces the error:
ORA-00936: missing expression.
I have simplified the report and attached it. The error can be duplicated
by previewing the "AREA Linked to AREA_MAIN" query. I used SQL Monitor to
determine the sql fired is as follows (the error is caused by the AND AND
found six lines below the where keyword:
SELECT DISTINCT unit.unit_seqno unit_seqno_2,
area.area_id area_id_2,
unit.unit_id unit_id_2,
equip_type.name name_2,
area_2.area_seqno,
area_2.area_id, unit_2.unit_id,
unit_2.unit_seqno,
equip_type_2.name,
equip_2.equip_seqno,
equip_2.equip_id
FROM area area, unit_area unit_area, unit unit
, equip equip, equip_type equip_type
, area area_2, unit_area unit_area_2
, unit unit_2, equip equip_2
, equip_type equip_type_2
WHERE (unit_area.area_seqno = area.area_seqno)
AND
(unit.unit_seqno = unit_area.unit_seqno)
AND (equip.unit_seqno = unit.unit_seqno)
AND
(equip_type.equip_type_seqno = equip.equip_type_seqno)
AND AND
(unit_area_2.area_seqno = area_2.area_seqno)
AND
(unit_2.unit_seqno = unit_area_2.unit_seqno)
AND (unit_2.unit_seqno = unit.unit_seqno)
AND
(equip_2.unit_seqno = unit_2.unit_seqno)
AND
(equip_type_2.equip_type_seqno = equip_2.equip_type_seqno)
AND (( unit.shop_yn = 'N' )
AND ( unit.warehouse_yn = 'N' )
AND ( unit.unit_id NOT LIKE 'Z%' )
AND ( equip_type.name IN
('DRUM','EXCHANGER','TOWER','HEATER','REACTOR','FIN FAN') )
AND ( equip.history_no = 0 )
AND ( equip_type_2.name IN
('DRUM','EXCHANGER','TOWER','HEATER','REACTOR','FIN FAN') )
)
ORDER BY area.area_id, unit.unit_id,
equip_type.name, unit_2.unit_seqno,
equip_2.equip_id
Any idea what is causing the bad sql?
Thanks for your time,
Bob
This discussion has been closed.
Comments
How are you connecting to your Oracle database? Are you able to run this
query directly on your Oracle server? If you place a query component on a
form with the SQL below entered in, are you able to successfully activate
it?
--
Nico Cizik
Digital Metaphors
http://www.digital-metaphors.com
We are using ORACLE native drivers via the BDE to connect to ORACLE. The
sql below will not run using any tool. As previously stated, the "AND AND"
in the sql (with no expression inbetween) is causing the problem. This
appears to be built by Report Builder as a result of the relationship drawn
between the 2 queries on the report.
Thanks,
Bob
Sorry for the delay in this response. If possible, will you please take a
screen shot of the "Search" tab in the Query Designer of your dataset and
send it to support@digital-metaphors.com. I am unable to fully see what is
happening here due to the fact that I do not have your database installed on
my machine.
--
Regards,
Nico Cizik
Digital Metaphors
http://www.digital-metaphors.com
Nico Cizik
Digital Metaphors
http://www.digital-metaphors.com