How to define this SQL ?

[Former Member]

Hi all,

I'm going to retrieve a field whose content contain date and time, i.e. 2005071300045678.

I only want to retrive the date, i.e. the first 8 offsets.

I've done something like:

Select * from db where field1+0(8) = '20050713'.

But the system can't identify field1+0(8),

what should I do?

Thanks.

[jayanthi_jayaraman]

Hi,

Try this.

select * from db where field1 like '20050713%'.

If it is not working, then select all the records from the db.

Then try like this inside the loop.

loop at itab into wa.

if wa-field1+0(7) ne '20050713'.

delete itab where field1 = wa-field1.

endif.

endloop.

[Former Member]

Thanks for your reply, this is very useful to me.

Now I have a variable of type c storing the field2 (select * from db where field1 like field2)

remember field1 is type d, e.g. 20050713

but field2 is type c, i got '13072005%'

How to change the field2 to '20050713%'?

[Former Member]

Hi, I'd like to ask what if field2 is a select options?

The SQL works fine for a single value.

select options field2 for xxx-yyy.

select * from db where field1 like field2.

How to make the SQL to search for a range?

Thanks.

[Former Member]

Hi,

U can search for a range using <b>IN</b>

eg:

select options field2 for xxx-yyy.

<b>select * from db where field1 IN field2.</b>

SELECT-OPTION: matnr for s_matnr.

SELECT * FROM MARA WHERE matnr IN s_matnr.

Hope this helps.

Thanks & Regards,

Judith.

[Former Member]

Hi,

IN is used in normal case for ranges,

The problem is that, I have to use

select * from db where field1 like field2.

but field2 is a select options, I can't simply use select * from db where field1 in field2.

[jayanthi_jayaraman]

Hi,

You have to use ranges.

That range should contain one more character than the select-option field.Suppose if the select-option field2 is 3 characters,then make the range for 4 character.Append % manually for each value inside the loop.Then use IN ranges in select statement.

Here is the sample code.

data v(3).

data v1(4).

select-options s_matnr for v.

ranges r_matnr for v1.

loop at s_matnr.

concatenate s_matnr-low '%' into r_matnr-low.

concatenate s_matnr-high '%' into r_matnr-high .

r_matnr-sign = s_matnr-sign.

r_matnr-option = s_matnr-option.

endloop.

data itab type standard table of mara.

select * from mara into table itab where matnr in r_matnr.

if sy-subrc eq 0.

write 'good'.

endif.

[Former Member]

Hi,

Thanks a lot for your detailed code.

But it retrieves so many irrelevant entries which is not desirable.

select * from db into table itab where field1 in r_field1.

For your reference,

field1 is 2005071311111111,

r_field1-low is '20050713%'

[jayanthi_jayaraman]

Hi,

In your first post,you mentioned as below.That's why I thought that you need all the values starting from 20050713.

I'm going to retrieve a field whose content contain date and time, i.e. 2005071300045678.

I only want to retrive the date, i.e. the first 8 offsets.

I've done something like:

Select * from db where field1+0(8) = '20050713'.

Could you please explain with an example actually what you from database?

Suppose if you enter '20050713' in select-option,then what you want the database to fetch?

[Former Member]

Sorry that I didn't say clearly.

I'm getting the spool id from db table TSP02 according to the creation date range specified on the selection screen.

So, the SQL is

SELECT-OPTIONS S_CDATE FOR SY-DATUM.

DATA TXT TYPE C '9'.

CONCATENATE S_CDATE-LOW '%' INTO TXT.

SELECT PJIDENT FROM TSP02 WHERE PJCREATIME LIKE TXT.

this works fine,

but what should be the SQL if users specifies the range in the selection screen , e.g. 20050201 to 20050208 ?

[jayanthi_jayaraman]

Hi,

If the user is entering one value,suppose '20050201'.Then the select statement will fetch '20050201111111','2005020122222',etc.,

If he enters the range 20050201 to 20050208 ,then obviously the select statement should fetch all the items in the range.i.e., for 20050201,20050202,20050203,...20050208.

Then the select statement should fetch

'20050201111111','2005020122222',etc.,

'20050202111111','2005020222222',etc.,

.....

'20050208111111','2005020822222',etc.,

So the details are not irrelevant.Since it is in select-options,we are supposed to do like that.

Hope this answered your question.

[Former Member]

Hi,

My problem is the system is fetching 20000808111111 although i input 20050201 on the selection screen.

This is totally irrelevant....><....

[jayanthi_jayaraman]

Hi,

If you are using no-extension,then you can use between.. and .

Here is the sample.

data v(8).

data itab type standard table of TSP02.

data wa type TSP02.

SELECT-OPTIONS S_CDATE FOR v <b>no-extension</b>.

DATA TXT(9) TYPE C .

ranges r_cdate for txt.

loop at s_cdate.

CONCATENATE s_cdate-low0(2) s_cdate-low2(2) s_cdate-low+4(2)

s_cdate-low+6(2) '%' into r_cdate-low.

CONCATENATE s_cdate-high0(2) s_cdate-high2(2) s_cdate-high+4(2)

s_cdate-high+6(2) '%' into r_cdate-high .

r_cdate-sign = s_cdate-sign.

r_cdate-option = s_cdate-option.

endloop.

select * from TSP02 into table itab

where PJCREATIME between r_cdate-low

and r_cdate-high.

sort itab by pjcreatime.

loop at itab into wa.

write : / wa-PJIDENT , wa-pjcreatime.

endloop.

Otherwise,it's very much complicated.

[Former Member]

Hi,

Thanks for your help.

That is what I want.

But there is a retrieval problem for r_cdate-high.

Say I input S_CDATE from 10.07.2005 to 13.07.2005

But the SQL only retrieves the data from 10.07.2005 up to 12.07.2005.

Is there anything wrong? (The database table does contain data for 13.07.2005)

[jayanthi_jayaraman]

Hi,

I am also able to see the problem.

Here is the modified sample code.

data v(8).

data itab type standard table of TSP02.

data wa type TSP02.

DATA TXT(9) TYPE C .

SELECT-OPTIONS S_CDATE FOR v no-extension.

ranges r_cdate for txt.

CONCATENATE s_cdate-low0(2) s_cdate-low2(2) s_cdate-low+4(2)

s_cdate-low+6(2) '%' into r_cdate-low.

CONCATENATE s_cdate-high0(2) s_cdate-high2(2) s_cdate-high+4(2)

s_cdate-high+6(2) '%' into r_cdate-high .

select * from TSP02 into table itab

where PJCREATIME between r_cdate-low and r_cdate-high or

pjcreatime like r_cdate-high.

sort itab by pjcreatime.

loop at itab into wa.

write : / wa-PJIDENT , wa-pjcreatime.

endloop.

Hope this meets your requirement.If so,could you please reward points for useful answers and close the thread?

If you need more clarifications,get back.

[Former Member]

Thanks so much,

I just added 1 day to the s_cdate. It works too. ^.

[Former Member]

By the way,

I just found a bug.

If I only specify the s_cdate-low to be >=20050710 on the selection screen,

it only retrieves the date with 20050710, but the date like 20050711, 20050712, 20050713 cannot be retrieved.

What should I do ?

Thanks.

[jayanthi_jayaraman]

Hi,

if option is 'I'.

if not (s_cdate-low is initial and s_cdate-high is initial).

select * from TSP02 into table itab

where PJCREATIME between r_cdate-low and r_cdate-high or

pjcreatime like r_cdate-high.

elseif s_cdate-low is initial.

select * from tsp02 into table itab

where pjcreatime like r_cdate-high.

elseif s_cdate-high is initial.

select * from tsp02 into table itab

where pjcreatime like r_cdate-low.

endif.

endif.

PS:It is applicable if the option is Include.If it is exclude you need to take care of that part also.

[Former Member]

Hi,

Thanks for your prompt reply.

I'm asking the sign, not the option.

How to handle the case for '>=', '>', '<=' for s_cdate-low?

[jayanthi_jayaraman]

Hi,

Sorry.You cannot see <=,>=,<,> if you are using NO-EXTENSION in addition with the select-option.Similarly option is always BT.[i.e. between]

Hope this is clear.

[Former Member]

Hi,

I've used NO-EXTENSION for S_CDATE,

but I still can select the signs from the field.

[jayanthi_jayaraman]

Hi,

Just try this code.I think it is possible to use select-option as such.I found no bug in this code.You test and let me know whether it is useful.Please reward points if it is useful.

data v(8).

data itab type standard table of TSP02.

data wa type TSP02.

DATA TXT(16) TYPE C .

SELECT-OPTIONS S_CDATE FOR v .

ranges r_cdate for txt.

loop at s_cdate.

r_cdate-option = s_cdate-option.

if s_cdate-sign = 'EQ'.

r_cdate-sign = 'BT'.

concatenate s_cdate-low '00000000' into r_cdate-low.

concatenate s_cdate-low '99999999' into r_cdate-high.

else.

r_cdate-sign = s_cdate-sign.

concatenate s_cdate-low '00000000' into r_cdate-low.

concatenate s_cdate-high '99999999' into r_cdate-high.

endif.

append r_cdate.

endloop.

select * from TSP02 into table itab

where PJCREATIME in r_cdate.

sort itab by pjcreatime.

loop at itab into wa.

write : / wa-PJIDENT , wa-pjcreatime.

endloop.

Try it and let me know the result.

Message was edited by: Jayanthi Jayaraman

[Former Member]

Hi,

U can gtry using <b>HAVING Clause...</b>

DATA: COUNT TYPE I, AVG TYPE F.

DATA: CONNID LIKE SBOOK-CONNID.

SELECT CONNID COUNT( * ) AVG( LUGGWEIGHT )

INTO (CONNID, COUNT, AVG)

FROM SBOOK

WHERE

CARRID = 'LH' AND

FLDATE = '19950228'

GROUP BY CONNID

HAVING AVG( LUGGWEIGHT ) > '20.0'.

WRITE: / CONNID, COUNT, AVG.

ENDSELECT.

<b>Try this

Select * from db where field1 = lv_field+0(8).</b>

[Former Member]

It works simply by using the MOVE statement.

Thanks anyway. ^.

[andreas_mann3]

Hi Macy,

for ranges try that:

RANGES r_d FOR bsik-zuonr.

r_d-low = '200507*'.

r_d-option = 'CP'.

r_d-sign = 'I'.

APPEND r_d.

SELECT * FROM bsik

WHERE bukrs = bukrs

AND gjahr = sy-datum(4)

AND lifnr IN lifnr

AND zuonr IN r_d.

...

regards Andreas

[Former Member]

Hi everyone,

I dont whether this is relevant to this topic or not,

But is this not possible???

Suppose i want to select field1 from db with first 8 characters as '20050713' and my field2 is a range table.

Then

data r_date for field1.

r_date-sign = 'I'.

r_date-option = 'CP'.

r_date-low = '20050713*'.

append r_date.

select * from DB

into itab

where field1 in r_date.

I tried this on MARA table and it works fine.

Please let me know ur inputs on this.

With regards,

R Sharath Kumar.