error in select statement

[Former Member]

Hi frnds,

i am unable to write select statement given i need to check with the first 6 characters of CHARG field.

SELECT MATNR

WERKS

LGORT

CHARG

FROM MCHB

INTO TABLE IT_MCHB

FOR ALL ENTRIES IN IT_MARA

WHERE MATNR = IT_MARA-MATNR

AND WERKS = WERKS

AND LGORT = LGORT

AND CHARG+0(6) <= DATE_FRM

AND CHARG+0(6)>= DATE_TO.

above hilighted code giving me error.

I am getting error as field charg+0(6) is unknow.

regards,

sanjay

Edited by: sanjay jaju on Nov 12, 2008 12:32 PM

[Former Member]

Hi,

Tell me the error what u r getting

Kalp...

[prasanth_kasturi]

hi,

or else pass that into a variable and use it for comparision

var = CHARG+0(6).

and use Var for comparision

regards

Prasanth

[Former Member]

Hi ,

It would definetly give an error ... the select query is looking for CHARG+0(6) in the table

which doesnt exist ...

u can get the validation done on the charg in later stages too if feasible

Regards

Renu

[Former Member]

How can take that with a variable.

how to compare we need to check it from the database table.

[prasanth_kasturi]

hi,

Sorry for the previous reply,

in where condition you must give a field in the table but CHARG+0(6) is not a field

You must bring all the data related to an internal table

then loop through the internal table check the requied condition and delete the unnecessary records

regards

Prasanth

Edited by: Prasanth Kasturi on Nov 12, 2008 12:41 PM

[Former Member]

Hi,

In Select query dont check that condition..After selection you can delete the internal table

delete lt_mchb where charg+0(6) <= sy-datum.

Regards,

Kalp..

[Former Member]

Hi,

It will definitely give error as it is checking for field CHARG+0(6) in database table MCHB that is not there. Select all the entries in IT_MCHB table:

SELECT MATNR
WERKS
LGORT
CHARG
FROM MCHB
INTO TABLE IT_MCHB
FOR ALL ENTRIES IN IT_MARA
WHERE MATNR = IT_MARA-MATNR
AND WERKS = WERKS
AND LGORT = LGORT.

Then loop into the entries and check the condition:

LOOP AT IT_MCHB INTO WA_MCHB.
    VAR1 = WA_MCHB-CHARG+0(6).                       "Declare variable of type char  (6 chars)
    IF VAR1<= DATE_FRM AND VAR1 >= DATE_TO. 
*Do Something
    ELSE.
* Delete the record from internal table
    ENDIF. 
ENDLOOP.

Regards,

Saba

[i048168]

Hi,

Please check this one out.

concatenate DATE_FRM '%' into DATE_FRM.

concatenate DATE_TO '%' into DATE_TO.

SELECT MATNR

WERKS

LGORT

CHARG

FROM MCHB

INTO TABLE IT_MCHB

FOR ALL ENTRIES IN IT_MARA

WHERE MATNR = IT_MARA-MATNR

AND WERKS = WERKS

AND LGORT = LGORT

AND CHARG <= DATE_FRM

AND CHARG >= DATE_TO.

Regards,

Vadi

Sorry, this won't workout. I forget the fact that the wild card characters will be applicable only to LIKE in the WHERE clause.

Edited by: Vadivelan B on Nov 12, 2008 1:10 PM

[ThomasZloch]

What is the logic for the MCHB-CHARG values? So the first six characters seem to be a date, what about the other four? Assuming they contain only letters A to Z, you could fill DATE_FRM with e.g. "080101AAAA" and DATE_TO with "081112ZZZZ" and select WHERE CHARG BETWEEN DATE_FRM AND DATE_TO.

Or similar, depending on the actual logic in CHARG.

Thomas

[Former Member]

Hi

the problem could be that you are comparing the same CHARG+0(6) for two AND conditions which is not possible.

please remove one condition or put a or statemnet between them

Thanks

Nitin Sachdeva

[Former Member]

Hi

the problem could be that you are comparing the same CHARG+0(6) for two AND conditions which is not possible.

please remove one condition or put a or statemnet between them

Thanks

Nitin Sachdeva

[Former Member]

thanks