topic innerjoin + possiblekeys... in Application Development and Automation Discussions

innerjoin + possiblekeys...

Former Member — Wed, 13 Sep 2006 09:24:53 GMT

Hello friends,

I have have two tables, lets saay tab1 have 5 fields ( all keys) and tab2 have more then 5 fields and ( 4 keys ). Now both tables have 4 keys same, and I want to select the 5th key from table tab1. Can I make an inner join like this

Select t1~fild5

from tab1 as t1

inner join tab2 as t2

on t1_{field1 = t2}field1

t1_{field2 = t2}field2

t1_{field3 = t2}field3

t1_{field4 = t2}field4

INTO l_field

where

t2~filed1 = s_val1

t2~filed2 = s_val2

t2~filed3 = s_val3

t2~filed4 = s_val4

Do you guys think it will work, ?? would be nice if you please let me know if there is any syntax error too.

Regards,

Malang

Re: innerjoin + possiblekeys...

Former Member — Wed, 13 Sep 2006 09:27:42 GMT

code looks fine , did u get any errors?

Re: innerjoin + possiblekeys...

Former Member — Wed, 13 Sep 2006 09:28:03 GMT

Hi,

Your query has no problem. Just change IN clause to SELECT-OPTIONS.

Select t1~fild5

from tab1 as t1

inner join tab2 as t2

on t1_{field1 = t2}field1

t1_{field2 = t2}field2

t1_{field3 = t2}field3

t1_{field4 = t2}field4

INTO l_field

where

<b>t2~filed1 IN s_val1

t2~filed2 IN s_val2

t2~filed3 IN s_val3

t2~filed4 IN s_val4.</b>

Thanks,

Vinay

Re: innerjoin + possiblekeys...

Former Member — Wed, 13 Sep 2006 09:29:17 GMT

Hello Chandraskhar,

Just want to confirm, is that the correct way to select the 5th field from the tab1 ?

I have not write code yet, just want to know if it works like this or not ? do you think is there any error ?

Re: innerjoin + possiblekeys...

anversha_s — Wed, 13 Sep 2006 09:29:22 GMT

hi,

chk this sample .

SELECT a_{Bill a}plant Number b~BillingDate

INTO TABLE int_table

FROM ( ztable_numplant AS a

INNER JOIN

ztable_numdate AS b

ON a_{billnimber = b}billnumber ).

************you change ur code like this ********

*use single in ur query

Select <b>single</b> t1~fild5

INTO l_field

from tab1 as t1

inner join tab2 as t2

on t1_{field1 = t2}field1

t1_{field2 = t2}field2

t1_{field3 = t2}field3

t1_{field4 = t2}field4

where

t2~filed1 = s_val1

t2~filed2 = s_val2

t2~filed3 = s_val3

t2~filed4 = s_val4

Re: innerjoin + possiblekeys...

Former Member — Wed, 13 Sep 2006 09:32:27 GMT

Syed,

It surely works like that..the only thing in your code which can be a worry is the where condition...

In your where condition you are equating the fields to s_val1 etc. I hope that S_val1 is a parameter and not select-option. If it is select-option better use IN operator and if it is a parameter make sure you enter some data otherwise your select would not return the specified data.

Re: innerjoin + possiblekeys...

Former Member — Wed, 13 Sep 2006 09:35:31 GMT

Hi Syed

Depending on the combination from Field 1 to 4, there

can be different entries in TAB1 for Field5. So you

should either use SELECT/ENDSELECT or INTO TABLE

combination for your select to extract Field 5. Also i

assume S_VAL 1 to 4 are either varialbes or parameters.

If these are select-options you need to use IN operator.

Itz also better if you join the other way TAB2 first

and TAB1 second as TAB1 will have more entries than TAB2.

It should be some thing like:

  select t1~fld5 into table it_values
         from table tab2 as t2 
         inner join tab1 as t1 
         on t2~fld1 = t1~fld1
         and t2~fld2 = t1~fld2
         and t2~fld3 = t1~fld3
         and t2~fld4 = t1~fld4
         where t2~fld1 = s_var1
         and   t2~fld2 = s_var2
         and   t2~fld3 = s_var3
         and   t2~fld4 = s_var4.

Kind Regards

Eswar

Re: innerjoin + possiblekeys...

Former Member — Wed, 13 Sep 2006 09:38:05 GMT

Hi syed,

Your code works perfetly , as suggestd by others use IN in the where condition

Re: innerjoin + possiblekeys...

Former Member — Wed, 13 Sep 2006 09:42:09 GMT

Hello All,

Many thanks for your suggestions, I will write the code and will test, and will share my results, till then, thanks again

Regards,