abap : problem in select query

[Former Member]

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

[kakshat]

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.

[former_member392866]

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.

[Former Member]

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

[Former Member]

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

[Former Member]

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.

[RaymondGiuseppi]

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

[Former Member]

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

[former_member392866]

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.

[Former Member]

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

[Jelena_Perfiljeva]

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.

[amy_king]

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