Andreas Grund wrote : >Hi,
>i just read in the SAP DB Library, that i have to write complete tablenames >(inclusive ownername) in my sql-statements. So i wrote the >following dbproc: >create dbproc monatsname2 (out name varchar(20)) as >var monat varchar(20); >try >select Month(Tag) as monat from ROOT.1_4_Calls_KNL where Calls=673; >fetch into :monat >case >when monat = 1 then set name = 'Januar'; >when monat = 2 then set name = 'Februar'; >when monat = 3 then set name = 'M�rz'; >when monat = 4 then set name = 'April'; >when monat = 5 then set name = 'Mai'; >when monat = 6 then set name = 'Juni'; >when monat = 7 then set name = 'Juli'; >when monat = 8 then set name = 'August'; >when monat = 9 then set name = 'September'; >when monat = 10 then set name = 'Oktober'; >when monat = 11 then set name = 'November'; >when monat = 12 then set name = 'Dezember'; >else stop (-29000, 'keine g�ltige Monatsziffer'); >end case; >catch >if $rc <> 100 then stop ($rc, 'unerwarteter Fehler'); >I want to get the number of the month in a specified tablerow. But when i execute >this sql-statement, i get the errormessage Owner must be >specified. But i think, that i have done it. Because i wrote ROOT.1_4_Calls_KNL. ROOT >is my SYSDBA and the SQL-Studio shows, that he is the >owner of the table >1_4_Calls_KNL. I work with SAP DB 7,4,3,25, Win NT 4 SP 6a. For testing my dbprocs i >ues the dbvisualizer 3.3.1. >So, where is the failure?? Your tablename 1_4_Calls_KNL is a special identifier, which must be enclosed by double quotes : select Month(Tag) as monat from ROOT."1_4_Calls_KNL" where Calls=673; Please note : a semicolon is missing after the fetch statement. >I have another question to the out-parameter of a dbproc: >My first idea was, to write a dbproc like: >CREATE DBPROC MONATSNAME (IN MONATSNUMMER INTEGER, OUT MONAT VARCHAR(20)) AS >CASE >WHEN MONATSNUMMER = 1 THEN SET MONAT = 'Januar'; >WHEN MONATSNUMMER = 2 THEN SET MONAT = 'Februar'; >WHEN MONATSNUMMER = 3 THEN SET MONAT = 'M�rz'; >WHEN MONATSNUMMER = 4 THEN SET MONAT = 'April'; >WHEN MONATSNUMMER = 5 THEN SET MONAT = 'Mai'; >WHEN MONATSNUMMER = 6 THEN SET MONAT = 'Juni'; >WHEN MONATSNUMMER = 7 THEN SET MONAT = 'Juli'; >WHEN MONATSNUMMER = 8 THEN SET MONAT = 'August'; >WHEN MONATSNUMMER = 9 THEN SET MONAT = 'September'; >WHEN MONATSNUMMER = 10 THEN SET MONAT = 'Oktober'; >WHEN MONATSNUMMER = 11 THEN SET MONAT = 'November'; >WHEN MONATSNUMMER = 12 THEN SET MONAT = 'Dezember'; >ELSE STOP (-29000, 'keine g�ltige Monatsziffer'); >END CASE; >This was the try to get a german monthname identified by the number of the month. But >when i try to call this dbproc with call MONATSNAME(4) >i get the error too few >parameters. So i think, that the database expected in value for the out parameter, >but with value should i send to >the database?? i tried call MONATSNAME(4, '') and call MONATSNAME(4, NULL) and call >MONATSNAME(4, 'April') always without any success. Where >is in this case my failure?? You have to provide 2 parameters for the call. Since the second parameter in an out-parameter, this must be a parameter, i.e. call MONATSNAME(4, ?) I fear that db-visualizer can't deal with parameters. If possible use SQLStudio, which is able to call and display db-procedures having out-parameters. Thomas -- Thomas Anhaus SAP DB, SAP Labs Berlin [EMAIL PROTECTED] http://www.sapdb.org/ _______________________________________________ sapdb.general mailing list [EMAIL PROTECTED] http://listserv.sap.com/mailman/listinfo/sapdb.general _______________________________________________ sapdb.general mailing list [EMAIL PROTECTED] http://listserv.sap.com/mailman/listinfo/sapdb.general
