Column Definition on an SQL Stored Proc
Moderators: chulett, rschirm, roy
-
- Participant
- Posts: 122
- Joined: Mon Jul 05, 2004 1:33 pm
- Location: MA
Column Definition on an SQL Stored Proc
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
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
Regards
Sonia Jacob
Sonia Jacob
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
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.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
-
- Participant
- Posts: 122
- Joined: Mon Jul 05, 2004 1:33 pm
- Location: MA
Hi Ray,ray.wurlod wrote: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 ...
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
Regards
Sonia Jacob
Sonia Jacob
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
Can you enclose the column name in double quotes in the Derivation field?
Code: Select all
"Column A"
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
-
- Participant
- Posts: 122
- Joined: Mon Jul 05, 2004 1:33 pm
- Location: MA
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.ray.wurlod wrote:Can you enclose the column name in double quotes in the Derivation field?
...Code: Select all
"Column A"
2. I did not find anything in the "properties" of the column.
Regards
Sonia Jacob
Sonia Jacob
-
- Premium Member
- Posts: 397
- Joined: Wed Apr 12, 2006 2:28 pm
- Location: Tennesse
-
- Participant
- Posts: 122
- Joined: Mon Jul 05, 2004 1:33 pm
- Location: MA
No I do not have a transformaer stage.samsuf2002 wrote:Derivation field will be in transformer if u r using one.
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
Regards
Sonia Jacob
Sonia Jacob
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
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.
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.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.