Joining 2 tables

[Former Member]

I am using the following select statement to select from 2 joined tables into an internal table:

  select DISTINCT zgtis_qtheader~qtenbr zgtis_qtheader~prjnam
         zgtis_qtheader~status zgtis_qtheader~expdte
    into table t_qtlist
    from zgtis_qtheader
      left join zgtis_qtline on
        ( zgtis_qtheader~mandt  = zgtis_qtline~mandt and
          zgtis_qtheader~qtenbr = zgtis_qtline~qtenbr
      and zgtis_qtheader~vers   = zgtis_qtline~vers )
    where (lv_where).

The shared key to the two tables is mandt, qtenbr and vers. I keep receiving the following error message:

At least one of the field names in the SELECT clause is not unique.

Can anybody tell me what I am doing wrong? I have each field preceded by the table it is coming from.

[Former Member]

the select caluse is wrong.

[Former Member]

Hi You are not selecting any thing from zgtis_qtline

but you are joining..

regards

vijay

[Former Member]

I am really only joining because the where statement uses criteria from the qtline table, but I really only want header values in the table. I guess you are saying I have to select at least one field from both tables.

[Former Member]

Hi Denise,

Thats fine but can you paste the where condition..so that we can be sure that the error is not due to this.

[RichHeilman]

No, you don't have to select something from the other table...... this passes a syntax check.

report zrich_0001 .


data: imara type table of mara with header line.


select distinct mara~matnr mara~matkl
         into corresponding fields of table imara
                from mara
                  left join marc
                      on mara~matnr = marc~matnr
                               where mara~matnr <> space.

Regards,

Rich Heilman

[Former Member]

Yeah ,thats fine,but you didn't mention it.

regards

vijay

[Former Member]

No need to select from that, But You need to Place some where condition in side where clause.

regards

vijay

[Former Member]

  select DISTINCT zgtis_qtheader~qtenbr zgtis_qtheader~prjnam
         zgtis_qtheader~status zgtis_qtheader~expdte
<b>        zgtis_qtline~xyzzz
        zgtis_qtline~abcdde 
        zgtis_qtline~123333</b>
    into table t_qtlist
    from zgtis_qtheader
      left join zgtis_qtline on
        ( zgtis_qtheader~mandt  = zgtis_qtline~mandt and
          zgtis_qtheader~qtenbr = zgtis_qtline~qtenbr
      and zgtis_qtheader~vers   = zgtis_qtline~vers )
    where (lv_where).

[Former Member]

In BOLD one try to replace the fields of zgtis_qtline.

then your query will work.

regards

vijay

try to reward for helful answers.

[Former Member]

What does lv_where look like?

Rob

[Former Member]

Are you doing something like

where qtenbr = 'XXX'
and   vers   = 'YYY'

If so, you have to qualify these names with the table.

Rob

[Former Member]

Where clause Did you mentioned in your code is same as you mentioned , what is you are checking there...

regards

vijay

[Former Member]

Hi,

Can you describe your Where condition??I guess thats where the problem is.

[Former Member]

Hi Denise,

The onlu possible reason could be, the system not able to take the table name as alias, can try writing as,

from zgtis_qtheader as h

left join zgtis_qtline as l

otherwise, i dont see anything wrong with your select statement...

See below code...it didnt throw any error

DATA: BEGIN OF I_TAB OCCURS 0,

MATNR TYPE MATNR,

WERKS TYPE WERKS_D,

LGORT TYPE LGORT_D,

DISMM TYPE DISMM,

END OF I_TAB.

SELECT DISTINCT MARC~MATNR

MARC~WERKS

MARC~DISMM

INTO TABLE I_TAB

FROM MARC LEFT JOIN

MARD ON

( MARD_{MATNR = MARC}MATNR AND

MARD_{WERKS = MARC}WERKS ).

Regards,

Raj

Message was edited by: Rajasekhar Dinavahi

[Former Member]

Denise,

There may be nothing else wrong with your code other than your where clause. Whether you use aliases or not doesn't really matter. It is also not necessary that you select at least one field from both the tables you are trying to join. If in your where clause you are trying to compare fields from one of the tables against data in an internal table, try this:

where zgtis_qtline-"fieldname" in lv_where.

Also not that you have to compare individual fields of the table. You cannot compare everything at once.

Hope this helps.

Message was edited by: Feroz Buksh