topic Inner Join for Dynamic Select statement in Application Development and Automation Discussions

Inner Join for Dynamic Select statement

Former Member — Thu, 25 Jun 2009 23:13:01 GMT

Hi All,

Can some one please help me in rewriting the below select statement where i have to remove the existing table1 by putting a dynamic table name which has the same table structure.


  select a~zfield1 
           a~zfield2
      from ztab1 as a
       inner join ztab2 as b
           on b~ztab1-zfield3 = a~ztab2-zfield3
     where a~zfield4 = 'A'.

I am looking something as below. But encountering an error when using the below statement
  select a~zfield1 
           a~zfield2
      from (v_ztab1) as a
       inner join ztab2 as b
           on b~ztab1-zfield3 = a~ztab2-zfield3
     where a~zfield4 = 'A'.

No Separate selects please. Please help me in rewriting the same select statement itself.

Regards,

PSK

Re: Inner Join for Dynamic Select statement

Former Member — Fri, 26 Jun 2009 00:04:15 GMT

Hello, PSK

Try to put the code INTO ... in the INNER JOIN

Greetings

Re: Inner Join for Dynamic Select statement

Former Member — Fri, 26 Jun 2009 04:18:52 GMT

Hi,

You are missing the 'INTO'.. I mean where is the resultant going to get stored... Please do the changes..

Re: Inner Join for Dynamic Select statement

Former Member — Fri, 26 Jun 2009 04:21:45 GMT

hi,

What error you are getting ?

Also INTO is missing from the statement.

SELECT p_{carrid p}connid f_{fldate b}bookid

INTO TABLE itab

FROM ( spfli AS p

INNER JOIN sflight AS f ON p_{carrid = f}carrid AND

p_{connid = f}connid )

WHERE p~cityfrom = 'FRANKFURT' AND

p~cityto = 'NEW YORK' .

thanks