select query not working

[Former Member]

Hello Experts,

i am using select query like

SELECT SHKZG SUM( DMBTR ) AS DMBTR 
  FROM BSIK INTO CORRESPONDING FIELDS OF TABLE ITAB_BSIK
  WHERE BUKRS = W_BUKRS
  AND LIFNR = WA_BSEG-LIFNR
  AND ( BLDAT BETWEEN W_DATe_low AND W_DATe_high )
  GROUP BY SHKZG.

I checked with table by giving the values same as here.

In table m getting data , but dont know y after executing this query m not getting results.

value of sy-subrc equals to 4.

Can any body tel me what might be the problem??

Thanks & Regards,

Manisha

Edited by: Rob Burbank on Aug 3, 2010 11:12 AM

[SuhaSaha]

Looks like conversion exit problem on LIFNR. Try using 'CONVERSION_EXIT_ALPHA_INPUT' before using the field in the SELECT stmt.

[Former Member]

Dear Suhas,

I have done that also.

When i removed that

BLDATE Between w_date_low and w_date_high

i get data in ITAB_BSIK

But i want for that particular date range only.

Thanks

Manisha

[Former Member]

but where the selection data do come from ? some selection screen or any other type if input (I mean other db tables, file.... and so on)

[Former Member]

What kind of data type does w_date_low and w_date_high has?

Regards,

Fabio Purcino

[bbalci]

Hello ,

SUM and AVG commands runs on your database system and calculations with type floating numbers.

It can cause a low performance and sometimes wrong results.

In your example ,

if you are using Packed numbers in your internal table for DMBTR ( like bseg-dmbtr )

and sum of the related records does not fit into this fields than select can not run.

I can recommend u this code :

DATA :

begin of w_bsik,

shkzg LIKE bsik-shkzg,

dmbtr LIKE bsik-dmbtr,

END OF w_bsik,

t_bsik LIKE STANDARD TABLE OF w_bsik.

SELECT shkzg dmbtr FROM bsik INTO w_bsik

WHERE ...

COLLECT w_bsik INTO t_bsik. <--- Collect command does sum operation based on SHKZG

ENDSELECT.

I hope it helps.

[Former Member]

SELECT SHKZG SUM( DMBTR ) AS DMBTR 
  FROM BSIK INTO CORRESPONDING FIELDS OF TABLE ITAB_BSIK
  WHERE BUKRS = W_BUKRS
  AND LIFNR = WA_BSEG-LIFNR
  AND  BLDAT in W_DATE   " Then entire range will be here.if w_date is a select options.
  GROUP BY SHKZG. 

Have you tried this??

Regards

Mishra

[former_member189059]

Not sure, but would this work?

SELECT SHKZG SUM( DMBTR ) AS DMBTR 
  FROM BSIK INTO CORRESPONDING FIELDS OF TABLE ITAB_BSIK
  WHERE BUKRS = W_BUKRS
  AND LIFNR = WA_BSEG-LIFNR
  AND ( BLDAT GE W_DATe_low AND BLDAT LE W_DATe_high )
  GROUP BY SHKZG.

[Former Member]

Hello Balci,

I tried ur code too, but problem remains same with the SELECT statement.

it works fine when i remove

BLDATE BETWEEN W_DATE_LOW AND W_DATE_HIGH

this condition from select query.

I am passing this values from script. When i debug i m getting values in this fields.

As Kris said, i tried that also but not getting results....

Can u plz tel me wat might b the problem with BLDATE????

Thanks & Regards,

Manisha

[Former Member]

Hi,

What is the value in parameters W_DATE_LOW & W_DATE_HIGH ?

Regards

Vinod

[Former Member]

Hi,

W_DATE_LOW = 28.07.2010

W_DATE_HIGH = 31.07.2010

that i am giving in F.27 transaction.

If i change this values it reflects in W_DATE_LOW n W_DATE_HIGH.

Thanks

Manisha

[Former Member]

Hi,

Problem may occur when you pass your w_date_low and w_date_high to script. Please have a check if those days has data type is date and internal format (if day is Aug 2rd, 2010 it should be 20100803).

regards,

[SuhaSaha]

>

> I am passing this values from script. 

Where in your previous posts you have mentioned this point ? Make sure you provide complete details for getting better & faster responses.

BR,

Suhas

[Former Member]

Hello everyone,

thanks for ur reply.

It help me!!!!

Thanks

Manisha

[SuhaSaha]

>

> that i am giving in F.27 transaction.
> If i change this values it reflects in W_DATE_LOW n W_DATE_HIGH.

As you have mentioned you're passing the values in your script i assume you've written a subroutine to fetch the data.

As the data in passed in ITCSY structure i assume the dates to be in external format (as you say in your case DD.MM.YYYY).

But when selecting data you've to pass the dates in internal format (YYYYMMDD). For this use the FM 'CONVERT_DATE_TO_INTERNAL'.

BR,

Suhas

[Former Member]

Hi Suhas,

Thanks for ur reply.

I got solution, i was doing the same mistake!!!

Thanks,

Manisha