jpa/org.eclipse.persistence.jpa.jpql/src/test/java/org/eclipse/persistence/jpa/tests/jpql/tools/model/ManualCreationStateObjectTest2_0.java

/*
 * Copyright (c) 2011, 2021 Oracle and/or its affiliates. All rights reserved.
 *
 * This program and the accompanying materials are made available under the
 * terms of the Eclipse Public License v. 2.0 which is available at
 * http://www.eclipse.org/legal/epl-2.0,
 * or the Eclipse Distribution License v. 1.0 which is available at
 * http://www.eclipse.org/org/documents/edl-v10.php.
 *
 * SPDX-License-Identifier: EPL-2.0 OR BSD-3-Clause
 */

// Contributors:
//     Oracle - initial API and implementation
//
package org.eclipse.persistence.jpa.tests.jpql.tools.model;

import org.eclipse.persistence.jpa.jpql.parser.Expression;
import org.eclipse.persistence.jpa.jpql.tools.model.ICaseExpressionStateObjectBuilder;
import org.eclipse.persistence.jpa.jpql.tools.model.IConditionalExpressionStateObjectBuilder;
import org.eclipse.persistence.jpa.jpql.tools.model.INewValueStateObjectBuilder;
import org.eclipse.persistence.jpa.jpql.tools.model.ISelectExpressionStateObjectBuilder;
import org.eclipse.persistence.jpa.jpql.tools.model.query.AndExpressionStateObject;
import org.eclipse.persistence.jpa.jpql.tools.model.query.AvgFunctionStateObject;
import org.eclipse.persistence.jpa.jpql.tools.model.query.CaseExpressionStateObject;
import org.eclipse.persistence.jpa.jpql.tools.model.query.ComparisonExpressionStateObject;
import org.eclipse.persistence.jpa.jpql.tools.model.query.ConcatExpressionStateObject;
import org.eclipse.persistence.jpa.jpql.tools.model.query.IdentificationVariableDeclarationStateObject;
import org.eclipse.persistence.jpa.jpql.tools.model.query.IdentificationVariableStateObject;
import org.eclipse.persistence.jpa.jpql.tools.model.query.IndexExpressionStateObject;
import org.eclipse.persistence.jpa.jpql.tools.model.query.InputParameterStateObject;
import org.eclipse.persistence.jpa.jpql.tools.model.query.JPQLQueryStateObject;
import org.eclipse.persistence.jpa.jpql.tools.model.query.JoinStateObject;
import org.eclipse.persistence.jpa.jpql.tools.model.query.ModExpressionStateObject;
import org.eclipse.persistence.jpa.jpql.tools.model.query.NumericLiteralStateObject;
import org.eclipse.persistence.jpa.jpql.tools.model.query.SelectStatementStateObject;
import org.eclipse.persistence.jpa.jpql.tools.model.query.StateFieldPathExpressionStateObject;
import org.eclipse.persistence.jpa.jpql.tools.model.query.StringLiteralStateObject;
import org.eclipse.persistence.jpa.jpql.tools.model.query.TypeExpressionStateObject;
import org.eclipse.persistence.jpa.jpql.tools.model.query.UpdateStatementStateObject;
import org.eclipse.persistence.jpa.jpql.tools.model.query.WhenClauseStateObject;
import org.eclipse.persistence.jpa.jpql.tools.model.query.WhereClauseStateObject;
import org.eclipse.persistence.jpa.tests.jpql.UniqueSignature;
import org.junit.Test;
import static org.eclipse.persistence.jpa.tests.jpql.JPQLQueries2_0.*;

/**
 * This tests the manual creation of a {@link org.eclipse.persistence.jpa.jpql.tools.model.query.StateObject} that can be parsed by the JPQL grammar
 * defined in JPA 2.0.
 *
 * @version 2.5
 * @since 2.4
 * @author Pascal Filion
 */
@UniqueSignature
@SuppressWarnings("nls")
public final class ManualCreationStateObjectTest2_0 extends AbstractStateObjectTest2_0 {

    protected JPQLQueryStateObject buildJPQLQueryStateObject() throws Exception {
        return new JPQLQueryStateObject(getQueryBuilder(), getPersistenceUnit());
    }

    @Test
    public void test_Query_139_a() throws Exception {

        // SELECT p
        // FROM Employee e JOIN e.projects p
        // WHERE e.id = :id AND INDEX(p) = 1

        JPQLQueryStateObject jpqlStateObject = buildJPQLQueryStateObject();

        SelectStatementStateObject select = jpqlStateObject.addSelectStatement();
        select.addRangeDeclaration("Employee", "e").addJoin("e.projects", "p");
        select.addSelectItem("p");
        select.addWhereClause("e.id = :id AND INDEX(p) = 1");

        test(stateObject_139(), jpqlStateObject, query_016());
    }

    @Test
    public void test_Query_139_b() throws Exception {

        // SELECT p
        // FROM Employee e JOIN e.projects p
        // WHERE e.id = :id AND INDEX(p) = 1

        JPQLQueryStateObject jpqlStateObject = buildJPQLQueryStateObject();

        SelectStatementStateObject select = jpqlStateObject.addSelectStatement();
        select.addRangeDeclaration("Employee", "e").addJoin("e.projects", "p");
        select.addSelectItem("p");

        IConditionalExpressionStateObjectBuilder builder = select.addWhereClause().getBuilder();
                builder.path("e.id").equal(builder.parameter(":id"))
            .and(
                builder.index("p").equal(builder.numeric(1))
        ).commit();

        test(stateObject_139(), jpqlStateObject, query_016());
    }

    @Test
    public void test_Query_139_c() throws Exception {

        // SELECT p
        // FROM Employee e JOIN e.projects p
        // WHERE e.id = :id AND INDEX(p) = 1

        JPQLQueryStateObject jpqlStateObject = buildJPQLQueryStateObject();

        SelectStatementStateObject select = jpqlStateObject.addSelectStatement();
        select.addRangeDeclaration("Employee", "e").addJoin("e.projects", "p");
        select.addSelectItem("p");

        WhereClauseStateObject where = select.addWhereClause();
        where.parse("e.id = :id");
        where.andParse("INDEX(p) = 1");

        test(stateObject_139(), jpqlStateObject, query_016());
    }

    @Test
    public void test_Query_139_d() throws Exception {

        // SELECT p
        // FROM Employee e JOIN e.projects p
        // WHERE e.id = :id AND INDEX(p) = 1

        JPQLQueryStateObject jpqlStateObject = buildJPQLQueryStateObject();

        SelectStatementStateObject select = jpqlStateObject.addSelectStatement();

        IdentificationVariableDeclarationStateObject rangeDeclaration = select.addRangeDeclaration();
        select.addSelectItem(new IdentificationVariableStateObject(select, "p"));
        rangeDeclaration.setEntityName("Employee");
        rangeDeclaration.setIdentificationVariable("e");

        JoinStateObject join = rangeDeclaration.addItem(
            new JoinStateObject(rangeDeclaration, Expression.JOIN, false)
        );
        join.setJoinAssociationPath("e.projects");
        join.setIdentificationVariable("p");

        WhereClauseStateObject where = select.addWhereClause();

        AndExpressionStateObject and = new AndExpressionStateObject(where);
        where.setConditional(and);

        and.setLeft(new ComparisonExpressionStateObject(
            and,
            new StateFieldPathExpressionStateObject(and, "e.id"),
            Expression.EQUAL,
            new InputParameterStateObject(and, ":id")
        ));

        and.setRight(new ComparisonExpressionStateObject(
            and,
            new IndexExpressionStateObject(and, "p"),
            Expression.EQUAL,
            new NumericLiteralStateObject(and, 1)
        ));

        test(stateObject_139(), jpqlStateObject, query_016());
    }

    @Test
    public void test_Query_205_a() throws Exception {

        // UPDATE Employee e
        // SET e.salary =
        //    CASE WHEN e.rating = 1 THEN e.salary * 1.1
        //         WHEN e.rating = 2 THEN e.salary * 1.05
        //         ELSE e.salary * 1.01
        //    END

        JPQLQueryStateObject jpqlStateObject = buildJPQLQueryStateObject();

        CaseExpressionStateObject case_ = new CaseExpressionStateObject(jpqlStateObject);
        case_.parseElse("e.salary * 1.01");

        WhenClauseStateObject when1 = case_.addWhenClause();
        when1.getBuilder().path("e.rating").equal(1).commit();
        when1.parseThen("e.salary * 1.1");

        WhenClauseStateObject when2 = case_.addWhenClause();
        when2.getBuilder().path("e.rating").equal(2).commit();
        when2.parseThen("e.salary * 1.05");

        UpdateStatementStateObject update = jpqlStateObject.addUpdateStatement();
        update.setDeclaration("Employee", "e");
        update.addItem("e.salary", case_);

        test(stateObject_205(), jpqlStateObject, query_001());
    }

    @Test
    public void test_Query_205_b() throws Exception {

        // UPDATE Employee e
        // SET e.salary =
        //    CASE WHEN e.rating = 1 THEN e.salary * 1.1
        //         WHEN e.rating = 2 THEN e.salary * 1.05
        //         ELSE e.salary * 1.01
        //    END

        JPQLQueryStateObject jpqlStateObject = buildJPQLQueryStateObject();

        UpdateStatementStateObject update = jpqlStateObject.addUpdateStatement();
        update.setDeclaration("Employee", "e");
        update.addItem("e.salary", "CASE WHEN e.rating = 1 THEN e.salary * 1.1 WHEN e.rating = 2 THEN e.salary * 1.05 ELSE e.salary * 1.01 END");

        test(stateObject_205(), jpqlStateObject, query_001());
    }

    @Test
    public void test_Query_206_a() throws Exception {

        // SELECT e.name,
        //        CASE TYPE(e) WHEN Exempt THEN 'Exempt'
        //                     WHEN Contractor THEN 'Contractor'
        //                     WHEN Intern THEN 'Intern'
        //                     ELSE 'NonExempt'
        //        END
        // FROM Employee e, Contractor c
        // WHERE e.dept.name = 'Engineering'

        JPQLQueryStateObject jpqlStateObject = buildJPQLQueryStateObject();

        CaseExpressionStateObject case_ = new CaseExpressionStateObject(jpqlStateObject);
        case_.setElse(new StringLiteralStateObject(case_, "'NonExempt'"));
        case_.setCaseOperand(new TypeExpressionStateObject(case_, "e"));

        WhenClauseStateObject when1 = case_.addWhenClause();
        when1.getBuilder().entityType("Exempt").commit();
        when1.setThen(new StringLiteralStateObject(when1, "'Exempt'"));

        WhenClauseStateObject when2 = case_.addWhenClause();
        when2.getBuilder().entityType("Contractor").commit();
        when2.setThen(new StringLiteralStateObject(when2, "'Contractor'"));

        WhenClauseStateObject when3 = case_.addWhenClause();
        when3.getBuilder().entityType("Intern").commit();
        when3.setThen(new StringLiteralStateObject(when3, "'Intern'"));

        SelectStatementStateObject select = jpqlStateObject.addSelectStatement();
        select.addRangeDeclaration("Employee", "e");
        select.addRangeDeclaration("Contractor", "c");
        select.addSelectItem("e.name");
        select.addSelectItem(case_);
        select.addWhereClause().getBuilder().path("e.dept.name").equal("'Engineering'").commit();

        test(stateObject_206(), jpqlStateObject, query_002());
    }

    @Test
    public void test_Query_206_b() throws Exception {

        // SELECT e.name,
        //        CASE TYPE(e) WHEN Exempt THEN 'Exempt'
        //                     WHEN Contractor THEN 'Contractor'
        //                     WHEN Intern THEN 'Intern'
        //                     ELSE 'NonExempt'
        //        END
        // FROM Employee e, Contractor c
        // WHERE e.dept.name = 'Engineering'

        JPQLQueryStateObject jpqlStateObject = buildJPQLQueryStateObject();

        SelectStatementStateObject select = jpqlStateObject.addSelectStatement();
        select.addRangeDeclaration("Employee", "e");
        select.addRangeDeclaration("Contractor", "c");
        select.addSelectItem("e.name");
        select.getSelectClause().parse("CASE TYPE(e) WHEN Exempt THEN 'Exempt' WHEN Contractor THEN 'Contractor' WHEN Intern THEN 'Intern' ELSE 'NonExempt' END");
        select.addWhereClause("e.dept.name = 'Engineering'");

        test(stateObject_206(), jpqlStateObject, query_002());
    }

    @Test
    public void test_Query_207_a() throws Exception {

        // SELECT e.name,
        //        f.name,
        //        CONCAT(CASE WHEN f.annualMiles > 50000 THEN 'Platinum '
        //                    WHEN f.annualMiles > 25000 THEN 'Gold '
        //                    ELSE ''
        //               END,
        //               'Frequent Flyer')
        // FROM Employee e JOIN e.frequentFlierPlan f

        JPQLQueryStateObject jpqlStateObject = buildJPQLQueryStateObject();

        CaseExpressionStateObject case_ = new CaseExpressionStateObject(jpqlStateObject);
        case_.setElse(new StringLiteralStateObject(case_, "''"));

        WhenClauseStateObject when1 = case_.addWhenClause();
        when1.getBuilder().path("f.annualMiles").greaterThan(50000).commit();
        when1.setThen(new StringLiteralStateObject(when1, "'Platinum '"));

        WhenClauseStateObject when2 = case_.addWhenClause();
        when2.getBuilder().path("f.annualMiles").greaterThan(25000).commit();
        when2.setThen(new StringLiteralStateObject(when2, "'Gold '"));

        SelectStatementStateObject select = jpqlStateObject.addSelectStatement();
        select.addRangeDeclaration("Employee", "e").
            addJoin("e.frequentFlierPlan", "f");
        select.addSelectItem("e.name");
        select.addSelectItem("f.name");
        select.addSelectItem(new ConcatExpressionStateObject(
            select,
            case_,
            new StringLiteralStateObject(select, "'Frequent Flyer'")
        ));

        test(stateObject_207(), jpqlStateObject, query_003());
    }

    @Test
    public void test_Query_207_b() throws Exception {

        // SELECT e.name,
        //        f.name,
        //        CONCAT(CASE WHEN f.annualMiles > 50000 THEN 'Platinum '
        //                    WHEN f.annualMiles > 25000 THEN 'Gold '
        //                    ELSE ''
        //               END,
        //               'Frequent Flyer')
        // FROM Employee e JOIN e.frequentFlierPlan f

        JPQLQueryStateObject jpqlStateObject = buildJPQLQueryStateObject();

        CaseExpressionStateObject case_ = new CaseExpressionStateObject(jpqlStateObject);
        case_.setElse(new StringLiteralStateObject(case_, "''"));

        WhenClauseStateObject when1 = case_.addWhenClause();
        when1.getBuilder().path("f.annualMiles").greaterThan(50000).commit();
        when1.setThen(new StringLiteralStateObject(when1, "'Platinum '"));

        WhenClauseStateObject when2 = case_.addWhenClause();
        when2.getBuilder().path("f.annualMiles").greaterThan(25000).commit();
        when2.setThen(new StringLiteralStateObject(when2, "'Gold '"));

        SelectStatementStateObject select = jpqlStateObject.addSelectStatement();
        select.addRangeDeclaration("Employee", "e").
            addJoin("e.frequentFlierPlan", "f");

        ISelectExpressionStateObjectBuilder builder = select.getSelectBuilder();
        ICaseExpressionStateObjectBuilder caseBuilder = builder.getCaseBuilder();

        // Select expression
        builder
            .path("e.name").append()
            .path("f.name").append()
            .concat(
                builder.case_(
                    caseBuilder.when(
                        caseBuilder.path("f.annualMiles").greaterThan(50000),
                        caseBuilder.string("'Platinum '")
                    ).
                    when(
                        caseBuilder.path("f.annualMiles").greaterThan(25000),
                        caseBuilder.string("'Gold '")
                    ).
                    string("''")
                ),
                builder.string("'Frequent Flyer'")
            )
        .commit();

        test(stateObject_207(), jpqlStateObject, query_003());
    }

    @Test
    public void test_Query_208_a() throws Exception {

        // SELECT e
        // FROM Employee e
        // WHERE TYPE(e) IN (Exempt, Contractor)

        JPQLQueryStateObject jpqlStateObject = buildJPQLQueryStateObject();

        SelectStatementStateObject select = jpqlStateObject.addSelectStatement();
        select.addRangeDeclaration("Employee", "e");
        select.addSelectItem("e");

        IConditionalExpressionStateObjectBuilder builder = select.addWhereClause().getBuilder();
        builder.type("e").in(
            builder.entityType("Exempt"),
            builder.entityType("Contractor")
        ).commit();

        test(stateObject_208(), jpqlStateObject, query_004());
    }

    @Test
    public void test_Query_208_b() throws Exception {

        // SELECT e
        // FROM Employee e
        // WHERE TYPE(e) IN (Exempt, Contractor)

        JPQLQueryStateObject jpqlStateObject = buildJPQLQueryStateObject();

        SelectStatementStateObject select = jpqlStateObject.addSelectStatement();
        select.addRangeDeclaration("Employee", "e");
        select.addSelectItem("e");
        select.addWhereClause("TYPE(e) IN (Exempt, Contractor)");

        test(stateObject_208(), jpqlStateObject, query_004());
    }

    @Test
    public void test_Query_209_a() throws Exception {

        // SELECT e
        // FROM Employee e
        // WHERE TYPE(e) IN (:empType1, :empType2)

        JPQLQueryStateObject jpqlStateObject = buildJPQLQueryStateObject();

        SelectStatementStateObject select = jpqlStateObject.addSelectStatement();
        select.addSelectItem("e");
        select.addRangeDeclaration("Employee", "e");

        IConditionalExpressionStateObjectBuilder builder = select.addWhereClause().getBuilder();
        builder.type("e").in(builder.parameter(":empType1"), builder.parameter(":empType2")).commit();

        test(stateObject_209(), jpqlStateObject, query_005());
    }

    @Test
    public void test_Query_209_b() throws Exception {

        // SELECT e
        // FROM Employee e
        // WHERE TYPE(e) IN (:empType1, :empType2)

        JPQLQueryStateObject jpqlStateObject = buildJPQLQueryStateObject();

        SelectStatementStateObject select = jpqlStateObject.addSelectStatement();
        select.addSelectItem("e");
        select.addRangeDeclaration("Employee", "e");
        select.addWhereClause("TYPE(e) IN (:empType1, :empType2)");

        test(stateObject_209(), jpqlStateObject, query_005());
    }

    @Test
    public void test_Query_210_a() throws Exception {

        // SELECT e
        // FROM Employee e
        // WHERE TYPE(e) IN :empTypes

        JPQLQueryStateObject jpqlStateObject = buildJPQLQueryStateObject();

        SelectStatementStateObject select = jpqlStateObject.addSelectStatement();
        select.addSelectItem("e");
        select.addRangeDeclaration("Employee", "e");

        IConditionalExpressionStateObjectBuilder builder = select.addWhereClause().getBuilder();
        builder.type("e").in(builder.parameter(":empTypes")).commit();

        test(stateObject_210(), jpqlStateObject, query_006());
    }

    @Test
    public void test_Query_210_b() throws Exception {

        // SELECT e
        // FROM Employee e
        // WHERE TYPE(e) IN :empTypes

        JPQLQueryStateObject jpqlStateObject = buildJPQLQueryStateObject();

        SelectStatementStateObject select = jpqlStateObject.addSelectStatement();
        select.addSelectItem("e");
        select.addRangeDeclaration("Employee", "e");
        select.addWhereClause("TYPE(e) IN :empTypes");

        test(stateObject_210(), jpqlStateObject, query_006());
    }

    @Test
    public void test_Query_211_a() throws Exception {

        // SELECT TYPE(employee)
        // FROM Employee employee
        // WHERE TYPE(employee) <> Exempt

        JPQLQueryStateObject jpqlStateObject = buildJPQLQueryStateObject();

        SelectStatementStateObject select = jpqlStateObject.addSelectStatement();
        select.addSelectItem(new TypeExpressionStateObject(select, "employee"));
        select.addRangeDeclaration("Employee", "employee");

        IConditionalExpressionStateObjectBuilder builder = select.addWhereClause().getBuilder();
        builder.type("employee").different(builder.variable("Exempt")).commit();

        test(stateObject_211(), jpqlStateObject, query_007());
    }

    @Test
    public void test_Query_211_b() throws Exception {

        // SELECT TYPE(employee)
        // FROM Employee employee
        // WHERE TYPE(employee) <> Exempt

        JPQLQueryStateObject jpqlStateObject = buildJPQLQueryStateObject();

        SelectStatementStateObject select = jpqlStateObject.addSelectStatement();
        select.addSelectItem(new TypeExpressionStateObject(select, "employee"));
        select.addRangeDeclaration("Employee", "employee");
        select.addWhereClause("TYPE(employee) <> Exempt");

        test(stateObject_211(), jpqlStateObject, query_007());
    }

    @Test
    public void test_Query_212_a() throws Exception {

        // SELECT t
        // FROM CreditCard c JOIN c.transactionHistory t
        // WHERE c.holder.name = 'John Doe' AND INDEX(t) BETWEEN 0 AND 9

        JPQLQueryStateObject jpqlStateObject = buildJPQLQueryStateObject();

        SelectStatementStateObject select = jpqlStateObject.addSelectStatement();
        select.addSelectItem("t");

        IdentificationVariableDeclarationStateObject range = select.addRangeDeclaration("CreditCard", "c");
        range.addJoin("c.transactionHistory", "t");

        IConditionalExpressionStateObjectBuilder builder = select.addWhereClause().getBuilder();
        builder.
                path("c.holder.name").equal("'John Doe'")
            .and(
                builder.index("t").between(builder.numeric(0), builder.numeric(9))
            )
        .commit();

        test(stateObject_212(), jpqlStateObject, query_008());
    }

    @Test
    public void test_Query_212_b() throws Exception {

        // SELECT t
        // FROM CreditCard c JOIN c.transactionHistory t
        // WHERE c.holder.name = 'John Doe' AND INDEX(t) BETWEEN 0 AND 9

        JPQLQueryStateObject jpqlStateObject = buildJPQLQueryStateObject();

        SelectStatementStateObject select = jpqlStateObject.addSelectStatement();
        select.addSelectItem("t");
        select.addRangeDeclaration("CreditCard", "c").addJoin("c.transactionHistory", "t");
        select.addWhereClause("c.holder.name = 'John Doe' AND INDEX(t) BETWEEN 0 AND 9");

        test(stateObject_212(), jpqlStateObject, query_008());
    }

    @Test
    public void test_Query_213_a() throws Exception {

        // SELECT w.name
        // FROM Course c JOIN c.studentWaitlist w
        // WHERE c.name = 'Calculus'
        //       AND
        //       INDEX(w) = 0

        JPQLQueryStateObject jpqlStateObject = buildJPQLQueryStateObject();

        SelectStatementStateObject select = jpqlStateObject.addSelectStatement();
        select.addSelectItem("w.name");

        IdentificationVariableDeclarationStateObject range = select.addRangeDeclaration("Course", "c");
        range.addJoin("c.studentWaitlist", "w");

        IConditionalExpressionStateObjectBuilder builder = select.addWhereClause().getBuilder();
        builder.
                path("c.name").equal("'Calculus'")
            .and(
                builder.index("w").equal(builder.numeric(0))
            )
        .commit();

        test(stateObject_213(), jpqlStateObject, query_009());
    }

    @Test
    public void test_Query_213_b() throws Exception {

        // SELECT w.name
        // FROM Course c JOIN c.studentWaitlist w
        // WHERE c.name = 'Calculus'
        //       AND
        //       INDEX(w) = 0

        JPQLQueryStateObject jpqlStateObject = buildJPQLQueryStateObject();

        SelectStatementStateObject select = jpqlStateObject.addSelectStatement();
        select.addSelectItem("w.name");
        select.addRangeDeclaration("Course", "c").
            addJoin("c.studentWaitlist", "w");
        select.addWhereClause("c.name = 'Calculus' AND INDEX(w) = 0");

        test(stateObject_213(), jpqlStateObject, query_009());
    }

    @Test
    public void test_Query_214_a() throws Exception {

        // UPDATE Employee e
        // SET e.salary = CASE e.rating WHEN 1 THEN e.salary * 1.1
        //                              WHEN 2 THEN e.salary * 1.05
        //                              ELSE e.salary * 1.01
        //                END

        JPQLQueryStateObject jpqlStateObject = buildJPQLQueryStateObject();

        CaseExpressionStateObject case_ = new CaseExpressionStateObject(jpqlStateObject);
        case_.parseCaseOperand("e.rating");
        case_.addWhenClause("1", "e.salary * 1.1");
        case_.addWhenClause("2", "e.salary * 1.05");
        case_.parseElse("e.salary * 1.01");

        UpdateStatementStateObject update = jpqlStateObject.addUpdateStatement();
        update.setDeclaration("Employee", "e");
        update.addItem("e.salary", case_);

        test(stateObject_214(), jpqlStateObject, query_010());
    }

    @Test
    public void test_Query_214_b() throws Exception {

        // UPDATE Employee e
        // SET e.salary = CASE e.rating WHEN 1 THEN e.salary * 1.1
        //                              WHEN 2 THEN e.salary * 1.05
        //                              ELSE e.salary * 1.01
        //                END

        JPQLQueryStateObject jpqlStateObject = buildJPQLQueryStateObject();

        UpdateStatementStateObject update = jpqlStateObject.addUpdateStatement();
        update.setDeclaration("Employee", "e");

        INewValueStateObjectBuilder newValue = update.addItem("e.salary").getBuilder();
        ICaseExpressionStateObjectBuilder builder = newValue.getCaseBuilder();

        newValue.case_(
            builder.path("e.rating").
            when(
                builder.numeric(1),
                builder.path("e.salary").multiply(builder.numeric(1.1))
            ).
            when(
                builder.numeric(2),
                builder.path("e.salary").multiply(builder.numeric(1.05))
            ).
            path("e.salary").multiply(builder.numeric(1.01))
        )
        .commit();

        test(stateObject_214(), jpqlStateObject, query_010());
    }

    @Test
    public void test_Query_217_a() throws Exception {

        // SELECT o.quantity, o.cost*1.08 AS taxedCost, a.zipcode
        // FROM Customer c JOIN c.orders o JOIN c.address a
        // WHERE a.state = 'CA' AND a.county = 'Santa Clara'
        // ORDER BY o.quantity, taxedCost, a.zipcode

        JPQLQueryStateObject jpqlStateObject = buildJPQLQueryStateObject();

        SelectStatementStateObject select = jpqlStateObject.addSelectStatement();
        select.addRangeDeclaration("Customer", "c").
            addJoin("c.orders",  "o").getParent().
            addJoin("c.address", "a");
        select.addSelectItem("o.quantity");
        select.addSelectItemAs("o.cost*1.08", "taxedCost");
        select.addSelectItem("a.zipcode");
        select.addOrderByClause().
            addItem("o.quantity").getParent().
            addItem("taxedCost").getParent().
            addItem("a.zipcode");

        IConditionalExpressionStateObjectBuilder builder = select.addWhereClause().getBuilder();
        builder.
                path("a.state").equal(builder.string("'CA'"))
            .and(
                builder.path("a.counry").equal(builder.string("'Santa Clara'"))
            )
        .commit();

        test(stateObject_217(), jpqlStateObject, query_011());
    }

    @Test
    public void test_Query_217_b() throws Exception {

        // SELECT o.quantity, o.cost*1.08 AS taxedCost, a.zipcode
        // FROM Customer c JOIN c.orders o JOIN c.address a
        // WHERE a.state = 'CA' AND a.county = 'Santa Clara'
        // ORDER BY o.quantity, taxedCost, a.zipcode

        JPQLQueryStateObject jpqlStateObject = buildJPQLQueryStateObject();

        SelectStatementStateObject select = jpqlStateObject.addSelectStatement();
        select.getSelectClause().parse("o.quantity, o.cost*1.08 AS taxedCost, a.zipcode");
        select.getFromClause().parse("Customer c JOIN c.orders o JOIN c.address a");
        select.addWhereClause("a.state = 'CA' AND a.county = 'Santa Clara'");
        select.addOrderByClause("o.quantity, taxedCost, a.zipcode");

        test(stateObject_217(), jpqlStateObject, query_011());
    }

    @Test
    public void test_Query_218_a() throws Exception {

        // SELECT AVG(o.quantity) as q, a.zipcode
        // FROM Customer c JOIN c.orders o JOIN c.address a
        // WHERE a.state = 'CA'
        // GROUP BY a.zipcode
        // ORDER BY q DESC";

        JPQLQueryStateObject jpqlStateObject = buildJPQLQueryStateObject();

        SelectStatementStateObject select = jpqlStateObject.addSelectStatement();
        select.addSelectItemAs(new AvgFunctionStateObject(select, "o.quantity"), "q");
        select.addSelectItem("a.zipcode");
        select.addRangeDeclaration("Customer", "c").
            addJoin("c.orders",  "o").getParent().
            addJoin("c.address", "a");
        select.addGroupByClause("a.zipcode");
        select.addOrderByClause().addItemDesc("q");

        IConditionalExpressionStateObjectBuilder builder = select.addWhereClause().getBuilder();
        builder.path("a.state").equal(builder.string("'CA'")).commit();

        test(stateObject_218(), jpqlStateObject, query_012());
    }

    @Test
    public void test_Query_218_b() throws Exception {

        // SELECT AVG(o.quantity) as q, a.zipcode
        // FROM Customer c JOIN c.orders o JOIN c.address a
        // WHERE a.state = 'CA'
        // GROUP BY a.zipcode
        // ORDER BY q DESC";

        JPQLQueryStateObject jpqlStateObject = buildJPQLQueryStateObject();

        SelectStatementStateObject select = jpqlStateObject.addSelectStatement();
        select.addRangeDeclaration("Customer", "c").
            addJoin("c.orders",  "o").getParent().
            addJoin("c.address", "a");

        ISelectExpressionStateObjectBuilder selectBuilder = select.getSelectBuilder();
        selectBuilder.
            avg("o.quantity").resultVariableAs("q").append().
            path("a.zipcode").
        commit();

        select.addWhereClause().getBuilder().path("a.state").equal("'CA'").commit();
        select.addGroupByClause("a.zipcode");
        select.addOrderByClause().addItemDesc("q");

        test(stateObject_218(), jpqlStateObject, query_012());
    }

    @Test
    public void test_Query_222_a() throws Exception {

        // SELECT e.salary / 1000D n
        // From Employee e

        JPQLQueryStateObject jpqlStateObject = buildJPQLQueryStateObject();

        SelectStatementStateObject select = jpqlStateObject.addSelectStatement();
        select.addRangeDeclaration("Employee", "e");
        select.addSelectItem("e.salary / 1000D", "n");

        test(stateObject_222(), jpqlStateObject, query_013());
    }

    @Test
    public void test_Query_222_b() throws Exception {

        // SELECT e.salary / 1000D n
        // From Employee e

        JPQLQueryStateObject jpqlStateObject = buildJPQLQueryStateObject();

        SelectStatementStateObject select = jpqlStateObject.addSelectStatement();
        select.addRangeDeclaration("Employee", "e");

        ISelectExpressionStateObjectBuilder builder = select.getSelectBuilder();
        builder.path("e.salary").divide(builder.numeric("1000D")).resultVariable("n").commit();

        test(stateObject_222(), jpqlStateObject, query_013());
    }

    @Test
    public void test_Query_223_a() throws Exception {

        // SELECT MOD(a.id, 2) AS m
        // FROM Address a JOIN FETCH a.customerList
        // ORDER BY m, a.zipcode

        JPQLQueryStateObject jpqlStateObject = buildJPQLQueryStateObject();

        SelectStatementStateObject select = jpqlStateObject.addSelectStatement();
        select.addSelectItemAs(new ModExpressionStateObject(select, "a.id", "2"), "m");
        select.addRangeDeclaration("Address", "a").
            addJoinFetch("a.customerList");
        select.addOrderByClause("m, a.zipcode");

        test(stateObject_223(), jpqlStateObject, query_014());
    }

    @Test
    public void test_Query_223_b() throws Exception {

        // SELECT MOD(a.id, 2) AS m
        // FROM Address a JOIN FETCH a.customerList
        // ORDER BY m, a.zipcode

        JPQLQueryStateObject jpqlStateObject = buildJPQLQueryStateObject();

        SelectStatementStateObject select = jpqlStateObject.addSelectStatement();
        select.addRangeDeclaration("Address", "a").
            addJoinFetch("a.customerList");
        select.addOrderByClause("m, a.zipcode");

        ISelectExpressionStateObjectBuilder builder = select.getSelectBuilder();
        builder.mod(builder.path("a.id"), builder.numeric(2)).resultVariableAs("m").commit();

        test(stateObject_223(), jpqlStateObject, query_014());
    }
}