Solved: Select statement

former_member188326 · ‎10-07-2005

In open sql select query i want to extract year from database column and compare it with entered value.

for eg.

i want to get employees joined in year 2005

for that i have to extract year from the database column and compare it with 2005

How is it possible in select query?

Please Reply soon i have to complete this today.

Thanks in advance,

Regards,

Bharat.

Former Member · ‎10-07-2005

Hi,

you have not told what is the column type you used in the database table.

lets consider the name of the column is join_date and type is of sy-datum, and your table name is zemp.

then do the following.

data: LT_zemp like zemp,

ls_zemp like line of lt_zemp.

data: limit_date like sy-datum values '20050101'. "1st jan 2005

select * from zemp into ls_zemp.

if ls_zemp-join_date >= limit_date.

append ls_zemp to lt_zemp.

endif.

clear ls_zemp.

endselect.

please reward point if it answers your need.

Former Member · ‎10-07-2005

Your question is not so clear...

please give more details about the column etc.

I think yopu can use

PARAMETERS PA_YEAR(4).

SELECT * FROM DB_TAB INTO TABLE WHERE YEAR = PA_YEAR.

If not satified give more details ...

former_member188326 · ‎10-07-2005

I want a query like this

join_dat = '20050101'.

select ename from pa0001 into ename where

begda4(4) = join_dat1(4).

here begda is column of type dats

so i have extract year from the date and then compare it with 2005.

andreas_mann3 · ‎10-07-2005

Hi,

try:


RANGES date FOR pa0001-begda.
CONCATENATE  joindat(4) '0101' INTO date-low.
CONCATENATE  joindat(4) '1231' INTO date-high.
date-option = 'BT'.
date-sign = 'I'.
APPEND date.


SELECT ename FROM pa0001 INTO ename WHERE
begda IN date.
  WRITE: / ename.
ENDSELECT.

Andreas

Former Member · ‎10-07-2005

Hi Andreas,

I think your solution is the BEST Optimized!

Congratulations!

except for the select ... endselect.

Prefer SELECT INTO TABLE

Former Member · ‎10-07-2005

Hi,

you have not told what is the column type you used in the database table.

lets consider the name of the column is join_date and type is of sy-datum, and your table name is zemp.

then do the following.

data: LT_zemp like zemp,

ls_zemp like line of lt_zemp.

data: limit_date like sy-datum values '20050101'. "1st jan 2005

select * from zemp into ls_zemp.

if ls_zemp-join_date >= limit_date.

append ls_zemp to lt_zemp.

endif.

clear ls_zemp.

endselect.

please reward point if it answers your need.

Former Member · ‎10-07-2005

Hi Bharat,

just one correction.

in the internal table declartion part..

instead of those like zemp statements..do the following

types: begin of ty_zemp.

include structure <your db table name>.

types: end of ty_zemp.

data: lt_zemp type ty_zemp,

ls_zemp like line of lt_zemp.

now declare the date "limit_date" as i wrote in previous post, and then do the select query as i already gave.

let us know in case of problem..and also let us know the type of date column in your table, if it is not of type sy-datum.

Does this solve your query??

Former Member · ‎10-07-2005

Hi,

I suggest you usr the LIKE addition in the where clause.

for example.

imagine something like this is stored in the database.

31 december 2004 = 20041231

parameters : pa_year type year.

start-of-selection.

data : l_like(10) value '______'.

concatenate year l_like into l_like.

select * from db_tab where date like l_like.

endselect.

this will retrieve everything for yea entered by the end user

PS :

-NOTE THAT THE LIKE ADDITION is not the best optimized select statement! It is runtime expensive.

-note that select...endselect is neither the best optimized select

Message was edited by: STEPHAN KAMINSKI

Former Member · ‎10-07-2005

Hi,

Please follow this.

PARAMETERS : P_YEAR TYPE VBKD-GJAHR.

select x1 x2 from tab_name appending corresponding values of table t_itab where year =

P_YEAR.

t_itab is the internal table containing the details you need from the database table.

Replace x1 , x2, year with the correct field values.

Please reward points if this explanation is useful.

Regards,

Siva

Former Member · ‎10-07-2005

Try this.

DATA WA(5).

DATA WA_YEAR(4) VALUE '2005'.

CONCATENATE WA_YEAR '%' INTO WA .

SELECT * FROM <DBTAB>

WHERE <DATE> LIKE WA.

Cheers.

Former Member · ‎10-07-2005

Hi Bharat,

thanks for your second mail..it cleared the doubt..here is the solution.please reward points if its ok.

I just tested this , it works.your lt_tab tabel will contain all the ename whose begda(4) > 2005

Data: begin of lt_tab occurs 0,

ename like pa0001-ename,

begda like pa0001-begda,

end of lt_tab.

select ename begda from pa0001 into lt_tab.

if lt_tab-begda(4) eq '2005'.

append lt_tab.

endif.

endselect.

former_member188326 · ‎10-07-2005

Hi Anid,

Thanks for your quick reply but my present query is here

select single SUM( anzhl ) sum( kverb )

into (SLQUOTA,slded)

from pa2006

where pernr = wa_pa0001-perno

and ktart = 60

and begda = begda+0(4)

group by ktart.

The line marked star is my query

i have to compare like in raw

year(begda) = begda+0(4)

does this is possible?

Please reply soon,

Thanks again,

Bharat.

Former Member · ‎10-07-2005

Hi Bharat

DATA WA(5).

CONCATENATE begda(4) '%' INTO WA .

*( begda(4) should have the year value say 2005 )

select single SUM( anzhl ) sum( kverb )

into (SLQUOTA,slded)

from pa2006

where pernr = wa_pa0001-perno

and ktart = 60

and begda like wa

group by ktart.

Cheers

Former Member · ‎10-07-2005

Bharat,

This answer was provided to you

-

Andreas Mann

Posts: 1,066

Registered: 3/12/04

Re: Select statement

Posted: Oct 7, 2005 10:08 AM Reply E-mail this post

Hi,

try:

RANGES date FOR pa0001-begda.

CONCATENATE joindat(4) '0101' INTO date-low.

CONCATENATE joindat(4) '1231' INTO date-high.

date-option = 'BT'.

date-sign = 'I'.

APPEND date.

SELECT ename FROM pa0001 INTO ename WHERE

begda IN date.

WRITE: / ename.

ENDSELECT.

Andreas

Former Member · ‎10-07-2005

Hi Continuing on Sanjay's explanation I guess that

CONCATENATE begda(4) '%' INTO WA .

should be written as

CONCATENATE '%' begda(4) INTO WA .

i.e the % should be prefixed.

Please let us know if it helps.

Regards,

Siva

Message was edited by: Sivakumar Muthusamy

Former Member · ‎10-07-2005

The best source has usual is SAP

Here is the way to read data in open sql

http://help.sap.com/saphelp_47x200/helpdata/en/fc/eb3983358411d1829f0000e829fbfe/content.htm

Former Member · ‎10-07-2005

Hi Sivakumar Muthusamy,

Your addition to Sanjay's post is incorrect. Just wanted to make sure it is noted.

Bharat,

There are quite a few perfectly correct answers in this thread (including what A Mann gave). Good if you reward and close it.

cheers,

Former Member · ‎10-07-2005

Hi,

here is the solution, for your updated query.

DATA: START_DAT LIKE SY-DATUM VALUE '20050101',

END_DAT LIKE SY-DATUM VALUE '20051231'.

select single SUM( anzhl ) sum( kverb )

into (SLQUOTA,slded)

from pa2006

where pernr = wa_pa0001-perno

and ktart = 60

and ( begda GE START_DAT OR

begda LE END_DAT )

group by ktart