documentation.adoc

var type = db.getColumnTypes("PERSON", ["FIRSTNAME"])[0]
var persons = db.table(["select FIRSTNAME, LASTNAME from PERSON where FIRSTNAME = ?", [[usereInput, type]]]);
var persons = newSelect("FIRSTNAME, LASTNAME")
                .from("PERSON")
                .where("PERSON.FIRSTNAME", userInput)
                .table()
var personSelect = new SqlBuilder()
                    .select("FIRSTNAME, LASTNAME")
                    .from("PERSON")
                    .where("PERSON.PERSONID", persId)
var personSelect = newSelect("FIRSTNAME, LASTNAME")
                    .from("PERSON")
                    .where("PERSON.PERSONID", persId)
var personCond = new SqlBuilder()
                    .where("PERSON.PERSONID", persId)
var personCond = newWhere("PERSON.PERSONID", persId)
new SqlCondition.select("COL1")
                .from("TAB1")
                .where("TAB1.COL2", "myVal")
                .and("TAB1.COL3", "myVal")
new SqlCondition.select("COL1")
                .from("TAB1")
                .and("TAB1.COL2", "myVal")
                .and("TAB1.COL3", "myVal")
newWhere("TAB1.COL2", "myVal")
    .and("TAB1.COL3", "myVal")
var cond = newWhere();

if (someCondition...)
{
    cond.and("TAB1.COL3", "myVal");
}

if (someOtherCondition...)
{
    cond.and("TAB1.COL2", "myVal2");
}
var value = null;

var cond = newWhere("TAB1.COL2", null)
                .and("TAB1.COL3", value)
// execution stops with error
var value = null;

var cond = newWhereIfSet("TAB1.COL2", null)
                    .and("TAB1.COL3", value)

// code is executet and cond.build will result in ["TAB1.COL3 = ?", [['myVal', SQLTYPES.VARCHAR]]]
// As you can see. TAB1.COL2 is just ignored completely
var costData = newSelect("CAMPAIGNCOSTID, CAMPAIGNSTEP_ID, CAMPAIGNSTEP.NAME, CATEGORY, NET")
                    .from("CAMPAIGNCOST")
                    .leftJoin("CAMPAIGNSTEP", newWhere("CAMPAIGNSTEP_ID = CAMPAIGNSTEPID"))
                    .where("CAMPAIGNCOST.CAMPAIGN_ID", campaignId)
                    .table();
var name = 'Admin'
var cond = newWhere("PERSON.FIRSTNAME is not null") // a simple String
                .and(newWhere("PERSON.LASTNAME", name)) // another SqlBuilder (note: only the condition is used from it)
                .or(["FIRSTNAME is null and LASTNAME = ?", [["Meier",  SQLTYPES.VARCHAR]]]) // a prepared statement array

//cond.build() will result in  ["( (PERSON.FIRSTNAME = 'Tim'  and PERSON.LASTNAME = 'Admin' ) or (PERSON.FIRSTNAME = 'Peter' ) and PERSON.LASTNAME = 'Meier' ) ", [['Tim', SQLTYPES.VARCHAR], ['Admin', SQLTYPES.VARCHAR], ['Peter', SQLTYPES.VARCHAR], ['Meier', SQLTYPES.VARCHAR]]]
var cond = newWhere(newWhere("PERSON.FIRSTNAME", "Tim")
                        .or("PERSON.FIRSTNAME", "Fritz"))
                .and("PERSON.FIRSTNAME", "Peter")
// (  ( PERSON.FIRSTNAME = 'Tim'  or PERSON.FIRSTNAME = 'Fritz'  )  and PERSON.FIRSTNAME = 'Peter' )
var cond = newWhere("PERSON.FIRSTNAME", "Tim")
                .or("PERSON.FIRSTNAME", "Fritz")
                .and("PERSON.FIRSTNAME", "Peter")
// ( PERSON.FIRSTNAME = 'Tim'  or PERSON.FIRSTNAME = 'Fritz'  and PERSON.FIRSTNAME = 'Peter' )
var cond = newWhere("PERSON.FIRSTNAME", "Tim")
                .and("PERSON.LASTNAME", "Admin")
                .or("PERSON.FIRSTNAME", "Peter")
                .and("PERSON.LASTNAME", "Meier")

//cond.build() will result in  ["( (PERSON.FIRSTNAME = 'Tim'  and PERSON.LASTNAME = 'Admin' ) or (PERSON.FIRSTNAME = 'Peter' ) and PERSON.LASTNAME = 'Meier' ) ", [['Tim', SQLTYPES.VARCHAR], ['Admin', SQLTYPES.VARCHAR], ['Peter', SQLTYPES.VARCHAR], ['Meier', SQLTYPES.VARCHAR]]]
newWhere("PERSON.FIRSTNAME", "$field.FIRSTNAME")
var aSqlBuilderContainingFullSelect = newSelect("LASTNAME")
                                        .from("PERSON")
                                        .where("PERSON.FIRSTNAME", "Fritz")
var cond = newWhere("TABLE.FIELD", aSqlBuilderContainingFullSelect)

// cond.build() results in ["( TABLE.FIELD =  ( select LASTNAME from PERSON where PERSON.FIRSTNAME = ?  ) ) ", [['Fritz', SQLTYPES.VARCHAR]]]
var aSqlBuilderContainingFullSelect = ["select LASTNAME from PERSON where PERSON.FIRSTNAME = ?", [['Fritz', SQLTYPES.VARCHAR]]]
var cond = newWhere("TABLE.FIELD", aSqlBuilderContainingFullSelect)

// cond.build() results in ["( TABLE.FIELD =  ( select LASTNAME from PERSON where PERSON.FIRSTNAME = ?  ) ) ", [['Fritz', SQLTYPES.VARCHAR]]]
var cond = newWhere("PERSON.FIRSTNAME", ["Fritz", "Franz", "Max"], SqlBuilder.IN()) // SqlBuilder.IN() == "in ?"
// Note also SqlBuilder.NOT_IN() is possible
// This is a example condition process containing some possible ways to use the SqlBuilder.

// Using newWhereIfSet() to create a new Condition.
// The 'IfSet' ignores the condition "CONTACT.ORGANISATION_ID" = "$param.OrgId_param" if "$param.OrgId_param" is null
var cond = newWhereIfSet("CONTACT.ORGANISATION_ID", "$param.OrgId_param")
                .andIfSet("PERSON.CONTACT_ID", "$param.ContactId_param"); // same again: if "$param.ContactId_param" is null, this line is ignored

// --> so if both parameters where null (not filled) the SqlBuilder cond won't contain any condition!


// Now we add a parameter containing a json Array

// we have to parse it
var excludedContacts = JSON.parse(vars.getString("$param.ExcludedContactIds_param"));

// and add it to cond using not in --------------------------------V
cond.andIfSet("CONTACT.CONTACTID", excludedContacts, SqlBuilder.NOT_IN());

// if excludedContacts is an empty array (or null), no condition is added as we used "andIfSet()"
// if you need a different behaviour you may wrap it by an if

// Same for onlyShowContactIds but by using an in-statement
var onlyShowContactIds = JSON.parse(vars.get("$param.OnlyShowContactIds_param"));
cond.andIfSet("CONTACT.CONTACTID", onlyShowContactIds, SqlBuilder.IN());

// The conditionProcesss doesn't support prepared statements. So .build() is currently not possible.
// using .toString() resolves all conditions and builds the finished select as string.
result.string(cond.toString());