NetSuite保存的搜索中可以使用哪些SQL函数? [英] What SQL Functions are Available in NetSuite saved searches?

问题描述

NetSuite保存的搜索(公式)中可以使用哪些SQL函数?

What SQL functions are available in NetSuite saved searches (formulas)?

推荐答案

以下是截至2019年9月在NetSuite保存的搜索中可用的SQL函数列表(显示为示例代码).这些保存的搜索不允许所有SQL函数，这样有助于了解可用的功能.

The following is a list of SQL functions (shown as example code) available to use in NetSuite saved searches as of September 2019. These saved searches do not allow all SQL functions so it is helpful to know what is available to work with.

如果您拥有有权访问SuiteAnswers的NetSuite帐户，则可以通过此链接访问答案ID:10101 .

If you have a NetSuite account with access to SuiteAnswers you can follow this link to Answer Id: 10101.

我花了一段时间才找到它，因为它没有出现在网络搜索中.我想让它容易找到.享受吧！

It took me a while to find this since it didn't show up in web searches. I want to make it easy to find. Enjoy!

数值函数

示例

ABS( {amount} )
ACOS( 0.35 )
ASIN( 1 )
ATAN( 0.2 )
ATAN2( 0.2, 0.3 )
BITAND( 5, 3 )
CEIL( {today}-{createddate} )
COS( 0.35 )
COSH( -3.15 )
EXP( {rate} )
FLOOR( {today}-{createddate} )
LN( 20 )
LOG( 10, 20 )
MOD( 3:56 pm-{lastmessagedate},7 )
NANVL( {itemisbn13}, '' )
POWER( {custcoldaystoship},-.196 )
REMAINDER( {transaction.totalamount}, {transaction.amountpaid} )
ROUND( ( {today}-{startdate} ), 0 )
SIGN( {quantity} )
SIN( 5.2 )
SINH( 3 )
SQRT( POWER( {taxamount}, 2 ) )
TAN( -5.2 )
TANH( 3 )
TRUNC( {amount}, 1 )

返回字符值的字符函数

示例

Character Functions Returning Character Values

Examples

CHR( 13 )
CONCAT( {number}​,​CONCAT( ​'​_​'​,​{​line}​ )​ )
INITCAP( {customer.​companyname}​ )
LOWER( {customer.​companyname}​ )
LPAD( {line},3,'0' )
LTRIM( {companyname},'-' )
REGEXP_REPLACE( {name}, '^.*:', '' )
REGEXP_SUBSTR( ​{​item}​,​'​[​^​:​]​+​$​'​ )
REPLACE( {serialnumber}, '&', ',' )
RPAD( {firstname},20 )
RTRIM( {paidtransaction.​externalid}​,​'​-​Invoice'​ )
SOUNDEX( {companyname} )
SUBSTR( {transaction.​salesrep}​,​1,​3 )
TRANSLATE( ​{​expensecategory}​,​ ' ', '+' )
TRIM ( BOTH ',' FROM {custrecord_assetcost} )
UPPER( {unit} )

返回数字值的字符函数

示例

Character Functions Returning Number Values

Examples

ASCII( {taxitem} )
INSTR( {messages.message}, 'cspdr3' )
LENGTH( {name} )
REGEXP_INSTR ( {item.unitstype}, '\d' )
TO_NUMBER( {quantity} )

日期时间功能

示例

Datetime Functions

Examples

ADD_MONTHS( {today},-1 )
LAST_DAY( {today} )
MONTHS_BETWEEN( ​SYSDATE,​{​createddate}​ )

NEXT_DAY( {today},'SATURDAY' )
ROUND( TO_DATE( '12/31/2014', 'mm/dd/yyyy' )-{datecreated} )
TO_CHAR( {date}, 'hh24' )
TO_DATE( '31.12.2011', 'DD.MM.YYYY' )
TRUNC( {today},'YYYY' )

另请参见下面的示例部分之一中的Sysdate.

与NULL相关的功能

示例

Also see Sysdate in one of the example sections below.

NULL-Related Functions

Examples

COALESCE( {quantitycommitted}, 0 )
NULLIF( {price}, 0 )
NVL( {quantity},'0' )
NVL2( {location}, 1, 2 )

解码

示例

Decode

Examples

DECODE( {systemnotes.name}, {assigned},'T','F' )

Sysdate

示例

Sysdate

Examples

TO_DATE( SYSDATE, 'DD.MM.YYYY' )

或

TO_CHAR( SYSDATE, 'mm/dd/yyyy' )

另请参见日期时间函数中的TO_DATE和TO_CHAR.

案例

示例

See also TO_DATE and TO_CHAR in the Datetime Functions.

Case

Examples

CASE {state}
WHEN 'NY' THEN 'New York'
WHEN 'CA' THEN 'California'
ELSE {state}
END

或

CASE
WHEN {quantityavailable} > 19 THEN 'In Stock'
WHEN {quantityavailable} > 1 THEN 'Limited Availability'
WHEN {quantityavailable} = 1 THEN 'The Last Piece'
WHEN {quantityavailable} IS NULL THEN 'Discontinued'
ELSE 'Out of Stock'
END

分析和汇总函数

示例

Analytic and Aggregate Functions

Examples

DENSE_RANK ( {amount} WITHIN GROUP ( ORDER BY {AMOUNT} ) )

或

DENSE_RANK(  ) OVER ( PARTITION BY {name}ORDER BY {trandate} DESC )
KEEP( DENSE_RANK LAST ORDER BY {internalid} )
RANK(  ) OVER ( PARTITION by {tranid} ORDER BY {line} DESC )

或

RANK ( {amount} WITHIN GROUP ( ORDER BY {amount} ) )

这篇关于NetSuite保存的搜索中可以使用哪些SQL函数?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！