topic Re: Need Small query Help. in Application Development and Automation Discussions

Need Small query Help.

former_member196331 — Tue, 28 Jun 2016 11:34:39 GMT

hi,

I have one ZZ field in Purchase order header And also At line level.

In selection Screen i had put ZZ field if i Enter the ZZ field, The records Which are Available At header or Rows will be come into

Internal table.

Some times header is having some value like 'project1'.

If i give in the selection Screen Header part Satisfies but Or part is picking unnecessary data. Because there is No Row level Data.

How could i get , Data will be Available in both the fields some times. Some times only at header, Some times only at row level.

How could i get the Exact data.The below one is my query.

SELECT a~ebeln

a~bsart

a~wkurs

a~lifnr

a~aedat

a~zzprojectnumber AS hzzprojectnumber

a~zzprojectname AS hzzprojectname

b~zzprojectnumber AS izzprojectnumber

b~zzprojectname AS izzprojectname

a~waers

a~knumv

a~ekgrp

b~ebelp

b~menge

b~netpr

b~matnr

b~meins

b~banfn

b~bnfpo

b~werks

b~loekz

b~peinh

b~knttp "Adding On 28-05-2016.

b~txz01 INTO CORRESPONDING FIELDS OF TABLE it_final[]

FROM ekko AS a

INNER JOIN ekpo AS b ON b~ebeln = a~ebeln

* INNER JOIN EKKN AS D ON D~EBELN = A~EBELN " NEW LINE

WHERE ( a~ebeln IN s_ebeln

AND a~bsart IN s_bsart

AND a~ekgrp IN s_ekgrp "Added On 06-06-2016

AND a~lifnr IN s_lifnr

AND a~aedat IN s_date

* AND a~zzprojectnumber IN s_prjnum

* OR b~zzprojectnumber IN s_prjnum "Addin On 28-06-2016

AND b~matnr IN s_matnr

AND b~banfn IN s_banfn

AND b~bnfpo IN s_bnfpo

AND b~werks IN s_werks

AND a~zzprojectnumber IN s_prjnum )

OR ( b~zzprojectnumber in s_prjnum ). "Addin On 28-06-2016

Re: Need Small query Help.

former_member196331 — Tue, 28 Jun 2016 12:52:45 GMT

Any information Please.

Re: Need Small query Help.

former_member185116 — Tue, 28 Jun 2016 13:06:30 GMT

hi,

here the problem is with OR,

don't user OR in select

Re: Need Small query Help.

Former Member — Tue, 28 Jun 2016 13:14:44 GMT

Your question makes very little sense. Could you please take the time to write it in such a way that other users have a chance of understanding:

what your actual problem is
what you have done or tried so far to rectify it
what it is you are actually trying to achieve

Re: Need Small query Help.

Former Member — Tue, 28 Jun 2016 13:15:11 GMT

Hi,

Check this, i think that you are missing some parentheses () in the selection

WHERE ( ( a~ebeln IN s_ebeln

AND a~bsart IN s_bsart

AND a~ekgrp IN s_ekgrp "Added On 06-06-2016

AND a~lifnr IN s_lifnr

AND a~aedat IN s_date

* AND a~zzprojectnumber IN s_prjnum )

* OR ( b~zzprojectnumber IN s_prjnum "Addin On 28-06-2016

AND b~matnr IN s_matnr

AND b~banfn IN s_banfn

AND b~bnfpo IN s_bnfpo

AND b~werks IN s_werks

AND a~zzprojectnumber IN s_prjnum ) )

OR ( b~zzprojectnumber in s_prjnum ).

Regards

Miguel

Re: Need Small query Help.

former_member196331 — Tue, 28 Jun 2016 13:17:55 GMT

So, what is the solutions.

I have one solution, But i hope bad solution, Except below i need other solutions.

in selection options i remove the ZZprojectname .

So the data will be fetch and save it is temp table. Now.

I will do some operations as per my requirement

Now, i am doing.

DELETE IT_FINAL ZZPROJECTNAME NOT IN S_PROJECTNAME.

It will be work definitely But While fetching if i use ZZPROJECTNAME I hope definitely it will be reduce the fetching the time. Now the time is increases I hope am i correct.

Correct me anything is wrong.

Need other solutions.

Re: Need Small query Help.

Former Member — Tue, 28 Jun 2016 13:19:26 GMT


NewB To Abap wrote:

hi,
I have one ZZ field in Purchase order header And also At line level.
In selection Screen i had put ZZ field if i Enter the ZZ field, The records Which are Available At header or Rows will be come into
Internal table.

Some times header is having some value like 'project1'.
If i give in the selection Screen Header part Satisfies but   Or part is picking unnecessary data. Because there is No Row level Data.
How could i get , Data will be Available in both the fields some times. Some times only at header, Some times only at row level.
How could i get the Exact data.The below one is my query.

SELECT a~ebeln
          a~bsart
          a~wkurs
          a~lifnr
          a~aedat
          a~zzprojectnumber AS hzzprojectnumber
          a~zzprojectname AS hzzprojectname
          b~zzprojectnumber AS izzprojectnumber
          b~zzprojectname AS izzprojectname
          a~waers
          a~knumv
          a~ekgrp
          b~ebelp
          b~menge
          b~netpr
          b~matnr
          b~meins
          b~banfn
          b~bnfpo
          b~werks
          b~loekz
          b~peinh
          b~knttp  "Adding On 28-05-2016.
          b~txz01 INTO CORRESPONDING FIELDS OF TABLE it_final[]
                         FROM ekko AS a
                         INNER JOIN ekpo AS b ON b~ebeln = a~ebeln
*                        INNER JOIN EKKN AS D ON D~EBELN = A~EBELN " NEW LINE
                                 WHERE ( a~ebeln IN s_ebeln
                                 AND   a~bsart IN s_bsart
                                 AND   a~ekgrp IN s_ekgrp  "Added On 06-06-2016
                                 AND   a~lifnr IN s_lifnr
                                 AND   a~aedat IN s_date
*                                AND   a~zzprojectnumber IN s_prjnum
*                                OR   b~zzprojectnumber IN s_prjnum  "Addin On 28-06-2016
                                 AND   b~matnr IN s_matnr
                                 AND   b~banfn IN s_banfn
                                 AND   b~bnfpo IN s_bnfpo
                                 AND   b~werks IN s_werks
                                 AND  ( a~zzprojectnumber IN s_prjnum OR    
                                             b~zzprojectnumber in s_prjnum ).  "Addin On 28-06-2016




All the conditions above AND!!! either ekko-zzprojnumber in s_prjnum or ekpo-zzprojnumber in s_prjnum


consider the parantheses


BR Alfons

Re: Need Small query Help.

former_member196331 — Tue, 28 Jun 2016 13:27:58 GMT

Hi,

There are two tables one is ekko and ekpo.

both are having one custom field zzprojectnaame.

Some times the value will be at header level only,in other case only at row table,

last case it should be both,

Now i had inner join the both the tables using foreign key .

But the values will be fetch based on the zzprojectname. both the tables.

I tried inner join i hope it will not work, then i used Or condition But not working.

Re: Need Small query Help.

former_member196331 — Tue, 28 Jun 2016 13:33:54 GMT

Thanks for your reply. Actually you did changes What i commented line also. the actual query is like below.

What is the problem i will tell you.

If condition is satisfies header then query will be working fine.

but if the Or condition satisfies First part and will not work it is not considering the Date.

I Given in selection screen plant and date then zzprojectname. Actually it was in Row table.

So, All the entries in the system it is showing. But date is not considering. Hope you understood.

SELECT a~ebeln

a~bsart

a~wkurs

a~lifnr

a~aedat

a~zzprojectnumber AS hzzprojectnumber

a~zzprojectname AS hzzprojectname

b~zzprojectnumber AS izzprojectnumber

b~zzprojectname AS izzprojectname

a~waers

a~knumv

a~ekgrp

b~ebelp

b~menge

b~netpr

b~matnr

b~meins

b~banfn

b~bnfpo

b~werks

b~loekz

b~peinh

b~knttp "Adding On 28-05-2016.

b~txz01 INTO CORRESPONDING FIELDS OF TABLE it_final[]

FROM ekko AS a

INNER JOIN ekpo AS b ON b~ebeln = a~ebeln

WHERE ( a~ebeln IN s_ebeln

AND a~bsart IN s_bsart

AND a~ekgrp IN s_ekgrp "Added On 06-06-2016

AND a~lifnr IN s_lifnr

AND a~aedat IN s_date

AND b~matnr IN s_matnr

AND b~banfn IN s_banfn

AND b~bnfpo IN s_bnfpo

AND b~werks IN s_werks

and a~zzprojectnumber in s_prjnum )

or ( b~zzprojectnumber in s_prjnum ). "Addin On 28-06-2016

Re: Need Small query Help.

former_member196331 — Tue, 28 Jun 2016 13:37:32 GMT

HI, Alfons Eberth

Little bit understood what you said.

Query will be work in two parts, One is And other one is Or .

But i would like to combine both.

the problem is Or condition need to combine with again Some other parameters also.

Re: Need Small query Help.

RaymondGiuseppi — Tue, 28 Jun 2016 13:42:00 GMT

In your current code the first OR broke the where clause in two union select, I really do not think that it fulfills your requirement, also you could try to reformulate this one. in logical expressions parentheses and order of operator are important.

Basically a '( a~zzprojectnumber IN s_prjnum or b~zzprojectnumber IN s_prjnum )' must appear in your WHERE clause. Read again and correct your source, or reformulate you requirement.

Regards,

Raymond

Re: Need Small query Help.

former_member196331 — Tue, 28 Jun 2016 13:45:19 GMT

Ok i am checking.

Re: Need Small query Help.

former_member185116 — Tue, 28 Jun 2016 14:06:42 GMT

yes u r right,

OR in select leads to inconsistency ..

Re: Need Small query Help.

Jelena_Perfiljeva — Tue, 28 Jun 2016 21:50:29 GMT

+1 to what Gareth said. Make sure to use a descriptive subject line (small help needed? help with a small query needed? oh, it's not even a query...), clearly state the question, remove commented code and also avoid bumping up the discussion just an hour after the creation. Kindly make sure your future posts are appropriate for a professional website.

I feel you're shooting yourself in the foot with the mixed header/item design. I'd suggest to remove the field from EKKO and leave it only in EKPO. You can have a field at the header for the convenient entry but store it only at the item level (look for how the rejection reason field is done in VA02, for example).

Otherwise it's the same problem as with the darn VBPA/VBKD tables - because the item level entry takes priority, it has to be checked first and only if it's not present then header entry applies.

Re: Need Small query Help.

former_member196331 — Thu, 30 Jun 2016 07:40:11 GMT

Thanks you,Your query Little bit helpful

I was added in my query like below.

a '( a~zzprojectnumber IN s_prjnum or b~zzprojectnumber IN s_prjnum )

But in one condition it is going to fail.

suppose.

header is having

project1

Row is having

projec1 Line1

project2 Line2

projec3. Line3

In selection Criteria i was given project code like project1

Now my result will be, query is picking all rows from the document.

Because header is having project1.

I was added one more statement like.

delete table where projectname not in s_project.

Now working. I hope i dont have any other solutions.