Invalid column name

dj352001 · Post by **dj352001** » Tue Mar 21, 2006 9:18 am

I have an odbc going to a transformer and the transformer going to an odbc. In the transformer i'm doing a derivation on a column. My logic works, but i get an invalid column name, my table that i'm pulling the information from doesn't have this column, is that why i'm getting this error? thanks for the help

ArndW · Post by **ArndW** » Tue Mar 21, 2006 9:37 am

hello dj352001,

There is very high likelihood that the error message and your missing column are related. You can get rid of your error message in DataStage by going to your database and entering "ALTER TABLE {tablename} ADD COLUMN {the invalid columname} VARCHAR(32);"

dj352001 · Post by **dj352001** » Tue Mar 21, 2006 9:44 am

Thank-you for the reply i will give it a shot, have a good day

ArndW · Post by **ArndW** » Tue Mar 21, 2006 9:51 am

dj,

please re-read your post and mine. You are getting the error message because the column doesn't exist - my response was intentionally facetious in that it only solved the symptom and not your problem and I did not intend you to follow it. Creating an unused column doesn't fix anything - you need to remove the unused column from your DataStage job.

ArndW · Post by **ArndW** » Tue Mar 21, 2006 12:13 pm

Of course you can do this. Add the column in a transform stage and fill it with values according to your logic. It isn't declared in the source (since it doesn't exist there) but created as part of your job design.

kcbland · Post by **kcbland** » Tue Mar 21, 2006 1:29 pm

Import table definitions for source and target and load into their respective stages. Press VIEW DATA and make sure it works. Now correct your mapping.

dj352001 · Post by **dj352001** » Tue Mar 21, 2006 2:03 pm

When i click on view data everything works fine. I go back an import a new column and click view and thats when i get an invalid column name. I don't understand why i'm getting this error, the column name is there for me to import it. does this make any sense?

I_Server_Whale · Post by **I_Server_Whale** » Tue Mar 21, 2006 3:50 pm

No. It is not making any sense. Could you frame your question in a different way?

Just tell us this! How many columns does your source ODBC stage contain? And how many columns does your actual source table in the database contain?

Thanks,
Naveen.

rcil · Post by **rcil** » Tue Mar 21, 2006 4:33 pm

dj352001 wrote:When i click on view data everything works fine. I go back an import a new column and click view and thats when i get an invalid column name. I don't understand why i'm getting this error, the column name is there for me to import it. does this make any sense?

Try this:

Open your source odbc stage and goto the columns tab and see if anything written in the derivation column. If there some data (usally you will get when you import the column) then delete that and try.

Hope it works and am not sure why we have to delete that from the derivation.

rasi · Post by **rasi** » Tue Mar 21, 2006 6:00 pm

Hi dj

In your ODBC stage you have three option in General tab for "Select using"
You would normally use Generated query or User-defined SQL query to do a select from database table.
If you use Generated query you have to make sure that the column you type in your Column tab matches the Column name in the table. If you want to use additional column which doesn't exist in your table then you type the new column name and in the derivation box type the sql derivation value.
If you are using user-defined SQL then have all the columns you want to view from ODBC and type the Select SQL matching the columns you defined.

dj352001 · Post by **dj352001** » Wed Mar 22, 2006 10:47 am

I click on odbc than input, then columns and load. I select the table and than just the column i'm trying to populate. After that i click on view data and i recieve an invalid column name. Any ideas why this is happening, the column name is A_did_no. I appreciate everyone's help thank-you
DJ

ArndW · Post by **ArndW** » Wed Mar 22, 2006 10:58 am

Your metadata for the table in the Manager does not match your actual table. reload the table's metadata from the database and use that.

dj352001 · Post by **dj352001** » Wed Mar 22, 2006 11:37 am

Thanks everyone for their help I GOT IT TO WORK!!!!! have a good day