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

• deba_20032004

  Default port

  Points: 1476

  More actions

  August 10, 2011 at 7:05 am

  #255489

  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: 148809

  More actions

  August 10, 2011 at 7:11 am

  #1366526

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

  John

• kramaswamy

  SSCoach

  Points: 18181

  More actions

  August 11, 2011 at 8:55 am

  #1367356

  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.