Select Query Problem

[Former Member]

Hi,

I have a select query problem that in selection screen when i picking the data on the basis of PO Date, all PO records showing like this:

7000002323........ 00010........

7000002324........ 00010........ 20.03.2009

7000002443........ 00010........

7000002463........ 00010........

7000002476........ 00010........

7000002485........ 00010........

but i want only this row

7000002324........ 00010........ 20.03.2009

why not other rows clear.

[former_member206377]

Paste your select query here.

[Former Member]

please check my select query...for date select query in BOLD.

***********

SELECT a~ebeln a~ebelp a~lfbnr a~werks a~budat a~belnr a~buzei a~bewtp a~menge a~dmbtr a~bwart a~rewrb a~matnr a~cpudt a~cputm b~txz01 b~netpr b~effwr INTO CORRESPONDING FIELDS OF TABLE itab FROM ( ekbe AS a INNER JOIN ekpo AS b ON a~ebeln = b~ebeln AND a~ebelp = b~ebelp ) WHERE a~ebeln IN ebeln AND a~budat IN budat AND a~belnr IN belnr AND a~matnr IN matnr and a~werks in werks AND a~bewtp = 'E' AND a~bwart IN ('101' , '102' , '105' , '106' , '122' , '123').

SORT itab BY ebeln ebelp lfbnr cpudt cputm.

LOOP AT itab.

IF itab-bwart = '102' OR itab-bwart = '106' OR itab-bwart = '122'.

itab-menge = itab-menge * ( -1 ).

itab-dmbtr = itab-dmbtr * ( -1 ).

ENDIF.

flag103 = 'N'.

AT NEW lfbnr.

flag103 = 'Y'.

ENDAT.

IF flag103 = 'Y'.

SELECT SINGLE * FROM ekpo WHERE ebeln = itab-ebeln AND ebelp = itab-ebelp.

itab2-menge = ekpo-menge.

itab2-ebeln = itab-ebeln.

itab2-ebelp = itab-ebelp.

itab2-aedat = itab-aedat.

itab2-werks = itab-werks.

itab2-grr_nbr = itab-belnr.

itab2-buzei = itab-buzei.

itab2-grr_dt = itab-budat.

SELECT SINGLE * FROM ekbe WHERE ebeln = itab-ebeln AND ebelp = itab-ebelp AND lfbnr = itab-lfbnr AND bewtp = 'Q'.

IF sy-subrc = 0.

itab2-iv_doc = ekbe-belnr.

itab2-iv_item = ekbe-buzei.

itab2-budat = ekbe-budat.

itab2-iv_qty = ekbe-menge.

itab2-iv_val = ekbe-dmbtr.

ENDIF.

itab2-matnr = itab-matnr.

itab2-txz01 = itab-txz01.

itab2-netpr = itab-netpr.

itab2-effwr = itab-effwr.

itab2-menge1 = itab-menge.

itab2-dmbtr = itab-dmbtr.

SELECT SINGLE aedat lifnr INTO (itab2-lifnr, itab2-aedat) FROM ekko WHERE ebeln = itab-ebeln.

SELECT SINGLE name1 INTO itab2-name1 FROM lfa1 WHERE lifnr = itab2-lifnr.

SELECT SINGLE xblnr ernam gjahr INTO (itab2-xblnr, itab2-ernam, itab2-gjahr) FROM ekbe WHERE ebeln = itab-ebeln AND belnr = itab2-grr_nbr.

CONCATENATE itab-belnr itab2-gjahr INTO x.

SELECT SINGLE belnr gjahr INTO (itab2-belnr1, itab2-gjahr1) FROM bkpf WHERE awkey = x AND blart = 'WE'.

SELECT SINGLE txt50 FROM skat INTO itab2-txt50 WHERE saknr = itab2-hkont AND spras EQ 'EN' AND ktopl EQ 'BIL'.

ELSE.

itab2-menge1 = itab2-menge1 + itab-menge.

itab2-dmbtr = itab2-dmbtr + itab-dmbtr.

ENDIF.

[SuhaSaha]

Dude,

Where is the AEDAT condition on EKKO ?

BR,

Suhas

[Former Member]

sorry...that was commented so i removed from here....code was like that..

SELECT SINGLE aedat lifnr INTO (itab2-lifnr, itab2-aedat) FROM ekko WHERE ebeln = itab-ebeln.

• SELECT SINGLE lifnr aedat INTO (itab2-lifnr, itab2-aedat) FROM ekko WHERE ebeln = itab-ebeln and aedat in aedat.

please check on below query...

SELECT SINGLE lifnr aedat INTO (itab2-lifnr, itab2-aedat) FROM ekko WHERE ebeln = itab-ebeln and aedat in aedat.

[SuhaSaha]

SELECT SINGLE lifnr aedat INTO (itab2-lifnr, itab2-aedat) FROM ekko WHERE ebeln = itab-ebeln and aedat in aedat.

OK .. Is AEDAT the selection-screen element ? Check in debug mode what is the value in AEDAT before the SELECT stmt & revert back.

BR,

Suhas

[Former Member]

Yes, AEDAT is the selection-screen element.

Check in debug mode what is the value in AEDAT before the SELECT stmt & revert back.

In debug mode, the value of AEDAT is 00000000 before and after select statement because that date has only 1 Purchase order and that is in 2nd position.

So when it comes on 1st place means other date PO, then AEDAT is showing 00000000 value.

and when date comes which is taking through selection screen, in 2nd place date is showing in header and body both.

But after 2nd..means from 3rd and so on...all AEDAT value is 00000000...because on that date only 2nd PO has.

[SuhaSaha]

What ????? If anyone understood the previous post please enlighten me.

In debug mode, the value of AEDAT is 00000000 before and after select statement because that date has only 1 Purchase order and that is in 2nd position

This means AEDAT is not input by the user in the selection-screen & you want to limit the PO selection as mentioned in your initial post based on the date ?

BR,

Suhas

[Former Member]

Hi,

 SELECT SINGLE lifnr aedat INTO (itab2-lifnr, itab2-aedat) FROM ekko WHERE ebeln = itab-ebeln and aedat  = aedat. 

The above query will give you the desired result as in where condition of your select query you are using IN which is for the Select-Options and for Parametes we use =.

so for,

Select-Options --> ...where aedat IN aedat

Parameters -

---

> ..where aedat = aedat.

Thanks!

[Former Member]

AEDAT is the input date given by user.

AEDAT Value : 20.03.2009 which is on selection screen.

output:

7000002323........ 00010........

7000002324........ 00010........ 20.03.2009

7000002443........ 00010........

7000002463........ 00010........

7000002476........ 00010........

7000002485........ 00010........

2nd row is the data which i want after insert input selection data 20.03.2009. Why other rows appearing data, because they are having other date..

[Former Member]

As per ur said select query

SELECT SINGLE lifnr aedat INTO (itab2-lifnr, itab2-aedat) FROM ekko WHERE ebeln = itab-ebeln and aedat = aedat.

*****

there is no value in PO date field. i used this time Parameter instead of select-options...

[Former Member]

Hi

I would like to know whether other records too contain value in the date column.or just the second row has date value rest all rows have null in that column?

Thanks

Subha

[Former Member]

Can you please elaborate a little more about your requirement.

Regards

Abhinab.

[Former Member]

Hi ,

This is due to the condition that PO Date criteria is not satisified by all the other records.

Please check this and confirm.

Hope this resolves your issue.