Re: Problem in Database Migration for Views

Kwan, I cannot see the error assuming that the exta blanks lines in the post do not appear in your actual source. If they do remove them.

I suggest retyping the view in a new member starting with a single column in the select list the FROM and full WHERE clauses. Run it in to test the code. Then just keep adding the select list to you either duplicate the error or are done typing the view in.

Depending on your platform, editor, and terminal emulation software I have ran into a couple of instances where Oracle kept rejecting SQL but if I deleted the line and retyped it then the SQL worked. I blame the problem on non-printable, garbage characters getting into the source.

Using 9.2 the 0.0 in the NVL and the column heading with underscores in them and no double quotes around them are legal, but you could try using nvl(col,0) instead of 0.0 and "Debit_Amount" to see if you might be running into a 10g parser bug of some kind.

I would also place table alias on all the tables and reference them in the scalar subquery in the select list and in the =any clause.

These are the only thinks I can think of besides verifing that all referenced table column names are correct in the code.

PS Why does you code look so much like SQL Server code, owner = DBO?

HTH -- Mark D Powell -- Received on Thu Mar 03 2005 - 10:06:57 CST