Unable to execute sql query with user defined function

Question

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!!

laalto · Accepted Answer · 2014-03-19 14:05:58Z

1

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.

answered Mar 19, 2014 at 14:05

laalto

153k68 gold badges293 silver badges306 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

user3264565 Over a year ago

:Now it is giving me no such column t1.splitFilePath1.Below is my edited query:SELECT Filepath,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 = t2.splitFilePath1

user3264565 Over a year ago

Now it gave me error : No such column t2.Filepath :( .This is my query "SELECT Filepath,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 splitFilePath1(t1.Filepath) = splitFilePath1(t2.Filepath)"

user3264565 Over a year ago

It got solved.I have added an alias in the inner select query to the filepath and then used the alias.

Collectives™ on Stack Overflow

Unable to execute sql query with user defined function

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related