hello David

thanks for your response, but i tried that with the same ugly result. the only solution i've come up with so far is to run a seperate querry 1st where i create a new 'combined' field:

sele ;
      q_a.*, ;
      q_a.pn+q_a.opt1+q_a.opt2+q_a.opt3+q_a.opt4+q_a.opt5+q_a.opt6+q_a.opt7+q_a.opt8 as combPN ;
   from pnvendor q_a ;
   into cursor TempCursor

and then use the TempCursor as the source for the pnvendor table - that works without memory error.


>Gunnar,
>
>Why not do your join like this instead?
>
>

>LEFT JOIN inv q_b ON ;
>     q_a.pn = q_b.pn and ;
>     q_a.opt1 = q_b.opt1 and ;
>     q_a.opt2 = q_b.opt2 and ;
>     q_a.opt3 = q_b.opt3 and ;
>     q_a.opt4 = q_b.opt4 and ;
>     q_a.opt5 = q_b.opt5 and ;
>     q_a.opt6 = q_b.opt6 and ;
>     q_a.opt7 = q_b.opt7 and ;
>     q_a.opt8 = q_b.opt8 ;
>

>
>>Ok, after finding a solution with the good help of Hilmar, Sergey, and Trey in Thread #840872 i have encountered another problem:

>>"There is not enough memory to complete this operation"

>>Wow - what - why?! i got 256 MB, that should be enough! well before i continue, maybe i should show you the querry. I realy would like to do it this way rather then settingt relations later on the result cursor because i need to be able to querry based on different fields of the 3 tables.

>>SELE ;
>>   q_a.pn, ;
>>   q_a.opt1, ;
>>   q_a.opt2, ;
>>   q_a.opt3, ;
>>   q_a.opt4, ;
>>   q_a.opt5, ;
>>   q_a.opt6, ;
>>   q_a.opt7, ;
>>   q_a.opt8, ;
>>   NVL(q_b.DESC, SPACE(35)) AS DESC, ;
>>   NVL(q_b.um, SPACE(4)) AS um, ;
>>   NVL(q_b.wgt, 000.00000) AS wgt, ;
>>   NVL(q_b.fsmat, 00000000.000) AS fsmat, ;
>>   NVL(q_c.vendorid, SPACE(8)) AS vendorid, ;
>>   NVL(q_c.NAME, SPACE(30)) AS NAME ;
>>   FROM ;
>>      pnvendor q_a ;
>>         LEFT JOIN inv q_b ON ;
>>              q_a.pn+q_a.opt1+q_a.opt2+q_a.opt3+q_a.opt4+q_a.opt5+q_a.opt6+q_a.opt7+q_a.opt8 = ;
>>              q_b.pn+q_b.opt1+q_b.opt2+q_b.opt3+q_b.opt4+q_b.opt5+q_b.opt6+q_b.opt7+q_b.opt8 ;
>>         LEFT JOIN vendor q_c ON q_a.vendorid = q_c.vendorid ;
>>   WHERE ;
>>      BETWEEN(q_a.vendorid, '1004    ', '1004    ') ;
>>   ORDER BY q_a.vendorid,q_a.pn,q_a.opt1,q_a.opt2,q_a.opt3,q_a.opt4,q_a.opt5,q_a.opt6,q_a.opt7,q_a.opt8 ;
>>   into cursor test
>>

>>after playing around and taking command lines out and/or removing some fields, i have found out, that the trouble maker is
>>

>>LEFT JOIN inv q_b ON ;
>>     q_a.pn+q_a.opt1+q_a.opt2+q_a.opt3+q_a.opt4+q_a.opt5+q_a.opt6+q_a.opt7+q_a.opt8 = ;
>>     q_b.pn+q_b.opt1+q_b.opt2+q_b.opt3+q_b.opt4+q_b.opt5+q_b.opt6+q_b.opt7+q_b.opt8 ;
>>

>>the field length of pn = 20 and opt# = 5. so the total length to compare here is 60 characters long. That's not too much to ask for - is it? i have noticed that by just talking out the comparising of the last option (q_a.opt8) it'll work.
>>
>>so i got myself a problem - any ideas?
>>
>>(p.s.: the table size (reccount())
>>- pnvendor = 36235
>>- inv = 57957
>>- vendor = 3118)