scalar type is not allowed;conversion possible?

[kgaurav2k14]

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

[pfefferf]

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

[kgaurav2k14]

Thanks Florian. The reason I am doing this is I want a date column in my output table which always shows today's date.

For Ex: If I am returning Sales Order No., Material, creation Date and Today's Date as 4 columns of my output of Table Function. I am not sure How would I do so as it contains date fields(scalar) as well? Also, the first three columns are coming from joining of SAP Tables whereas Todays date would be static value which will be coming as default. How can we combine columns in one table output?

Please suggest.

Regards,

Gaurav

[pfefferf]

Why not just selecting the result you wanna have from your join result + the additional "current date" column? You can do that in one select.

[kgaurav2k14]

Thanks Florian. That should have been the right approach. Infact, I just tried with single select query and it worked.

RETURN
SELECT VBELN , NETWR , ERDAT , CURDATE ( ) AS "CDATE" FROM VBAP;

However, the earlier suggestion which you gave seems to be not working. I changed the code according to what you had written.

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

The error is "Syntax error in table function object: scalar type is not allowed; RESULT"

Thanks,

Gaurav

[pfefferf]

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