Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.

  • deba_20032004

    Default port

    Points: 1476

    Sir,

    I have written a function but giving above error how to solve the problem please show me.

    [Code]

    CREATE FUNCTION dbo.uf_GetClg_Rate

    (@Co As Varchar(02),

    @Itemcd As Varchar(10),

    @Date As Datetime)

    RETURNS NUMERIC(14,4)

    AS

    BEGIN

    --This Query Getting closing rate of last date within given period

    --!

    DECLARE @Retval AS NUMERIC(14,4)

    SET @Retval=(SELECT CO,[DATE],CLNG_RATE FROM RATES WHERE ITEMCD=@Itemcd AND

    [DATE]=(SELECT MAX([DATE]) FROM RATES WHERE [DATE]<=@Date AND CLNG_RATE<>0))

    --!

    Return (@Retval)

    END

    [/code]

  • John Mitchell-245523

    SSC Guru

    Points: 148519

    What do you want to set @Retval to - CO, [DATE] or CLNG_RATE?

    John

  • kramaswamy

    SSCoach

    Points: 18135

    what you probably want is all three i'm guessing. so you could do something like this:

    CREATE FUNCTION dbo.uf_GetClg_Rate

    (@Co As Varchar(02),

    @Itemcd As Varchar(10),

    @Date As Datetime)

    RETURNS NUMERIC(14,4)

    AS

    BEGIN

    --This Query Getting closing rate of last date within given period

    --!

    DECLARE @Retval AS NUMERIC(14,4)

    SET @Retval=(SELECT 'CO: ' + CO + ' - DATE: ' + [DATE] + ' - CLNG_RATE' + CLNG_RATE FROM RATES WHERE ITEMCD=@Itemcd AND

    [DATE]=(SELECT MAX([DATE]) FROM RATES WHERE [DATE]<=@Date AND CLNG_RATE<>0))

    --!

    Return (@Retval)

    END

    If you actually want to return all three, then what you want to do is use a table-valued function.

Viewing 3 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply