0

I'm trying to create function in SQL Server 2008 with this query

CREATE FUNCTION [dbo].[fJatah]
                    (@kodewil CHAR (30), @tgl CHAR (19))
                    RETURNS INT
                    AS
                    BEGIN
                    RETURN
                    (
                        SELECT a.AFDL, sum(Libur) as Libur, sum(Aktif) as Aktif
                        FROM (SELECT TOP (100) PERCENT a.AFDL, a.kd_wil, ISNULL(MAX(b.libur), 0) AS Libur, ISNULL(MAX(b.aktif), 0) AS Aktif
                        FROM dbo.vM_KEL AS a LEFT OUTER JOIN
                                                                    dbo.TblJatahSPTA AS b ON a.KD_KT = b.kd_kt LEFT OUTER JOIN
                                                                    dbo.tblSPA AS d ON a.KD_KT = LEFT(d.Kdptn, 5) AND d.tglberlaku = @tgl
                        WHERE a.AFDL = @kodewil
                        GROUP BY a.AFDL,a.KD_KT, a.kd_wil
                        ORDER BY a.AFDL) as a
                        GROUP BY a.AFDL
                    )
                    END

But i got error like this

[Err] 42000 - [SQL Server]Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.

I have no idea how to fix that, how can i fix my query?

1
  • So is it SQLServer or MySQL? Commented Jun 25, 2017 at 17:50

1 Answer 1

2

Your function is declared with RETURNS INT. That means, that you have to select exactly one single value (=scalar value) to return. In your case your SELECT specifies multiple return columns (a.AFDL, Libur and Aktif).

One thing you should know: Scalar functions (called UDF) are very bad performing. Much better in this case was an inline table valued function (called iTVF). Try it like this (not knowing what you are really doing here...):

CREATE FUNCTION [dbo].[fJatah]
                (@kodewil CHAR (30), @tgl CHAR (19))
                RETURNS TABLE
                AS
                RETURN
                    SELECT a.AFDL, sum(Libur) as Libur, sum(Aktif) as Aktif
                    FROM (SELECT TOP (100) PERCENT a.AFDL, a.kd_wil, ISNULL(MAX(b.libur), 0) AS Libur, ISNULL(MAX(b.aktif), 0) AS Aktif
                    FROM dbo.vM_KEL AS a LEFT OUTER JOIN
                                                                dbo.TblJatahSPTA AS b ON a.KD_KT = b.kd_kt LEFT OUTER JOIN
                                                                dbo.tblSPA AS d ON a.KD_KT = LEFT(d.Kdptn, 5) AND d.tglberlaku = @tgl
                    WHERE a.AFDL = @kodewil
                    GROUP BY a.AFDL,a.KD_KT, a.kd_wil
                    ORDER BY a.AFDL) as a
                    GROUP BY a.AFDL;
GO

SELECT * FROM [dbo].[fJatah]('SomeKodewil', 'SomeTgl')
GO

You can bind this iTVF into your commands with CROSS APPLY or use it where ever you might use a table (FROM, JOIN ...)

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

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.