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

dynamic query not working

Former Member — Mon, 20 Jul 2009 12:11:49 GMT

im using a dynamic query to put in an internal table but its not working

Can you see whats wrong in that?

gv_query = ' au~uname AS OTCTUSERNM,'.

CONCATENATE gv_query 'a1_{object' '.' 'a1}field' ' AS 0TCTAUTH' INTO gv_query.

CONCATENATE gv_query 'au~to_dat AS OTCTADTO, OTCTOBJNM, ' INTO gv_query.

SELECT DISTINCT (gv_query)

INTO CORRESPONDING FIELDS OF TABLE gi_ds1

FROM agr_users AS au INNER JOIN

agr_1251 AS a1

ON au_{agr_name = a1}agr_name.

Re: dynamic query not working

Former Member — Mon, 20 Jul 2009 12:18:49 GMT

'.'

What's it doing there?

You better print out the value in gv_query to see how it actually looks like. That might give you some hints as well.

Re: dynamic query not working

Former Member — Mon, 20 Jul 2009 12:22:12 GMT

the '.' is the concatenation of 2 fields. Is it the correct way of merging 2 fields in a dynamic query?

Re: dynamic query not working

Former Member — Mon, 20 Jul 2009 12:24:16 GMT

Actually, i don;t know: haven't used it like that.

But, my advice stands: print out the content of gv_query and you'll find a few more errors.

Re: dynamic query not working

Former Member — Mon, 20 Jul 2009 12:29:18 GMT

If I am not wrong then '.' is not required I hope so.

You can try like this

gv_query = 'au~uname AS OTCTUSERNM'.
CONCATENATE gv_query 'a1~object a1~field AS 0TCTAUTH' INTO gv_query SEPARATED BY space.

CONCATENATE gv_query 'au~to_dat AS OTCTADTO' INTO gv_query SEPARATED BY space.

It may work

Regards

Shiba Prasad Dutta

Re: dynamic query not working

Former Member — Mon, 20 Jul 2009 12:29:54 GMT

Hello,

Following are the observations:

1. gv_query = ' au~uname AS OTCTUSERNM,'.

>> In above string i think you want to use uname column as OTCTUSERNM and it is correct

2. CONCATENATE gv_query 'a1_{object' '.' 'a1}field' ' AS 0TCTAUTH' INTO

gv_query.

>> In statement 2 above I am not able to understand whey 'a1_{object' '.' 'a1}field' this is incorrect.

Also '.' (dot) in not required in query it will be either '~' or 'AS' as valid chars.

3. CONCATENATE gv_query 'au~to_dat AS OTCTADTO, OTCTOBJNM, ' INTO gv_query.

>> In statement 3 above whey OTCTOBJNM is used there is no such column exit in AGR_USERS.

4. SELECT DISTINCT (gv_query)

INTO CORRESPONDING FIELDS OF TABLE gi_ds1

FROM agr_users AS au INNER JOIN

agr_1251 AS a1

ON au_{agr_name = a1}agr_name.

>> In above statement a1 is used as alias for AGR_USERS then why it it not there in statement 3 where instead au is used.

Normarlly query is like this

'<alias1>_{<col_name1> as <col_desc1>, <alias2>}<col_name2> as <col_desc2>......up to n cols

In this case it is possible that alias1 and alias2 are used for different table in case of joins and for same table in case of self joins.

Finally, You can only define colums in dynamic query those are available in the table from which we have to select the data.

Hope this helps.

Thanks,

Augustin.

Re: dynamic query not working

Former Member — Mon, 20 Jul 2009 12:40:02 GMT

Hi Augustarian

I need the concatenation of the two fields as OCTCTAUTH i.e a1_object.a1field

Re: dynamic query not working

Former Member — Mon, 20 Jul 2009 12:47:11 GMT

Hello Anjali,

There are two things:

1. you want to contcatenate two columns and give them in query for fetching result which is impossible as

ABAP sql require well formed SQL and concatenation is not allowed in query.

2. you want to concatenate output of two columsn so in that case first get values of both the fields in internal table and then

either in SELECT..ENDSELECt or in internal table LOOP you can go on concatenating values for required column.

Finally, concatenating using "." is not allowed in ABAP query.

It has nothing to do with dynamic query. if you try same thing using static query that will give you same error.

Hope this helps,

Augustin.

Re: dynamic query not working

Former Member — Mon, 20 Jul 2009 12:58:27 GMT

oh i see

i didnt know concatenation is not allowed during dynamic query

in fact its because of the concatenation that i did a dynamic query

is there other alternative or i should do a loop to concatenate the fields?

plz advise

Re: dynamic query not working

Former Member — Tue, 21 Jul 2009 05:32:57 GMT

Hello Anjali,

Best practice in ABAP is to query the data into internal table and then process it in internal table loop.

So you can do concatenation in loop it will be much faster than SELECT..ENDSELECT loop.

Thanks,

Augustin.

Re: dynamic query not working

Former Member — Thu, 23 Jul 2009 10:16:39 GMT

thanks a lot Augustin