将排序顺序作为参数传递

Question

将排序顺序作为参数传递

3

参考以下示例SQL语句。我能够将参数值传递给语句中的占位符'?'。但是我想知道是否可能以同样的方式传递排序顺序？

所以，而不是这样：

//Create SQL query
var getAccountsTransactionsStatement = WL.Server.createSQLStatement(
  "SELECT transactionId, fromAccount, toAccount, transactionDate, transactionAmount, transactionType " +
  "FROM accounttransactions " +
  "WHERE accounttransactions.fromAccount = ? OR accounttransactions.toAccount = ? " +
  "ORDER BY transactionDate DESC " +
  "LIMIT 20;"
);

我能要这个吗：

//Create SQL query
var getAccountsTransactionsStatement = WL.Server.createSQLStatement(
  "SELECT transactionId, fromAccount, toAccount, transactionDate, transactionAmount, transactionType " +
  "FROM accounttransactions " +
  "WHERE accounttransactions.fromAccount = ? OR accounttransactions.toAccount = ? " +
  "ORDER BY ? DESC " +
  "LIMIT 20;"
);

要调用它：

//Invoke prepared SQL query and return invocation result
function getAccountTransactions1(accountId){
  return WL.Server.invokeSQLStatement({
    preparedStatement : getAccountsTransactionsStatement,
    parameters : [accountId, accountId, transactionDate]
  });
}

- Got Hima

1个回答

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- nimdil · Accepted Answer

两件事情：

This query piece:

WHERE accounttransactions.fromAccount = ? OR accounttransactions.toAccount = ?

Could be replaced with this:

WHERE ? in (accounttransactions.fromAccount, accounttransactions.toAccount)

No you can't. Parameters are values - kind of static stuff - while column names are not. You could probably work around the issue somehow in limited way by using s.t. like this:

ORDER BY 
  CASE ?
    WHEN 'transactionDate' THEN transactionDate
    WHEN 'someotherdate' THEN someotherdate
    ELSE DATE '2010-01-01'
  END

请注意，这是一个混乱的结构。另外，根据您使用的数据库类型，您可能希望将所有列转换为一个数据类型，即字符串。因此，to_char（transactionDate，'yyyy-mm-dd hh24：mm：ss'）可能是有序的，但您需要确保在您的情况下排序正确（因为数字往往会搞砸像“2”>“13”的东西）。