topic Re: Dynamic where clause issue in Application Development and Automation Discussions

Dynamic where clause issue

Former Member — Wed, 10 May 2006 09:34:33 GMT

Hi,

The code returns system error which is caught in the Catch statement for the where condition:

( BRFAM LIKE '%L&%' ) AND ( ITPCK = '25' ) AND ( MKLEN = 'KS' ) AND ( MKTGR = 'SE04' ) AND PARPR IN LR_MATNR

If I take out the ITPCK field from the string the program works fine.

Including the ITPCK field which is a quan data type in the where clause works fine in the dev server but returns an error in the QA server.

I have checked the patch levels and the only noticeable diff is the kernel patch level which is 79 in Dev but 85 in QA.

Looking forward to your inputs.

cheers

Aveek

Re: Dynamic where clause issue

Former Member — Wed, 10 May 2006 09:43:37 GMT

Here is the catch statement which traps the error:

CATCH cx_sy_dynamic_osql_error.

cheers

Aveek

Re: Dynamic where clause issue

naimesh_patel — Wed, 10 May 2006 09:47:15 GMT

Hello,

Try to give the value in the quantity field as it is stroed in the field,

data: l_itpck like ztab-itpck,

c_itpck(20).

c_itpck = l_itpck.

and then concatnate your where condition.

Regards,

Naimesh

Re: Dynamic where clause issue

Former Member — Wed, 10 May 2006 09:54:13 GMT

Hai Check the following Code

REPORT ZDYNAMIC_SELECT .

TABLES: VBAK.

DATA: CONDITION TYPE STRING.

DATA: BEGIN OF ITAB OCCURS 0,

VBELN LIKE VBAK-VBELN,

POSNR LIKE VBAP-POSNR,

END OF ITAB.

SELECT-OPTIONS: S_VBELN FOR VBAK-VBELN.

CONCATENATE 'VBELN' 'IN' 'S_VBELN.'

INTO CONDITION SEPARATED BY SPACE.

SELECT VBELN POSNR FROM VBAP INTO TABLE ITAB

WHERE (CONDITION).

LOOP AT ITAB.

WRITE 'hai'.

ENDLOOP.

Thanks & regards

Sreenivasulu P

Re: Dynamic where clause issue

Former Member — Wed, 10 May 2006 10:24:45 GMT

Hi,

I am using the SAP standard conversion routines for the ITCPCK field.

lv_where_condn TYPE SYCHAR512,

lv_col_value_low TYPE string,

lv_col_value_low TYPE string.

lv_col_name = 'ITPCK'.

CALL FUNCTION 'CONVERSION_EXIT_ALPHA_INPUT'

EXPORTING

INPUT = lv_col_value_low

IMPORTING

OUTPUT = lv_col_value_low.

CONCATENATE lv_where_condn lv_par

lv_col_name

lc_equal

lv_col_value_low

INTO lv_where_condn SEPARATED BY space.

I will try to move the quan field to the char format for the specific field ITPCK and get back.

What could be the justifiction for the problem occuring in QA server but not in the DEV server.

cheers

Aveek

Re: Dynamic where clause issue

athavanraja — Wed, 10 May 2006 10:32:07 GMT

whats the error text says in class

cx_sy_dynamic_osql_error

Regards

Raja

Re: Dynamic where clause issue

Former Member — Wed, 10 May 2006 10:42:15 GMT

Hi,

I set the vreak-point in the class below for the all the methods but after the select the flow deoes not seem to go to the catch statemnet. Anyway the error message is:

Exception from the class CX_SY_OPEN_SQL_DB was caught

cheers

Aveek

Re: Dynamic where clause issue

Former Member — Wed, 10 May 2006 11:13:24 GMT

Hi,

I am using the statements:

data:

dref type ref to data,

xref type ref to cx_dynamic_check,

message_txt type string.

CATCH cx_sy_dynamic_osql_error

cx_sy_create_data_error into xref.

message_txt = xref->get_text( ).

message message_txt type 'E'.

MESSAGE s802(dh).

to trap the error message.

Is there a better way to catch the dynamic sql error in the catch statement. The program needs to be transported to the QA server before I can check the message.

cheers

Aveek

Re: Dynamic where clause issue

Former Member — Thu, 11 May 2006 07:47:14 GMT

Hi,

Is this the correct way to trap the error text.

TRY.

SELECT

TAB1~PRVAR

TAB1~PARPR

TAB1~VARID

TAB1~VARCT

TAB1~VARTX

TAB1~DMOAP

TAB2~PRDID

TAB2~BRFAM

TAB2~MATKL

TAB2~BRDIF

TAB2~MEIND

TAB2~MKLEN

TAB2~PCKTY

TAB2~ITPCK

TAB2~PMEIN

TAB2~MKTHK

TAB2~FLIND

TAB2~EDTID

TAB2~BNDTY

TAB2~ITBND

TAB2~ITCSE

TAB2~TPCOL

TAB2~SPFLV

TAB2~PRSHP

TAB2~MKTGR

TAB2~SUBMK

TAB2~BOMHD

TAB2~BRDSC

TAB2~MKSTR

TAB2~EDDSC

TAB2~EDCAT

TAB2~CCIND

TAB2~CGSTY

TAB2~FLTTY

TAB2~CPIND

TAB2~MBIND

TAB2~USPRP

TAB2~EDDET

TAB2~BRGEW

TAB2~NTGEW

TAB2~GEWEI

TAB2~VOLUM

TAB2~VOLEH

TAB2~MAKTX

TAB2~RPLVL

FROM ZDM0705 AS TAB1

INNER JOIN ZDMPCG1 AS TAB2

ON TAB1_{PARPR = TAB2}PRDCD

INTO CORRESPONDING FIELDS OF TABLE lt_data

WHERE (lv_where_condn).

CATCH cx_sy_dynamic_osql_error into xref.

message_txt = xref->get_text( ).

message message_txt type 'I'.

MESSAGE s802(dh).

catch CX_SY_OPEN_SQL_DB into xref.

message_txt = xref->get_text( ).

message message_txt type 'I'.

MESSAGE s801(dh).

ENDTRY.

Looking forward to the inputs.

Is there a better way to get the error text in BADI apart from the sy-msgv1, etc.

cheers

Aveek

Re: Dynamic where clause issue

Former Member — Thu, 11 May 2006 08:06:06 GMT

Hai Aveek

REPORT ZDYNAMIC_SELECT .

TABLES: VBAK.

DATA: CONDITION TYPE STRING.

DATA: BEGIN OF ITAB OCCURS 0,

VBELN LIKE VBAK-VBELN,

POSNR LIKE VBAP-POSNR,

END OF ITAB.

SELECT-OPTIONS: S_VBELN FOR VBAK-VBELN.

CONCATENATE 'VBELN' 'IN' 'S_VBELN.'

INTO CONDITION SEPARATED BY SPACE.

SELECT VBELN POSNR FROM VBAP INTO TABLE ITAB

WHERE (CONDITION).

LOOP AT ITAB.

WRITE 'hello'.

ENDLOOP.

Thanks & Regards

Sreenivasulu P

Re: Dynamic where clause issue

Former Member — Thu, 11 May 2006 08:10:30 GMT

Hi,

The question was:

CATCH cx_sy_dynamic_osql_error into xref.

message_txt = xref->get_text( ).

message message_txt type 'I'.

MESSAGE s802(dh).

catch CX_SY_OPEN_SQL_DB into xref.

message_txt = xref->get_text( ).

message message_txt type 'I'.

MESSAGE s801(dh).

Is this the correct way to get the error text from a catch statement.

cheers

Aveek

Re: Dynamic where clause issue

Former Member — Thu, 11 May 2006 13:41:38 GMT

Hi,

The error is:

An SQL error occurred in the database when accessing a table.

cheers

Aveek

Any inputs will be apreciated.

Re: Dynamic where clause issue

Former Member — Tue, 01 Aug 2006 14:46:15 GMT

Hello.

I think that your latest statement in the concatenation

--> 'S_VBELN.'

has an extra . in the end.

Please remove it and try again.

Hope it helps.

RDM

Re: Dynamic where clause issue

Former Member — Tue, 01 Aug 2006 14:56:59 GMT

Hai Aveek

Go through the following Code

REPORT ZDYNAMIC_SELECT .

TABLES: VBAK.

DATA: CONDITION TYPE STRING.

DATA: BEGIN OF ITAB OCCURS 0,

VBELN LIKE VBAK-VBELN,

POSNR LIKE VBAP-POSNR,

END OF ITAB.

SELECT-OPTIONS: S_VBELN FOR VBAK-VBELN.

CONCATENATE 'VBELN' 'IN' 'S_VBELN.'

INTO CONDITION SEPARATED BY SPACE.

SELECT VBELN POSNR FROM VBAP INTO TABLE ITAB

WHERE (CONDITION).

LOOP AT ITAB.

WRITE 'hello'.

ENDLOOP.

Regards

Sreeni

Re: Dynamic where clause issue

Former Member — Tue, 01 Aug 2006 15:15:47 GMT

hi,

While writing dynamic where codition, we cant specify some conditions like matnr in s_matnr.

IN operator can not be specified.

That is the reason why you are getting dump while execution.

See the following lines in ABAP Help.

... (source_text)

Effect

In Open SQL, you can dynamically specify, at runtime, the logical conditions of the WHERE clause , the HAVING clause, as well as the ON conditions of the FROM clause in a variable source_text as ABAP source text. The condition is true if the contents of the table fields satisfy the logical condition stored in the source_text variable.

Notes

The logical conditions stored in source_text must have the same form as corresponding conditions in the ABAP source text. However, the operator must not be used in the form <b>f1 IN itab1</b>.

Regards,

Sailaja.

Message was edited by: Sailaja N.L.