topic Need help with SQL and Date Range in Application Development and Automation Discussions

Need help with SQL and Date Range

Former Member — Mon, 02 Oct 2006 16:12:41 GMT

I need to run this sql with the user entered date range and go through the data. here is what i have.

***

REPORT ZTEST.

Tables: AUFM.

SELECTION-SCREEN BEGIN OF BLOCK date WITH FRAME TITLE TEXT-000

NO INTERVALS.

SELECT-OPTIONS: begdate FOR AUFM-BUDAT,

enddate FOR AUFM-BUDAT.

SELECTION-SCREEN END OF BLOCK date.

SELECT * FROM AUFM

WHERE

( BUDAT BETWEEN begDate AND endDate ) AND

( BWART = '261' OR BWART = '262' OR BWART = '101' OR BWART = '102'

OR BWART = '202' OR BWART = '201' ).

if sy-subrc = 0.

write: / AUFM-AUFNR, AUFM-BUDAT.

else.

write:/ 'no rec'.

endif.

endselect.

*******

Right now its not working and while debugging i found out that if the user puts 7/1/2006 the begdate actually shows in debug mode: IEQ2006070100000000 and hence my sql is not even checking the sy-subrc.

Please let me know what I am doing wrong here...

Re: Need help with SQL and Date Range

Former Member — Mon, 02 Oct 2006 16:15:44 GMT

SELECTION-SCREEN BEGIN OF BLOCK date WITH FRAME TITLE TEXT-000

NO INTERVALS.

SELECT-OPTIONS: begdate FOR AUFM-BUDAT,

enddate FOR AUFM-BUDAT.

SELECTION-SCREEN END OF BLOCK date.

SELECT * FROM AUFM

WHERE

( BUDAT BETWEEN begDate0(8) AND endDate0(8) ) AND

( BWART = '261' OR BWART = '262' OR BWART = '101' OR BWART = '102'

OR BWART = '202' OR BWART = '201' ).

if sy-subrc = 0.

write: / AUFM-AUFNR, AUFM-BUDAT.

else.

write:/ 'no rec'.

endif.

endselect.

Re: Need help with SQL and Date Range

Former Member — Mon, 02 Oct 2006 16:16:05 GMT

Hi,

Just change the high lighted code, it should work.Your begdate is select option so the high value will be end date . You dont require one more select option for end date. if you are looking for begin date and enddate then declare them as parameters not select-options.

SELECTION-SCREEN BEGIN OF BLOCK date WITH FRAME TITLE TEXT-000

NO INTERVALS.

SELECT-OPTIONS: begdate FOR AUFM-BUDAT.

*enddate FOR AUFM-BUDAT.

SELECTION-SCREEN END OF BLOCK date.

SELECT * FROM AUFM

WHERE

*( BUDAT BETWEEN begDate AND endDate ) AND

BUDAT IN begDate and

( BWART = '261' OR BWART = '262' OR BWART = '101' OR BWART = '102'

OR BWART = '202' OR BWART = '201' ).

if sy-subrc = 0.

write: / AUFM-AUFNR, AUFM-BUDAT.

else.

write:/ 'no rec'.

endif.

endselect.

Hope this helps.

Re: Need help with SQL and Date Range

Former Member — Mon, 02 Oct 2006 16:17:51 GMT

Hi,

Make the BEGDATE and ENDDATE as a parameter instead of a select-options.

Otherwise..

Make it as one select-options..

Ex..

SELECT-OPTIONS SO_DATE FOR AUFM-BUDAT.

SO_DATE-LOW will be your begdate

SO_DATE-HIGH will be your enddate.

Thanks,

Naren

Re: Need help with SQL and Date Range

Former Member — Mon, 02 Oct 2006 16:19:54 GMT

Hi,

SELECTION-SCREEN BEGIN OF BLOCK date WITH FRAME TITLE TEXT-000

NO INTERVALS.

SELECT-OPTIONS: begdate FOR AUFM-BUDAT,

enddate FOR AUFM-BUDAT.

SELECTION-SCREEN END OF BLOCK date.

SELECT * FROM AUFM

WHERE

( BUDAT between begDate-low AND endDate-low ) AND( BWART = '261' OR BWART = '262' OR BWART = '101' OR BWART = '102'

OR BWART = '202' OR BWART = '201' ).

if sy-subrc = 0.

write: / AUFM-AUFNR, AUFM-BUDAT.

else.

write:/ 'no rec'.

endif.

endselect.

Regards

amole

Re: Need help with SQL and Date Range

Former Member — Mon, 02 Oct 2006 16:20:39 GMT

Instead try like this:

REPORT ZTEST.

data: i_aufm type standard table of aufm,

wa_aufm type aufm.

SELECTION-SCREEN BEGIN OF BLOCK date WITH FRAME TITLE TEXT-000

NO INTERVALS.

Parameters: begdate FOR AUFM-BUDAT,

enddate FOR AUFM-BUDAT.

SELECTION-SCREEN END OF BLOCK date.

SELECT * FROM AUFM into table i_aufm

WHERE

( BUDAT BETWEEN begDate AND endDate ) AND

( BWART = '261' OR BWART = '262' OR BWART = '101' OR BWART = '102'

OR BWART = '202' OR BWART = '201' ).

if sy-subrc = 0.

loop at i_aufm into wa_aufm.

write: / wa_aufm-AUFNR, wa_aufm-BUDAT.

endloop.

else.

write:/ 'no rec'.

endif.

Re: Need help with SQL and Date Range

Former Member — Mon, 02 Oct 2006 16:23:40 GMT

Hi Kabir,

Here is the code:

TABLES: aufm.

SELECTION-SCREEN BEGIN OF BLOCK date WITH FRAME TITLE text-000.

SELECT-OPTIONS: s_date FOR aufm-budat.

SELECTION-SCREEN END OF BLOCK date.

SELECT * FROM aufm

WHERE

budat in s_date AND

( bwart = '261' OR bwart = '262' OR bwart = '101' OR bwart = '102'

OR bwart = '202' OR bwart = '201' ).

IF sy-subrc = 0.

WRITE: / aufm-aufnr, aufm-budat.

ELSE.

WRITE:/ 'no rec'.

ENDIF.

ENDSELECT.

Thanks

Eswar

Re: Need help with SQL and Date Range

Former Member — Mon, 02 Oct 2006 16:26:22 GMT

Just try:


SELECT * FROM AUFM
  WHERE
  ( BUDAT <b>IN begDate</b> ) AND
  ( BWART = '261' OR BWART = '262' OR BWART = '101' OR
    BWART = '102'
 OR BWART = '202' OR BWART = '201' ).

As well, you don't need enddate.

Rob