Hello Jesus, SAP DB had the feature of using the syntax
SELECT <name>(<field_list>) FROM ... to explicitely name the result table (cursor) (same as you can do with the DECLARE CURSOR FOR ... statement or with special interfaces in the various programming languages). IIRC that syntax feature has been finally dropped with the upcoming MaxDB 7.6 release. You possibly want to use the VALUE function, which can be used to replace NULL values with replacement values. Regards Alexander Schr�der SAP DB, SAP Labs Berlin > -----Original Message----- > From: Jesus Barreto [mailto:[EMAIL PROTECTED] > Sent: Friday, October 29, 2004 3:12 PM > To: [EMAIL PROTECTED] > Subject: Re: AW: missing delimiter error with REPMCLI > > El Friday 29 October 2004 06:26, Zabach, Elke escribi�: > Thanks Elke, > your solution was fine, but about the function IFNULL it > does exist, because > if you execute a simple select in SQL Studio like this: > > SELECT IFNULL(null,'hello World') FROM DUAL > > the result is: > > 'hello World' > i've done many satisfactory tests with this function but when > using with > loadercli or repmcli i got problems. > > And there are a lot of things(function, command,......) that > actually doesn't > appear in manual. > > > Jesus Barreto wrote: > > > Hi, > > > I have a problem with REPMCLI y execute something(Create > a View, Create a > > > Function, Create a StroredProcedure) that it content the > function IFNULL > > > for > > > ej: > > > CREATE VIEW > > > "RV_C_INVOICELINE" > > > > ("AD_CLIENT_ID","AD_ORG_ID","C_INVOICELINE_ID","C_INVOICE_ID", > "SALESREP_I > > >D > > > > ","C_BPARTNER_ID","C_BP_GROUP_ID","M_PRODUCT_ID","M_PRODUCT_CA > TEGORY_ID", > > >" > > > > DATEINVOICED","DATEACCT","QTYINVOICED","PRICELIST","PRICEACTUA > L","PRICELI > > >M > > > > IT","DISCOUNT","MARGIN","LINENETAMT","LINELISTAMT","LINELIMITA > MT","LINEDI > > >S COUNTAMT","LINEOVERLIMITAMT") > > > AS > > > SELECT > > > il.AD_Client_ID, il.AD_Org_ID, > > > il.C_InvoiceLine_ID, i.C_Invoice_ID, i.SalesRep_ID, > > > i.C_BPartner_ID, i.C_BP_Group_ID, > > > il.M_Product_ID, p.M_Product_Category_ID, > > > i.DateInvoiced, i.DateAcct,-- Qty > > > il.QtyInvoiced*i.Multiplier AS QtyInvoiced,-- Item Amounts > > > il.PriceList, il.PriceActual, il.PriceLimit, > > > DECODE(PriceList, 0, 0, > > > ROUND((PriceList-PriceActual)/PriceList*100,2)) AS Discount, > > > DECODE(PriceLimit, 0, 0, > > > ROUND((PriceActual-PriceLimit)/PriceLimit*100,2)) AS Margin,-- > > > Line Amounts > > > ROUND(i.Multiplier*LineNetAmt, 2) AS LineNetAmt, > > > ROUND(i.Multiplier*PriceList*QtyInvoiced, 2) AS LineListAmt, > > > DECODE(IFNULL(il.PriceLimit, 0), 0, > > > ROUND(i.Multiplier*LineNetAmt,2), > > > * * *PROBLEM----^ > > > ROUND(i.Multiplier*PriceLimit*QtyInvoiced,2)) > AS LineLimitAmt, > > > ROUND(i.Multiplier*PriceList*QtyInvoiced-LineNetAmt,2) AS > > > LineDiscountAmt, > > > DECODE(IFNULL(il.PriceLimit,0), 0, 0, > > > > ROUND(i.Multiplier*LineNetAmt-PriceLimit*QtyInvoiced,2)) AS > > > LineOverLimitAmt > > > FROM RV_C_Invoice i, C_InvoiceLine il, M_Product p > > > WHERE i.C_Invoice_ID=il.C_Invoice_ID > > > AND il.M_Product_ID=p.M_Product_ID > > > > > > > > > And I get the next Error: > > > Missing Delimiter > > > > > > I detect that is this function, because if I omit the > function IFNULL, it > > > works fine > > > > If you will check the reference manual, chapter functions, > you will NOT > > find the function IFNULL. Therefore I am not astonished > that using such an > > unknown something will cause an error. Please use function > VALUE instead > > and you will succeed. > > > > Elke > > SAP Labs Berlin > > > > > Thanks, > > > Jesus Barreto > > > > > > -- > > > MaxDB Discussion Mailing List > > > For list archives: http://lists.mysql.com/maxdb > > > To unsubscribe: > http://lists.mysql.com/[EMAIL PROTECTED] > > -- > MaxDB Discussion Mailing List > For list archives: http://lists.mysql.com/maxdb > To unsubscribe: > http://lists.mysql.com/[EMAIL PROTECTED] > > -- MaxDB Discussion Mailing List For list archives: http://lists.mysql.com/maxdb To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
