WITH Clause

Syntax

WITH {keys | timestamp | versions | scannerCacheSize | queryLimit | serverFilter | clientFilter}+

keys:
      KEYS key_Range [, ...]
    | KEYS ALL

keyRange:
      FIRST TO key_value
    | key_value TO LAST
    | key_value TO key_value
    | key_value

timestamp:
      TIMESTAMP RANGE timestamp_value TO timestamp_value
    | TIMESTAMP timestamp_value

versions:
      VERSIONS version_value
    | VERSIONS MAX

scannerCacheSize:
    SCANNER_CACHE_SIZE cache_size

queryLimit:
    LIMIT limit_value

serverFilter:
    SERVER FILTER WHERE where_clause

clientFilter:
    CLIENT FILTER WHERE where_clause

Description

The WITH clause is used in the insert, select and delete statements.

  • Named parameters can be applied to any of the values above.
  • key_value is any valid string expression (cannot reference columns).

    Indicates key values for query. A key_value can be bound to a Collection of strings with a named parameter.

  • timestamp_value is any valid date expression (cannot reference columns).

    Indicates the timestamp value for the query.

  • version_value is any valid integer expression (cannot reference columns).

    Indicates the maximum number of column versions returned in query.

  • limit_value is any valid long expression (cannot reference columns).

    Indicates the maximum number of rows returned, i.e., evaluated as true, in a query.

  • where_clause is any valid boolean expression (can reference columns).

    The SERVER FILTER where_clause is evaluated on the server, while the CLIENT FILTER where_clause is evaluated on the client.

Example

        HConnection conn = HConnectionManager.newConnection();

        conn.execute("CREATE TEMP MAPPING tab1 FOR TABLE table1"
                     + "("
                     + "keyval KEY, "
                     + "f1 INCLUDE UNMAPPED ("
                     + "  val1 STRING ALIAS val1, "
                     + "  val2 INT ALIAS val5"
                     + "),  "
                     + "f2 INCLUDE UNMAPPED, "
                     + "f3 INCLUDE UNMAPPED ("
                     + "  val2 INT ALIAS val6, "
                     + "  val3 INT ALIAS val7 "
                     + "))");

        HPreparedStatement pstmt = conn.prepareStatement("SELECT keyval, f1:val1, val5 FROM tab1 "
                                                         + "WITH KEYS FIRST TO :endkey "
                                                         + "VERSIONS 4 "
                                                         + "CLIENT FILTER WHERE val6 > 4");

        pstmt.setParameter("endkey", Util.getZeroPaddedNonNegativeNumber(34, 10));

        HResultSet<HRecord> records = pstmt.executeQuery();

        for (HRecord record : records) {
            System.out.println("Key = " + record.getCurrentValue("keyval"));
        }

        pstmt.close();