Hello Matthias, The is a way that may look syntactically a little bit ugly: RIGHT(x, n) is same as SUBSTR(x, LENGTH(x) - n - 1)
Cheers Alexander Schr�der SAP DB, SAP Labs Berlin > -----Original Message----- > From: Matthias Suttner [mailto:[EMAIL PROTECTED] > Sent: Wednesday, June 18, 2003 9:58 AM > To: [EMAIL PROTECTED] > Subject: Re: how can i execute long SQL-Statements via DBMCLI > > > thanks Alexander, > > but one thing i've got forgotten to ask: > How can i solve RIGHT(s,i)? This function is also in my SQL-Statement. > > Greetings > Matthias > > --- In [EMAIL PROTECTED], "Schroeder, Alexander" > <[EMAIL PROTECTED]> wrote: > > Hello Matthias, > > > > SQL Studio uses ODBC. > > > > Regards > > Alexander Schr�der > > SAP DB, SAP Labs Berlin > > > > > -----Original Message----- > > > From: Matthias Suttner [mailto:[EMAIL PROTECTED] > > > Sent: Wednesday, June 18, 2003 9:36 AM > > > To: [EMAIL PROTECTED] > > > Subject: Re: how can i execute long SQL-Statements via DBMCLI > > > > > > > > > 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 > > > > > _______________________________________________ > > 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
