Repository: paustint/soql-parser-js (ts)

Issue:
Add support for FOR clause
<!--
  Please include the following classification.
  If your classification is not added, you can modify the list below
-->
- [x] Feature
- [ ] Bug Request

<!--If this is a bug, please include the steps to reproduce the bug-->
## Description

```
[FOR {VIEW  | REFERENCE}[,...] ]
      [ UPDATE {TRACKING|VIEWSTAT}[,...] ]
```


https://developer.salesforce.com/docs/atlas.en-us.soql_sosl.meta/soql_sosl/sforce_api_calls_soql_select_for_view.htm

https://developer.salesforce.com/docs/atlas.en-us.soql_sosl.meta/soql_sosl/sforce_api_calls_soql_select_for_reference.htm

https://developer.salesforce.com/docs/atlas.en-us.soql_sosl.meta/soql_sosl/sforce_api_calls_soql_select_for_update.htm
UPDATE TRACKING and UPDATE VIEWSTAT optional clauses are not parsed
<!--
  Please include the following classification.
  If your classification is not added, you can modify the list below
-->
- [ ] Feature
- [ ] Bug Request

<!--If this is a bug, please include the steps to reproduce the bug-->
## Description
Example from: https://developer.salesforce.com/docs/atlas.en-us.soql_sosl.meta/soql_sosl/sforce_api_calls_soql_select_update_tracking.htm
#### Query:
``` sql
SELECT Title FROM FAQ__kav
WHERE Keyword='Apex' and
Language = 'en_US' and
KnowledgeArticleVersion = 'ka230000000PCiy'
UPDATE TRACKING
```
#### Parsed output:
``` json
{
    "fields": [
        {
            "text": "Title"
        }
    ],
    "subqueries": [],
    "sObject": "FAQ__kav",
    "whereClause": {
        "left": {
            "field": "Keyword",
            "operator": "=",
            "value": "'Apex'"
        },
        "operator": "AND",
        "right": {
            "left": {
                "field": "Language",
                "operator": "=",
                "value": "'en_US'"
            },
            "operator": "AND",
            "right": {
                "left": {
                    "field": "KnowledgeArticleVersion",
                    "operator": "=",
                    "value": "'ka230000000PCiy'"
                }
            }
        }
    }
}
```
#### Query 2:
``` sql
SELECT Title FROM FAQ__kav
   WHERE PublishStatus='online' and
   Language = 'en_US' and
   KnowledgeArticleVersion = 'ka230000000PCiy'
   UPDATE VIEWSTAT
```
#### Parsed result:
``` json
{
    "fields": [
        {
            "text": "Title"
        }
    ],
    "subqueries": [],
    "sObject": "FAQ__kav",
    "whereClause": {
        "left": {
            "field": "PublishStatus",
            "operator": "=",
            "value": "'online'"
        },
        "operator": "AND",
        "right": {
            "left": {
                "field": "Language",
                "operator": "=",
                "value": "'en_US'"
            },
            "operator": "AND",
            "right": {
                "left": {
                    "field": "KnowledgeArticleVersion",
                    "operator": "=",
                    "value": "'ka230000000PCiy'"
                }
            }
        }
    }
}
```

The UPDATE TRACKING and UPDATE VIEWSTAT clauses are not parsed.
FORMAT is not parsed
<!--
  Please include the following classification.
  If your classification is not added, you can modify the list below
-->
- [ ] Feature
- [ ] Bug Request
<!--If this is a bug, please include the steps to reproduce the bug-->
## Description
Example from: https://developer.salesforce.com/docs/atlas.en-us.soql_sosl.meta/soql_sosl/sforce_api_calls_soql_select_format.htm
#### Query:
``` sql
SELECT FORMAT(convertCurrency(amount)) convertedCurrency 
FROM Opportunity 
WHERE id = '12345'
```
#### Parsed output:
``` json
{
    "fields": [
        {
            "fn": {
                "text": "convertCurrency(amount)",
                "name": "convertCurrency",
                "parameter": [
                    "convertCurrency(amount)",
                    "amount"
                ],
                "alias": "convertedCurrency"
            }
        }
    ],
    "subqueries": [],
    "sObject": "Opportunity",
    "whereClause": {
        "left": {
            "field": "id",
            "operator": "=",
            "value": "'12345'"
        }
    }
}
```
Add support for WITH clause
<!--
  Please include the following classification.
  If your classification is not added, you can modify the list below
-->
- [x] Feature
- [ ] Bug Request

<!--If this is a bug, please include the steps to reproduce the bug-->
## Description
`WITH` was not initially implemented
https://developer.salesforce.com/docs/atlas.en-us.soql_sosl.meta/soql_sosl/sforce_api_calls_soql_select_with_datacategory.htm
```sql
SELECT Title FROM KnowledgeArticleVersion WHERE PublishStatus='online' WITH DATA CATEGORY Geography__c ABOVE usa__c
```
```sql
SELECT Id FROM UserProfileFeed WITH UserId='005D0000001AamR' ORDER BY CreatedDate DESC, Id DESC LIMIT 20
```

Pull request:
multiple bugfixes
Added support for WITH, FOR, UPDATE, nested functions
resolves #18
resolves #19
resolves #27
resolves #25

Patch:
diff --git a/debug/cli.js b/debug/cli.js
deleted file mode 100644
index 21a7080..0000000
--- a/debug/cli.js
+++ /dev/null
@@ -1,4 +0,0 @@
-var argv = require('minimist')(process.argv.slice(2));
-
-console.log('argv:');
-console.log(JSON.stringify(argv, null, 2));
diff --git a/docs/src/components/sample-queries.tsx b/docs/src/components/sample-queries.tsx
index 98c39ba..14dda3d 100644
--- a/docs/src/components/sample-queries.tsx
+++ b/docs/src/components/sample-queries.tsx
@@ -142,6 +142,30 @@ export default class SampleQueries extends React.Component<ISampleQueriesProps,
         num: 31,
         soql: `SELECT LeadSource, COUNT(Name) FROM Lead GROUP BY LeadSource HAVING COUNT(Name) > 100 and LeadSource > 'Phone'`,
       },
+      {
+        key: 32,
+        num: 32,
+        soql: `SELECT Title FROM KnowledgeArticleVersion WHERE PublishStatus='online' WITH DATA CATEGORY Geography__c ABOVE usa__c`,
+      },
+      {
+        key: 33,
+        num: 33,
+        soql: `SELECT Title FROM Question WHERE LastReplyDate > 2005-10-08T01:02:03Z WITH DATA CATEGORY Geography__c AT (usa__c, uk__c)`,
+      },
+      {
+        key: 34,
+        num: 34,
+        soql: `SELECT UrlName FROM KnowledgeArticleVersion WHERE PublishStatus='draft' WITH DATA CATEGORY Geography__c AT usa__c AND Product__c ABOVE_OR_BELOW mobile_phones__c`,
+      },
+      { key: 35, num: 35, soql: `SELECT Name, ID FROM Contact LIMIT 1 FOR VIEW` },
+      { key: 36, num: 36, soql: `SELECT Name, ID FROM Contact LIMIT 1 FOR REFERENCE` },
+      { key: 37, num: 37, soql: `SELECT Id FROM Account LIMIT 2 FOR UPDATE UPDATE TRACKING` },
+      {
+        key: 38,
+        num: 38,
+        soql: `SELECT amount, FORMAT(amount) Amt, convertCurrency(amount) editDate, FORMAT(convertCurrency(amount)) convertedCurrency FROM Opportunity where id = '12345'`,
+      },
+      { key: 39, num: 39, soql: `SELECT FORMAT(MIN(closedate)) Amt FROM opportunity` },
     ];
   };
 
diff --git a/lib/SoqlComposer.ts b/lib/SoqlComposer.ts
index a8fdc3e..b9a758d 100644
--- a/lib/SoqlComposer.ts
+++ b/lib/SoqlComposer.ts
@@ -7,6 +7,8 @@ import {
   HavingClause,
   OrderByClause,
   TypeOfField,
+  WithDataCategoryClause,
+  ForClause,
 } from './models/SoqlQuery.model';
 import * as utils from './utils';
 
@@ -72,8 +74,8 @@ export class Compose {
     output += ` ${utils.get(query.sObjectPrefix, '.')}${query.sObject}${utils.get(query.sObjectAlias, '', ' ')}`;
     this.log(output);
 
-    if (query.whereClause) {
-      output += ` WHERE ${this.parseWhereClause(query.whereClause)}`;
+    if (query.where) {
+      output += ` WHERE ${this.parseWhereClause(query.where)}`;
       this.log(output);
     }
 
@@ -103,6 +105,21 @@ export class Compose {
       this.log(output);
     }
 
+    if (query.withDataCategory) {
+      output += ` WITH DATA CATEGORY ${this.parseWithDataCategory(query.withDataCategory)}`;
+      this.log(output);
+    }
+
+    if (query.for) {
+      output += ` FOR ${query.for}`;
+      this.log(output);
+    }
+
+    if (query.update) {
+      output += ` UPDATE ${query.update}`;
+      this.log(output);
+    }
+
     // TODO: add FOR support https://github.com/paustint/soql-parser-js/issues/19
 
     return output;
@@ -195,4 +212,16 @@ export class Compose {
       return output.trim();
     }
   }
+
+  private parseWithDataCategory(withDataCategory: WithDataCategoryClause): string {
+    return withDataCategory.conditions
+      .map(condition => {
+        const params =
+          condition.parameters.length > 1
+            ? `(${condition.parameters.join(', ')})`
+            : `${condition.parameters.join(', ')}`;
+        return `${condition.groupName} ${condition.selector} ${params}`;
+      })
+      .join(' AND ');
+  }
 }
diff --git a/lib/SoqlListener.ts b/lib/SoqlListener.ts
index d684e74..0fa7cf6 100644
--- a/lib/SoqlListener.ts
+++ b/lib/SoqlListener.ts
@@ -10,10 +10,15 @@ import {
   OrderByClause,
   Query,
   WhereClause,
+  WithDataCategoryCondition,
+  GroupSelector,
+  ForClause,
+  TypeOfFieldCondition,
+  UpdateClause,
 } from './models/SoqlQuery.model';
 import { SoqlQueryConfig } from './SoqlParser';
 
-export type currItem = 'field' | 'typeof' | 'from' | 'where' | 'groupby' | 'orderby' | 'having';
+export type currItem = 'field' | 'typeof' | 'from' | 'where' | 'groupby' | 'orderby' | 'having' | 'withDataCategory';
 
 export interface Context {
   isSubQuery: boolean;
@@ -29,7 +34,7 @@ export class SoqlQuery implements Query {
   subqueries: Query[];
   sObject: string;
   sObjectAlias?: string;
-  whereClause?: WhereClause;
+  where?: WhereClause;
   limit?: number;
   offset?: number;
   groupBy?: GroupByClause;
@@ -166,6 +171,9 @@ export class Listener implements SOQLListener {
     if (this.config.logging) {
       console.log('enterData_category_group_name:', ctx.text);
     }
+    this.context.tempData.conditions.push({
+      groupName: ctx.text,
+    });
   }
   exitData_category_group_name(ctx: Parser.Data_category_group_nameContext) {
     if (this.config.logging) {
@@ -176,6 +184,8 @@ export class Listener implements SOQLListener {
     if (this.config.logging) {
       console.log('enterData_category_name:', ctx.text);
     }
+    const condition = utils.getLastItem<WithDataCategoryCondition>(this.context.tempData.conditions);
+    condition.parameters.push(ctx.text);
   }
   exitData_category_name(ctx: Parser.Data_category_nameContext) {
     if (this.config.logging) {
@@ -350,7 +360,8 @@ export class Listener implements SOQLListener {
       console.log('enterFunction_name:', ctx.text);
     }
     if (this.context.currentItem === 'field') {
-      this.context.tempData.name = ctx.text;
+      const currFn: FunctionExp = this.context.tempData.fn || this.context.tempData;
+      currFn.name = ctx.text;
     }
     if (this.context.currentItem === 'having') {
       this.context.tempData.currConditionOperation.left.fn.name = ctx.text;
@@ -467,7 +478,7 @@ export class Listener implements SOQLListener {
       console.log('exitWhere_clause:', ctx.text);
     }
 
-    this.getSoqlQuery().whereClause = this.context.tempData.data;
+    this.getSoqlQuery().where = this.context.tempData.data;
     this.context.tempData = null;
   }
   enterGroupby_clause(ctx: Parser.Groupby_clauseContext) {
@@ -623,6 +634,7 @@ export class Listener implements SOQLListener {
       console.log('enterFunction_call_spec:', ctx.text);
     }
     if (this.context.currentItem === 'field') {
+      // If nested function, init nested fn operator
       this.context.tempData = {};
     }
     if (this.context.currentItem === 'having') {
@@ -655,7 +667,11 @@ export class Listener implements SOQLListener {
     }
     // COUNT(ID) or Count()
     if (this.context.currentItem === 'field') {
-      this.context.tempData.text = ctx.text;
+      if (this.context.tempData.text) {
+        this.context.tempData.fn = {};
+      }
+      const currFn: FunctionExp = this.context.tempData.fn || this.context.tempData;
+      currFn.text = ctx.text;
     }
     if (this.context.currentItem === 'having') {
       this.context.tempData.currConditionOperation.left.fn = {
@@ -760,7 +776,7 @@ export class Listener implements SOQLListener {
     if (this.config.logging) {
       console.log('enterTypeof_then_clause:', ctx.text);
     }
-    const whenThenClause = this.context.tempData.typeOf.conditions[this.context.tempData.typeOf.conditions.length - 1];
+    const whenThenClause = utils.getLastItem<TypeOfFieldCondition>(this.context.tempData.typeOf.conditions);
     whenThenClause.fieldList = ctx.getChild(1).text.split(',');
   }
   exitTypeof_then_clause(ctx: Parser.Typeof_then_clauseContext) {
@@ -1072,11 +1088,17 @@ export class Listener implements SOQLListener {
     if (this.config.logging) {
       console.log('enterWith_data_category_clause:', ctx.text);
     }
+    this.context.currentItem = 'withDataCategory';
+    this.context.tempData = {
+      conditions: [],
+    };
   }
   exitWith_data_category_clause(ctx: Parser.With_data_category_clauseContext) {
     if (this.config.logging) {
       console.log('exitWith_data_category_clause:', ctx.text);
     }
+    this.getSoqlQuery().withDataCategory = this.context.tempData;
+    this.context.tempData = null;
   }
   enterData_category_spec_list(ctx: Parser.Data_category_spec_listContext) {
     if (this.config.logging) {
@@ -1102,6 +1124,8 @@ export class Listener implements SOQLListener {
     if (this.config.logging) {
       console.log('enterData_category_parameter_list:', ctx.text);
     }
+    const condition = utils.getLastItem<WithDataCategoryCondition>(this.context.tempData.conditions);
+    condition.parameters = [];
   }
   exitData_category_parameter_list(ctx: Parser.Data_category_parameter_listContext) {
     if (this.config.logging) {
@@ -1112,6 +1136,8 @@ export class Listener implements SOQLListener {
     if (this.config.logging) {
       console.log('enterData_category_selector:', ctx.text);
     }
+    const condition = utils.getLastItem<WithDataCategoryCondition>(this.context.tempData.conditions);
+    condition.selector = ctx.text.toUpperCase() as GroupSelector;
   }
   exitData_category_selector(ctx: Parser.Data_category_selectorContext) {
     if (this.config.logging) {
@@ -1249,6 +1275,7 @@ export class Listener implements SOQLListener {
     if (this.config.logging) {
       console.log('enterFor_value:', ctx.text);
     }
+    this.getSoqlQuery().for = ctx.text.toUpperCase() as ForClause;
   }
   exitFor_value(ctx: Parser.For_valueContext) {
     if (this.config.logging) {
@@ -1264,5 +1291,6 @@ export class Listener implements SOQLListener {
     if (this.config.logging) {
       console.log('exitUpdate_value:', ctx.text);
     }
+    this.getSoqlQuery().update = ctx.text as UpdateClause;
   }
 }
diff --git a/lib/models/SoqlQuery.model.ts b/lib/models/SoqlQuery.model.ts
index dc50505..73ac864 100644
--- a/lib/models/SoqlQuery.model.ts
+++ b/lib/models/SoqlQuery.model.ts
@@ -1,5 +1,10 @@
 export type LogicalOperator = 'AND' | 'OR';
 export type Operator = '=' | '<=' | '>=' | '>' | '<' | 'LIKE' | 'IN' | 'NOT IN' | 'INCLUDES' | 'EXCLUDES';
+export type TypeOfFieldConditionType = 'WHEN' | 'ELSE';
+export type GroupSelector = 'ABOVE' | 'AT' | 'BELOW' | 'ABOVE_OR_BELOW';
+export type LogicalPrefix = 'NOT';
+export type ForClause = 'VIEW' | 'UPDATE' | 'REFERENCE';
+export type UpdateClause = 'TRACKING' | 'VIEWSTAT';
 
 export interface Query {
   fields: Field[];
@@ -7,12 +12,15 @@ export interface Query {
   sObject: string;
   sObjectAlias?: string;
   sObjectPrefix?: string;
-  whereClause?: WhereClause;
+  where?: WhereClause;
   limit?: number;
   offset?: number;
   groupBy?: GroupByClause;
   having?: HavingClause;
   orderBy?: OrderByClause | OrderByClause[];
+  withDataCategory?: WithDataCategoryClause;
+  for?: ForClause;
+  update?: UpdateClause;
 }
 
 export interface SelectStatement {
@@ -34,7 +42,7 @@ export interface TypeOfField {
 }
 
 export interface TypeOfFieldCondition {
-  type: 'WHEN' | 'ELSE';
+  type: TypeOfFieldConditionType;
   objectType?: string; // not present when ELSE
   fieldList: string[];
 }
@@ -48,7 +56,7 @@ export interface WhereClause {
 export interface Condition {
   openParen?: number;
   closeParen?: number;
-  logicalPrefix?: 'NOT';
+  logicalPrefix?: LogicalPrefix;
   field: string;
   operator: Operator;
   value: string | string[];
@@ -86,4 +94,15 @@ export interface FunctionExp {
   name?: string; // Count
   alias?: string;
   parameter?: string | string[];
+  fn?: FunctionExp; // used for nested functions FORMAT(MIN(CloseDate))
+}
+
+export interface WithDataCategoryClause {
+  conditions: WithDataCategoryCondition[];
+}
+
+export interface WithDataCategoryCondition {
+  groupName: string;
+  selector: GroupSelector;
+  parameters: string[];
 }
diff --git a/lib/utils.ts b/lib/utils.ts
index 163f42a..481269a 100644
--- a/lib/utils.ts
+++ b/lib/utils.ts
@@ -28,6 +28,10 @@ export function getIfTrue(val: boolean | null | undefined, returnStr: string): s
   return isBoolean(val) && val ? returnStr : '';
 }
 
+export function getLastItem<T>(arr: T[]): T {
+  return arr[arr.length - 1];
+}
+
 export function getAsArrayStr(val: string | string[], alwaysParens: boolean = false): string {
   if (isArray(val)) {
     if (val.length > 0) {


Test patch:
diff --git a/debug/test.js b/debug/test.js
index 1be70fc..8f2d00e 100644
--- a/debug/test.js
+++ b/debug/test.js
@@ -1,9 +1,12 @@
 var soqlParserJs = require('../dist');
 
 const query = `
-SELECT Account.Name, (SELECT Contact.LastName FROM Account.Contacts) FROM Account
+SELECT Title FROM FAQ__kav WHERE PublishStatus='online' and Language = 'en_US' and KnowledgeArticleVersion = 'ka230000000PCiy' UPDATE VIEWSTAT
 `;
 
 const parsedQuery = soqlParserJs.parseQuery(query, { logging: true });
 
 console.log(JSON.stringify(parsedQuery, null, 2));
+
+// SELECT amount, FORMAT(amount) Amt, convertCurrency(amount) editDate, FORMAT(convertCurrency(amount)) convertedCurrency FROM Opportunity where id = '12345'
+// SELECT FORMAT(MIN(closedate)) Amt FROM opportunity
diff --git a/test/TestCases.ts b/test/TestCases.ts
index fb167f8..7b56bef 100644
--- a/test/TestCases.ts
+++ b/test/TestCases.ts
@@ -45,7 +45,7 @@ export const testCases: TestCase[] = [
       ],
       subqueries: [],
       sObject: 'Contact',
-      whereClause: {
+      where: {
         left: {
           field: 'Name',
           operator: 'LIKE',
@@ -91,7 +91,7 @@ export const testCases: TestCase[] = [
       ],
       subqueries: [],
       sObject: 'Account',
-      whereClause: {
+      where: {
         left: {
           field: 'Industry',
           operator: '=',
@@ -112,7 +112,7 @@ export const testCases: TestCase[] = [
       ],
       subqueries: [],
       sObject: 'Account',
-      whereClause: {
+      where: {
         left: {
           field: 'Industry',
           operator: '=',
@@ -277,7 +277,7 @@ export const testCases: TestCase[] = [
       ],
       subqueries: [],
       sObject: 'Contact',
-      whereClause: {
+      where: {
         left: {
           field: 'Account.Industry',
           operator: '=',
@@ -363,7 +363,7 @@ export const testCases: TestCase[] = [
           ],
           subqueries: [],
           sObject: 'Contacts',
-          whereClause: {
+          where: {
             left: {
               field: 'CreatedBy.Alias',
               operator: '=',
@@ -373,7 +373,7 @@ export const testCases: TestCase[] = [
         },
       ],
       sObject: 'Account',
-      whereClause: {
+      where: {
         left: {
           field: 'Industry',
           operator: '=',
@@ -401,7 +401,7 @@ export const testCases: TestCase[] = [
       ],
       subqueries: [],
       sObject: 'Daughter__c',
-      whereClause: {
+      where: {
         left: {
           field: 'Mother_of_Child__r.LastName__c',
           operator: 'LIKE',
@@ -434,7 +434,7 @@ export const testCases: TestCase[] = [
         },
       ],
       sObject: 'Merchandise__c',
-      whereClause: {
+      where: {
         left: {
           field: 'Name',
           operator: 'LIKE',
@@ -458,7 +458,7 @@ export const testCases: TestCase[] = [
       ],
       subqueries: [],
       sObject: 'Task',
-      whereClause: {
+      where: {
         left: {
           field: 'Owner.FirstName',
           operator: 'LIKE',
@@ -486,7 +486,7 @@ export const testCases: TestCase[] = [
       ],
       subqueries: [],
       sObject: 'Task',
-      whereClause: {
+      where: {
         left: {
           field: 'Owner.FirstName',
           operator: 'LIKE',
@@ -650,7 +650,7 @@ export const testCases: TestCase[] = [
       ],
       subqueries: [],
       sObject: 'LoginHistory',
-      whereClause: {
+      where: {
         left: {
           field: 'LoginTime',
           operator: '>',
@@ -888,7 +888,7 @@ export const testCases: TestCase[] = [
       ],
       subqueries: [],
       sObject: 'Account',
-      whereClause: {
+      where: {
         left: {
           openParen: 1,
           field: 'Id',
@@ -1026,5 +1026,254 @@ export const testCases: TestCase[] = [
       sObjectAlias: 'a',
     },
   },
+  {
+    testCase: 35,
+    soql: `SELECT Title FROM KnowledgeArticleVersion WHERE PublishStatus = 'online' WITH DATA CATEGORY Geography__c ABOVE usa__c`,
+    output: {
+      fields: [
+        {
+          text: 'Title',
+        },
+      ],
+      subqueries: [],
+      sObject: 'KnowledgeArticleVersion',
+      where: {
+        left: {
+          field: 'PublishStatus',
+          operator: '=',
+          value: "'online'",
+        },
+      },
+      withDataCategory: {
+        conditions: [
+          {
+            groupName: 'Geography__c',
+            selector: 'ABOVE',
+            parameters: ['usa__c'],
+          },
+        ],
+      },
+    },
+  },
+  {
+    testCase: 36,
+    soql: `SELECT Title FROM Question WHERE LastReplyDate > 2005-10-08T01:02:03Z WITH DATA CATEGORY Geography__c AT (usa__c, uk__c)`,
+    output: {
+      fields: [
+        {
+          text: 'Title',
+        },
+      ],
+      subqueries: [],
+      sObject: 'Question',
+      where: {
+        left: {
+          field: 'LastReplyDate',
+          operator: '>',
+          value: '2005-10-08T01:02:03Z',
+        },
+      },
+      withDataCategory: {
+        conditions: [
+          {
+            groupName: 'Geography__c',
+            selector: 'AT',
+            parameters: ['usa__c', 'uk__c'],
+          },
+        ],
+      },
+    },
+  },
+  {
+    testCase: 37,
+    soql: `SELECT UrlName FROM KnowledgeArticleVersion WHERE PublishStatus = 'draft' WITH DATA CATEGORY Geography__c AT usa__c AND Product__c ABOVE_OR_BELOW mobile_phones__c`,
+    output: {
+      fields: [
+        {
+          text: 'UrlName',
+        },
+      ],
+      subqueries: [],
+      sObject: 'KnowledgeArticleVersion',
+      where: {
+        left: {
+          field: 'PublishStatus',
+          operator: '=',
+          value: "'draft'",
+        },
+      },
+      withDataCategory: {
+        conditions: [
+          {
+            groupName: 'Geography__c',
+            selector: 'AT',
+            parameters: ['usa__c'],
+          },
+          {
+            groupName: 'Product__c',
+            selector: 'ABOVE_OR_BELOW',
+            parameters: ['mobile_phones__c'],
+          },
+        ],
+      },
+    },
+  },
+  {
+    testCase: 38,
+    soql: `SELECT Id FROM Contact FOR VIEW`,
+    output: {
+      fields: [
+        {
+          text: 'Id',
+        },
+      ],
+      subqueries: [],
+      sObject: 'Contact',
+      for: 'VIEW',
+    },
+  },
+  {
+    testCase: 39,
+    soql: `SELECT Id FROM Contact FOR REFERENCE`,
+    output: {
+      fields: [
+        {
+          text: 'Id',
+        },
+      ],
+      subqueries: [],
+      sObject: 'Contact',
+      for: 'REFERENCE',
+    },
+  },
+  {
+    testCase: 40,
+    soql: `SELECT Id FROM Contact FOR UPDATE`,
+    output: {
+      fields: [
+        {
+          text: 'Id',
+        },
+      ],
+      subqueries: [],
+      sObject: 'Contact',
+      for: 'UPDATE',
+    },
+  },
+  {
+    testCase: 41,
+    soql: `SELECT Id FROM FAQ__kav FOR UPDATE`,
+    output: {
+      fields: [
+        {
+          text: 'Id',
+        },
+      ],
+      subqueries: [],
+      sObject: 'FAQ__kav',
+      for: 'UPDATE',
+    },
+  },
+  {
+    testCase: 42,
+    soql: `SELECT Id FROM FAQ__kav FOR VIEW UPDATE TRACKING`,
+    output: {
+      fields: [
+        {
+          text: 'Id',
+        },
+      ],
+      subqueries: [],
+      sObject: 'FAQ__kav',
+      for: 'VIEW',
+      update: 'TRACKING',
+    },
+  },
+  {
+    testCase: 43,
+    soql: `SELECT Id FROM FAQ__kav UPDATE VIEWSTAT`,
+    output: {
+      fields: [
+        {
+          text: 'Id',
+        },
+      ],
+      subqueries: [],
+      sObject: 'FAQ__kav',
+      update: 'VIEWSTAT',
+    },
+  },
+  {
+    testCase: 44,
+    soql: `SELECT amount, FORMAT(amount) Amt, convertCurrency(amount) editDate, FORMAT(convertCurrency(amount)) convertedCurrency FROM Opportunity WHERE id = '12345'`,
+    output: {
+      fields: [
+        {
+          text: 'amount',
+        },
+        {
+          fn: {
+            text: 'FORMAT(amount)',
+            name: 'FORMAT',
+            parameter: 'amount',
+            alias: 'Amt',
+          },
+        },
+        {
+          fn: {
+            text: 'convertCurrency(amount)',
+            name: 'convertCurrency',
+            parameter: 'amount',
+            alias: 'editDate',
+          },
+        },
+        {
+          fn: {
+            text: 'FORMAT(convertCurrency(amount))',
+            name: 'FORMAT',
+            parameter: 'convertCurrency(amount)',
+            fn: {
+              text: 'convertCurrency(amount)',
+              name: 'convertCurrency',
+              parameter: 'amount',
+            },
+            alias: 'convertedCurrency',
+          },
+        },
+      ],
+      subqueries: [],
+      sObject: 'Opportunity',
+      where: {
+        left: {
+          field: 'id',
+          operator: '=',
+          value: "'12345'",
+        },
+      },
+    },
+  },
+  {
+    testCase: 45,
+    soql: `SELECT FORMAT(MIN(closedate)) Amt FROM Opportunity`,
+    output: {
+      fields: [
+        {
+          fn: {
+            text: 'FORMAT(MIN(closedate))',
+            name: 'FORMAT',
+            parameter: 'MIN(closedate)',
+            fn: {
+              text: 'MIN(closedate)',
+              name: 'MIN',
+              parameter: 'closedate',
+            },
+            alias: 'Amt',
+          },
+        },
+      ],
+      subqueries: [],
+      sObject: 'Opportunity',
+    },
+  },
 ];
 export default testCases;
diff --git a/test/utils.spec.ts b/test/utils.spec.ts
index 0c0c033..f4427e3 100644
--- a/test/utils.spec.ts
+++ b/test/utils.spec.ts
@@ -140,6 +140,15 @@ describe('getAsArrayStr', () => {
   });
 });
 
+describe('getLastItem', () => {
+  it(`Should correctly pad suffix`, () => {
+    const str = 'TEST';
+    expect(utils.getLastItem<number>([1, 2, 3, 4, 5])).equal(5);
+    expect(utils.getLastItem<string>(['a', 'b', 'c'])).equal('c');
+    expect(utils.getLastItem<string>([])).equal(undefined);
+  });
+});
+
 describe('pad', () => {
   it(`Should correctly pad suffix`, () => {
     const str = 'TEST';