Reg:Views

[ds_sai]

Hi all,

Can we write the user-defined sql for creating the view inside the odbc stage.If yes means i have done the same thing in the odbc stage.

O.S:windows 2003 server
Version:DS 7.5x2
Oracle client:Oracle 10g.

odbcstage--->copy--->sequential file

Inside odbcstage i have given user-definedsql as:

CREATE OR REPLACE VIEW SCOTT.testview
AS
select emp.ename,dept.dname from emp,dept where emp.deptno = dept.deptno;

I am getting the error as below:

Sequential_File_0: Error when checking operator: Could not find input field "EMPNO"
ODBC_Enterprise_11: When checking operator: Modify operator has binding for non-existent output field "EMPNO"
ODBC_Enterprise_11: When checking operator: Modify operator has binding for non-existent output field "ENAME"
Sequential_File_0: Error when checking operator: Could not find input field "ENAME"
Error when checking operator: Could not find input field "JOB"
ODBC_Enterprise_11: When checking operator: Modify operator has binding for non-existent output field "JOB"
ODBC_Enterprise_11: When checking operator: Modify operator has binding for non-existent output field "MGR"
Sequential_File_0: Error when checking operator: Could not find input field "MGR"
Error when checking operator: Could not find input field "HIREDATE"
ODBC_Enterprise_11: When checking operator: Modify operator has binding for non-existent output field "HIREDATE"
ODBC_Enterprise_11: When checking operator: Modify operator has binding for non-existent output field "SAL"
Sequential_File_0: Error when checking operator: Could not find input field "SAL"
Error when checking operator: Could not find input field "COMM"
ODBC_Enterprise_11: When checking operator: Modify operator has binding for non-existent output field "COMM"
ODBC_Enterprise_11: When checking operator: Modify operator has binding for non-existent output field "DEPTNO"
Sequential_File_0: Error when checking operator: Could not find input field "DEPTNO"
Error when checking operator: Could not find input field "DNAME"
ODBC_Enterprise_11: When checking operator: Modify operator has binding for non-existent output field "DNAME"
ODBC_Enterprise_11: When checking operator: Modify operator has binding for non-existent output field "LOC"
Copy_3: When checking operator: Modify operator has binding for non-existent output field "HIREDATE"
Sequential_File_0: When checking operator: When validating export schema: At field "SAL": "null_field" length (0) must match field's fixed width (9)
Sequential_File_0: When checking operator: When validating export schema: At field "SAL": Exporting a fixed length field with a "null_field" of length 0 will prohibit a re-import of this field
Sequential_File_0: When checking operator: When validating export schema: At field "COMM": "null_field" length (0) must match field's fixed width (9)
Sequential_File_0: When checking operator: When validating export schema: At field "COMM": Exporting a fixed length field with a "null_field" of length 0 will prohibit a re-import of this field
Sequential_File_0: Error when checking operator: Could not find input field "LOC"
main_program: Could not check all operators because of previous error(s)
main_program: Creation of step finished with status = FAILED
Job plsql aborted.

My doult is, is this the way to create view in oracle,or we have to create the view in the oracle itself and call that view name in the odbc stage.Please clarify my doult.Thank you.

Cheers,
ds_sai

[kcbland]

This is a silly way to create a view. If you need a view create it once and be done with it.

If you need to have a complex query simplified, then use the view logic inline with a query.

Code: Select all

select inline_vu.a, inline_vu.b, inline_vu.c from
   (select x as "a", y as "b", z as "c" from super_complicated_query) as "inline_vu"

You really don't need a view if an inline view does exactly the same thing.

Creating a view is DML. The user-defined SQL is SQL, not DML.