thanks for the help, it is working now : here the command i use : $ibis -v -q "SELECT nation.name FROM nation,region where region.name='AFRICA' and nation.regionkey = region.regionkey " -o $outfile -d $db/nation -d $db/region
and the output : Constructed a part named nation Constructed a part named region countQuery::evaluate -- Select count(*) From region Where region.name == "AFRICA" --> 1 countQuery::evaluate -- duration: 0 sec(CPU), 0.0324917 sec(elapsed) jNatural::count(From nation,region Where region.name='AFRICA' and nation.regionkey = region.regionkey ) -- duration: 0.001 sec(CPU), 0.0315905 sec(elapsed) select nation.name From nation,region Where region.name='AFRICA' and nation.regionkey = region.regionkey -- duration: 0.001 sec(CPU), 0.0845362 sec(elapsed) doQuaere -- "SELECT nation.name FROM nation,region WHERE region.name='AFRICA' and nation.regionkey = region.regionkey " produced a table with 5 rows and 1 column, took 0.003 CPU seconds, 0.200545 elapsed seconds /home/agonen/Code/fastbit-ibis1.3.5/examples/.libs/lt-ibis -- total CPU time 0.006 s, total elapsed time 0.224089 s On Tue, Apr 2, 2013 at 7:44 AM, K. John Wu <[email protected]> wrote: > Hi, Amihay, > > Looks like you are mixing the syntax of two different join > expressions, please stay with one. For example, you join in SQL > expression might be something like > > -q "select nation.name from nation, region where region.name = > 'AFRICA' and name.regionkey = region.regionkey" > > > The -j option of ibis is a hack which probably should not be used > ever, if you really want to try, here is the equivalent of what is > shown above > > -j region nation regionkey name='AFRICA' '' nation.name > > in the above command line > > region == table 1 > nation == table 2 > regionkey is the join column (i.e. region.regionkey = nation.regionkey) > name='AFRICA' == condition on table 1 > '' == condition on table 2 > nation.name == column to output > > Anyway, -j option is a hack, please don't use it. The -q option > should process two-table join correctly following the SQL syntax. > > John > > > > > On 3/30/13 3:54 PM, amihay gonen wrote: > > Hi , I'm try to do join between two tables , but without success. > > any idea , what i'm doing wrong ? > > > > if i'm doing the following query : > > $ibis -v -q "SELECT nation.name <http://nation.name> FROM > > nation,region where region.name <http://region.name>='AFRICA' " -o > > $outfile -d $db/nation -d $db/region -j nation region regionkey > > regionkey=region.regionkey regionkey=nation.regionkey > > > > i get : > > > > Constructed a part named nation > > Constructed a part named region > > filter::sift2(SELECT nation.name <http://nation.name> FROM 1 data > > partition WHERE region ...) -- processing data partition region > > countQuery::evaluate -- Select count(*) From region Where region.name > > <http://region.name> == "AFRICA" --> 1 > > countQuery::evaluate -- duration: 0.001 sec(CPU), 0.000271094 > sec(elapsed) > > Warning -- filter::sift2(SELECT nation.name <http://nation.name> FROM > > 1 data partition WHERE region ...) failed to append 1 row from region, > > ierr = -13 > > Warning -- doQuaere(SELECT nation.name <http://nation.name> FROM > > nation,region WHERE region.name <http://region.name>='AFRICA' ) failed > > to produce a result table > > countQuery::evaluate -- Select count(*) From nation Where regionkey == > > region.regionkey --> 25 > > countQuery::evaluate -- duration: 0 sec(CPU), 0.000177332 sec(elapsed) > > countQuery::evaluate -- Select count(*) From region Where regionkey == > > nation.regionkey --> 5 > > countQuery::evaluate -- duration: 0 sec(CPU), 9.994e-05 sec(elapsed) > > jNatural::count(From nation Join region Using(regionkey) Where ...) -- > > duration: 0 sec(CPU), 8.1054e-05 sec(elapsed) > > doJoin(From nation Join region Using(regionkey) Where > > regionkey=region.regionkey And regionkey=nation.regionkey) -- counted > > 25 hits > > doJoin(From nation Join region Using(regionkey) Where > > regionkey=region.regionkey And regionkey=nation.regionkey) -- > > duration: 0 sec(CPU), 0.000648564 sec(elapsed) > > > > > > > > _______________________________________________ > > FastBit-users mailing list > > [email protected] > > https://hpcrdm.lbl.gov/cgi-bin/mailman/listinfo/fastbit-users > > >
_______________________________________________ FastBit-users mailing list [email protected] https://hpcrdm.lbl.gov/cgi-bin/mailman/listinfo/fastbit-users
