select query the same variable different table problem

[former_member780404]

Hi friends,

By 6 table inner join , take the data . My problem CRDAT and  CRUZT  always coming from  ZQM_T001 . I don't understand why that.

Already thanks for your help.

SELECT DISTINCT ZPP_T004~CRDAT SNAME ZPP_T004~CRUZT ARBPL ZPP_T004~MATNR MAKTX ZTBARKOD BARKOD ZTELCAP1 ZTELCAP2 ZMUKAVEMET ZBURMA ZHIZ LOW HIGH ZPARTI ZSABUN ZDRESAJ ZACIKLAMA

                 ZPP_T004~AUFNR KANTAR KADET SEPET BRAGR NTAGR TEYIT ZQM_T001~CRDAT ZQM_T001~CRUZT

                 INTO CORRESPONDING FIELDS OF TABLE gt_tablo UP TO SAYI ROWS FROM ZQM_T001

                 INNER JOIN ZPP_T004 ON  ZQM_T001~ZTBARKOD = ZPP_T004~CHARG

                 INNER JOIN MAKT ON ZPP_T004~MATNR = MAKT~MATNR

                 INNER JOIN AUFK ON ZPP_T004~AUFNR = AUFK~AUFNR

                 INNER JOIN PA0001 ON ZPP_T004~PERNR = PA0001~PERNR

                 INNER JOIN ZPP_T001 ON ZPP_T001~MKVNO = AUFK~MKVNO

                 "FOR ALL ENTRIES IN TAB_ZQM_T001 WHERE ZQM_T001~ZTBARKOD = TAB_ZQM_T001-ZTBARKOD

                 where ZTBARKOD IN TBARKOD AND ZPP_T004~CRDAT IN KKTARIH AND ZPP_T004~CRUZT IN KKSAAT

                 AND BARKOD IN KBARKOD AND ZPP_T004~AUFNR IN SIPNO AND ZPP_T004~MATNR IN MALZNO AND ZPARTI IN PARTINO

                 AND SNAME IN URTPERNO AND ZQM_T001~CRDAT IN TRTMTAR AND ZQM_T001~CRUZT IN TRTMSAAT  .

[matt]

You are using INTO CORRESPONDING and your select contains TWO instances each of CRDAT and  CRUZT. The system first puts in CRDAT and CRUZT from ZQM_T001 into those fields, and then the ones from ZQM_T004.

[custodio_deoliveira]

It is as Matthew says, but the other way round (first from ZQM_T004 then from ZQM_T001, overwriting ZQM_T004). In case you need CRDAT/CRUZT from both tables, you will have to:

1) add 2 new fields to your internal table definition, let's call them T004_CRDAT AND t004_CRUZT

2) Add alias in the select as below:

SELECT DISTINCT zpp_t004~crdat AS t004_crdat sname zpp_t004~cruzt AS t004_cruzt arbpl zpp_t004~matnr maktx

                 ztbarkod barkod ztelcap1 ztelcap2 zmukavemet zburma zhiz low high zparti zsabun zdresaj zaciklama

                 zpp_t004~aufnr kantar kadet sepet bragr ntagr teyit zqm_t001~crdat zqm_t001~cruzt

                 INTO CORRESPONDING FIELDS OF TABLE gt_tablo UP TO sayi ROWS FROM zqm_t001

                 INNER JOIN zpp_t004 ON  zqm_t001~ztbarkod = zpp_t004~charg

                 INNER JOIN makt ON zpp_t004~matnr = makt~matnr

                 INNER JOIN aufk ON zpp_t004~aufnr = aufk~aufnr

                 INNER JOIN pa0001 ON zpp_t004~pernr = pa0001~pernr

                 INNER JOIN zpp_t001 ON zpp_t001~mkvno = aufk~mkvno

                 "FOR ALL ENTRIES IN TAB_ZQM_T001 WHERE ZQM_T001~ZTBARKOD = TAB_ZQM_T001-ZTBARKOD

                 WHERE ztbarkod IN tbarkod AND zpp_t004~crdat IN kktarih AND zpp_t004~cruzt IN kksaat

                 AND barkod IN kbarkod AND zpp_t004~aufnr IN sipno AND zpp_t004~matnr IN malzno AND zparti IN partino

                 AND sname IN urtperno AND zqm_t001~crdat IN trtmtar AND zqm_t001~cruzt IN trtmsaat  .

Regards,

Custodio

[former_member620069]

Hi,

Why don't you go for for all entries insted of inner join?

Regards,

Srini.

[custodio_deoliveira]

This is not the problem.

Also:

[matt]

Precisely. I was waiting for that kind of answer! I wonder who will be first with "never use more than 3 inner joins" - another popular myth.

[Former Member]

Hi,

Why you are picking same fields from different tables and passing the same fields to same internal table ?

Here the problem is you are selecting the fields ZPP_T004~CRDAT ZPP_T004~CRUZT

ZQM_T001~CRDAT ZQM_T001~CRUZT

from two tables and using INTO CORRESPONDING due to this you are getting wrong data. It's better to select those two fields from one table only either ZPP_T004 OR ZQM_T001 and check.

Try this and revert.

Regards

Mani

[custodio_deoliveira]

Nagamani kola wrote:

Hi,
Here the problem is you are selecting the fields ZPP_T004~CRDAT ZPP_T004~CRUZT
ZQM_T001~CRDAT ZQM_T001~CRUZT
from two tables and using INTO CORRESPONDING due to this you are getting wrong data.

Isn't this exactly what Matthew said above?

Nagamani kola wrote:

 It's better to select those two fields from one table only either ZPP_T004 OR ZQM_T001 and check.

Try this and revert.

What if Alper needs the fields from both tables?!?!

[matt]

It is what I wrote above. I guess some people either don't read earlier answers or are hoping to get a "helpful answer" award.

[Former Member]

I hope Here everybody tying to help other , Not for Rewards .

[matt]

I hope that too. Unfortunately my experience is that it isn't always the case. I'm glad you're not one of them - though please do check that your answer doesn't duplicate what has already been posted.

[rosenberg_eitan]

Hi,

If you can you can use the "Inline Declarations" (I am on 740 sp 008) .

For example:

SELECT * INTO TABLE @DATA(it_scarr_1)

         FROM scarr

         JOIN sflight ON scarr~carrid EQ sflight~carrid .

This will create this:

line 1 :

Regards.

[former_member780404]

Thanks everbody for advice.

I could not write during the day for lack of internet.

Custodia olivia second  advice  I am trying . I thing it was  Thank you very much.

[former_member780404]

Hi Srivinas ,

My english not very well . I understand my , why  I don't use for all entries take all data ,you ask .

I use to try for all entries but  I couldn't for all entries both filtering.