Hi All,

It is me again. :-) Here is what I want to do, but not sure if it is possible. Need your professional advices.

I have a database table(Health_Table) that I extracted from a mainframe. In this table, the ONLY data I have is:

Health_ID Deduction_Amount
02BEE 10.00
03BEA 20.00

The problem with this is I know what each Health_ID stands for by heart. However, if I give this report to someone else, they will not be able to figure it out. Therefore, my question is, can I create an additional field(such as Health_ID_Description) in this table, AND then write some codes(or stored procedures) in SQL to add in the descriptions? If it is possible, do you have any idea how?

If it is NOT possible, is the only solution is to create MY OWN table with the descriptions and link Health_table and my table together?

Thank you for your help!!! :-)

Leon

If there are only a handful - use a CASE statement inside your SQL - however; if there are more then create a table with the id as the key and a description field. You may need to add dates and/or a different primary key depending on if they ID's map to different names or get deleted.

_________________________
Joe Horton
Database Developer / Software Engineer
WISHA/Legal Services Software Development
Department of Labor and Industries
Voice (360) 902-5928 fax (360) 902-6200

Hi Leon (It is me again;)),

That is possible. But it depends on the volume of data in the table.

If I am right, as you say You can open the table in EM and manually add a field Health_Desc and add data to these two records. And there on if any new record is to be added you can put the description for that too from the client app or using stored procs.

If the volume of data is huge and ant alteration to this table would affect the live apps or something to that effect, you can always go for the second option that you suggested. Create another table and heelp the same HEALTH_ID as reference there, and add health description to that correspondingly and then you got to make changes in all places where you generate reports using Health_Table, to use the HEALTH_DESC table too.

You can decide which one is comfortable and does not affect the existing setup much.

But I would always suggest you to go for the first solution, considering the Integrity and maintenance factors in the future.

Hope that helps.
Cheers!

_________________________
-Vijay G
Strive for Perfection

Thank you Joe and ViJay for your advise! I think the solution i'll have to use is to create a new table with a Health_ID and Health_Description colunm. The reason is, I have only 10 different Health_ID. But when I run a query against the mainframe, I get thousands of employees data, each with a Health_ID associated with them.

Thanks again!

Leon

I dont understand, why you want to have a new table having this as a reason.

That doesn't matter, whatever the number of employees records be associated with Health_ID, but still you can alter the table to add one more field as description and populate data for those 10 records. As I see no other changes needed after this.

In case you create another table, then you got to make join with this new table just for the sake of getting the description. If such things were already done, then you got to recode them.

Anyways the call is yours.;)
Cheers!

_________________________
-Vijay G
Strive for Perfection

Vijay,

that was what I meant, alter the Health_table to add one more column for the description (since I only have 10 records or Helath_ID). Then when I get the employee records(which is about 13,000 records), I can link them with the Health_table to get the description. In this case, the Health_Table serves as a 'key' table. Make sense?

Thanks again!

Leon

Yes. Makes sense.

Seeing this I thought you were going for a new table just for the sake of having Description. Thats why the confusion.

Cheers!

_________________________
-Vijay G
Strive for Perfection