topic Re: SELECT Statement with WHERE Clause Question in Application Development and Automation Discussions

SELECT Statement with WHERE Clause Question

Former Member — Wed, 08 Apr 2009 21:49:43 GMT

Hello All!

Perhaps this is a simple question, and I will slap myself in the forehead when I can figure out the answer, but here it goes...

One of the methods of a class I am building simply populates an internal table of the class. I originally had this section of code working (Labeled Original Below); the requirements have changed and I need to select the data slightly differently (Labeled New Below). Now the SELECT Statement is returning NO DATA.

I have verified that at least one record exists that should meet the requirements.

Original Code

- This code will select the fields listed for Shipments that fall with in the bTKNUM and eTKNUM fields and have an appointment date between bDPLBG and eDPLBG, only if the shipment had a carrier assigned (Inner Join LFA1 ON VTTK_{TDLNR = LFA1}LIFNR). This was working!


METHOD GetHeadInfo .
  " -->  bTKNUM
  " -->  eTKNUM
  " -->  bDPLBG
  " -->  eDPLBG

  SELECT DISTINCT VTTK~TKNUM VTTK~DPLBG VTTK~UPLBG VTTK~TDLNR 
        LFA1~NAME1 LFA1~TELFX
        INTO CORRESPONDING FIELDS OF TABLE itHeader
        FROM VTTK
          INNER JOIN LFA1
            ON VTTK~TDLNR = LFA1~LIFNR
        WHERE VTTK~TKNUM BETWEEN bTKNUM AND eTKNUM
          AND VTTK~DPLBG BETWEEN bDPLBG AND eDPLBG .

ENDMETHOD .

New Code

The code had to be changed however. The requirements changed to Shipments that have a carrier and do not have an appointment date set.


METHOD GetHeadInfo .
  " -->  bTKNUM
  " -->  eTKNUM

  SELECT DISTINCT VTTK~TKNUM VTTK~DPLBG VTTK~UPLBG VTTK~TDLNR LFA1~NAME1 LFA1~TELFX
      INTO CORRESPONDING FIELDS OF TABLE itHeader
      FROM VTTK
        INNER JOIN LFA1
          ON VTTK~TDLNR = LFA1~LIFNR
      WHERE VTTK~DPLBG IS NULL 
        AND VTTK~TKNUM BETWEEN bTKNUM AND eTKNUM .
*        AND VTTK~DPLBG BETWEEN bDPLBG AND eDPLBG .

ENDMETHOD .

As you can see, I simply added a test for NULL Appointment Dates and removed the BETWEEN section for appointment dates. In addition, the UI was modified to not allow entrance of Appointment Date values.

This code is not working, everything else about the application seems to work, but this section of code.

I have an idea that it is due to the BETWEEN statement with a TYPE TKNUM field. TKNUM is TYPE N, and I am basically asking for records that are between two Numeric Character fields and it doesn't know how to compare them.

This is currently in a MODULE-POOL, so the screens have been built with Screen Painter. Perhaps I need to learn to implement SELECT-OPTIONS in Screen Painter build screen.

Any other ideas on why the new code would fail while the old code worked?

Thank you in Advance!

Re: SELECT Statement with WHERE Clause Question

awin_prabhu — Thu, 09 Apr 2009 03:33:11 GMT

Hi Steve,

Change line 'WHERE VTTK_{DPLBG IS NULL' to 'WHERE VTTK}DPLBG = 00000000'. Ur code will work.

Ur code:

SELECT DISTINCT VTTK_{TKNUM VTTK}DPLBG VTTK_{UPLBG VTTK}TDLNR LFA1_{NAME1 LFA1}TELFX

INTO CORRESPONDING FIELDS OF TABLE itHeader

FROM VTTK

INNER JOIN LFA1

ON VTTK_{TDLNR = LFA1}LIFNR

WHERE VTTK_{DPLBG IS NULL <----- Change this line to WHERE VTTK}DPLBG = 00000000

AND VTTK~TKNUM BETWEEN bTKNUM AND eTKNUM .

AND VTTK~DPLBG BETWEEN bDPLBG AND eDPLBG .

Thanks.

Re: SELECT Statement with WHERE Clause Question

Former Member — Thu, 09 Apr 2009 03:56:37 GMT

Hi Steven,

DPLBG-Planned date for start of loading is a date field, For a date field if you use NULL operator it will not work,

I mean there is a difference b/n NULL & INITIAL.

Thats it,

Perhaps I need to learn to implement SELECT-OPTIONS in Screen Painter build screen.

And for what you said, Once a long back when I was a Begginer in ABAP I tried & failed,

Thanks for remembering me, Now I will solve my known backlog...

Thanks & regards,

Dileep .C

Re: SELECT Statement with WHERE Clause Question

Former Member — Thu, 09 Apr 2009 05:15:09 GMT

Replace null by initial.

regards,

lalit mohan gupta.

Re: SELECT Statement with WHERE Clause Question

Former Member — Thu, 09 Apr 2009 05:19:56 GMT

Hi!

in this select clause u used the date field can not be null but '00000000' and hence if u change this

WHERE VTTK~DPLBG IS NULL

(to)

WHERE VTTK~DPLBG = '00000000'

this will work..........

and u will get ur one record selected.......


 SELECT DISTINCT VTTK~TKNUM VTTK~DPLBG VTTK~UPLBG VTTK~TDLNR LFA1~NAME1 LFA1~TELFX
      INTO CORRESPONDING FIELDS OF TABLE itHeader
      FROM VTTK
        INNER JOIN LFA1
          ON VTTK~TDLNR = LFA1~LIFNR
      WHERE VTTK~DPLBG  eq '00000000'
        AND VTTK~TKNUM BETWEEN bTKNUM AND eTKNUM .
      AND VTTK~DPLBG BETWEEN bDPLBG AND eDPLBG .

Edited by: Richa Tripathi on Apr 9, 2009 7:20 AM

Re: SELECT Statement with WHERE Clause Question

Former Member — Thu, 09 Apr 2009 13:19:33 GMT

Thank you all very much. That actually makes a lot of sense.