[EMAIL PROTECTED] wrote: > > > Thanks Elke. > Value seems to do the same thing as NVL,Coalesce or ISNULL > however it still > doesnt return a default result if the table is empty in the > same way the oracle > sql server or DB2 do. > > Basically I would expect the result of > select 'MyCol1Value' "MyColumnNameForCol1", value(Col2,0) > "MyColumnNameForCol2" from mytable > > to be: > > MyColumnNameForCol1 MyColumnNameForCol2 > -------------------------------------------------------------- > ------------------- > MyCol1Value 0 > > > As opposed to: "Statement succesfuly executed. No result" >
You seem to have a different Oracle than I have. When I do a select 'xxx' myname, nvl(known_column, 0) from known_but_empty_table NO result is shown. When I do select 'xxx' myname, nvl(MAX(known_column), 0) from known_but_empty_table THEN xxx and 0 are the results in the one and only resultrow. And it is the same, no matter if Oracle or MaxDB. I think with your last test you just missed the MAX/SUM/COUNT. Elke SAP Labs Berlin > > > |--------+-----------------------> > | | "Zabach, | > | | Elke" | > | | <elke.zabach@| > | | sap.com> | > | | | > | | 21/10/2003 | > | | 15:04 | > | | | > |--------+-----------------------> > > >------------------------------------------------------------- > ---------------| > | > | > | To: Mark Blakey/London/[EMAIL PROTECTED], > [EMAIL PROTECTED] | > | cc: > | > | Subject: RE: Problem with decoding null values > and returning | > | result from a n empty table > | > > >------------------------------------------------------------- > ---------------| > > > > > [EMAIL PROTECTED] wrote: > > > > > In oracle, sqlserver and DB2 I have a view that will return a > > value from a table > > even if the table is empty. I use this within a union to > > obtain the max value > > from a column for a number of tables. > > > > In DB2 UDB this looks like: > > > > SELECT 'ACCOUNTVIEWCOUNTER' "ColumnName" , > > COALESCE(MAX(accountViewCounter)+1, 0) "CurrentValue" > > FROM accountView > > UNION ALL > > SELECT 'ANALYSISGROUPCOUNTER' "ColumnName" , > > COALESCE(MAX(analysisGroupCounter)+1, 0) "CurrentValue" > > FROM analysisGroup > > UNION ALL > > """ > > In MaxDB use function VALUE > http://www.sapdb.org/7.4/htmhelp/cf/633171c03511d2a97100a0c944 > 9261/frameset.htm > > Elke > SAP Labs Berlin > > > > In Oracle this looks like: > > > > > > SELECT 'ACCOUNTVIEWCOUNTER' ColumnName , > > NVL(MAX(accountViewCounter)+1, > > 0) CurrentValue > > FROM accountView > > UNION ALL > > SELECT 'ANALYSISGROUPCOUNTER' ColumnName , > > NVL(MAX(analysisGroupCounter)+1, 0) CurrentValue > > FROM analysisGroup > > UNION ALL > > > > > > In MS Sql Server this looks like: > > > > SELECT 'ACCOUNTVIEWCOUNTER' "ColumnName" , > > ISNULL(MAX(accountViewCounter)+1, 0) "CurrentValue" > > FROM accountView > > UNION ALL > > SELECT 'ANALYSISGROUPCOUNTER' "ColumnName" , > > ISNULL(MAX(analysisGroupCounter)+1, 0) "CurrentValue" > > FROM analysisGroup > > UNION ALL > > > > For a set of empty tables the output will then look like: > > > > COLUMNNAME CURRENTVALUE > > -------------------------------------------------------------- > > ------------------------ > > ACCOUNTVIEWCOUNTER 0 > > ANALYSISGROUPCOUNTER 0 > > > > > > However when I try to do a similar thing in SAPDB I get no > > result. To replicate > > this I tried the following: > > > > create table mytable (col1 varchar(50), col2 int) > > > > select 'MyCol1Value' "MyColumnNameForCol1", decode(Col2, > > NULL,0) from mytable > > > > Is this a bug or is there a way to force SAPDB to return some > > result when the > > table is empty? > > > > > > > > > > > > > > ************************************************************** > > ************************************************************** > > ************************** > > This e-mail has been prepared using information believed by > > the author to be > > reliable and accurate, but Thales Information Systems Finance > > makes no > > warranty as to accuracy or completeness. In particular > > Thales Information > > Systems Finance does not accept responsibility for > > changes made to this > > e-mail after it was sent. Any opinions expressed in this > > document are those of > > the author and do not necessarily reflect the opinions of > > the company or its > > affiliates. They may be subject to change without notice. > > This e-mail, its content and any files transmitted with > > it are intended > > solely for the addressee(s) and may be legally > > privileged and/or > > confidential. Access by any other party is unauthorised > > without the express > > written permission of the sender. If you have received this > > e-mail in error you > > may not copy or use the contents, attachments or > > information in any way. > > Please destroy it and contact the sender via the Thales > > Information Systems > > Finance switchboard in London at +44 (0) 20 7650 0100 or via > > e-mail return. > > This message and any attachments have been scanned > > for viruses prior to leaving the originators network. The > > originator does not > > guarantee the security of this message and will not be > > responsible for any > > damages arising from any alteration of this message by a > > third party or as a > > result of any virus being passed on. > > ************************************************************** > > ************************************************************** > > *************************** > > > > > > > > -- > > 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]
