topic Re: select query not working in Application Development and Automation Discussions

select query not working

Former Member — Tue, 03 Aug 2010 12:23:28 GMT

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

Re: select query not working

SuhaSaha — Tue, 03 Aug 2010 12:36:21 GMT

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

Re: select query not working

Former Member — Tue, 03 Aug 2010 12:40:31 GMT

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

Re: select query not working

Former Member — Tue, 03 Aug 2010 13:09:37 GMT

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)

Re: select query not working

Former Member — Tue, 03 Aug 2010 13:37:04 GMT

What kind of data type does w_date_low and w_date_high has?

Regards,

Fabio Purcino

Re: select query not working

bbalci — Tue, 03 Aug 2010 14:59:29 GMT

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.

Re: select query not working

Former Member — Tue, 03 Aug 2010 18:34:37 GMT

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

Re: select query not working

former_member189059 — Wed, 04 Aug 2010 04:19:19 GMT

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.

Re: select query not working

Former Member — Wed, 04 Aug 2010 08:45:59 GMT

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

Re: select query not working

Former Member — Wed, 04 Aug 2010 08:56:38 GMT

Hi,

What is the value in parameters W_DATE_LOW & W_DATE_HIGH ?

Regards

Vinod

Re: select query not working

Former Member — Wed, 04 Aug 2010 08:59:13 GMT

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

Re: select query not working

Former Member — Wed, 04 Aug 2010 09:00:13 GMT

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,

Re: select query not working

SuhaSaha — Wed, 04 Aug 2010 09:02:54 GMT


> 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

Re: select query not working

Former Member — Wed, 04 Aug 2010 09:12:01 GMT

Hello everyone,

thanks for ur reply.

It help me!!!!

Thanks

Manisha

Re: select query not working

SuhaSaha — Wed, 04 Aug 2010 09:14:27 GMT


> 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

Re: select query not working

Former Member — Wed, 04 Aug 2010 09:20:44 GMT

Hi Suhas,

Thanks for ur reply.

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

Thanks,

Manisha