topic Re: SQL + not in between in Application Development and Automation Discussions

SQL + not in between

Former Member — Thu, 01 Jun 2006 13:29:28 GMT

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

Re: SQL + not in between

Former Member — Thu, 01 Jun 2006 13:31:14 GMT

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

Re: SQL + not in between

Former Member — Thu, 01 Jun 2006 13:32:18 GMT

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

s_matnr-sign = 'E'. "I for Include E foe exclude

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

s_matnr-sign = 'I'. "I for Include E foe exclude

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

append s_matnr.

select *from mara

into it_mara

where matnr in s_Matnr.

Regards,

Ravi

Re: SQL + not in between

Former Member — Thu, 01 Jun 2006 13:32:43 GMT

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

Re: SQL + not in between

Former Member — Thu, 01 Jun 2006 13:32:56 GMT

hi,

Select * from test

where f1 > f2

or f1 < f3.

Message was edited by: Manoj Gupta

Re: SQL + not in between

Former Member — Thu, 01 Jun 2006 13:33:57 GMT

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

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

Regards,

Santosh

Re: SQL + not in between

Former Member — Thu, 01 Jun 2006 13:35:01 GMT

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

Re: SQL + not in between

Former Member — Thu, 01 Jun 2006 13:37:09 GMT

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

Re: SQL + not in between

Former Member — Thu, 01 Jun 2006 13:37:17 GMT

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

Re: SQL + not in between

Former Member — Thu, 01 Jun 2006 13:38:18 GMT

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.

Re: SQL + not in between

abdul_hakim — Thu, 01 Jun 2006 13:38:23 GMT

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

Re: SQL + not in between

Former Member — Thu, 01 Jun 2006 13:50:06 GMT

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.

Re: SQL + not in between

Former Member — Thu, 01 Jun 2006 13:51:52 GMT

hi Shah,

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

Regards,

Santosh

Note: Please Mark the helpful answers

Re: SQL + not in between

Former Member — Thu, 01 Jun 2006 13:53:45 GMT

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 into table t_test 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.

Re: SQL + not in between

Former Member — Thu, 01 Jun 2006 13:59:27 GMT

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,