Sql Syntax problem / PB 7.0.3

[1stpoint]

I have a Window that calls a datawindow with lookup values. I have unit tested the data window and it works properly but when I run the Window I get the error:

Select Error ORA-00911: invalid character.

Also, in the data window SQL builder I cannot see the entire SQL statement. Has anyone had this problem?

 

[Kido]

Perhaps if I can see the SQL statement you have and all the fields in your datawindow.

 

[mbalent]

You can always choose 'convert to syntax' while in the dw painter to get the entire sql statement for the datawindow.

 

[1stpoint]

Here is the SQL code:
SELECT BWDBO.stsu_ship_unit.cont_scl_no ,
BWDBO.stsu_ship_unit.cont_ordr_no ,
...(snip)...
BWDBO.stsu_ship_unit.su_expt_ctl_class_no
FROM {oj BWDBO.stsu_ship_unit LEFT OUTER JOIN bwdbo.stsc_contract_register ON BWDBO.stsu_ship_unit.cont_scl_no = bwdbo.stsc_contract_register.cont_scl_no AND BWDBO.stsu_ship_unit.cont_ordr_no = bwdbo.stsc_contract_register.cont_ordr_no}
WHERE ( BWDBO.STSU_SHIP_UNIT.CONT_SCL_NO = :sales_class ) and ( BWDBO.STSU_SHIP_UNIT.CONT_ORDR_NO = rder_no ) and ( BWDBO.STSU_SHIP_UNIT.CONT_UNIT_NO = :unit_no) and ( BWDBO.STSU_SHIP_UNIT.SU_ID_SOURCE_CODE = :source_code) and ( BWDBO.STSU_SHIP_UNIT.SU_ID_SEQ_NO = :suid_seq_no)

When I open the data window and to Rows->Retrieve, it works but through the Data Window is responds with a ORA error meesage.

 

[1stpoint]

Update:

Apparently the LEFT OUTER JOIN syntax, which works find in an ODBC tool is not being interpreted by Oracle correctly.

"FROM {oj BWDBO.stsu_ship_unit LEFT OUTER JOIN ..."
causes the ORA-00911 error message. If I change the outer join to .. WHERE a = b (+), it works properly.

Go figure.

 

[mbalent]

You have just found a reason to use ANSI SQL Join syntax for these types of queries.

 

[1stpoint]

LEFT OUTER JOIN is Ansi Standard. the (+) syntax is not. How do I have PB use the Ansi with Oracle?

 

[mbalent]

Convert your datawindow SQL to syntax and PB will use the exact statement written.

 

[1stpoint]

The datawindow is already converted to syntax. I don't seem to be able to convert it back. ugh!

 

[mbalent]

The point is - you don't convert it back. PB then uses the syntax as written - it does not try to 'convert' it which is what happens when you use graphic mode.