comparing to year, comparing a select statement against 2 itabs.

[aris_hidalgo]

Hello experts,

I have a couple of questions for my select statement. Here it is:

1. how can I compare a certain field against the current year? for example:

where gjahr eq '2006'...

2. I have a select statement that needs to be compared to two itabs. Here it is:

select f1 f2 f3 f4 from anlc where bukrs = company code

derived from it_equz-iwerk, anln1 eq it_iloa-anlnr, gjahr = system date year, afabe = '01'.

it_equz and it_iloa are the 2 itabs.

Thanks a lot guys and have a nice day!

[vinod_gunaware2]

use system field or function module to get current year or fiscal year

GET_CURRENT_YEAR Get the current fiscal year.

CALL FUNCTION <b>'GET_CURRENT_YEAR'</b>

EXPORTING

BUKRS = '1000' " Company Code

DATE = SY-DATUM " Date to find fiscal year for

IMPORTING

CURRM = w_currm " Current Fiscal Month

CURRY = w_curry " Current Fiscal Year

PREVM = w_prevm " Previous Fiscal Month

PREVY = w_prevy. " Previous Fiscal Year

And use <b>EQ or LIKE</b> statement in select statemt

And use <b>for all entries</b> in another case by combining to internal table into one.

<b>

Tabular Conditions</b>

The WHERE clause of the SELECT statement has a special variant that allows you to derive conditions from the lines and columns of an internal table:

SELECT ... FOR ALL ENTRIES IN <itab> WHERE <cond> ...

<cond> may be formulated as described above. If you specify a field of the internal table <itab> as an operand in a condition, you address all lines of the internal table. The comparison is then performed for each line of the internal table. For each line, the system selects the lines from the database table that satisfy the condition. The result set of the SELECT statement is the union of the individual selections for each line of the internal table. Duplicate lines are automatically eliminated from the result set. If <itab> is empty, the addition FOR ALL ENTRIES is disregarded, and all entries are read.

The internal table <itab> must have a structured line type, and each field that occurs in the condition <cond> must be compatible with the column of the database with which it is compared. Do not use the operators LIKE, BETWEEN, and IN in comparisons using internal table fields. You may not use the ORDER BY clause in the same SELECT statement.

You can use the option FOR ALL ENTRIES to replace nested select loops by operations on internal tables. This can significantly improve the performance for large sets of selected data.

<b>Examples</b>

This condition is true if the column NUM contains numbers between 15 and 45.

... WHERE NUM NOT BETWEEN 1 AND 99.

This condition is true if the column NUM contains numbers not between 1 and 99.

... WHERE NAME NOT BETWEEN 'A' AND 'H'.

This condition is true if the column NAME is one character long and its contents are not between A and H.

... WHERE CITY LIKE '%town%'.

This condition is true if the column CITY contains a string containing the pattern ‘town’.

... WHERE NAME NOT LIKE '_n%'.

This condition is true if the column NAME contains a value whose second character is not ‘n’.

... WHERE FUNCNAME LIKE 'EDIT#_%' ESCAPE '#'.

This condition is true if the contents of the column FUNCNAME begin with EDIT_.

... WHERE CITY IN ('BERLIN', 'NEW YORK', 'LONDON').

This condition is true if the column CITY contains one of the values BERLIN, NEW YORK, or LONDON.

... WHERE CITY NOT IN ('FRANKFURT', 'ROME').

This condition is true if the column CITY does not contain the values FRANKFURT or ROME.

... WHERE ( NUMBER = '0001' OR NUMBER = '0002' ) AND

NOT ( COUNTRY = 'F' OR COUNTRY = 'USA' ).

This condition is true if the column NUMBER contains the value 0001 or 0002 and the column COUNTRY contains neither F nor USA.

regards

vinod

[Former Member]

data:gv_year like gjahr.

gv_year = sy-datum+0(4).

select f1 f2 f3 f4 from anlc where bukrs = company code

derived from it_equz-iwerk, anln1 eq it_iloa-anlnr, gjahr = <b>gv_year</b>, afabe = '01'.

Message was edited by: kishan negi

[former_member186078]

Hi,

You can make use of sy-datum in ur select statements for retrieving the current fiscal year data.

In the where clause of select statements you can use offset of sy-datum.

For example,

gjahr eq sy-datum(4).

[Former Member]

Hi,

u cannot use 2 internal in a single select query ;

to solve this problem u have to convert any one of the internal table into select-option.

this can be done using ranges.

data : select_iloa type ranges of anlnr.

loop at iloa.

select_iloa-SIGN = 'I'.

select_iloa-OPTION = 'EQ'.

select_iloa-LOW = iloa-anlnr.

append select_iloa.

endloop.

Now ur select query

select f1 f2 f3 f4 from anlc

into table <Enter internal table name >

for all entries in it_equz

where bukrs = it_equz-iwerk

and anln1 in select_iloa

and gjahr = sy-datum+0(4).

Please feel free to ask further question

Mark helpful answers

Regards

[Former Member]

Hi,

To get the current year you can use CACS_DATE_GET_YEAR_MONTH

Pass the SY_DATUM it will return the Month and Year.

You can not use a SELECT for internal tables.

You will have to loop on one table using where condition.

Inside the loop you will have to use READ or another LOOP with where condition to read the second table.

If both the condition is satisfied then you can append the required fields in the final internal table.

Regards,

Sameena

[aris_hidalgo]

Hello again friends,

In my select statement where I compare bukrs with it_equz-iwerk, the bukrs is the name of the company where as iwerk is the plant. For example,

bukrs IWERK

AAA 1000

BBB 2000

CCC 3000

Is there an FM that I can use to automatically convert the iwerk value to its respective company code?

[Former Member]

Hello viraylab,

werks i.e plant and the bukrs i.e company code are two different entity they cannot be equated to each other. However based on one you can derive the other. U can read via table t001w of use CF_TA_T001W_read.

[aris_hidalgo]

Hi abhijiit,

How can I put that in my code? sorry i'm kinda new to ABAP. Thanks again for all your wonderful help!