topic Re: Joining 2 tables in Application Development and Automation Discussions

Joining 2 tables

Former Member — Mon, 05 Dec 2005 15:48:53 GMT

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.

Re: Joining 2 tables

Former Member — Mon, 05 Dec 2005 15:51:10 GMT

the select caluse is wrong.

Re: Joining 2 tables

Former Member — Mon, 05 Dec 2005 15:51:59 GMT

Hi You are not selecting any thing from zgtis_qtline

but you are joining..

regards

vijay

Re: Joining 2 tables

Former Member — Mon, 05 Dec 2005 15:53:47 GMT

  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).

Re: Joining 2 tables

Former Member — Mon, 05 Dec 2005 15:55:30 GMT

In BOLD one try to replace the fields of zgtis_qtline.

then your query will work.

regards

vijay

try to reward for helful answers.

Re: Joining 2 tables

Former Member — Mon, 05 Dec 2005 15:56:20 GMT

What does lv_where look like?

Rob

Re: Joining 2 tables

Former Member — Mon, 05 Dec 2005 16:00:20 GMT

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

regards

vijay

Re: Joining 2 tables

Former Member — Mon, 05 Dec 2005 16:01:30 GMT

Hi,

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

Re: Joining 2 tables

Former Member — Mon, 05 Dec 2005 16:02:42 GMT

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.

Re: Joining 2 tables

Former Member — Mon, 05 Dec 2005 16:06:16 GMT

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.

Re: Joining 2 tables

RichHeilman — Mon, 05 Dec 2005 16:06:27 GMT

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

Re: Joining 2 tables

Former Member — Mon, 05 Dec 2005 16:06:45 GMT

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

regards

vijay

Re: Joining 2 tables

Former Member — Mon, 05 Dec 2005 16:07:52 GMT

Are you doing something like


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

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

Rob

Re: Joining 2 tables

Former Member — Mon, 05 Dec 2005 16:09:34 GMT

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

regards

vijay

Re: Joining 2 tables

Former Member — Mon, 05 Dec 2005 16:12:31 GMT

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

Re: Joining 2 tables

Former Member — Tue, 06 Dec 2005 00:33:52 GMT

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