Hi Alexander, and why does this SQL-Statement works in the SQL-Studio? Isn't it a SAP product, too? ;-)
Matthias --- In [EMAIL PROTECTED], "Schroeder, Alexander" <[EMAIL PROTECTED]> wrote: > 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 _______________________________________________ sapdb.general mailing list [EMAIL PROTECTED] http://listserv.sap.com/mailman/listinfo/sapdb.general
