Subject | UDF in ORDER BY clause |
---|---|
Author | Roberto Freitas |
Post date | 2005-12-05T23:03:58Z |
I'm using FireBird 1.5 Version WI-V1.5.2.4731
F_CHAVESORT is a UDF
This sentence works:
SELECT C.CCT_ORDEM, C.CCT_CONTA, C.CCT_MAE,
F_CHAVESORT(C.CCT_ORDEM, C.CCT_CONTA, COALESCE(T.CCT_CONTA, 0),
C.CCT_TITULO) AS EXEMPLO
FROM CONTA C LEFT JOIN CONTA T ON C.CCT_MAE = T.CCT_IDENT
ORDER BY C.CCT_CONTA
but if I change ORDER BY clause it doesn't work:
SELECT C.CCT_ORDEM, C.CCT_CONTA, C.CCT_MAE
FROM CONTA C LEFT JOIN CONTA T ON C.CCT_MAE = T.CCT_IDENT
ORDER BY F_CHAVESORT(C.CCT_ORDEM, C.CCT_CONTA, COALESCE(T.CCT_CONTA,
0), C.CCT_TITULO)
I get this error:
SQL error code = -104
Token unknown - line 3, char 34
I known it's possible to use UDF in ORDER BY clauses, because this
sentence works:
SELECT C.CCT_ORDEM, C.CCT_CONTA, C.CCT_MAE
FROM CONTA C LEFT JOIN CONTA T ON C.CCT_MAE = T.CCT_IDENT
ORDER BY ABS(C.CCT_IDENT)
but it doesn't work with this one:
SELECT C.CCT_ORDEM, C.CCT_CONTA, C.CCT_MAE
FROM CONTA C LEFT JOIN CONTA T ON C.CCT_MAE = T.CCT_IDENT
ORDER BY SUBSTR(C.CCT_CONTA, 1, 5)
Token unknown - line 3, char 29
Aparently, if UDF has more than one parameter, we get token unknown
in the first character after the first ,
Am I doing something wrong?
F_CHAVESORT is a UDF
This sentence works:
SELECT C.CCT_ORDEM, C.CCT_CONTA, C.CCT_MAE,
F_CHAVESORT(C.CCT_ORDEM, C.CCT_CONTA, COALESCE(T.CCT_CONTA, 0),
C.CCT_TITULO) AS EXEMPLO
FROM CONTA C LEFT JOIN CONTA T ON C.CCT_MAE = T.CCT_IDENT
ORDER BY C.CCT_CONTA
but if I change ORDER BY clause it doesn't work:
SELECT C.CCT_ORDEM, C.CCT_CONTA, C.CCT_MAE
FROM CONTA C LEFT JOIN CONTA T ON C.CCT_MAE = T.CCT_IDENT
ORDER BY F_CHAVESORT(C.CCT_ORDEM, C.CCT_CONTA, COALESCE(T.CCT_CONTA,
0), C.CCT_TITULO)
I get this error:
SQL error code = -104
Token unknown - line 3, char 34
I known it's possible to use UDF in ORDER BY clauses, because this
sentence works:
SELECT C.CCT_ORDEM, C.CCT_CONTA, C.CCT_MAE
FROM CONTA C LEFT JOIN CONTA T ON C.CCT_MAE = T.CCT_IDENT
ORDER BY ABS(C.CCT_IDENT)
but it doesn't work with this one:
SELECT C.CCT_ORDEM, C.CCT_CONTA, C.CCT_MAE
FROM CONTA C LEFT JOIN CONTA T ON C.CCT_MAE = T.CCT_IDENT
ORDER BY SUBSTR(C.CCT_CONTA, 1, 5)
Token unknown - line 3, char 29
Aparently, if UDF has more than one parameter, we get token unknown
in the first character after the first ,
Am I doing something wrong?