topic Re: select query the same variable different table problem in Application Development and Automation Discussions

select query the same variable different table problem

former_member780404 — Mon, 18 May 2015 15:25:14 GMT

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 .

Re: select query the same variable different table problem

matt — Mon, 18 May 2015 16:59:08 GMT

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.

Re: select query the same variable different table problem

custodio_deoliveira — Tue, 19 May 2015 01:04:26 GMT

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

Re: select query the same variable different table problem

former_member620069 — Tue, 19 May 2015 06:55:36 GMT

Hi,

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

Regards,

Srini.

Re: select query the same variable different table problem

custodio_deoliveira — Tue, 19 May 2015 07:11:56 GMT

This is not the problem.

Also:

Re: select query the same variable different table problem

Former Member — Tue, 19 May 2015 07:17:43 GMT

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

Re: select query the same variable different table problem

rosenberg_eitan — Tue, 19 May 2015 07:46:58 GMT

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.

Re: select query the same variable different table problem

custodio_deoliveira — Tue, 19 May 2015 08:14:36 GMT


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?!?!

Re: select query the same variable different table problem

matt — Tue, 19 May 2015 09:17:11 GMT

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.

Re: select query the same variable different table problem

matt — Tue, 19 May 2015 09:17:59 GMT

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.

Re: select query the same variable different table problem

Former Member — Tue, 19 May 2015 09:22:25 GMT

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

Re: select query the same variable different table problem

matt — Tue, 19 May 2015 09:29:25 GMT

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.

Re: select query the same variable different table problem

former_member780404 — Tue, 19 May 2015 19:04:16 GMT

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.

Re: select query the same variable different table problem

former_member780404 — Tue, 19 May 2015 19:08:56 GMT

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.