Можем ли мы использовать "Дело" в ColdFusion Query-of-Query
Я подаю заявку case в запросе ColdFusion запроса, но он выдает ошибку.
Запрос:
<cfquery name="qEmployees1" dbtype="query">
select (
case
when ISNUMERIC(u.userdefined)=1
then right('00000000'+u.userdefined,8)
else userdefined
end
) as hello
from all_employees
order by hello ASC
</cfquery>
Сообщение об ошибке:
Encountered "when" at line 3, column 22. Was expecting one of:
"AND" ... "BETWEEN" ... "IN" ... "IS" ... "LIKE" ... "NOT" ...
"OR" ... ")" ... "=" ... "." ... "!=" ... "#" ... "<>" ...
">" ... ">=" ... "<" ... "<=" ... "+" ... "-" ... "*" ...
"||" ... "/" ... "**" ... "(" ...
5 ответов
Обновить:
Первоначальное предложение не сработает, потому что оно смотрит только на один ряд. На самом деле вам нужно пройти через ваш all_employees Записать и применить его к каждой отдельной строке.
Вы можете достичь этого без QoQ, если вы просто выводите результаты на страницу. Как это:
<cfoutput>
<cfloop query="all_employees">
<cfif isNumeric(all_employees.userdefined)>
#Right('00000000'&all_employees.userdefined,8)#
<cfelse>
#all_employees.userdefined#
<cfif>
</cfloop>
</cfoutput>
Оригинальный ответ:
Как насчет этого?
<cfquery name="qEmployees1" dbtype="query">
SELECT
<cfif isNumeric([all_employees].[u.userdefined])>
right('00000000'+u.userdefined,8)
<cfelse>
u.userdefined
</cfif> AS hello
FROM all_employees
ORDER by hello
</cfquery>
Я не проверял это, но не думаю, что точечная нотация в имени столбца SQL будет работать правильно. Я все равно заключил его в квадратные скобки.
РЕДАКТИРОВАТЬ:
Я подумал об этом и решил изменить его на фактический ответ. Поскольку вы используете CF2016+, у вас есть доступ к некоторым из более современных функций, которые предлагает CF. Во-первых, Query of Query - отличный инструмент, но он может быть очень медленным. Особенно для меньшего количества записей. И затем, если в вашем базовом запросе много записей, это может поглотить память вашего сервера, поскольку это операция в памяти. Мы можем достичь нашей цели без необходимости QoQ.
Один из способов, которым мы можем что-то дублировать, - это найти некоторые новые функции CF. filter, each а также sort все работают над объектом запроса. Эти member function Версии эти, но я думаю, что они выглядят чище. Кроме того, я использовал синтаксис cfscript.
Я в основном повторно использовал свой оригинальный запрос CFSCript (all_employees), который создает объект запроса, но я добавил f столбец к нему, который содержит текст для фильтрации.
all_employees = QueryNew("userdefined,hello,f", "varchar,varchar,varchar",
[
["test","pure text","takeMe"],
["2","number as varchar","takeMe"],
["03","leading zero","takeMe"],
[" 4 ","leading and trailing spaces","takeMe"],
["5 ","extra trailing spaces","takeMe"],
[" 6","extra leading spaces","takeMe"],
["aasdfadsf","adsfasdfasd","dontTakeMe"],
["165e73","scientific notation","takeMe"],
["1.5","decimal","takeMe"],
["1,5","comma-delimited (or non-US decimal)","takeMe"],
["1.0","valid decimal","takeMe"],
["1.","invalid decimal","takeMe"],
["1,000","number with comma","takeMe"]
]
) ;
Исходный базовый запрос не имел WHERE пункт, поэтому не было никакой дополнительной фильтрации по первоначальным результатам. Но если бы нам было нужно, мы могли бы дублировать это с QueryFilter или же .filter,
filt = all_employees.filter( function(whereclause){ return ( whereclause.f == "takeMe"); } ) ;
Это занимает all_employees запрос и применяет функцию, которая будет возвращать только те строки, которые соответствуют требованиям нашей функции. Так что любая строка запроса, где f == "takeMe", Это как WHERE f = 'takeMe' в запросе. Это устанавливает новые отфильтрованные результаты в новый объект запроса filt,
Тогда мы можем использовать QueryEach или же .each просмотреть каждую строку нашего нового отфильтрованного запроса, чтобы изменить то, что нам нужно. В этом случае мы создаем новый массив для значений, которые мы хотим. for/in цикл, вероятно, будет быстрее; Я не проверял.
filt.each(
function(r) {
retval.append(
ISNUMERIC(r.userDefined) ? right("00000000"<rim(rtrim((r.userdefined))),8) : r.userDefined
) ;
}
) ;
Теперь, когда у нас есть новый массив с желаемыми результатами, оригинальный QoQ хотел упорядочить эти результаты. Мы можем сделать это с ArraySort или же .sort,
retval.sort("textnocase") ;
В моем тесте CF2016, казалось, прошел retval.sort() как логическое значение и не вернул отсортированный массив, но CF2018 сделал. Это было ожидаемое поведение, так как тип возврата был изменен в CF2018. Независимо от того, оба будут сортировать retval массив, так что когда мы дамп retval массив, это в выбранном порядке.
И, как я всегда советую, загрузите тестирование в вашей системе с вашими данными. Как я уже сказал, это только один способ понять, что вы пытаетесь сделать. Есть и другие, которые могут быть быстрее.
(CF2018)> https://trycf.com/gist/2a3762dabf10ad695a925d2bc8e55b09/acf2018?theme=monokai
https://helpx.adobe.com/coldfusion/cfml-reference/coldfusion-functions/functions-m-r/queryeach.html
https://helpx.adobe.com/coldfusion/cfml-reference/coldfusion-functions/functions-a-b/arraysort.html
ОРИГИНАЛ:
Это скорее комментарий, чем ответ, но он слишком длинный для комментария.
Я хотел бы упомянуть пару вещей, на которые стоит обратить внимание.
Во-первых, ColdFusion's isNumeric() иногда может иметь неожиданные результаты. Это на самом деле не проверяет, является ли значение числом. Он проверяет, можно ли преобразовать строку в число. Таким образом, есть все виды ценностей, которые isNumeric() увидим как numeric, EX: 1e3 это научное обозначение 1000, isNumeric("1e3") вернусь true,
Мое второе предложение заключается в том, как бороться с начальным и конечным пробелом в "числовом" значении, например: " 4 ", isNumeric() вернусь true для этого, но когда вы добавляете и обрезаете свое окончательное значение, оно будет выглядеть как "000000 4", Мое предложение иметь дело с этим, чтобы использовать val() или же ltrim(rtrim()) вокруг вашей колонны. val() уменьшит его до базового числа (" 1.0 " >> "1") но ltrim(rtrim()) сохранит номер, но избавится от пробела (" 1.0 " >> "1.0"), а также сохранить значение "научное обозначение" (" 1e3 " >> "1e3"). Оба до сих пор скучают 1,000так что, если это проблема, вы должны справиться с этим. Но используемый вами метод полностью зависит от значений, содержащихся в ваших данных. Проверка номера не всегда так проста, как кажется.
Я всегда был твердым сторонником GIGO - Garbage In, Garbage Out. Я считаю, что чистка основных данных является частью моей работы. Но если это будет экстремально или регулярно, я скажу источнику исправить это, или их вещи не будут работать правильно. Когда дело доходит до данных, невозможно учесть все возможности, но мы можем проверить общие ожидания. Белый список всегда легче, чем черный.
<cfscript>
all_employees = QueryNew("userdefined,hello", "varchar,varchar",
[
["test","pure text"],
["2","number as varchar"],
["03","leading zero"],
[" 4 ","leading and trailing spaces"],
["5 ","extra trailing spaces"],
[" 6","extra leading spaces"],
["165e73","scientific notation"],
["1.5","decimal"],
["1,5","comma-delimited (or non-US decimal)"],
["1.0","valid decimal"],
["1.","invalid decimal"],
["1,000","number with comma"]
]
)
//writedump(all_employees) ;
retval = [] ;
for (r in all_employees) {
retval.append(
{
"1 - RowInput" : r.userdefined.replace(" ","*","all") , // Replace space with * for output visibility.
"2 - IsNumeric?" : ISNUMERIC(r.userdefined) ,
"3 - FirstOutput": ( ISNUMERIC(r.userDefined) ? right("00000000"&r.userdefined,8) : r.userDefined ) ,
"4 - ValOutput" : ( ISNUMERIC(r.userDefined) ? right("00000000"&val(r.userdefined),8) : r.userDefined ) ,
"5 - TrimOutput" : ( ISNUMERIC(r.userDefined) ? right("00000000"<rim(rtrim((r.userdefined))),8) : r.userDefined )
}
) ;
}
writeDump(retval) ;
</cfscript>
https://trycf.com/gist/03164081321977462f8e9e4916476ed3/acf2018?theme=monokai
В случае, если кто-то еще решит попробовать QoQ ниже, следует отметить одну очень важную вещь: даже если он выполняется без ошибок, он НЕ делает то же самое, что и CASE, CASE Оператор применяет логику к значениям в каждой строке таблицы - индивидуально. В версии QoQ выражение CFIF работает не со всеми значениями в запросе. Он только проверяет значение в 1-й строке, а затем применяет решение для этого единственного значения ко ВСЕМ строкам в запросе.
Обратите внимание, как QoQ ниже (неправильно) сообщает, что все значения являются числовыми? В то время как запрос к базе данных (правильно) сообщает о сочетании "числовых" и "нечисловых" значений. Таким образом, код QoQ не эквивалентен CASE.
Данные TestTable:
id userDefined
1 22
2 AA
3 BB
4 CC
Запрос к базе данных:
SELECT CASE
WHEN ISNUMERIC(userDefined)=1 THEN 'Number: '+ userDefined
ELSE 'Not a number: ' + userDefined
END AS TheColumnAlias
FROM TestTable
ORDER BY ID ASC
Результат запроса к базе данных:
относительно предыдущего квартала
<cfquery name="qQueryOfQuery" dbtype="query">
SELECT
<cfif isNumeric(qDatabaseQuery2.userDefined)>
'Number: '+ userDefined
<cfelse>
'Not a number: ' + userDefined
</cfif>
AS TheColumnAlias
FROM qDatabaseQuery2
ORDER by ID
</cfquery>
QoQ Результат
Что вы пытаетесь сделать именно? Пожалуйста, поделитесь некоторым контекстом цели для вашего поста.
Мне кажется, ваш запрос может быть отформатирован неправильно. Это будет означать что-то вроде:
select ( 0000000099
) as hello
from all_employees
order by hello ASC
Попробуйте сделать это. Положить <cfabort> прямо здесь... А потом дайте мне знать, какой запрос был произведен на экране, когда вы запустите его.
<cfquery name="qEmployees1" dbtype="query">
select (
case
when ISNUMERIC(u.userdefined)=1
then right('00000000'+u.userdefined,8)
else userdefined
end
) as hello
from all_employees
order by hello ASC
<cfabort>
</cfquery>
<cfquery name="qEmployees1" dbtype="query">
SELECT
(
<cfif isNumeric(all_employees.userdefined)>
right('00000000'+all_employees.userdefined,8)
<cfelse>
all_employees.userdefined
</cfif>
) AS hello
FROM all_employees
ORDER by hello
</cfquery>
это свободный от синтаксиса ответ благодаря @volumeone