Dump in Inner join

[Former Member]

Hi ,

I am selecting data (Around 100 fields)from 4 different tables(a,b,c,d) using INNER JOIN .I get the Runtime error (DUMP) SAPSQL_AMBIGUOUS_FIELDNAME .Since one of the selection field is availble in both c & d tables but i need that to be selected from c alone.

Now i have used Select * to select the fields.

SELECT *
INTO CORRESPONDING FIELDS OF TABLE int_tab
FROM a AS a INNER JOIN b AS b
ON a~key1 = b~key1
INNER JOIN c as c
on c~key2 = a~key2
INNER JOIN d as d 
on d~key2 = a~key2
where......

thanks,

srini

[Former Member]

Hi ,

I am selecting data (Around 100 fields)from 4 different tables(a,b,c,d) using INNER JOIN .I get the Runtime error (DUMP) SAPSQL_AMBIGUOUS_FIELDNAME .Since one of the selection field is availble in both c & d tables but i need that to be selected from c alone.

Now i have used Select * to select the fields.

SELECT *
INTO CORRESPONDING FIELDS OF TABLE int_tab
FROM a AS a INNER JOIN b AS b
ON a~key1 = b~key1
INNER JOIN c as c
on c~key2 = a~key2
INNER JOIN d as d 
on d~key2 = a~key2
where......

thanks,

srini

[Former Member]

Hi,

Split your select ... write innerjoin on 3 tables and write for all entries on the 4th table , or give the individual field names

while selecting.

Regards,

Srini.

[Former Member]

Hi Sri

I would recomend to split your selection query.

there are some common fields in all the four tables.

create 4 diffrent internal table and use for all entries statement.

and select only the fields you require do not use select *.

because select * will fetch all the fields from all the 4 table and because fields are common hence the dump is obvious.

So use for all entries statement

Thanks

Lalit

[Former Member]

Hi,

First of all it is not recommended to use inner join on 4 tables also while using join u have to give the field name along with the table name to specify from which table the field should be picked u can use select statement as below

select a~fieldname(field name from table a)

a~fieldname(field name from table a)

b~fieldname(field name from table b)

c~fieldname(field name from table c)

and so on

rgds

shivraj

[Clemenss]

Hi,

ShrivajSina is right: You can not use SELECT * if you have fields of the same name in more than one of the joined tables (which is almost always the case).

The dataase interface always translates the * into all available field names resulting in a big performance waste. This is done out of pure laziness with upward compatibility guaranteed.

SELECT tab_a~field_1 tab_b~field_2
INTO CORRESPONDING FIELDS OF TABLE int_tab
FROM tab_a INNER JOIN tab_b
ON tab_a~key1 = tab_b~key1
INNER JOIN tab_c
on tab_c~key2 = tab_a~key2
INNER JOIN tab_d 
on tab_d~key2 = tab_a~key2
where......

Also, in ABAP, you do not need to specify an ALIAS with keyword AS. This is only required if you join a table with itself.

Code is more transparent if you do not use ALIAS.

Regards,

Clemens

[ThomasZloch]

From what I have observed (SQL traces, release 7.00), the DBI converts the * not into all fields of the involved DB tables, but rather all fields of int_tab (as per declaration), which means usually significantly less fields than in the DB tables. So there is much less of a performance problem than commonly assumed.

There is still the problem with ambiguous field names, however I have not seen the short dump SAPSQL_AMBIGUOUS_FIELDNAME in his context, so I wonder if this occurred in previous releases where the logic I described above might not have been in effect yet.

Thomas

[Clemenss]

Thank you Thomas,

that's new to me that the DBI converts the * to the fields of the target area. I will check with our system.

Regards,

Clemens

[former_member186741]

as others have said, state the exact source of the fields you wish to retrieve. This is always good practice even if a shortdump does not eventuate as you are in control of the sourceing of each of the fields rather han being at the whim of the sql making an arbitrary decision. It also makes it easier for reader's of the sql to understand without them having to delve into the structure of the tables to discover where they all come from.

I agree with clemens with the use of aliases. I find it more confusing working out what a~ and b~ are rather than seeing the actual names like mara~ and marc~. But I do use aliases sometimes when I have very long table names which make the qualifications unwieldy.

[Former Member]

actually I am in favor of the a~ b~ aliases, simply because the fieldlist is much shorter an easier to read, and ou should be aware that only old tables have the very short names, newer ones have much longer names.

Additionally the a, b, c gives an intended order, whcih does not have to identical to the actually executed order, but it is good style if the order is one, which makes sense.

Actually I do not understand the original question, the dump of the system is perfectly o.k. for me, if there are 2 fields with the same name, then you MUST specif which one you need. No dump would be a bug, because there is 50% chance that you get the wrong field and an unknown chance that it is different from the other.

Siegfried

[Clemenss]

As the question is still marked as open, let me add another cent:

It is true that the field list takes more space if you do do not use an alias. I prefer to gain transparency just by formatting, i.e.

SELECT 
  tab_a~field_1 
  tab_a~field_2 
  tab_a~field_3 
  tab_b~field_4
  tab_b~field_5
  tab_b~field_6
  tab_c~field_7
  tab_c~field_8
  tab_d~field_9
INTO CORRESPONDING FIELDS OF TABLE int_tab
FROM tab_a INNER JOIN tab_b
  ON tab_a~key1 = tab_b~key1
INNER JOIN tab_c
  on tab_c~key2 = tab_a~key2
INNER JOIN tab_d 
  on tab_d~key2 = tab_a~key2
where......

This way you get it on first glance as opposed to

SELECT a~field_1 a~field_2 a~field_3 b~field_4 b~field_5 b~field_6 c~field_7 c~field_8 d~field_9 INTO CORRESPONDING FIELDS OF TABLE int_tab

With the new editor we can see more lines anyway, remember the old step-loop-editor?

Regards,

Clemens

[Former Member]

Hi Sri,

Give the fieldnames in the select query instead of ' * '. Because you are fetching from four different tables. How the control knows Which all fileds to be selected from which table.? So we need to make it clear by giving the fieldnames witlh table alias.

Like select a~ xxxx

a~key1

b~key1

b~yyyy

INTO CORRESPONDING FIELDS OF TABLE int_tab

FROM a AS db_tab1 INNER JOIN b AS db_tab2

ON a_{key1 = b}key1

where conditions.

Now you are making the control clear to fetch XXXX and key1 from a(db_tab1) and key1, YYYY from table b(db_tab2). though you need all the fileds, you have to give seperately instead of *.

Regards,

Chellamma Chandrasekar.

[Clemenss]

Hi Chellama,

although this thread is already passed, you might be interested in understanding the use of an ALIAS in open SQL clauses:

You can use the AS clause to define a new name (alias) for a fieldname or table name.

Use select a b c as d into corresponding fields of destination if you want the values of fields a and b to be moved to fields a and b of the destination structure but the value of field c to go to field d of destination structure.

For table names it is the same logic. Yor sample code

select a~ xxxx
a~key1
b~key1
b~yyyy
INTO CORRESPONDING FIELDS OF TABLE int_tab
FROM a AS db_tab1 INNER JOIN b AS db_tab2
ON a~key1 = b~key1

is syntactically wrong because you defined the alias db_tab1for table a and db_tab2 for table b. Now the fields a_{key1 and so on are unknown, you could use only db_tab1}key1 etc.

In Join conditions, you will almost never need an alias clause. Corrected code is

 
select a~xxxx
a~key1
b~key1
b~yyyy
INTO CORRESPONDING FIELDS OF TABLE int_tab
FROM a
INNER JOIN b 
ON a~key1 = b~key1

This is much more transparent for the one who has to read and understand the code.

Regards,

Clemens

[Former Member]

Hi Clemens,

I got it. Thank you .

Regards,

Chellamma Chandrasekar

[ravi_lanjewar]

Hi,

May people had discussion on your problem, I am not doing any think new, SAPSQL_AMBIGUOUS_FIELDNAME error can happend only due to name of fields.

In your SQL you are mention * (i.e. All fields for selection ) which mean diffrent table have same fields name, So you have to define the required fields name instant of * with alias name.

In your internal table have only one fields for that column and in your select having select * having multiple fields. You have to define fields name which your required as per you internal table.