无法使用用户定义的函数执行sql查询 [英] Unable to execute sql query with user defined function

问题描述

我写了一个SQL查询，它将获取名称和大小的重复项：

  SELECT t1.Filepath，
 t1.splitFilePath1（Filepath）
 FROM Filemanager t1 
 INNER JOIN（SELECT splitFilePath1（Filepath），
 Size 
 FROM Filemanager 
 BY splicePath1（Filepath），
 Size 
 HAVING COUNT（*）> 1）t2 
 ON t1.Size = t2.Size AND 
 t1.splitFilePath1 .splitFilePath1（Filepath）
  

在上面的查询中，splitFilePath1（Filepath）文件路径作为输入并返回文件名。接收到文件名后，我必须找到文件名和大小的重复项。

收到错误：
Near'（'：语法错误

我无法理解它预期的 b
$ b

编辑：解决！
查询：
SELECT Filepath，splitFilePath1（Filepath）FROM Filemanager t1 INNER JOIN（SELECT Filepath as Filepath1，splitFilePath1（Filepath），Size FROM Filemanager GROUP BY splitFilePath1（Filepath），Size HAVING COUNT 1）t2 ON t1.Size = t2.Size AND splitFilePath1（t1.Filepath）= splitFilePath1（t2.Filepath1）

感谢lollat​​o !!

解决方案

将 t1.splitFilePath1（Filepath）更改为 splitFilePath1 .Filepath），对于其他事件也是如此。 t1.splitFilePath 会引用 t1 中的一列，并具有（）括号后面的语法错误。

I have written a SQL query which would fetch duplicates of name and size:

SELECT t1.Filepath,
       t1.splitFilePath1(Filepath)
FROM Filemanager t1
INNER JOIN (SELECT splitFilePath1(Filepath),
                   Size
            FROM Filemanager
            GROUP BY splitFilePath1(Filepath),
                     Size
            HAVING COUNT(*) > 1) t2
ON t1.Size = t2.Size AND
   t1.splitFilePath1(Filepath) = t2.splitFilePath1(Filepath)

In the above query,splitFilePath1(Filepath) is a user defined function which takes Filepath as the input and returns filename.After receiving filename,I have to find duplicates of filename and size.

Error received: Near'(' :Syntax error

I am unable to understand where exactly it expects the '('.

Edit:Solved!! Query: SELECT Filepath,splitFilePath1(Filepath) FROM Filemanager t1 INNER JOIN (SELECT Filepath as Filepath1,splitFilePath1(Filepath),Size FROM Filemanager GROUP BY splitFilePath1(Filepath), Size HAVING COUNT(*) > 1) t2 ON t1.Size = t2.Size AND splitFilePath1(t1.Filepath) = splitFilePath1(t2.Filepath1)

Thanks to lollato!!

解决方案

Change t1.splitFilePath1(Filepath) to splitFilePath1(t1.Filepath) and similarly for the other occurences. t1.splitFilePath would refer to a column in t1 and having () parens after it is a syntax error.

这篇关于无法使用用户定义的函数执行sql查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！