Hello all, just an addition - actually there is a function called LEFT, but it is available only for ODBC and JDBC (as these clients require some functions, and some syntactic constructs to be available).
They are not listed in the documentation, as they are available only for ODBC and JDBC, and sometimes may be a little bit ambigous with the set of scalar functions generally available in SAP DB. If using ODBC, SQLGetInfo (see http://msdn.microsoft.com/library/en-us/odbc/htm/odch21gpr_7.asp?frame=true ) returns these functions, if using JDBC, the DatabaseMetaData methods getNumericFunctions, getStringFunctions, getSystemFunctions, and getTimeDateFunctions will return these functions (see http://java.sun.com/j2se/1.4.1/docs/api/java/sql/DatabaseMetaData.html#getNumericFunctions() ) Their semantic is defined by X/Open, and can be looked at at http://msdn.microsoft.com/library/en-us/odbc/htm/odappepr.asp?frame=true . However, always beware that these functions are only supported in JDBC and ODBC, and if they are not listed in the SAP DB documentation, they will probably not work when used from dbm, loader or C precompiler. For you, with the need to execute the statement from dbmcli or loadercli, the suggestion using SUBSTR is still the valid workaround. Sorry for any inconvenience caused, Alexander Schr�der SAP DB, SAP Labs Berlin > -----Original Message----- > From: Schroeder, Alexander > Sent: Tuesday, June 17, 2003 6:27 PM > To: 'Matthias Suttner'; [EMAIL PROTECTED] > Subject: RE: how can i execute long SQL-Statements via DBMCLI > > > Hello Matthias, > > the answer here is relatively easy - you didn't hit any length limit. > > There is no such thing like a string function called LEFT in SAP DB, > neither in internal nor Oracle mode. > > Try a SUBSTR(value, 1, 128) for your usage, and check > > http://www.sapdb.org/7.4/htmhelp/48/0d801eb4f211d2a97100a0c944 > 9261/content.htm > > for the functions supported with SAP DB. > > The error pos usually gives you a hint at which point in the statement > the complaint was found - in your case it was position 63, which is > around the first occurence of LEFT in your statement. > > Regards > Alexander Schr�der > SAP DB, SAP Labs Berlin > > > -----Original Message----- > > From: Matthias Suttner [mailto:[EMAIL PROTECTED] > > Sent: Tuesday, June 17, 2003 6:14 PM > > To: [EMAIL PROTECTED] > > Subject: Re: how can i execute long SQL-Statements via DBMCLI > > > > > > --- In [EMAIL PROTECTED], "Schildberg, Steffen" > > <[EMAIL PROTECTED]> wrote: > > > Hi Matthias, > > > > > > Matthias Suttner [mailto:[EMAIL PROTECTED] wrote: > > > > > > > > I'd like to execute SQL-Statements (INSERT, DELETE) once each > > day. > > > > > > > > So far no problem for DBMCLI if the statement is short enough. > > But I > > > > have to execute a few that have approx. 1300 characters. > > > > > > > > When I execute these with DBMCLI's sql_execute it > throws an error: > > > > > > > > ERR > > > > -24988,ERR_SQL: sql error > > > > -3008,Invalid keyword or missing delimiter > > > > > > > > > > > > Is there any other solution to execute big > SQL-Statements? I read > > > > about DBPROC. Is this an option? > > > > > > > It surely is. But how about the Loader? Despite the name > it is able > > > to execute SQL statements, too. And it has a CLI > (LoaderCLI), too ;- > > ) > > > Regards, > > > Steffen > > > -- > > > Steffen Schildberg > > > SAP DB Team > > > SAP Labs Berlin > > > _______________________________________________ > > > sapdb.general mailing list > > > [EMAIL PROTECTED] > > > http://listserv.sap.com/mailman/listinfo/sapdb.general > > > > > > Hi, > > > > I've tested REPMCLI and also a failure occurs. > > > > what's wrong on position: 63? The left statement? > > > > // * > > // E -25010: SQL error -3008 = Invalid keyword or missing > > delimiter (error position: 63) > > > > > > *** schnipp *** > > insert into "SAPUSER"."REPORT_FAMILY_PREVIEWS" > > ( > > select > > left(REPORTVALUES.SVAL02, 128) as "CAMPAIGN", > > left(REPORTVALUES.SVAL03, 128) as "FAMILY", > > left(REPORTVALUES.SVAL05, 128) as "PREVIEW" > > from > > REPORTEVALUATIONS, > > REPORTVALUES, > > REPORT_PARAMETERS, > > ( > > select > > left(REPORTVALUES.SVAL02, 128) as "CAMPAIGN", > > left(REPORTVALUES.SVAL03, 128) as "FAMILY", > > max > > ( > > right('00' & trim(REPORT_PARAMETERS.VALUE), 2) & > > right('00' & trim(REPORTVALUES.LVAL04) , 2) & > > right('00' & trim(REPORTVALUES.LVAL01) , 2) > > ) as "MAX_ART_COLUMNS_COLORS" > > from > > REPORTEVALUATIONS, > > REPORTVALUES, > > REPORT_PARAMETERS > > where > > REPORTEVALUATIONS.EVALID = REPORTVALUES.EVALID > > and > > REPORTEVALUATIONS.EVALNAME = 'schaltung' > and > > REPORT_PARAMETERS.FORM = 'ART' > and > > REPORT_PARAMETERS.NAME = REPORTVALUES.WVAL04 > > group by > > left(REPORTVALUES.SVAL02, 128), > > left(REPORTVALUES.SVAL03, 128) > > ) x > > where > > REPORTEVALUATIONS.EVALID = > > REPORTVALUES.EVALID and > > REPORTEVALUATIONS.EVALNAME > > = 'schaltung' and > > REPORT_PARAMETERS.FORM > > = 'ART' and > > REPORT_PARAMETERS.VALUE = substr > > (x."MAX_ART_COLUMNS_COLORS", 1, 2) and > > REPORTVALUES.WVAL04 = > > REPORT_PARAMETERS.NAME and > > left(REPORTVALUES.SVAL02, 128) = > > x."CAMPAIGN" and > > left(REPORTVALUES.SVAL03, 128) = > > x."FAMILY" and > > REPORTVALUES.LVAL04 = num(substr > > (x."MAX_ART_COLUMNS_COLORS", 3, 2)) and > > REPORTVALUES.LVAL01 = num(substr > > (x."MAX_ART_COLUMNS_COLORS", 5, 2)) > > group by > > left(REPORTVALUES.SVAL02, 128), > > left(REPORTVALUES.SVAL03, 128), > > left(REPORTVALUES.SVAL05, 128) > > ) > > > > *** schnapp *** > > > > > > > > > > > > _______________________________________________ > > 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 > _______________________________________________ sapdb.general mailing list [EMAIL PROTECTED] http://listserv.sap.com/mailman/listinfo/sapdb.general
