topic Re: modification in the query in Application Development and Automation Discussions

modification in the query

Former Member — Tue, 05 Jul 2005 20:12:50 GMT

hi folks,

I need help in writing this query.This is what I have been working...

select kposn kschl krech kawrt kbetr kwert from

konv INNER JOIN jhak on konv_{knumv = jhak}knumv inner join jhaga on jhak_{avm_nr = jhaga}avm_nr into table cdata where jhaga~inserent = p_custid.

I need to extract data from table 'konv'(cluster table)and the way you link the fields has to be this way.<b>I know using cluster table I cannot use JOIN statements</b> but the data i want is stored in cluster table while the selection field is located is 'jhaga' (transparent table)

Can anyone help me in modifying this query without changing the where condition?

thanks

Santhosh

Re: modification in the query

RichHeilman — Tue, 05 Jul 2005 20:29:08 GMT

I'm not really sure if this will work for you or not. I don't have those 2 tables(jhak and jhaga) in my system.



report zrich_0001.

data: begin of cdata occurs 0,
      kposn type konv-kposn,
      kschl type konv-kschl,
      krech type konv-krech,
      kawrt type konv-kawrt,
      kbetr type konv-kbetr,
      kwert type konv-kwert,
      end of cdata.

  data: begin of ijha occurs 0,
         knumv    type konv-knumv,
         inserent type jhaga-inserent,
        end of ijha.

parameters: p_custid type kna1-kunnr.

start-of-selection.

  select * into corresponding fields of table ijha
        inner join jhak
           on konv~knumv = jhak~knumv
        inner join jhaga
           on jhak~avm_nr = jhaga~avm_nr
                  where jhaga~inserent = p_custid.

  select  kposn kschl krech kawrt kbetr kwert
        from konv
             into table cdata
                  for all entries in ijha
                             where konv~knumv = ijha-knumv.

Regards,

Rich Heilman

Re: modification in the query

Former Member — Tue, 05 Jul 2005 20:41:58 GMT

it is throwing an error

"FROM..." "CONNECTION..." "BYPASSING BUFFER" "UP... TO...ROWS" expected after ijha.

Santhosh

Re: modification in the query

RichHeilman — Tue, 05 Jul 2005 20:52:47 GMT

oh...sorry. This is what happens when you can't do a syntax check because you don't have the tables in your system. Use this select statment in place of the other select to jhak/jhaga.



  select * into corresponding fields of table ijha
        from jhak
           inner join jhaga
             on jhak~avm_nr = jhaga~avm_nr
                  where jhaga~inserent = p_custid.

Regards,

Rich Heilman

Re: modification in the query

Former Member — Tue, 05 Jul 2005 22:07:35 GMT

thanks Rich, I shall award six points to you because my problem is not solved. I will get back to you on that tomorrow. probably I need to rephrase the query, not sure.

Anyway thanks a lot for your help.

santhosh