SQL + not in between

[Former Member]

Hello,

might be very simple question, but would be nice if some one help me out, ...

what would be the sql statement if I dont want to have ranges in between

e.g

Select * from test

where f1 between f2 and f3.

and I want to have with NOT

could it be possible

select * from test

where f1 not between f2 and f3.

it does not give atleast any syntax error ?

many thanks in advance..

[Former Member]

hi Shah,

The way in which you ahve declared is not possible and i guess it gives the syntax error for the same.

Regards,

Santosh

[Former Member]

Hi santosh,

It does not give me syntax error when I use like

Select * from test

where f1 not between f2 and f3.

What do u think the possible way can be ??

thanks

[Former Member]

hi shah,

Try the way as told by Ravi... hope that way it works,

i.e,

s_matnr-low = '0000000000100'.

s_matnr-high = '000000000500'.

s_matnr-sign = 'E'.

s_matnr-high = 'BT'.

append s_matnr.

select *from mara

into it_mara

where matnr in s_Matnr.

else do this way

<b>Select * from test where f1 > f2 and f1 < f3.</b>

Regards,

Santosh

[Former Member]

YOu can use ranges/select options for the same.

This query would give me all the materials which are not in the range 1 to 100.

s_matnr-low = '0000000000001'.

s_matnr-high = '000000000100'.

<b>s_matnr-sign = 'E'. "I for Include E foe exclude</b>

s_matnr-high = 'BT'. "BT for between

append s_matnr.

select *from mara

into it_mara

where matnr in s_Matnr.

This query would give me all the materials which are in the range 1 to 100.

s_matnr-low = '0000000000001'.

s_matnr-high = '000000000100'.

<b>s_matnr-sign = 'I'. "I for Include E foe exclude</b>

s_matnr-high = 'BT'. "BT for between

append s_matnr.

select *from mara

into it_mara

where matnr in s_Matnr.

Regards,

Ravi

[Former Member]

Hi Ravi,

Many thanks for your input, actually I am making dynamically selction statement and putting like you put in internal table "where matnr in s_Matnr."

I have a question, if i give the sign flage with e.g select option is EQ and sign is E, then my entry ( which I put in internal table looks like -> f1 E EQ low, so now do you think, that sql will automaticlly convert the EQ to NE ??

Many thanks...

[Former Member]

hi,

Select * from test

where f1 > f2

or f1 < f3.

Message was edited by: Manoj Gupta

[Former Member]

Hi Shah,

is it a Standart SAP-Table f. Ex. like MARA?

if it is, you can do it with select-option.

f. Ex. S_MATNR .. and fill this table.

If you want, i can give you an litte Code-Example.

Regards, Dieter

Ignore my statement, Ravi shows the way.

Message was edited by: Dieter Gröhn

[Former Member]

Hi,

use following logic

rf1-sign = 'E'.

rf1-option = 'BT'.

rf1-low = '001'.

rf1-high = '010'.

append rf1.

select * from test where f1 in rf1.

Regards,

Amole

[Former Member]

<b>DATA sflight_tab TYPE TABLE OF sflight.

DATA date TYPE d.

date = sy-datum + 30.

SELECT carrid connid fldate

FROM sflight

INTO CORRESPONDING FIELDS OF TABLE sflight_tab

WHERE fldate not BETWEEN sy-datum AND date.

The above code will also work.</b>

[Former Member]

So actually as I wrote the select statement

select * from test

where f1 not between low and high.

will also work ?

Regards,

DATA sflight_tab TYPE TABLE OF sflight.

DATA date TYPE d.

date = sy-datum + 30.

SELECT carrid connid fldate

FROM sflight

INTO CORRESPONDING FIELDS OF TABLE sflight_tab

WHERE fldate not BETWEEN sy-datum AND date.

The above code will also work.

[Former Member]

hi Shah,

You cannot use NOT BETWEEN in select. So it the code that you ahve givne doesnot work.

Regards,

Santosh

<b>Note: Please Mark the helpful answers</b>

[Former Member]

select * from test

into table t_test

where f1 not between low and high.

will work if test is a valid table and if you specify the <b>into table t_test</b> to tell that you are selecting the data into an internal table.

t_test must be declared earlier to this code.

Regards,

Ravi

P.S: Please close the thread and reward all helpful posts if your query has been addressed.

[Former Member]

Hi Ravi,

Many thanks for your input, I was somehow sure it will work as it does not gave me any syntax error when I ran the code. but thanks for double confirm me.

Hi santosch, as Ravi said, and I tested, it shows it works, but many thanks for all of you guys for a quick response.

Many thanks once again....

Regards,

[abdul_hakim]

You cannot use NOT BETWEEN in SELECT variant...

instead you define your NB or BT condition in your selection table itself as Ravi mentioned and use it in ur SELECT variant..

Cheers,

Abdul

Mark all useful answers..