topic Re: abap : problem in select query in Application Development and Automation Discussions

abap : problem in select query

Former Member — Wed, 19 Dec 2012 06:14:26 GMT

Dear Friends ,

I have a problem to use select query , I have my select query like that .

SELECT BELNR BUDAT BLART GJAHR AUGDT AUGBL FROM BSAS INTO CORRESPONDING FIELDS OF TABLE IBSIG

where BUKRS = '1100'

AND BUDAT IN S_BUDAT

AND AUGDT ?

AND GJAHR IN S_GJAHR

AND blart in ('RE','WE','WA','JV')

%_HINTS ORACLE 'index(BSAS"ZNK")'.

here I have a document ,

Doc no AUGDT BUDAT

00200588 05.10.2012 10.05.2012

now In input parameters I Pass the values in S_BUDAT ( 08.05.2012 to 11.05.2012 ) then this documents should show in report

that mean If the augdt is greater than budat then it should be shown in the report , For this I have used the query and that is

working fine.

SELECT BELNR BUDAT BLART GJAHR AUGDT AUGBL FROM BSAS INTO CORRESPONDING FIELDS OF TABLE IBSIG

where BUKRS = '1100'

AND BUDAT IN S_BUDAT

AND ( AUGDT GT S_BUDAT-LOW AND AUGDT GT S_BUDAT-HIGH )

AND GJAHR IN S_GJAHR

AND blart in ('RE','WE','WA','JV')

%_HINTS ORACLE 'index(BSAS"ZNK")'.

now In input screen I am giving the date not in range only like (11.05.2012) then documents should be shown in report

but by above query it is not shown while the Augdt is greater then budat ,

In select query what I have to use for augdt to fullfill both above conditions .

hope you got me .

Regards,

Rihan

Re: abap : problem in select query

kakshat — Wed, 19 Dec 2012 06:24:01 GMT

When you give a single value for a select-option parameter in the screen, you would not have values in both S_BUDAT-LOW and S_BUDAT-HIGH but only in one of them. So you would need to check which ones has a value and then do the comparison.

Re: abap : problem in select query

former_member392866 — Wed, 19 Dec 2012 06:25:13 GMT

Rihan,

loop at S_BUDAT.

AT FIRST.

if S_BUDAT-HIGH IS INITIAL.

SELECT BELNR BUDAT BLART GJAHR AUGDT AUGBL FROM BSAS INTO CORRESPONDING FIELDS OF TABLE IBSIG

where BUKRS = '1100'

AND BUDAT IN S_BUDAT

AND AUGDT EQ S_BUDAT-LOW

AND GJAHR IN S_GJAHR

AND blart in ('RE','WE','WA','JV')

%_HINTS ORACLE 'index(BSAS"ZNK")'.

ELSE.

SELECT BELNR BUDAT BLART GJAHR AUGDT AUGBL FROM BSAS INTO CORRESPONDING FIELDS OF TABLE IBSIG

where BUKRS = '1100'

AND BUDAT IN S_BUDAT

AND ( AUGDT GT S_BUDAT-LOW AND AUGDT GT S_BUDAT-HIGH )

AND GJAHR IN S_GJAHR

AND blart in ('RE','WE','WA','JV')

%_HINTS ORACLE 'index(BSAS"ZNK")'.

ENDIF.

ENDAT.

ENDLOOP.

Use this code.. Hope it'll solve your problem.

Thank you.

Regards,

BALAJI.

Re: abap : problem in select query

Former Member — Wed, 19 Dec 2012 06:44:01 GMT

Hi,

Just a small point to be noticed in this

You are passing AUGDT GT S_BUDAT-LOW AND AUGDT GT S_BUDAT-HIGH.

I believe this condition will never be true, because if AUGDT is lying in a range s_budat

then it has to be AUGDT GT S_BUDAT-LOW AND AUGDT LT S_BUDAT-HIGH.

Can you simply write --> AUGDT IN S_BUDAT.

Please check and confirm.

Regards,

Amit

Re: abap : problem in select query

Former Member — Wed, 19 Dec 2012 06:44:36 GMT

Hello Rihan,

I have tried similar query and its working fine for me. Could be issue with document type or company code(hardcode value) not matching a criteria. just try to put similar entries in SE16N and see if u get entries there.

regards,

Deepti

Re: abap : problem in select query

Former Member — Wed, 19 Dec 2012 07:06:57 GMT

Hi Rihan,

I think you input for BUDAT is 11.05.2012 is at wrong place.

1. If you are using no intervals in select option then you input will be only in BUDAT-LOW and

BUDAT-HIGH will be 00000000 then this will not validate the BUDAT condition because your

record is at BUDAT Eq 10.05.2012 it will search for BUDAT greater than 11.05.2012.

SOLUTION: Use Select-option and input the condition 11.05.2012 in the high section of BUDAT

Put a condition

if s_budat-low is initial and s_budat-high is not initial.

s_budat-low = '18000101'. " Starting Date

select BELNR BUDAT BLART GJAHR AUGDT AUGBL

form BSAS INTO CORRESPONDING FIELDS OF TABLE IBSIG

where BUKRS = '1100'

AND BUDAT IN S_BUDAT

AND ( AUGDT GT S_BUDAT-LOW AND AUGDT GT S_BUDAT-HIGH )

AND GJAHR IN S_GJAHR

AND blart in ('RE','WE','WA','JV')

%_HINTS ORACLE 'index(BSAS"ZNK")'.

elseif.

select BELNR BUDAT BLART GJAHR AUGDT AUGBL

form BSAS INTO CORRESPONDING FIELDS OF TABLE IBSIG

where BUKRS = '1100'

AND BUDAT IN S_BUDAT

AND ( AUGDT GT S_BUDAT-LOW AND AUGDT GT S_BUDAT-HIGH )

AND GJAHR IN S_GJAHR

AND blart in ('RE','WE','WA','JV')

%_HINTS ORACLE 'index(BSAS"ZNK")'.

endif.

Regards,

Waliullah.

Re: abap : problem in select query

RaymondGiuseppi — Wed, 19 Dec 2012 07:17:39 GMT

Try to explain in plain english what you want to display like "document in a posting date range, open (not cleared) at a reference date" . Once you successully write this the select should become obvious.

Regards,

Raymond

Re: abap : problem in select query

former_member392866 — Wed, 19 Dec 2012 07:24:30 GMT

Rihan,

small correction.. try below code.

loop at S_BUDAT.

AT FIRST.

if S_BUDAT-HIGH IS INITIAL.

SELECT BELNR BUDAT BLART GJAHR AUGDT AUGBL FROM BSAS INTO CORRESPONDING FIELDS OF TABLE IBSIG

where BUKRS = '1100'

AND BUDAT IN S_BUDAT

AND AUGDT EQ S_BUDAT-LOW

AND GJAHR IN S_GJAHR

AND blart in ('RE','WE','WA','JV')

%_HINTS ORACLE 'index(BSAS"ZNK")'.

ELSE.

SELECT BELNR BUDAT BLART GJAHR AUGDT AUGBL FROM BSAS INTO CORRESPONDING FIELDS OF TABLE IBSIG

where BUKRS = '1100'

AND BUDAT IN S_BUDAT

AND ( AUGDT GT S_BUDAT-LOW AND AUGDT LT S_BUDAT-HIGH )

AND GJAHR IN S_GJAHR

AND blart in ('RE','WE','WA','JV')

%_HINTS ORACLE 'index(BSAS"ZNK")'.

ENDIF.

ENDAT.

ENDLOOP.

Use this code.. Hope it'll solve your problem.

Thank you.

Regards,

BALAJI.

Re: abap : problem in select query

Former Member — Wed, 19 Dec 2012 08:05:24 GMT

dear Raymond Giuseppi,

you are absolutely right that is my exact requirement but above select queries are

not helping me properly , If I used these then if clearing date is greater than posting date

then all the documents are not displayed .

Please request you all to help me.

Rihan

Re: abap : problem in select query

Former Member — Wed, 19 Dec 2012 08:14:10 GMT

I am giving you the example.

my clearing date (augdt is ) : 05.10.2012 and its posting date budat is : 10.05.2012

now in input if I pass ( 10.05.2012 to 04.05.2012) then this documents must show in

report and if I pass (10.05.2012 to 05.05.2012 ) or (10.05.2012 to 06.05.2012 )

now the selection date is greater then the clearing date now the documents must no

show this is my exact requirement.

so that is why I was using the condition on AUGDT , but I am not getting the soltuion .

Please suggest

Rihan

Re: abap : problem in select query

Jelena_Perfiljeva — Wed, 19 Dec 2012 15:54:21 GMT

If the requirement is to include only the records where AUGDT > BUDAT then this might be easier in a separate LOOP. First select the records using the criteria from selection screen into a table and then run through it and eliminate the unneeded records.

Otherwise you'd have to limit what may be entered in selection option S_BUDAT. Any criteria may be entered in selection option (exclusion, wildcards, multiple single values, etc.), it's not just a date range. But even if selection option is replaced by two single fields (e.g. date_from and date_to), then still some additional work would be needed. Because even if AUGDT > date_from, it does not mean it's also AUGDT > BUDAT for this specific record.

In a simple SELECT it's not feasible, to my knowledge, to compare one DB field to another. This may be done with a subquery, but I think an additional LOOP would be easier and faster.

Re: abap : problem in select query

amy_king — Wed, 19 Dec 2012 17:03:19 GMT

Hi Rihan,

In your scenario, I believe you are trying to ensure that the value of BSAS-AUGDT is greater than the value of BSAS-BUDAT for each record you read from BSAS. I like to use "self joins" when I need to compare the values of two fields within the same record during a query. You can achieve this with a join condition that specifies AUGDT > BUDAT...

SELECT t1~belnr
                 t1~budat
                 t1~blart
                 t1~gjahr
                 t1~augdt
                 t1~augbl
                 FROM bsas AS t1
                 JOIN bsas AS t2 ON    t2~mandt = t1~mandt   " <<<  specify the full key as the join condition
                                               AND t2~bukrs = t1~bukrs
                                               AND t2~hkont = t1~hkont
                                               AND t2~augdt = t1~augdt
                                               AND t2~augbl = t1~augbl
                                               AND t2~zuonr = t1~zuonr
                                               AND t2~gjahr = t1~gjahr
                                               AND t2~belnr = t1~belnr
                                               AND t2~buzei = t1~buzei
                                               AND t2~augdt > t1~budat   " <<< additionally specify AUGDT of t2 be greater than BUDAT of t1
                INTO CORRESPONDING FIELDS OF TABLE ibsig
                WHERE t1~bukrs =  '1100'
                AND       t1~budat IN s_budat
                AND       t1~gjahr  IN s_gjahr
                AND       t1~blart   IN ('RE','WE','WA','JV').

Cheers,

Amy