select query issue

[Former Member]

i have this select query but isn't work  properly.    SELECT DISTINCT Q~PRUEFLOS Q~WERK Q~OBJNR Q~ENSTEHDAT Q~KTEXTLOS Q~SELMATNR Q~KTEXTMAT Q~CHARG J~STAT INTO CORRESPONDING FIELDS OF TABLE    gt_qals  FROM QALS AS Q INNER JOIN                  jest AS J ON  Q~OBJNR = J~OBJNR                  WHERE Q~ENSTEHDAT IN LV_DATE                  AND Q~WERK IN LV_plant                  AND J~INACT NE 'X'                  AND (  J~STAT IN ('I0002','I0212') AND  J~STAT NOT IN ('I0224','I0218','I0204') ).   problem in fetching data in where condition how i can use IN and NOT in in same field.  AND (  J~STAT IN ('I0002','I0212') AND  J~STAT NOT IN ('I0224','I0218','I0204') ).  regards, ams

[former_member491621]

Hi Ams,

Why do you want to use NOT IN in the select query when you are able to get the required data using IN in where clause itself?

[jogeswararao_kavala]

Perhaps you want OR instead of AND between IN and  NOT IN values.

Jogeswara Rao K

[Former Member]

I think this code below is enough, just eliminate NOT IN condition.

If return records match condition J~STAT IN ('I0002','I0212') then automatically it will not match condition J~STAT NOT IN ('I0224','I0218','I0204')

=================================

SELECT DISTINCT

Q~PRUEFLOS Q~WERK Q~OBJNR Q~ENSTEHDAT Q~KTEXTLOS Q~SELMATNR Q~KTEXTMAT Q~CHARG J~STAT

INTO CORRESPONDING FIELDS OF TABLE gt_qals

FROM QALS AS Q INNER JOIN jest AS J

ON Q~OBJNR = J~OBJNR

WHERE Q~ENSTEHDAT IN LV_DATE AND Q~WERK IN LV_plant AND J~INACT NE 'X'

AND ( J~STAT IN ('I0002','I0212')

=================================

If it is still not answered, then perhaps you would give me sample data you want to get. Probably we need to rebuild the select statement.

[Former Member]

Hi,

   you can declare range and use in where condition.

  TABLES: JEST.

  RANGES: R_STAT FOR JEST-STAT.

  DEFINE FILL_R_STAT.
      R_STAT-SIGN    = &1.
      R_STAT-OPTION  = 'EQ'.
      R_STAT-LOW     = &2.
      APPEND R_STAT.
      CLEAR  R_STAT.
  END-OF-DEFINITION.

  FILL_R_STAT 'I' 'I0002'.
  FILL_R_STAT 'I' 'I0212'.
  FILL_R_STAT 'E' 'I0224'.
  FILL_R_STAT 'E' 'I0218'.
  FILL_R_STAT 'E' 'I0204'.

    SELECT DISTINCT Q~PRUEFLOS
                  Q~WERK
                  Q~OBJNR
                  Q~ENSTEHDAT
                  Q~KTEXTLOS
                  Q~SELMATNR
                  Q~KTEXTMAT
                  Q~CHARG
                  J~STAT
    INTO CORRESPONDING FIELDS OF TABLE GT_QALS
      FROM QALS AS Q INNER JOIN JEST AS J ON Q~OBJNR = J~OBJNR
        WHERE Q~ENSTEHDAT IN LV_DATE
          AND Q~WERK      IN LV_PLANT
          AND J~INACT     NE 'X'
          AND J~STAT      IN R_STAT.

Regards,

Mordhwaj

[former_member209120]

Hi Ams,

SELECT DISTINCT Q~PRUEFLOS Q~WERK Q~OBJNR Q~ENSTEHDAT Q~KTEXTLOS Q~SELMATNR Q~KTEXTMAT Q~CHARG J~STAT
    INTO CORRESPONDING FIELDS OF TABLE gt_qals  FROM
    QALS AS Q INNER JOIN jest AS J ON  Q~OBJNR = J~OBJN
    WHERE Q~ENSTEHDAT IN LV_DATE
    AND Q~WERK IN LV_plant
    AND J~INACT NE 'X'
    AND J~STAT IN ('I0002','I0212').             

" your condition J-STAT should in  I0002 , I0212 then why we need to use not in statement it is not required.
   
    AND  J~STAT NOT IN ('I0224','I0218','I0204') ). 

[Former Member]

Hi Ams,

Instead of that you have to use ranges for this as below.

DATA: stat TYPE j_status,
       lr_stat_tab TYPE RANGE OF stat,
       lr_stat_line TYPE LINE OF lr_stat_tab.

lr_stat_line-low = 'I0002'.
lr_stat_line-sign = 'I'.
lr_stat_line-option = 'EQ'.
APPEND lr_stat_line TO lr_stat_tab.

lr_stat_line-low = 'I0212'.
lr_stat_line-sign = 'I'.
lr_stat_line-option = 'EQ'.
APPEND lr_stat_line TO lr_stat_tab.
lr_stat_line-low = 'I0224'.
lr_stat_line-sign = 'E'.
lr_stat_line-option = 'EQ'.
APPEND lr_stat_line TO lr_stat_tab.
lr_stat_line-low = 'I0218'.
lr_stat_line-sign = 'E'.
lr_stat_line-option = 'EQ'.
APPEND lr_stat_line TO lr_stat_tab.
lr_stat_line-low = 'I0204'.
lr_stat_line-sign = 'E'.
lr_stat_line-option = 'EQ'.
APPEND lr_stat_line TO lr_stat_tab.

SELECT DISTINCT q~prueflos q~werk q~objnr q~enstehdat q~ktextlos q~selmatnr q~ktextmat q~charg j~stat
INTO CORRESPONDING FIELDS OF TABLE    gt_qals
FROM qals AS q INNER JOIN jest AS j ON  q~objnr = j~objnr
WHERE q~enstehdat IN lv_date
   AND q~werk IN lv_plant
   AND j~inact NE 'X'
   AND j~stat IN lr_stat_tab.

Here sign I indicates Inclusive of the value and E indicates Exclusive of the value.

try this.

thanks,

Aswath.

[Former Member]

Hi all,

thanx for replay,  but yet not resolve with single select statment.   i used ranges but actually the records having multiple values like 1 records have j~stat =  'I0002' 'I0212' 'I0215' 'I0224' 'I0204' 

                                                  2 records have j~stat =  'I0002' 'I0210' 'I0213' 'I0214' 'I0215'   

                                                  3 records have j~stat =  'I0002' 'I0202' 'I0218' 'I0224' 'I0204'     

                                                  4 records have j~stat =  'I0002' 'I0210' 'I0288' 'I0289' 'I0204'

IN THIS LIST I NEED TO FETCH ONLY SECOND RECORD.  when i used ranges with I and E this all records satisfied ( I = 'I0002')  that way the all records i get. i want only second records.

regards,

Ams.

[Former Member]

Hey I understood that you only want values I0002, I0210 and it can have any other values except 10210, 10204, and 10224. But how can j~stat have multiple values as you are saying you need second record which contains 5 values.

I am sorry if i didnt understand but give us clear understanding here.

thanks,

Aswath.

[Former Member]

QALS~PRUEFLOS =  160000013238

QALS~ OBJNR =  QL160000013238

The OBJNR passed to Table JEST and I get following multiple records for single OBJNR. In that I have check the J~STAT condition.  Like  J~STAT IN ('I0002','I0212') AND  J~STAT NOT IN ('I0224','I0218','I0204') ).

Regards,

Ams

[Former Member]

This will get the second record for each PRUEFLOS

SELECT Q~PRUEFLOS Q~WERK Q~OBJNR Q~ENSTEHDAT Q~KTEXTLOS Q~SELMATNR Q~KTEXTMAT Q~CHARG J~STAT

INTO CORRESPONDING FIELDS OF TABLE gt_qals

FROM QALS AS Q INNER JOIN jest AS J ON Q~OBJNR = J~OBJNR

WHERE Q~ENSTEHDAT IN LV_DATE AND Q~WERK IN LV_plant AND J~INACT NE 'X'

AND J~STAT IN ('I0002','I0212').

SORT gt_gals BY PRUEFLOS STAT DESCENDING.

DELETE ADJACENT DUPLICATES FROM gt_gals COMPARING PRUEFLOS STAT.

[Former Member]

In this case if my understanding is right you want all values except those you provide in NOT IN section right? If so you can write only not in statement and no need of IN statement ( or you can only use E option in select options).

try this method.

thanks

[Former Member]

Hi all,

I solved this issue with using two select query,

SELECT DISTINCT Q~PRUEFLOS Q~WERK Q~OBJNR Q~ENSTEHDAT Q~KTEXTLOS Q~SELMATNR Q~KTEXTMAT Q~CHARG J~STAT INTO CORRESPONDING FIELDS OF TABLE

   gt_qals  FROM QALS AS Q INNER JOIN

                 jest AS J ON  Q~OBJNR = J~OBJNR

                 WHERE Q~ENSTEHDAT IN LV_DATE

                 AND Q~WERK IN LV_plant

                 AND J~INACT NE 'X'

                 AND J~STAT IN ('I0002','I0212').

                SELECT DISTINCT Q~PRUEFLOS Q~WERK Q~OBJNR Q~ENSTEHDAT Q~KTEXTLOS Q~SELMATNR Q~KTEXTMAT Q~CHARG J~STAT INTO CORRESPONDING FIELDS OF TABLE

   gt_qals_temp  FROM QALS AS Q INNER JOIN

                 jest AS J ON  Q~OBJNR = J~OBJNR

                 WHERE Q~ENSTEHDAT IN LV_DATE

                 AND Q~WERK IN LV_plant

                 AND J~INACT NE 'X'

                 AND J~STAT  IN ('I0224','I0218','I0204').

DELETE ADJACENT DUPLICATES FROM gt_qals COMPARING PRUEFLOS WERK.

DELETE ADJACENT DUPLICATES FROM gt_qals_temp COMPARING PRUEFLOS WERK.

LOOP AT gt_qals_temp INTO wa_qals_temp.

  READ TABLE gt_qals  INTO wa_qals  WITH KEY PRUEFLOS = wa_qals_temp-PRUEFLOS

                                             WERK = wa_qals_temp-WERK.

  IF sy-subrc = 0.

  DELETE TABLE gt_qals FROM wa_qals.

  ENDIF.

ENDLOOP.

But still i want to solved this using single select query if any one know this then please share solution .

Regards,

Ams.

[Former Member]

SELECT DISTINCT Q~PRUEFLOS Q~WERK Q~OBJNR Q~ENSTEHDAT Q~KTEXTLOS Q~SELMATNR Q~KTEXTMAT Q~CHARG J~STAT INTO CORRESPONDING FIELDS OF TABLE gt_qals FROM QALS AS Q INNER JOIN jest AS J ON  Q~OBJNR = J~OBJNR

WHERE Q~ENSTEHDAT IN LV_DATE AND Q~WERK IN LV_plant AND J~INACT NE 'X' AND J~STAT IN ('I0002','I0212')

AND NOT Q~PRUEFLOS IN

(

SELECT Q~PRUEFLOS

FROM QALS AS Q INNER JOIN

jest AS J ON  Q~OBJNR = J~OBJNR

WHERE Q~ENSTEHDAT IN LV_DATE AND Q~WERK IN LV_plant AND J~INACT NE 'X' AND J~STAT  IN ('I0224','I0218','I0204').

)