Andreas Grund wrote:
>
> Hi,
>
> I wrote the following dbproc:
> create dbproc Tagesansicht (in monat smallint, in hotline
> smallint, in spaltea varchar(30) ) returns cursor as
> $cursor = 'cs_Tagesansicht';
> declare :$cursor CURSOR FOR
> SELECT Left("Tag",10) AS "Datum", :spaltea , "Belegung",
> YEAR("Tag") AS "kj" FROM "ROOT"."1_4_Calls_KNL" where
> MONTH("Tag") = :monat AND "Hotline_ID" = :hotline ORDER BY "Tag";
>
> As you can see, spaltea is an paramter iwth the name of a
> column needed for the sql-query. The table "1_4_Calls_KNL" is
> declared so:
>
> CREATE TABLE "ROOT"."1_4_Calls_KNL"
> (
> "Tag" Timestamp,
> "KC_ID" Integer,
> "Hotline_ID" Integer,
> "Belegung" Float (16),
> "Calls" Float (16),
> "Calls_E20" Float (16),
> "Fore" Float (16)
> )
>
> If I start the dbproc with CALL Tagesansicht (4, 1,
> "Calls_E20"); in the dbvisualizer i�ve get the error Constant
> must be compatible with column type and length. So there must
> be a failure in the dbproc. But how can I change the column
> of an sql-query in an dbproc??
>
> Thanks and greetings,
> Andreas
As Thomas wrote in May 2003:
A table name must be no parameter. You therefore have to use dynamic sql inside your
db-procedure.
Please try the following :
CREATE DBPROC scripts_that_print (IN tabStr VARCHAR(32), IN whereStr VARCHAR(100))
RETURNS CURSOR AS
VAR
stmt char(1000);
$CURSOR = 'MY_CURSOR';
stmt = 'SELECT MYCURSOR(*) FROM ' || tabstr || ' WHERE ' || wherestr;
execute stmt;
if ($rc <> 0) AND ($rc <> 100) THEN STOP ($rc);
please have a look to the input parameters of your db-procedure. I changed VARCHAR
to VARCHAR(32) and VARCHAR(100) respectively. SAPDB must know the size of
the parameters at compile time, VARCHAR has the meaning of VARCHAR(1).
Best Regards,
Thomas
This is not only true for table_name, but for column_name, view_name, ..._name as well.
Elke
SAP Labs Berlin
_______________________________________________
sapdb.general mailing list
[EMAIL PROTECTED]
http://listserv.sap.com/mailman/listinfo/sapdb.general
