Duplicate Attribute Name in HANA procedure

[Former Member]

Hi,

I'm a beginner and just wanted to ask a basic question.

I have two tables

1. Employee (emp_id, emp_name, emp_grp, salary, level, skill)
2. Bonus (skill, level, bonus)

in my procedure I'm joining these two table and storing in variable.

I'm getting Duplicate attribute name error.

I assume this is because I had skill and level as common name in both tables.

After changing the column names in Bonus table to B_SKILL and B_LEVEL, issue was resolved.

But I believe there has to be a better way to fix this because changing the column name will not be feasible every time.

[michael_eaton3 · Accepted solution]

Hello

There is indeed a solution to this - explicitly listing the columns you require, and column aliases.

You haven't showed SQL, however I guess it is something like this

select

  *

from

  employee, bonus

where

  employee.skill = bonus.skill and employee.level = employee.level

This won't work as the column skill exists in both tables, and using * isn't a good idea anywhere except when messing about.  The easiest solution would be to explicitly list the columns in the select clause as below (I added a table alias too, you know, just for fun).  There's no need to pull the skill column twice, as it will have the same value in this case

select

  e.emp_id, e.skill, e.level, b.bonus

from

  employee e, bonus b

where

  e.skill = b.skill and e.level = b.level

In some cases you might want the values from both skill columns (an outer join for example), then in which case you can alias the columns, as below

select

  e.emp_id, e.skill as emp_skill, b.skill as bonus_skill, e.level, b.bonus

from

  employee e, bonus b

Michael

[Former Member · Accepted solution]

Thanks for the detailed answer Michael this is exactly what I needed.

Just wanted to know one more thing if we have huge number of columns then explicitly calling them will be big line of column name when I require 199 out of 200 columns. Is there a simpler way for this as well ?

[rejiomusic]

Nope!

If you have 100-200 fields, you should always be conservative of what you are picking up.

Conservativeness comes with more specific selections.

with *, you become very complacent about what you need and do a full selection, which is not a good approach. Rather pick only the fields you need.

Design in detail, and then code. it will help in overall development thought process.

[rejiomusic]

Hey Shubham,

If you are picking up  the same columns from both the tables (which I feel is not needed, maybe in your scenario it is valid) in the selection list, then you would have to give different names to the columns.

Normally if you have a two tables with the same column, while selection we will pick up field from the most relevant table than from both.

for e.g:

either SELECT ....... a.skill, a.level from ....

or

SELECT .... b.skill b.level from......

or mix match.

in any case employing above technique, you wont have this problem.

Let me know if I understand you issue right and whether the answer was relevant.

Thanks,

Reji

[Former Member]

Hi Reji,

Correct, since I was using Select * from table I had the duplicate column error. If I modify the code as you suggested it will be fine.

Just wanted to understand If we have 200 columns in each table and I'm extracting almost all columns except one or two do I need to write Select a.skill, a.level.... till 200 or is there a better approach to exclude one or two.

[BenedictV]

Hi Shubham,

That is how SQL works. You have to specify the table alias to let the system know which table the particular field has to be picked from. Otherwise the system has no way of knowing which table to select the fields from when you join multiple fields in a SELECT statement.

Benedict

[anindya_bose]

Hi

Can you post the procedure code ?

Regards

Anindya