topic Re: How to define this SQL ? in Application Development and Automation Discussions

How to define this SQL ?

Former Member — Wed, 13 Jul 2005 03:47:39 GMT

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.

Re: How to define this SQL ?

jayanthi_jayaraman — Wed, 13 Jul 2005 03:55:43 GMT

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.

Re: How to define this SQL ?

Former Member — Wed, 13 Jul 2005 04:04:14 GMT

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%'?

Re: How to define this SQL ?

Former Member — Wed, 13 Jul 2005 04:08:53 GMT

Hi,

U can gtry using HAVING Clause...

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.

Try this

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

Re: How to define this SQL ?

Former Member — Wed, 13 Jul 2005 04:11:22 GMT

It works simply by using the MOVE statement.

Thanks anyway. ^.

Re: How to define this SQL ?

Former Member — Wed, 13 Jul 2005 08:36:50 GMT

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.

Re: How to define this SQL ?

Former Member — Wed, 13 Jul 2005 08:50:57 GMT

Hi,

U can search for a range using IN

eg:

select options field2 for xxx-yyy.

select * from db where field1 IN field2.

SELECT-OPTION: matnr for s_matnr.

SELECT * FROM MARA WHERE matnr IN s_matnr.

Hope this helps.

Thanks & Regards,

Judith.

Re: How to define this SQL ?

andreas_mann3 — Wed, 13 Jul 2005 09:12:36 GMT

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

Re: How to define this SQL ?

Former Member — Wed, 13 Jul 2005 09:15:30 GMT

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.

Re: How to define this SQL ?

jayanthi_jayaraman — Wed, 13 Jul 2005 09:37:54 GMT

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.

Re: How to define this SQL ?

Former Member — Wed, 13 Jul 2005 10:03:01 GMT

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

Re: How to define this SQL ?

jayanthi_jayaraman — Wed, 13 Jul 2005 10:08:37 GMT

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?

Re: How to define this SQL ?

Former Member — Wed, 13 Jul 2005 10:15:43 GMT

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 ?

Re: How to define this SQL ?

jayanthi_jayaraman — Wed, 13 Jul 2005 10:23:38 GMT

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.

Re: How to define this SQL ?

Former Member — Wed, 13 Jul 2005 10:27:56 GMT

Hi,

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

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

Re: How to define this SQL ?

jayanthi_jayaraman — Wed, 13 Jul 2005 11:53:24 GMT

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

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.

Re: How to define this SQL ?

Former Member — Thu, 14 Jul 2005 03:13:41 GMT

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)

Re: How to define this SQL ?

jayanthi_jayaraman — Thu, 14 Jul 2005 03:42:05 GMT

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.

Re: How to define this SQL ?

Former Member — Thu, 14 Jul 2005 04:30:03 GMT

Thanks so much,

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

Re: How to define this SQL ?

Former Member — Thu, 14 Jul 2005 09:11:27 GMT

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.