Column Definition on an SQL Stored Proc

[sonia jacob]

Hi all,

I have a stored procedure that as a column defined as 'Column A'

eg. select field as 'Column A' from table

How can I refer to this column in my ODBC stage that calls the Procedure?

regards
Sonia

[ray.wurlod]

If you import the stored procedure definition you will have a "table definition" through which you can refer to the record structure processed by the stored procedure. After that it should be straightforward.

[sonia jacob]

If you import the stored procedure definition you will have a "table definition" through which you can refer to the record structure processed by the stored procedure. After that it should be straigh ...

Hi Ray,

Sorry not a premium member

1.
I imported the structure of the SP. I imported the column as Column A. But when I try to view data then

>##E TCOS 000003 09:27:06(034) <main_program> Syntax error: Error in "odbcread" operator: Error in output redirection: Error in output parameters: Error in modify adapter: Error in binding: Expected '=', got: "A", line 21
>Expected ';' or ','; got: "Name", line 26

2. I tried to change the column name to Column_A, but

>##F TFID 000001 09:29:22(034) <main_program> Fatal Error: Not a valid identifier: Column A

The issue, to my knowledge, is due to column name being defined as "Column A" with a space between. Hence would I be able to read / refer to such column definitions in DS?

Sorry if I am missing anything from your previous post.

regards,
Sonia

[ray.wurlod]

Can you enclose the column name in double quotes in the Derivation field?

Code: Select all

"Column A"

[sonia jacob]

Can you enclose the column name in double quotes in the Derivation field?

Code: Select all

"Column A"

...

1. I am using an ODBC stage and in which the column derivation field is not editable nor I could find a "derivation" column as in the Stored Proc Stage.
2. I did not find anything in the "properties" of the column.

[samsuf2002]

Derivation field will be in transformer if u r using one.

[sonia jacob]

Derivation field will be in transformer if u r using one.

No I do not have a transformaer stage.
And the issue is at the ODBC stage, when I try to view data / run the job

I asked the DBA to change column definition from Column A to ColumnA.
But was wondering whether there was a way to do it otherwie using DS itself

thanks

[ray.wurlod]

User-defined SQL could use the quoted identifier "Column A". Apart from that I can't think of any other strategy.

Put an annotation and/or some description text into your design to alert future maintainers of your reason for using user-defined SQL rather than generated SQL.