Skip to Content

scalar type is not allowed;conversion possible?

Hi Friends,

I am trying to return today's date using table function. As I understand, Date data type is scalar in nature however I am trying to convert it into character but it is not allowing.

What is the issue? Is this conversion possible? I am getting error

"Could not create catalog object: scalar type is not allowed; line 14 col 9 (at pos 367)"

FUNCTION "BIUSER"."GK1.Func_Proc::TF1" ( ) 
	RETURNS TABLE (DT1 VARCHAR(20))
	LANGUAGE SQLSCRIPT
	SQL SECURITY INVOKER AS
	DT1 VARCHAR(20);
BEGIN
/***************************** 
	Write your function logic
 *****************************/
 DECLARE DT DATE;
 
 SELECT CURDATE() INTO DT FROM DUMMY;
 SELECT TO_CHAR(:DT, 'DD-MON-YYYY') INTO DT1 FROM DUMMY;
 RETURN :DT1;
END;

Please suggest.

Thanks,

Gaurav

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

1 Answer

  • Sep 12, 2017 at 03:35 PM

    Not sure why you need an own function for the determination of the character date value (maybe that you only have to do the formatting logic at one place?), but the issue is that "DT1" is a scalar value and you try to return it as a table. That is not possible.

    As I assume, that you do not need a table function I would recommend to change your function to a scalar function.

    Otherwise if you really need a table result with only one line (for whatever reason) you have to replace the lines

    SELECT TO_CHAR(:DT, 'DD-MON-YYYY') INTO DT1 FROM DUMMY;
    RETURN :DT1;

    by something like this

    result = SELECT TO_CHAR(:DT, 'DD-MON-YYYY') as "DT1" FROM DUMMY;
    RETURN :result;

    Regards,
    Florian

    Add comment
    10|10000 characters needed characters exceeded

    • It would work if you wouldn't have declared "result" as scalar variable. It is not necessary to declare it. Another point is that you have changed the column name in your returing type to "result". This does not match the column name of the selection.

      It works in following form:

      FUNCTION "BIUSER"."GK1.Func_Proc::TF1" ( ) 
      	RETURNS table ("DT1" VARCHAR(20))
      	LANGUAGE SQLSCRIPT
      	SQL SECURITY INVOKER AS
      BEGIN
       result = SELECT TO_CHAR(CURDATE(), 'DD-MON-YYYY') as "DT1" FROM DUMMY;
       RETURN :result;
      end;

      Or one line shorter:

      FUNCTION "BIUSER"."GK1.Func_Proc::TF1" ( ) 
      	RETURNS table ("DT1" VARCHAR(20))
      	LANGUAGE SQLSCRIPT
      	SQL SECURITY INVOKER AS
      BEGIN
       return SELECT TO_CHAR(CURDATE(), 'DD-MON-YYYY') as "DT1" FROM DUMMY;
      end;

      Regards,
      Florian