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" |--------+-----------------------> | | "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/633171c03511d2a97100a0c9449261/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]
