Hi, Petr, I just tried it, the expression "col4 != 0 || not (col4 not NULL)" seems to work even if col4 is not in any of the data partitions. However, this logical is probably not compliant with any SQL standard.
I have not found a "standard" way of dealing with this case in SQL. Do you happen to have more information on this? John On 10/12/13 4:16 AM, Petr Velan wrote: > Hi John, > > thanks for the advice. What about the case when the column is not > present in any of the tables and therefore it is not in the logical > table? Is there any way to tell the query that the column is not > really required, but if it is there, then it should have a certain value? > > This would help our usecase a lot. > > Thanks, > Petr > > > On Thu, Oct 10, 2013 at 5:41 PM, K. John Wu <[email protected] > <mailto:[email protected]>> wrote: > > Hi, Petr, > > If a column does not appear in a partition (but in other partition of > your logical table), then it is treated as containing on NULL values. > The condition "col4 != 0" still implies that "col4" is NOT NULL. > > You might want to simply leave out "col4" if your query logical is > fine with this options. > > If your where clause need to be "col4 != 0 || col4 is NULL", then you > will need to do the following > > "col4 != 0 || not (col4 not NULL)" > > This double negative is necessary because FastBit does not currently > support the expression of the form "col4 is NULL". > > Hope this works for you. > > John > > > > > On 10/9/13 10:17 PM, Petr Velan wrote: > > Hi John, > > > > I've run into following issue: We have multiple tables, each with > > different set of columns. We want to run the same query on all of > > them. The problem is when the query contains a column that is not > > present in a table. Even if the query is something like col1 != > 0, the > > column col1 is required and no results are returned. > > > > An example: > > table1: col1, col2, col3 > > table2: col2, col3, col4 > > > > query: col2 = 42 and col4 != 0 > > > > I would want the query to give me all rows from table1 with col2 > = 42, > > thus ignoring the missing col4. Is that possible to achieve in > FastBit > > query? Since we have quite lot of different table schemes, it is not > > easy to adjust the given query for each of the tables. > > > > Thank you for your help, > > Petr > > > > > > _______________________________________________ > > FastBit-users mailing list > > [email protected] <mailto:[email protected]> > > https://hpcrdm.lbl.gov/cgi-bin/mailman/listinfo/fastbit-users > > > _______________________________________________ > FastBit-users mailing list > [email protected] <mailto:[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 > _______________________________________________ FastBit-users mailing list [email protected] https://hpcrdm.lbl.gov/cgi-bin/mailman/listinfo/fastbit-users
