Suitescript SuiteQL错误，IN运算符

Question

SuiteQL

SELECT *
FROM   transactionline
WHERE  ( transactionline.department, transactionline.cseg_grant ) IN (( '11',
'1' ))

当使用suiteQL运行上述查询时，它返回以下error

错误：

Search error occurred: Invalid or unsupported search

这是ORACLE中的有效语法我在Oracle中尝试了一个示例查询，在执行中没有问题，而此语法在SuiteQL中不起作用

Answer 1

我们的解决方案是创建一个RESTlet，该RESTlet可以通过query.runSuiteQL()执行SQL。我认为您遇到的问题是由于本地web服务提供分页响应，而无限制和偏移参数地调用/query/v1/suiteql端点并不能解决问题。

你可以自己确认/尝试：通过query.runSuiteQL()测试你的SQL，你会得到结果，但通过query.run SuiteQLPaged()使用相同的SQL，会得到错误消息"无效或不支持的搜索"。

我认为使用query.runSuiteQL().的记录限制为5000条

以下是RESTlet的JS代码：

/**
* @NApiVersion 2.1
* @NScriptType Restlet
* @NModuleScope SameAccount
Name:
UH SuiteQL API
ID:
_uh_suiteql_api_restlet
Description:
A RESTlet that serves as an RPC-style API for SuiteQL.
Developers:
Tim Dietrich
Simeon Bartley
*/
define(["require", "exports", "N/query", "N/error", "N/log"], function (require, exports, query, error, log) {
Object.defineProperty(exports, "__esModule", { value: true });
exports.throwError = exports.post = void 0;
const post = async (requestParameters) => {
try {
if (typeof requestParameters.function === "undefined" || requestParameters.function === "") {
throwError("No function was specified.");
}
switch (requestParameters.function) {
case "sqlRunUnpaged":
return JSON.stringify(await sqlRunUnpaged(requestParameters));
case "sqlRunPaged":
return JSON.stringify(await sqlRunPaged(requestParameters));
default:
throwError("Unsupported function.");
}
}
catch (e) {
return JSON.stringify({ "error": e.message });
}
};
exports.post = post;
function throwError(message, name = "") {
const errorObj = error.create({ name, message, notifyOff: false });
log.error({ title: "", details: errorObj });
throw new Error(errorObj.message);
}
exports.throwError = throwError;
/** Unpaged queries will return up to 5000 records. */
async function sqlRunUnpaged(requestParameters) {
if (typeof requestParameters.sql === "undefined" || requestParameters.sql === "") {
throwError("No SQL specified.");
}
try {
const res = await query.runSuiteQL.promise({ query: requestParameters.sql });
const records = res.asMappedResults();
return {
"items": records
};
}
catch (e) {
throwError(e.message);
}
}
/** Paged query handler - useful where > 5000 records may be required, or subsets are desirable. */
async function sqlRunPaged(requestParameters) {
if (typeof requestParameters.sql === "undefined" || requestParameters.sql === "") {
throwError("No SQL specified.");
}
const maxPageSize = 5000;
let pageSize = typeof requestParameters.pageSize === "undefined" ? maxPageSize : Number.parseInt(requestParameters.pageSize.toString());
if (pageSize < 0) {
pageSize = maxPageSize;
}
let pageNumber = typeof requestParameters.pageNumber === "undefined" ? 1 : Number.parseInt(requestParameters.pageNumber.toString());
if (pageNumber < 1) {
pageNumber = 1;
}
try {
const res = await query.runSuiteQLPaged.promise({ query: requestParameters.sql, pageSize: pageSize });
let items = [];
let hasMore = false;
if (res.pageRanges.length > 0) {
pageNumber = pageNumber <= res.pageRanges.length ? pageNumber : 1;
const page = res.fetch(pageNumber - 1);
items = page.data.asMappedResults();
hasMore = !page.isLast;
}
return {
"totalResults": res.count,
"count": items.length,
"offset": (pageNumber - 1) * res.pageSize,
"hasMore": hasMore,
"pageSize": res.pageSize,
"pageNumber": pageNumber,
"items": items
};
}
catch (e) {
throwError(e.message);
}
}
});

这个RESTlet改编自Tim Dietrich的作品。上面的JS是从TypeScript传输的(你会想要@hitc/netsuite类型)：

/**
* @NApiVersion 2.1
* @NScriptType Restlet
* @NModuleScope SameAccount
Name:
UH SuiteQL API
ID:
_uh_suiteql_api_restlet
Description:
A RESTlet that serves as an RPC-style API for SuiteQL.
Developers:
Tim Dietrich
Simeon Bartley
*/
import { EntryPoints } from "N/types";
import * as query from "N/query";
import * as error from "N/error";
import * as log from "N/log";
interface IRouteBaseParameters {
function?: string
}
interface ISuiteQLRunUnpagedParameters {
sql?: string;
}
interface ISuiteQLRunPagedParameters {
sql?: string;
pageSize?: number;
pageNumber?: number;
}
interface ISuiteQlUnpagedResultJson {
items: unknown[];
}
interface ISuiteQlPagedResultJson {
totalResults: number;
count: number;
offset: number;
hasMore: boolean;
pageSize: number,
pageNumber: number,
items: unknown[];
}
type IAllRequestParameters = IRouteBaseParameters & ISuiteQLRunUnpagedParameters & ISuiteQLRunPagedParameters;
export const post: EntryPoints.RESTlet.post = async (requestParameters: IAllRequestParameters): Promise<string> => {
try {
if (typeof requestParameters.function === "undefined" || requestParameters.function === "") {
throwError("No function was specified.");
}
switch (requestParameters.function) {
case "sqlRunUnpaged":
return JSON.stringify(await sqlRunUnpaged(requestParameters));
case "sqlRunPaged":
return JSON.stringify(await sqlRunPaged(requestParameters));
default:
throwError("Unsupported function.");
}
}
catch (e: unknown) {
return JSON.stringify({ "error": (e as Error).message });
}
}
export function throwError(message: string, name: string = ""): never {
const errorObj = error.create({ name, message, notifyOff: false });
log.error({ title: "", details: errorObj });
throw new Error(errorObj.message);
}
/** Unpaged queries will return up to 5000 records. */
async function sqlRunUnpaged(requestParameters: ISuiteQLRunUnpagedParameters): Promise<ISuiteQlUnpagedResultJson> {
if (typeof requestParameters.sql === "undefined" || requestParameters.sql === "") {
throwError("No SQL specified.");
}
try {
const res = await query.runSuiteQL.promise({ query: requestParameters.sql });
const records = res.asMappedResults();
return {
"items": records
};
}
catch (e: unknown) {
throwError((e as Error).message);
}
}
/** Paged query handler - useful where > 5000 records may be required, or subsets are desirable. */
async function sqlRunPaged(requestParameters: ISuiteQLRunPagedParameters): Promise<ISuiteQlPagedResultJson> {
if (typeof requestParameters.sql === "undefined" || requestParameters.sql === "") {
throwError("No SQL specified.");
}
const maxPageSize = 5000;
let pageSize = typeof requestParameters.pageSize === "undefined" ? maxPageSize : Number.parseInt(requestParameters.pageSize.toString());
if (pageSize < 0) {
pageSize = maxPageSize;
}
let pageNumber = typeof requestParameters.pageNumber === "undefined" ? 1 : Number.parseInt(requestParameters.pageNumber.toString());
if (pageNumber < 1) {
pageNumber = 1;
}
try {
const res = await query.runSuiteQLPaged.promise({ query: requestParameters.sql, pageSize: pageSize });
let items: unknown[] = [];
let hasMore = false;
if (res.pageRanges.length > 0) {
pageNumber = pageNumber <= res.pageRanges.length ? pageNumber : 1;
const page = res.fetch(pageNumber - 1);
items = page.data.asMappedResults();
hasMore = !page.isLast;
}
return {
"totalResults": res.count,
"count": items.length,
"offset": (pageNumber - 1) * res.pageSize,
"hasMore": hasMore,
"pageSize": res.pageSize,
"pageNumber": pageNumber,
"items": items
};
}
catch (e: unknown) {
throwError((e as Error).message);
}
}

我希望本机web服务会及时改进，使这种方法变得多余。