topic Re: select query issue in Application Development and Automation Discussions

select query issue

Former Member — Mon, 22 Jul 2013 09:15:30 GMT

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

Re: select query issue

former_member491621 — Mon, 22 Jul 2013 14:20:57 GMT

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?

Re: select query issue

jogeswararao_kavala — Mon, 22 Jul 2013 14:27:08 GMT

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

Jogeswara Rao K

Re: select query issue

Former Member — Tue, 23 Jul 2013 03:39:20 GMT

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.

Re: select query issue

Former Member — Tue, 23 Jul 2013 04:40:21 GMT

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

Re: select query issue

former_member209120 — Tue, 23 Jul 2013 04:58:14 GMT

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') ).

Re: select query issue

Former Member — Tue, 23 Jul 2013 05:05:43 GMT

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.

Re: select query issue

Former Member — Tue, 23 Jul 2013 11:30:26 GMT

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.

Re: select query issue

Former Member — Wed, 24 Jul 2013 05:25:53 GMT

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.

Re: select query issue

Former Member — Wed, 24 Jul 2013 06:09:08 GMT

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

Re: select query issue

Former Member — Wed, 24 Jul 2013 07:01:59 GMT

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.

Re: select query issue

Former Member — Wed, 24 Jul 2013 07:03:42 GMT

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

Re: select query issue

Former Member — Fri, 26 Jul 2013 04:02:47 GMT

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.

Re: select query issue

Former Member — Fri, 26 Jul 2013 06:14:13 GMT

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').

)