topic Re: Regarding select statement in Application Development and Automation Discussions

Regarding select statement

Former Member — Tue, 07 Apr 2009 12:06:44 GMT

hi,

i hav written select as below

SELECT a~ebeln "PO Number

a~bsart "Document type

a~bsakz "Control indicator for PO/STO

a~aedat "Date Created on

a~ekorg "Purchase Organisation

a~lifnr "Supplier

a~reswk "Supplying site

b~ebelp "PO item Number

b~matnr "Article Number

b~werks "Receiving site

b~menge "PO Quantity

c~mtart "Article Type

c~matkl "Merchandise Category

c~zeiar "Department

c~zeifo "Sub Brand

c~labor "Gender

c~saiso "Season Category

FROM ( ( ekko AS a INNER JOIN ekpo AS b ON a_{ebeln = b}ebeln )

INNER JOIN mara AS c ON b_{matnr = c}matnr )

INTO TABLE it_ekko

WHERE a~aedat IN s_date

AND a~ebeln = p_ponum

AND ( a_{bsart EQ 'NB' OR a}bsart EQ 'UDS')

AND ( a_{bsakz EQ ' ' OR a}bsakz EQ 'T' )

OR a~ekorg EQ p_purorg

OR a~reswk EQ p_spsite

AND a~lifnr EQ p_vendor

AND b~werks EQ p_resite.

now the issue is if i give date it should bring all the purchase orders in that date range....

if i give both po and date it should give that particular record............

but if i give onli date it is giving no records..........thogh there are records........

Re: Regarding select statement

Former Member — Tue, 07 Apr 2009 12:09:21 GMT

Hi,

Please change the following in where condition of SQL statement:

a~ebeln = p_ponum

Ranges: ra_ponum for p_ponum.

ra_ponum-low = p_ponum.

ra_ponum-sign = 'I'.

ra_ponum-option = 'EQ'.

append ra_ponum.

Change this to a~ebeln in ra_ponum.

Best Regards,

Suresh

Re: Regarding select statement

Former Member — Tue, 07 Apr 2009 12:11:46 GMT

Hi,

in this case, u should write three selects or make date & PO are mandatory.

if NOT ekko-ebeln is initial AND not ekko-date is initial.

select as u wrote.

elseif ekko-ebeln is not initial.

select......................

..............................

remove date = ekko-date condition

elseif ekko-date is not initial.

select......................

..............................

remove ebeln = ekko-ebeln condition

endif.

Hope it helps!!

Regards,

Pavan

Re: Regarding select statement

Former Member — Tue, 07 Apr 2009 12:13:28 GMT

use select-options instead of parameters.

Regards,

Lalit Mohan Gupta.

Re: Regarding select statement

Former Member — Tue, 07 Apr 2009 12:14:18 GMT

Hi,

First be shure that the criteria you specify in your select statement should give a result. So you need to read sy-subrc (= return value after abap statements).

sy-subrc = 0 records are found,

sy-subrc = 4 no records are found

When it's 0 there are records found and somehow not displayed.

When 4, you need to adjust the where clause.

Best regards,

Guido Koopmann

Re: Regarding select statement

Former Member — Tue, 07 Apr 2009 12:14:52 GMT

date is mandatory and po is not mandatory.........but if i give date or date range it should give the pos in between that range

Re: Regarding select statement

SuhaSaha — Tue, 07 Apr 2009 12:17:41 GMT


> date is mandatory and po is not mandatory.........but if i give date or date range it should give the pos in between that range

Hello Friend,

No it wont. Because when you DONOT input any p_ponum the compiler tries to search the Database for PO's where

a~ebeln = p_ponum "which is blank

It does not find any entry & returns you no value.

Hope this is clear.

Suhas

Re: Regarding select statement

Former Member — Tue, 07 Apr 2009 12:22:33 GMT

then what might be my select statement???????????

Re: Regarding select statement

Former Member — Tue, 07 Apr 2009 12:25:27 GMT

then what might be my select statement............if date range is given all pos in that range should be displayed where as if they give date and po no then that po details onli shuld be displayed

Re: Regarding select statement

Former Member — Tue, 07 Apr 2009 12:26:38 GMT

Hi,

Try code written below for your requirement.

DATA: R_EBELN TYPE EKKO-EBELN.

IF S_EBELN IS INITIAL.

SELECT EBELN

FROM EKKO

INTO TABLE I_EBELN

WHERE AEDAT IN S_AEDAT

IF SY-SUBRC EQ 0.

SORT I_EBELN BY EBELN.

DELETE ADJACENT DUPLICATES FROM I_EBELN COMPARING EBELN.

LOOP AT I_EBELN. INTO.

R_EBELN-SIGN = 'I'.

R_EBELN-OPTION = 'EQ'.

R_EBELN-LOW = I_EBELN-EBELN.

APPEND R_EBELN

ENDLOOP.

ENDIF.

ELSE.

R_EBELN[] = S_EBELN[].

AND WHILE SELECTING THE DATA FROM TABLE USE R_EBELN INSTEAD OF S_EBELN.

HOPE THIS WILL SOLVE YOUR PROBLEM.

Best Regards,

Deepa Kulkarni

Re: Regarding select statement

Former Member — Tue, 07 Apr 2009 12:27:59 GMT

ebeln is not selet-option it is parameter where as date is select option

Edited by: NEED HELP on Apr 7, 2009 5:58 PM

Re: Regarding select statement

Former Member — Tue, 07 Apr 2009 12:29:58 GMT

Hi ,

SELECT a~ebeln "PO Number

a~bsart "Document type

a~bsakz "Control indicator for PO/STO

a~aedat "Date Created on

a~ekorg "Purchase Organisation

a~lifnr "Supplier

a~reswk "Supplying site

b~ebelp "PO item Number

b~matnr "Article Number

b~werks "Receiving site

b~menge "PO Quantity

c~mtart "Article Type

c~matkl "Merchandise Category

c~zeiar "Department

c~zeifo "Sub Brand

c~labor "Gender

c~saiso "Season Category

FROM ( ( ekko AS a INNER JOIN ekpo AS b ON a_{ebeln = b}ebeln )

INNER JOIN mara AS c ON b_{matnr = c}matnr )

INTO TABLE it_ekko

WHERE a~aedat IN s_date

AND a~ebeln = p_ponum

AND ( a_{bsart EQ 'NB' OR a}bsart EQ 'UDS')

AND ( a_{bsakz EQ ' ' OR a}bsakz EQ 'T' )

OR a~ekorg EQ p_purorg

OR a~reswk EQ p_spsite

AND a~lifnr EQ p_vendor

AND b~werks EQ p_resite.

I will suggest avoid too many tables in inner join. you can use inner join with in header and item table . and then select from mara by using for all entries.

In select query if you use parameter with blank values, it won't gives any output. Either you can use select option with no interval for multiple value and with both no interval and no extension for single value.

Also you can use ranges instead of giving values directly to the select queries.

Let's try with this method. Hope you will solve your problem.

Feel free to ask if you have any issues.

Regards,

Vijay

Re: Regarding select statement

Former Member — Tue, 07 Apr 2009 12:30:29 GMT

Hi,

But for the given date range, there might be multiple orders.

how will you take that as parameters.

better make it select-options only.

Regards,

Deepa Kulkarni

Re: Regarding select statement

Former Member — Tue, 07 Apr 2009 12:32:13 GMT

Hi,

you can use the solution which i have suggested. It will surely work.

Still if it doesn't work, then, there are someother things in where condition which you have declared as EQ. Change all the parameters to select options and declare as IN.

Otherwise, use ranges for the parameters.

Best Regards,

Suresh

Re: Regarding select statement

Former Member — Tue, 07 Apr 2009 12:36:32 GMT

You have to change all parameters to select-options. Only then the select will work. IN case of Parameters, the select will fail if it doesnt find any matching records. SO even if you give date range, the select will find for other parameters and on the whole the select itself will fail. If you make the other parameters as select options, even if no value is found, select will just bypass those conditions and will fetch values corresponding to date range only. Change the parameters to select options, this is the only way to fetch values for date range or ponum.

Re: Regarding select statement

Former Member — Tue, 07 Apr 2009 13:16:46 GMT

Try to code it like below,

if NOT p_ponum is initial .

SELECT a~ebeln "PO Number

a~bsart "Document type

a~bsakz "Control indicator for PO/STO

a~aedat "Date Created on

a~ekorg "Purchase Organisation

a~lifnr "Supplier

a~reswk "Supplying site

b~ebelp "PO item Number

b~matnr "Article Number

b~werks "Receiving site

b~menge "PO Quantity

c~mtart "Article Type

c~matkl "Merchandise Category

c~zeiar "Department

c~zeifo "Sub Brand

c~labor "Gender

c~saiso "Season Category

FROM ( ( ekko AS a INNER JOIN ekpo AS b ON a_{ebeln = b}ebeln )

INNER JOIN mara AS c ON b_{matnr = c}matnr )

INTO TABLE it_ekko

WHERE a~aedat IN s_date

AND a~ebeln = p_ponum

AND ( a_{bsart EQ 'NB' OR a}bsart EQ 'UDS')

AND ( a_{bsakz EQ ' ' OR a}bsakz EQ 'T' )

OR a~ekorg EQ p_purorg

OR a~reswk EQ p_spsite

AND a~lifnr EQ p_vendor

AND b~werks EQ p_resite.

elseif p_ponum IS INITIAL.

SELECT a~ebeln "PO Number

a~bsart "Document type

a~bsakz "Control indicator for PO/STO

a~aedat "Date Created on

a~ekorg "Purchase Organisation

a~lifnr "Supplier

a~reswk "Supplying site

b~ebelp "PO item Number

b~matnr "Article Number

b~werks "Receiving site

b~menge "PO Quantity

c~mtart "Article Type

c~matkl "Merchandise Category

c~zeiar "Department

c~zeifo "Sub Brand

c~labor "Gender

c~saiso "Season Category

FROM ( ( ekko AS a INNER JOIN ekpo AS b ON a_{ebeln = b}ebeln )

INNER JOIN mara AS c ON b_{matnr = c}matnr )

INTO TABLE it_ekko

WHERE a~aedat IN s_date

AND a~ebeln = p_ponum (Remove this in where condition)

AND ( a_{bsart EQ 'NB' OR a}bsart EQ 'UDS')

AND ( a_{bsakz EQ ' ' OR a}bsakz EQ 'T' )

OR a~ekorg EQ p_purorg

OR a~reswk EQ p_spsite

AND a~lifnr EQ p_vendor

AND b~werks EQ p_resite.

endif.

now, if PO is not initial it will select only that particular PO in the given date range.

if PO is initial it will select all the PO's in the given date range.

Hope it helps!!

Regards,

Pavan