 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussion Groups Database Servers DB2InformixIngresMS SQLOraclePervasive.SQLPostgreSQLProgressSybaseDesktop Databases FileMakerFoxProMS AccessParadoxGeneral General DB TopicsDatabase TheoryRelated Topics Java Development.NET DevelopmentVB DevelopmentMore Topics ... |
Database Forum / Oracle / Oracle Server / August 2007Oracle NULL vs '' revisited
Ok, I'm developing another oracle application where the distinction between NULL and '' will be important. Given Oracle doesn't adhere to SQL standards for the distinction, is there any best practices for comparing, and storing blank strings in a table where the field is conceptually NOT NULL. Thank you.  SignaturePinging self [127.0.0.1] with 32 bites of banana cake: Ping statistics for 127.0.0.1: Slices: Sent = 4, Received = 0, Lost = 4 (100% loss), > Ok, I'm developing another oracle application where the distinction > between NULL and '' will be important. [quoted text clipped - 10 lines] > Ping statistics for 127.0.0.1: > Slices: Sent = 4, Received = 0, Lost = 4 (100% loss), I have always had difficulty with the concept that an empty string should not be considered a NULL value to begin with. What does an empty string hold? Oh wait, there is a binary zero used to indicate that the last user data value preceeds the binary zero. And if you compare two binary zeroes you get an equality so two empty strings are considered equal, but logically if the value is unknown there is no way determine if A = b, A < B, or A > B. The string construct was a way for the developers of C to deal with the fact the processor they were using did not have instructions that delt with character data very well. Now we are apparently stuck with it. For the most part when dealing with character data that could be unknown I have not found NULLs to be a problem even when the null had to be transferred into a string variable in a program. The database behaves by one set of rules and the program by another. If you need to treat nulls in a specific column as equal (emtpy strings) then you can use the NVL function in your where clause conditions, the is null and is not null conditions, and/or an additional attribute column to indicate empty where the column could also be NULL to handle the situation. IMHO -- Mark D Powell -- > I have always had difficulty with the concept that an empty string > should not be considered a NULL value to begin with. What does an > empty string hold? Take an ex-girlfriend of mine, she had a child in the States and *_specifically_* didn't give her (the child) a middle name. So, Middle_Initial is blank '' - and not NULL, since it is a known quantity - as Donald Rumsfeld might say, a "known unknown". NULLs are unknown unknowns. Despite Mr. Rumsfeld's verbal gymnastics, there is no such thing as an unknown known. You concatenate blank with a string, and you simply get the string back - do the same with NULL and you get NULL. I'd say NULLs are readily distinguishable from blank strings. Paul... > IMHO -- Mark D Powell -- On Aug 22, 11:22 am, "Paul Linehan" <plinehan__A@T__yahoo__D.OT__COM> wrote: > NULLs are unknown unknowns. Despite Mr. Rumsfeld's verbal > gymnastics, there is no such thing as an unknown known. Well, how 'bout an encrypted value? And what if you encrypted NULL? jg -- @home.com is bogus. You can screw up anything you want at Murphy's Law restaurant http://www.signonsandiego.com/news/metro/20070822-9999-1n22otay.html > On Aug 22, 11:22 am, "Paul Linehan" <plinehan__A@T__yahoo__D.OT__COM> > wrote: [quoted text clipped - 10 lines] > at Murphy's Law restaurant > http://www.signonsandiego.com/news/metro/20070822-9999-1n22otay.html Encrypting is not the problem, decripting is..... Shakespeare On Aug 22, 7:22 pm, "Paul Linehan" <plinehan__A@T__yahoo__D.OT__COM> wrote: > > I have always had difficulty with the concept that an empty string > > should not be considered a NULL value to begin with. What does an [quoted text clipped - 15 lines] > > Paul... Yes, everybody uses the middle name example (although I'm missing the relevance of the country of birth). True, it isn't an unknown value, but I'm still not convinced the length is 0, that the number of names is 3, or indeed that the US-born lovechild's middle name is legally ''. Perhaps if I worked more with SQL Server I would see things differently. Are there any other examples? Oracle treats nulls as empty strings when concatenating, often leading to cries of inconsistency in this sort of debate, but it is the overwhelmingly more useful behaviour. > On Aug 22, 7:22 pm, "Paul Linehan" <plinehan__A@T__yahoo__D.OT__COM> > wrote: [quoted text clipped - 22 lines] > to cries of inconsistency in this sort of debate, but it is the > overwhelmingly more useful behaviour. Yeah. And Length('') == NULL which is bloody awful. Imho, Oracle just messed up on varchar2 and NULL. (from a practical, not a philosophical point of view) br, Martin >> On Aug 22, 7:22 pm, "Paul Linehan" <plinehan__A@T__yahoo__D.OT__COM> >> wrote: [quoted text clipped - 29 lines] > br, > Martin Much ado about nothing (or null or '' or ""). Even worse: in Dutch 0 (zero) is called "nul" and pronounced (almost) like null..... Shakespeare >>> On Aug 22, 7:22 pm, "Paul Linehan" <plinehan__A@T__yahoo__D.OT__COM> >>> wrote: [quoted text clipped - 35 lines] > > Shakespeare And in German it's exactly the same word: zero(en) = Null(de) It really gets ugly when you try to discuss this face to face as it's a constant confusion as to what you're talking about :-) > > On Aug 22, 7:22 pm, "Paul Linehan" <plinehan__A@T__yahoo__D.OT__COM> > > wrote: [quoted text clipped - 29 lines] > br, > Martin Well as theses debates always show it can be debated endlessly, but surely a length of 0 is incorrect for an unknown value; and even in the case of specifically-no-middle-name guy, if we are not including it in a count (how many names does he have?) surely it would be inconsistent to give it a length (what's the average length of his names?) >>> On Aug 22, 7:22 pm, "Paul Linehan" <plinehan__A@T__yahoo__D.OT__COM> >>> wrote: [quoted text clipped - 23 lines] > > Well as theses debates always show it can be debated endlessly, but Well yes. It's what we are doing atm, isn't it? :-) > surely a length of 0 is incorrect for an unknown value; and even in Which is exactly the problem with Oracle. LENGTH(NULL) should be NULL but LENGTH('') be better off being 0 (for all string processing puroses I can think of atm) which is o.c. not possible in Oracle. > the case of specifically-no-middle-name guy, if we are not including > it in a count (how many names does he have?) surely it would be > inconsistent to give it a length (what's the average length of his > names?) I must say I really don't get the middle-name example, sorry. It's a strange analogy gone rampant, if you ask me. :-P cheers, Martin > >>> On Aug 22, 7:22 pm, "Paul Linehan" <plinehan__A@T__yahoo__D.OT__COM> > >>> wrote: [quoted text clipped - 42 lines] > cheers, > Martin I'm still struggling to find an example of a non-null empty string, especially if we're agreed that no-middle-name guy has two names and not three. If we had one of those then it might be easier to agree that its length should be 0. >>>>> On Aug 22, 7:22 pm, "Paul Linehan" <plinehan__A@T__yahoo__D.OT__COM> >>>>> wrote: [quoted text clipped - 42 lines] > not three. If we had one of those then it might be easier to agree > that its length should be 0. The (for me) intuitive way: --------------------------- SELECT * from MY_TABLE WHERE LENGTH(VARCHAR_COLUMN) = LENGTH(:p_search_equal_length_string); The Oracle way: --------------- SELECT * from MY_TABLE WHERE LENGTH(VARCHAR_COLUMN) = LENGTH(:p_search_equal_length_string) OR (VARCHAR_COLUMN IS NULL AND :p_search_equal_length_string IS NULL); ... I'm not even sure that oracle statement does what it's supposed to do. Too far fetched? br, Martin > >>>>> On Aug 22, 7:22 pm, "Paul Linehan" <plinehan__A@T__yahoo__D.OT__COM> > >>>>> wrote: [quoted text clipped - 60 lines] > br, > Martin Well, that is because null means unknown so we can't say what the length is - unless it can also mean known-to-be-empty, which is what this whole thread is about. I was hoping someone could come up with some more examples as, if you recall, neither of us were convinced by the no-middle-name guy one. > Well, that is because null means unknown so we can't say what the > length is - unless it can also mean known-to-be-empty, which is what > this whole thread is about. I was hoping someone could come up with > some more examples as, if you recall, neither of us were convinced by > the no-middle-name guy one. I'd also like to see these examples - I'm hopeful that Matthew who started the thread with a specific application might fill us in on that. SignatureNiall Litchfield Oracle DBA http://www.orawin.info/services > The (for me) intuitive way: > --------------------------- > SELECT * from MY_TABLE > WHERE LENGTH(VARCHAR_COLUMN) = LENGTH(:p_search_equal_length_string); Actually, you measure what was entered into the column. "AAA" will return a length of 3. Now - what if nothing was filled in at the first place? > The Oracle way: > --------------- [quoted text clipped - 5 lines] > > Too far fetched? Not from where I live. Again, what if nothing was filled in into your character column? Would that be an empty string, or just unknown? NULL <> NULL, but '' = '', if that would help. The length of NULL is NULL again. NULL is the unknown, the Amazon logic. (Yup - it was *not* invented by Oracle!) - -- Regards, Frank van Bortel Top-posting is one way to shut me up... On Aug 26, 2:17 pm, Frank van Bortel <frank.van.bor...@gmail.com> wrote: > NULL <> NULL, but '' = '', if that would help. > The length of NULL is NULL again. If we had a '' in Oracle then its length would be 0, but we don't, which is the subject of this thread. I was hoping someone could provide an example of non-null '' being a useful value. > On Aug 26, 2:17 pm, Frank van Bortel <frank.van.bor...@gmail.com> > wrote: [quoted text clipped - 4 lines] > which is the subject of this thread. I was hoping someone could > provide an example of non-null '' being a useful value. You won't find one. What you will find is that people who have it as an option create designs that use it. Those that don't have it as an option don't. In the end you only have three values, NULL, 0 and > 0. There are a nearly unlimited ways to code this in any language and in any tool. SignatureDaniel A. Morgan University of Washington damorgan@x.washington.edu (replace x with u to respond) Puget Sound Oracle Users Group www.psoug.org >> On Aug 26, 2:17 pm, Frank van Bortel <frank.van.bor...@gmail.com> >> wrote: [quoted text clipped - 4 lines] >> which is the subject of this thread. I was hoping someone could >> provide an example of non-null '' being a useful value. Here are two examples, one real and one hypothetical. I used to work for an audit firm. One of our services was to analyse accounts payable data to look for duplicate payments. In support of this, various statistics and exception reports were produced from the clients' financial systems. Typically, the financial systems we dealt with implemented the business rule that the combination of payee and document number (such as an invoice, debit or credit note) had to be unique. So the only way for a document to be processed twice was if the document was entered with a different number, perhaps due to a keying error or due to an operator forcing a payment through by modifying the number in some way, for example by entering "123A" instead of "123". As part of the audit, one of the transforms we did was to "clean" invoice and credit note numbers by removing non-numeric characters from the unique document number - so "123A" would become "123" and could be matched automatically with any similarly numbered document. Inevitably there was a lot of surplus "noise" generated by this technique - we were usually dealing with millions of invoices and many false matches were unavoidable - but it did produce the valid result that the column containing a cleaned document number would be blank if the original didn't contain any numeric characters. Those cases were probably of no interest most of the time but we certainly wouldn't be allowed to exclude them. Converting them to nulls wouldn't be acceptable either because the consumers of the data were accountants, who probably didn't know what a null was. And why should they? They just wanted to peform their usual queries and analysis, which might well include joins on the cleaned number column. Nulls would have been nothing like equivalent to using an empty string and would have added needless complexity. Now the hypothetical scenario. User names and passwords stored in a table and used for ODBC / OLEDB connection strings. Depending on the provider, a password may have to be supplied, even if it is a blank one. I do not advocate using blank passwords, but a blank password may be necessary or unavoidable in some cases (for example over a trusted connection, where the target system can't support the appropriate password management). Why should I be forced to make the password column nullable when the valid and correct value is a zero-length string? > In the end you only have three values, NULL, 0 > and > 0. There are a nearly unlimited ways to code this in any > language and in any tool. I agree (I assume 0 refers to a zero-length string). I don't agree that zero-length strings are "empty" or not useful or that nulls are always an acceptable substitute. SignatureDavid Portas On Aug 26, 7:42 pm, "David Portas" <REMOVE_BEFORE_REPLYING_dpor...@acm.org> wrote: > >> On Aug 26, 2:17 pm, Frank van Bortel <frank.van.bor...@gmail.com> > >> wrote: [quoted text clipped - 53 lines] > -- > David Portas Thanks for the examples. I have to say they aren't as everyday as no- middle-name guy with his two names and his three name lengths. OK, given a business requirement to strip non-numeric characters from mandatory document IDs such as '123A', what do you do with document 'Z'? I can imagine going back to the business and explaining that if there are only non-numeric characters, that won't leave anything to identify the document by. Surely even an accountant would appreciate that problem? I would want to replace the ID with 'Audit 2007' or some similar code (presumably unique?) to flag them explicitly. With the password example, surely when the designers of the system made the password a not-null column the intention was to force all users to provide a password, and allowing '' subverts that rule. If you are allowed to enter '' as a password, why have a mandatory constraint in the first place? Unless of course you are stuck with a password validator that inconveniently requires you to enter one. I'm afraid both examples seem like workarounds to bad data or a design you can't change. Yes, if the system allows empty strings you might use them in this sort of situation, but if not it doesn't seem such a big deal. At the risk of returning to the OP's original problem, it seemed to be something to do with distinguishing between myproc(''), myproc() and myproc(NULL), to which there are solutions in PL/SQL as I illustrated. On Aug 26, 2:42 pm, "David Portas" <REMOVE_BEFORE_REPLYING_dpor...@acm.org> wrote: > >> On Aug 26, 2:17 pm, Frank van Bortel <frank.van.bor...@gmail.com> > >> wrote: [quoted text clipped - 53 lines] > -- > David Portas David, I notice in both descriptions you mention "blank" results or data > did produce the valid result that the column containing a cleaned document > number would be blank if the original didn't contain any numeric characters. Now in this case you likely really do end up with '' (as opposed to ' '), but the intention is the same. a cleaned value implies a result that is blank. (Consider a CHAR versus VARCHAR2 datatype). So you might need some extra code to deal with this case, but you want a blank not an "empty" string. > password may have to be supplied, even if it is a blank one. I do not And here it is pretty much how the UI might be coded. If the user merely presses <enter> at the password field, the error back to him would be something like: Password Required. Eventually they would enter a blank (' ') and the UI is happy, and the DB is happy (you can encrypt ' ' easily). I understand the frustation. If you are a UNIX programmer, used to things like /dev/null (such a wonderful creation), then you just want '' to be there. But notice, even in C from the user interface you will not really get just "" as the input. There is no input until the user presses <enter>, so the raw input always contains at least "\n". That \n might be stripped out but it was there. So I still don't see much use for '' except for maintaining some mathematical properties on strings (' ' is a poor identity operand). Ed > On Aug 26, 2:42 pm, "David Portas" > <REMOVE_BEFORE_REPLYING_dpor...@acm.org> wrote: [quoted text clipped - 106 lines] > > Ed I'm not denying the obvious fact that a zero-length string value could be coded in other ways (just as it would be possible to devise alternative representations for 0 or 1 in a system that didn't support those integer values). My only point was to show that there may be valid and sensible reasons to wish to store zero-length strings. Using Oracle means that some compromises are necessary, whereas in other languages or in DBMSs that follow the SQL standard for strings those compromises are unnecessary. SignatureDavid Portas, SQL Server MVP Whenever possible please post enough code to reproduce your problem. Including CREATE TABLE and INSERT statements usually helps. State what version of SQL Server you are using and specify the content of any error messages. SQL Server Books Online: http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx -- >>> On Aug 26, 2:17 pm, Frank van Bortel <frank.van.bor...@gmail.com> >>> wrote: [quoted text clipped - 3 lines] >>> which is the subject of this thread. I was hoping someone could >>> provide an example of non-null '' being a useful value. [snip] > operator forcing a payment through by modifying the number in some way, for > example by entering "123A" instead of "123". That's a bad design (or choice of words from your side) to begin with. That is not an invoice number, it's an invoice identifier. Numbers: [0-9] - not an "A" to be seen. > As part of the audit, one of the transforms we did was to "clean" invoice > and credit note numbers by removing non-numeric characters from the unique [quoted text clipped - 4 lines] > did produce the valid result that the column containing a cleaned document > number would be blank if the original didn't contain any numeric characters. If it were a true number, the result would be NULL. As described, in Oracle, it would be an empty character field, or NULL. See the beauty? Both cases would be NULL, undefined, not known. An empty string would be a known. > Those cases were probably of no interest most of the time but we certainly > wouldn't be allowed to exclude them. Converting them to nulls wouldn't be > acceptable either because the consumers of the data were accountants, who > probably didn't know what a null was. And why should they? They just wanted Well - do they understand how long cables introduce capacity, so hat your disk cables are limited in length? No - and they don't need to know either. You could have explained the invoice codes simply are unknown. And that is exactly what it is. > to peform their usual queries and analysis, which might well include joins > on the cleaned number column. Nulls would have been nothing like equivalent > to using an empty string and would have added needless complexity. Oh, come on! where ... = '' is less complex than where ... is null? [snip] > I agree (I assume 0 refers to a zero-length string). I don't agree that > zero-length strings are "empty" or not useful or that nulls are always an > acceptable substitute. or the other way around :) - -- Regards, Frank van Bortel Top-posting is one way to shut me up... >> operator forcing a payment through by modifying the number in some way, >> for [quoted text clipped - 4 lines] > invoice identifier. > Numbers: [0-9] - not an "A" to be seen. Quite true, although it is a very standard accounting convention to us the term "Invoice Number" even when the identifier is an alpha-numeric. >> As part of the audit, one of the transforms we did was to "clean" invoice >> and credit note numbers by removing non-numeric characters from the [quoted text clipped - 14 lines] > in Oracle, it would be an empty character field, or NULL. > See the beauty? Both cases would be NULL, undefined, not known. That just does not follow. The value in question IS known. The value is a function representung the string less its non-numeric characters. How can that be an unknown value if the input string is known? Even if what you say made sense, I hope I don't need to remind you that NULL is no logical substitute for an unknown value. > An empty string would be a known. Of course. >> Those cases were probably of no interest most of the time but we >> certainly [quoted text clipped - 14 lines] > > Oh, come on! where ... = '' is less complex than where ... is null? IS NULL isn't enough. To simulate ANSI SQL string comparison in Oracle you would need something like: a.x = b.x OR (a.x IS NULL AND b.x IS NULL) SignatureDavid Portas >> On Aug 26, 2:17 pm, Frank van Bortel <frank.van.bor...@gmail.com> >> wrote: [quoted text clipped - 10 lines] > and > 0. There are a nearly unlimited ways to code this in any > language and in any tool. I think you are right in that you can design both ways and I also think that it is probably really hard to find a case where '' means anything useful different from NULL from a semantic point of view. However, the point is was trying to make in my posts was that most systems/programming languages make the distinction and in general string processing the distinction is also very useful ( LENGTH(A+B) === LENGTH(A) + LENGTH(B) ). So the behavior of Oracle just makes the usage more complicated and error prone, imho. br, Martin > >> On Aug 26, 2:17 pm, Frank van Bortel <frank.van.bor...@gmail.com> > >> wrote: [quoted text clipped - 24 lines] > br, > Martin Nulls are a fact of life in SQL generally (despite being regarded as an abomination by some relational theorists) and are not unique to Oracle. PL/SQL has to follow SQL, as making it different would create even more complication and errors. The SQL Server/Sybase etc way does not eliminate nulls, it just gives you a second type of emptiness to code around. I can appreciate that in a programming context you might code something like v_middle_name = ''; and if length(v_middle_name) = 0 or indeed javascript:alert(''.length) but while that might be fine for JavaScript (no disrespect btw - I like JavaScript) you can't get away with that in a database query for the kind of reasons we have been discussing in this thread. >>>> On Aug 26, 2:17 pm, Frank van Bortel <frank.van.bor...@gmail.com> >>>> wrote: [quoted text clipped - 45 lines] > like JavaScript) you can't get away with that in a database query for > the kind of reasons we have been discussing in this thread. Of course NULLs are a fact. But I think a column with NOT NULL is often acceptable. While we are at Java*, lets assume this very-pseudo code here: --- String str = "hello"; int len = 0; str.substr(0, len); JDBC.insert("MY_VARCHAR_COL", str); ... String strx = JDBC.select("MY_VARCHAR_COLUMN"); // And now, because it's oracle, we do NOT get a String object with len 0, but we get a null. So we have to program around orcacle's varchar if(strx == null) { strx = new string(); } --- Since most of the languages used to get data out of Oracle make the distinction of NULL/null/undefined vs. '' so should oracle. It would make working with VARCHAR2 columns easier I think. br, Martin >>>>> On Aug 26, 2:17 pm, Frank van Bortel <frank.van.bor...@gmail.com> >>>>> wrote: [quoted text clipped - 72 lines] > br, > Martin Shakespeare has left the building....... Sorry guys, threads are getting this long the tree fills up my screen... Shakespeare (What's in a null?) > On Aug 26, 2:17 pm, Frank van Bortel <frank.van.bor...@gmail.com> > wrote: [quoted text clipped - 4 lines] > which is the subject of this thread. I was hoping someone could > provide an example of non-null '' being a useful value. Well, what's there to say? Apart from two obvious things: - - it's not according to the ANSI SQL standard (yeah, yeah - we know) - - learn to live with it (or ditch Oracle). <ot> I was quite surprised to find out recently, there's a tribe that actually uses the NULL in daily speak; sort of maybe yes, maybe no - it's not true, but not false, either. Hence the name Amazon logic. </ot> - -- Regards, Frank van Bortel Top-posting is one way to shut me up... Matthew Harrison wrote on 17.08.2007 16:49: > Ok, I'm developing another oracle application where the distinction > between NULL and '' will be important. I would be interested: if this is important for character columns, then I assume this is important for other columns as well. How do you implement that distinction for e.g. NUMBER or DATE columns? Thomas > Matthew Harrison wrote on 17.08.2007 16:49: >> Ok, I'm developing another oracle application where the distinction [quoted text clipped - 3 lines] > I assume this is important for other columns as well. > How do you implement that distinction for e.g. NUMBER or DATE columns? If I get an assignment and I hand in an empty sheet that would be well empty. I typed zero characters. If I don't hand it in it is missing. It is not decidable whether my work is good or bad, long short.. there is the NULL. For numbers I think one can reasonably argue that 0 is the equivalent of empty. I don't think that there is a similar "empty" concept for dates. Question: If empty strings make no sense for VARCHAR, why have them for CLOBs? AFAIK Oracle has helper functions to produce empty lobs. Cheers Serge PS: The Romans had some severe issues "getting" the concept of 0. Today we take it for granted. Perhaps '' is no different :-) SignatureSerge Rielau DB2 Solutions Development IBM Toronto Lab Serge Rielau wrote on 17.08.2007 19:38: >> I would be interested: if this is important for character columns, >> then I assume this is important for other columns as well. >> How do you implement that distinction for e.g. NUMBER or DATE columns? > If I get an assignment and I hand in an empty sheet that would be well > empty. I typed zero characters. > If I don't hand it in it is missing. It is not decidable whether my work > is good or bad, long short.. there is the NULL. If I don't hand it in, I don't "create a row in the database" that is something different compared to handing in an empty "row/assignment" > For numbers I think one can reasonably argue that 0 is the equivalent of > empty. Hmm. I get an an assignment where I should calculate something. I hand in a sheet where I put 0 (zero) as the solution. So that is the same as handing in an assignment where I didn't enter anything. Doesn't sound logical to me. > I don't think that there is a similar "empty" concept for dates. That's my point. Character seems to be the only data were everybody requires the distinction between "nothing" and "empty" but nobody has ever requested this distinction for dates or numbers. > Question: If empty strings make no sense for VARCHAR, why have them for > CLOBs? AFAIK Oracle has helper functions to produce empty lobs. I think that is a mere technical reason as for the CLOB some "management structures" (such as a locator) need to be created while this is not necessary with character data. Thomas > Serge Rielau wrote on 17.08.2007 19:38: >>> I would be interested: if this is important for character columns, [quoted text clipped - 7 lines] > If I don't hand it in, I don't "create a row in the database" that is > something different compared to handing in an empty "row/assignment" Depends on your data model doesn't it? What if the table looks like this: STUDENT EXAM1 EXAM2 Bob 75 82 Tom 83 Kristi 94 92 Chick 56 47 So what does the entry for Tom in EXAM2 mean? There is a row in the table after all! >> For numbers I think one can reasonably argue that 0 is the equivalent >> of empty. > Hmm. I get an an assignment where I should calculate something. I hand > in a sheet where I put 0 (zero) as the solution. So that is the same as > handing in an assignment where I didn't enter anything. > Doesn't sound logical to me. I don't think that I'd ever equate 0 to empty for numbers. But maybe that's the mathematician in me. When computing averages or min values, a 0 can have a profound affect on the result where as no value can mean something else. Take for example the following set of numbers: {0, 11, 22, 33, 44} The average is 22 and the min is 0. Now take these numbers: {NULL, 11, 22, 33, 44} Now the average is 27.5 and the min is 11. So for those reasons, I would not reasonably argue that 0 is the equivalent of empty. Of course...it does depend on how you are going to use this data. There are cases where it does not make any difference. >> I don't think that there is a similar "empty" concept for dates. > That's my point. Character seems to be the only data were everybody [quoted text clipped - 6 lines] > structures" (such as a locator) need to be created while this is not > necessary with character data. Agreed. Cheers! Brian Signature=================================================================== Brian Peasland dba@nospam.peasland.net http://www.peasland.net Remove the "nospam." from the email address to email me. "I can give it to you cheap, quick, and good. Now pick two out of the three" - Unknown -- Posted via a free Usenet account from http://www.teranews.com > STUDENT EXAM1 EXAM2 > Bob 75 82 [quoted text clipped - 4 lines] > So what does the entry for Tom in EXAM2 mean? There is a row in the > table after all! Correct. So if Tom just got everything wrong he gets a 0. If the TA hasn't reviewed the result yet that would be a NULL. Or perhaps Tom was sick and is going to do the exam later. Now let's use: Firstname, Middlename, Lastname George Bush Which one do I mean? If Middlename is an empty string then Sr. If Middlename is NULL it could be either. It's not specified. There are plenty of debates about the usage of NULL in general in data bases. You know me under 'Serge' NULL 'Rielau'. Homeland Security should not confuse that with 'Serge' '' 'Rielau' because that ain't me. Cheers Serge SignatureSerge Rielau DB2 Solutions Development IBM Toronto Lab > > STUDENT EXAM1 EXAM2 > > Bob 75 82 [quoted text clipped - 29 lines] > DB2 Solutions Development > IBM Toronto Lab Y'all got some of that there homeland security stuff up in Toronto? > Y'all got some of that there homeland security stuff up in Toronto? US customs is at the Toronto Airport. Cheers Serge SignatureSerge Rielau DB2 Solutions Development IBM Toronto Lab > > Y'all got some of that there homeland security stuff up in Toronto? > [quoted text clipped - 7 lines] > DB2 Solutions Development > IBM Toronto Lab Unless the airport security staff is a whole lot smarter than the ones we see around here I wouldn't advise any discussion about middle names being NULL or NOT NULL. >>> Y'all got some of that there homeland security stuff up in Toronto? >> US customs is at the Toronto Airport. > Unless the airport security staff is a whole lot smarter than the ones > we see around here I wouldn't advise any discussion about middle names > being NULL or NOT NULL. Better not. That's why IBM sells MDM solutions ;-) Cheers Serge SignatureSerge Rielau DB2 Solutions Development IBM Toronto Lab > There are plenty of debates about the usage of NULL in general in data In the case I'm working on the string is the command. In their infinite wisdom, '' (the empty string) has a critical meaning .. I need to ensure that this string is specified (i.e. NOT NULL), but it can be specified as '' (meaning cancel). SignaturePinging self [127.0.0.1] with 32 bites of banana cake: Ping statistics for 127.0.0.1: Slices: Sent = 4, Received = 0, Lost = 4 (100% loss), >> There are plenty of debates about the usage of NULL in general in data > [quoted text clipped - 3 lines] > ... I need to ensure that this string is specified (i.e. NOT NULL), but > it can be specified as '' (meaning cancel). Actually you have less of a problem than you think. Only if you had to have a column where NULLs and '' would be something different would you be in trouble. As it stands now, you just have to allow NULL in this column and treat it as ''. (In the application layer.) If you also use other DBs besides oracle just make the column NOT NULL and treat '' as ''. Simple(?) as that. cheers, Martin > If you also use other DBs besides oracle just make the column NOT NULL > and treat '' as ''. > > Simple(?) as that. WHERE c1 = '' will behave differently. Unless NULL = NULL, which I recall from a past discussion is not the case. Others introduced the notion of a "known unknown". I think '' is a known absense of a value (I cringe since '' to me is a value like 0). NULL is unknown. Undecidable. It is an integral part of the predicate logic. Similar problems appear with LENGTH(SUBSTR('Hello', 1, 0)). Shouldn't it be 0? Just like in CONCAT NULL is "treated" as an empty string. IMHO, following that logic, LENGTH(NULL) = 0, alas it is not. For "most" functions NULL in means NULL out... Cheers Serge SignatureSerge Rielau DB2 Solutions Development IBM Toronto Lab >> If you also use other DBs besides oracle just make the column NOT NULL >> and treat '' as ''. [quoted text clipped - 14 lines] > Cheers > Serge You are of course right. Oracle's treatment of '' necessitates a lot of workarounds and explicit checking for NULL when generically working with strings. cheers, Martin > You know me under 'Serge' NULL 'Rielau'. Homeland Security should not > confuse that with 'Serge' '' 'Rielau' because that ain't me. Et en quoi est "homeland security" melangé avec quelqu'un qui habite au Canada? Paul... > Serge > Et en quoi est "homeland security" melangé avec quelqu'un qui > habite au Canada? As if all of Canada speaks Canadian (trying not to offend the French, they live closer to where I live). - -- Regards, Frank van Bortel Top-posting is one way to shut me up... > -----BEGIN PGP SIGNED MESSAGE----- > Hash: SHA1 [quoted text clipped - 17 lines] > =IX+3 > -----END PGP SIGNATURE----- From what I hear the French don't think the Canadians speak their language any more than the English think I speak theirs. Is it a truck or a lorry? Do I rent it or let it? Is it an elevator or a lift? And then there's nasty issue about wearing your pants in pubic. SignatureDaniel A. Morgan University of Washington damorgan@x.washington.edu (replace x with u to respond) Puget Sound Oracle Users Group www.psoug.org >> -----BEGIN PGP SIGNED MESSAGE----- >> Hash: SHA1 [quoted text clipped - 24 lines] > or a lift? And then there's nasty issue about wearing your pants in > pubic. Pubic pants? :-) Generally I think if a language is spoken by more than two people there will be some amount of disagreement. (Swiss <-> French/German/Italian | Austrian <-> German <-> Swiss | probably Norwegian <-> Danish | oh, yes, Spanish <-> Spanish ) There are many databases speaking SQL and I think we are in agreement that they do not always speak the same language :-P br, Martin > Paul Linehan wrote: >>>>> Et en quoi est "homeland security" melangé avec quelqu'un qui [quoted text clipped - 5 lines] >> From what I hear the French don't think the Canadians speak their >> language any more than the English think I speak theirs. The point was: not all of Canada speaks french (Just as Belgians do not all speak the Dutch dialect flamish) >> Is it a truck or a lorry? Do I rent it or let it? Is it an elevator >> or a lift? And then there's nasty issue about wearing your pants in >> pubic. Autch! Tomato's <> tomatoes? (Both valid, according to the spell checker) > Pubic pants? :-) > Generally I think if a language is spoken by more than two people there > will be some amount of disagreement. (Swiss <-> French/German/Italian | > Austrian <-> German <-> Swiss | probably Norwegian <-> Danish | oh, yes, > Spanish <-> Spanish ) Now you offended the Swiss - the Swiss Navy will come over and bombard your cities. :D Swiss is not a language (worked in the German speaking as well as the French speaking parts), it's a dialect at most. And there are four official languages defined in Switzerland; you forgot Roman. You have also offended the Norwegian people - they speak Bokmal (the o should have a slash, iirc), certainly not Danish. Danish and Swedish might have similarities, as these countries have a common background. About the similarities between French and English (which seems quite substantial). Spanish is not a language, either; you speak either Catalan or Castillian. Or Bask, of course :) I always forget which, but one is spoken in Spain, the other in South America (Brazil excluded - they speak Portuguese in a similar way South Africans spoke Dutch) - -- Regards, Frank van Bortel Top-posting is one way to shut me up... On Aug 23, 2:10 pm, Frank van Bortel <frank.van.bor...@gmail.com> wrote: > (...) > Spanish is not a language, either; you speak either Catalan or [quoted text clipped - 6 lines] > Regards, > Frank van Bortel Castillian (castellano) is spoken in South America. Brazil (S.America) and Angola (S. Africa) speak Portuguese Regards, Eliezer Daneluti. > On Aug 23, 2:10 pm, Frank van Bortel <frank.van.bor...@gmail.com> > wrote: [quoted text clipped - 15 lines] > Regards, > Eliezer Daneluti. And of course, Catalan is a completely different language, see wikipedia Catalan language. jg -- @home.com is bogus. "This chapter describes the four Oracle Database editions and the features available with those editions. ... The Oracle Database Product Family Oracle Database is available in five editions,..." - http://download.oracle.com/docs/cd/B28359_01/license.111/b28287/editions.htm#BABDJGGI >> On Aug 23, 2:10 pm, Frank van Bortel <frank.van.bor...@gmail.com> >> wrote: [quoted text clipped - 26 lines] > Oracle Database is available in five editions,..." - > http://download.oracle.com/docs/cd/B28359_01/license.111/b28287/editions.htm#BABDJGGI And if you visit Barcelona the locals will also gladly explain to you that the country of Spain exists only in the minds of some people in Madrid. Not much different from what you hear talking about England while standing in Edinburgh. <g> SignatureDaniel A. Morgan University of Washington damorgan@x.washington.edu (replace x with u to respond) Puget Sound Oracle Users Group www.psoug.org >>> On Aug 23, 2:10 pm, Frank van Bortel <frank.van.bor...@gmail.com> >>> wrote: [quoted text clipped - 23 lines] > Madrid. Not much different from what you hear talking about England > while standing in Edinburgh. <g> My, my. You probably get lynched when y' tried to tell Scotts that they're part of England (as opposed to Britain and even that's disputed apparently.) ;-) On Aug 23, 7:10 pm, Frank van Bortel <frank.van.bor...@gmail.com> wrote: > Tomato's <> tomatoes? (Both valid, according to the spell checker) Both valid perhaps, but they mean different things. > -----BEGIN PGP SIGNED MESSAGE----- > Hash: SHA1 [quoted text clipped - 57 lines] > =tb80 > -----END PGP SIGNATURE----- Sorry to amend you Frank, but spanish (espa?ol) IS a language. It is a synonym for Castillian (castellano). You can use both names for the language. Catalan is a spanish language -in the sense that it is spoken in (some parts of) Spain-, but it is NOT *THE* spanish language (espa?ol). There are other spanish languages apart from spanish/castillian and catalan, by the way. Cheers. Carlos. >> -----BEGIN PGP SIGNED MESSAGE----- >> Hash: SHA1 [quoted text clipped - 65 lines] > > Carlos. Then again. I'm not entirely sure the South Americas agree that they are actually speaking (talking) castellano. I guess this would be like declaring the german swiss speak high-german ... :-) cheers, Martin > >> -----BEGIN PGP SIGNED MESSAGE----- > >> Hash: SHA1 [quoted text clipped - 73 lines] > cheers, > Martin >>I'm not entirely sure the South Americas agree that they are >>actually speaking (talking) castellano. But some of them do... I have a mexican friend and sometimes he says he speaks 'castellano' and sometimes he says he speaks 'espa?ol' . The truth is that 'espa?ol' is more used worldwide, and 'castellano' is more used in Spain. Cheers. Carlos. > Sorry to amend you Frank, but spanish (español) IS a language. It is a > synonym for Castillian (castellano). You can use both names for the > language. Catalan is a spanish language -in the sense that it is > spoken in (some parts of) Spain-, but it is NOT *THE* spanish language > (español). You're a Spaniard, not doubt. :) Thanks for the clarification, though > There are other spanish languages apart from spanish/castillian and > catalan, by the way. I did mention Bask - I am ignorant about any other. > Cheers. > Carlos. - -- Regards, Frank van Bortel Top-posting is one way to shut me up... >> I don't think that there is a similar "empty" concept for dates. > That's my point. Character seems to be the only data were everybody > requires the distinction between "nothing" and "empty" but nobody has ever > requested this distinction for dates or numbers. The question of whether we choose to refer to some value as "empty" is entirely beside the point in my opinion. The issue is that the domain of string values supported by Oracle is not equivalent to the domain of string values supported by any other DBMS or programming language that I know of. Null is not a value. A string consisting of zero characters IS a value anywhere except Oracle. You could "design out" the problem as Daniel Morgan suggests but then you may have to accept that some process or function which potentially has to support zero-length string values can no longer use Oracle as a data store. Alternatively, you have to compromise by using nulls to represent values or converting the strings to some other data type or representation. SignatureDavid Portas >>> I don't think that there is a similar "empty" concept for dates. >> That's my point. Character seems to be the only data were everybody [quoted text clipped - 9 lines] > > (...) Allow me to second that. We could debate without end on the philosophical merits of "empty" but fact is that any programming language I have ever worked with makes a distinction btw. NULL and '' string. Since every DBMS ultimately has to interface with another programming language, the way Oracle treats strings is bound to generate problems in some cases. (not to mention the confusion it always generates) cheers, Martin On Aug 17, 2:44 pm, "David Portas" <REMOVE_BEFORE_REPLYING_dpor...@acm.org> wrote: > >> I don't think that there is a similar "empty" concept for dates. > > That's my point. Character seems to be the only data were everybody [quoted text clipped - 7 lines] > Null is not a value. A string consisting of zero characters IS a value > anywhere except Oracle. Oracle is not the only DBMS with this characteristic. UNIFY DataServer has the same "feature". I don't agree that a string consisting of zero characters IS a value. Is "" logically different from " "? If I have a user filling out an on- line form and they enter nothing into one of the fields, I would load a NULL in the DB. Would you really try loading an empty string? What would that mean exactly?? > You could "design out" the problem as Daniel Morgan suggests but then you > may have to accept that some process or function which potentially has to > support zero-length string values can no longer use Oracle as a data store. Only if "" carries some meaning. I for one don't think it has any meaning outside a C program (where it is a one byte array initialized with a zero byte value). If you really want "" then you likely want a BLOB column. > Alternatively, you have to compromise by using nulls to represent values or > converting the strings to some other data type or representation. > > -- > David Portas The compromises only are needed if you embue "" with some meaning. Otherwise, life is good. ed > On Aug 17, 2:44 pm, "David Portas" > <REMOVE_BEFORE_REPLYING_dpor...@acm.org> wrote: [quoted text clipped - 39 lines] > > ed The problem is not, as others have repeatedly stated, if '' has any meaning. The problem is that the LENGTH('') should be 0 and not NULL. "Should" because it's (nearly) everywhere else except in oracle. The empty string has a length of 0. NULL has a length of NULL. In Oracle ("only") we have the stupid situation that the length of two concatenated strings can be different from the added length of the single strings. Now tell me how that is good. cheers, Martin > On Aug 17, 2:44 pm, "David Portas" > <REMOVE_BEFORE_REPLYING_dpor...@acm.org> wrote: [quoted text clipped - 45 lines] > > ed And even if "" had some meaning, life could still be good.... Shakespeare > Matthew Harrison wrote on 17.08.2007 16:49: > > Ok, I'm developing another oracle application where the distinction > > between NULL and '' will be important. > > I would be interested: if this is important for character columns, then I > assume this is important for other columns as well. Sure it is. > How do you implement that distinction for e.g. NUMBER or DATE columns? Since Oracle didn't screw NUMBER up by deciding that zero doesn't exist and should be magically converted to NULL, there is no need to do anything special, it just works. I've never needed to specify the exact date of the big bang, so I don't whether Oracle screwed that one up too, or not. Xho Signature-------------------- http://NewsReader.Com/ -------------------- Usenet Newsgroup Service $9.95/Month 30GB >> How do you implement that distinction for e.g. NUMBER or DATE columns? > > Since Oracle didn't screw NUMBER up by deciding that zero doesn't exist and > should be magically converted to NULL, there is no need to do anything > special, it just works. I've never needed to specify the exact date of the > big bang, so I don't whether Oracle screwed that one up too, or not. Right on. SignatureSerge Rielau DB2 Solutions Development IBM Toronto Lab > Ok, I'm developing another oracle application where the distinction > between NULL and '' will be important. [quoted text clipped - 4 lines] > > Thank you. SQL> create table t ( 2 col1 varchar2(10), 3 col2 varchar2(10)); Table created. SQL> INSERT INTO t VALUES (NULL, ''); 1 row created. SQL> commit; Commit complete. SQL> SELECT sys_op_map_nonnull(col1), sys_op_map_nonnull(col2) 2 FROM t; SYS_OP_MAP_NONNULL(COL SYS_OP_MAP_NONNULL(COL ---------------------- ---------------------- FF FF SQL> Not unless you create a kludge. For example insert 'ZZYZX' where ever you intend an empty string. The better choice would be, no matter the database product, to never have NULLs or empty strings. This can be done with proper design. SignatureDaniel A. Morgan University of Washington damorgan@x.washington.edu (replace x with u to respond) Puget Sound Oracle Users Group www.psoug.org > > Ok, I'm developing another oracle application where the distinction > > between NULL and '' will be important. [quoted text clipped - 38 lines] > damor...@x.washington.edu (replace x with u to respond) > Puget Sound Oracle Users Groupwww.psoug.org Proper design should reflect how the application being modeled works in the real world. In the real world information is often missing or unknown so a proper design will have nullable columns. In Cost-Based Oracle Fundamentals Jonathin Lewis shows some examples of how using default values in place of nulls can mess up the CBO in its determination of cardinality, which is what other optimizer decisions are based on. IMHO -- Mark D Powell -- > Ok, I'm developing another oracle application where the distinction > between NULL and '' will be important. > > Given Oracle doesn't adhere to SQL standards for the distinction, is > there any best practices for comparing, and storing blank strings in a > table where the field is conceptually NOT NULL. Having problems with "where the field is conceptually NOT NULL"? Well it's either defined as NOT NULL in the database or it will allow nulls. Why don't you define it as NOT NULL and remove the "is conceptually" out of the equation. Providing a default value is also sometimes a good option. Tom Kyte has a bunch to say on this area in his books and his site http://asktom.oracle.com > Why don't you define it as NOT NULL and remove the "is conceptually" > out of the equation. I need to define it as NOT NULL, however '' is a valid value (and has a critical meaning) for the value. SO - it is a NOT NULL value which has to store '' as a possible value. SignaturePinging self [127.0.0.1] with 32 bites of banana cake: Ping statistics for 127.0.0.1: Slices: Sent = 4, Received = 0, Lost = 4 (100% loss), > Ok, I'm developing another oracle application where the distinction > between NULL and '' will be important. [quoted text clipped - 10 lines] > Ping statistics for 127.0.0.1: > Slices: Sent = 4, Received = 0, Lost = 4 (100% loss), I do not believe, one has to set default values or some other value in the field to treat as null. Any value you put in the field/variable is not same thing as null. One has to store information whether a value is null outside of field (such as in an indicator variable). Oracle took care of this long time ago. When one writes code, e.g., in PRO*C, Oracle has host variables and indicator variables. Host variable stores the actual value of the field and indicator variables store the fact whether the field is null or not. So when you want to save a null in database, you set indicator variable, e.g., to -1. When you get a value from Oracle it will set indicator variable to -1 if value was null. Any time you do a check in your code to find whether a value is null or not, you look at the indicator variable. Oracle provides similar concepts in other labguages such Java. In PL/SQL, internally Oracle perhaps uses same approach but Oracle has built short cuts in PL/SQL language. For example to find whether i is null, one writes code: If (i is null). To insert null, insert table (col1) values (null); Any other mechanism used such as empty value for Strings , 0 or - 32767 for integer, or using default values are kludges which get you in trouble sooner or later because null is not same as any of these values. As long one follows Oracle's approach, there will not be any problems. In my view, Oracle's approach is the right approach. > Any value you put in the field/variable is > not same thing as null. Agreed. Any value is not the same thing as null > In my view, Oracle's approach is the right approach. Huh? But Oracle's approach is that the empty string value IS the same thing as null! SignatureDavid Portas On Aug 17, 5:23 pm, "David Portas" <REMOVE_BEFORE_REPLYING_dpor...@acm.org> wrote: > <zigzag...@yahoo.com> wrote in message > [quoted text clipped - 12 lines] > -- > David Portas I was not aware of that, but I do not belive same applied to other data types such as integer, > On Aug 17, 5:23 pm, "David Portas" > <REMOVE_BEFORE_REPLYING_dpor...@acm.org> wrote: [quoted text clipped - 15 lines] > I was not aware of that, but I do not belive same applied to other > data types such as integer, Then you would be incorrect: It does. Though I wouldn't suggest inserting an empty string into a DATE column. SignatureDaniel A. Morgan University of Washington damorgan@x.washington.edu (replace x with u to respond) Puget Sound Oracle Users Group www.psoug.org > zigzag...@yahoo.com wrote: > > On Aug 17, 5:23 pm, "David Portas" [quoted text clipped - 27 lines] > > - Show quoted text - Why did then Oracle developed host variables, indicator variables etc. Keeping null indicator separate from the actual value is the right approach. When I have to insert null in a column, either I do not use that column in insert or use indicator variable (in PRO*C) or null in PL/SQL. It has always worked for me. Oracle may have messed up things but if a disciplined approach by keeping null indicator separate from the actual value is followed all through the code, things will still work. >It has always worked for me. Oracle may have messed up > things but if a disciplined approach by keeping null indicator > separate from the actual value is followed all through the code, > things will still work. So , I have a stored proc with a parameter MSG. I have a table with values MSG and MSGISNULL. How do I tell the parameter is '' or NULL ? Or does the client user have to pass two parameters and do the test in their own langauge. The catch here is that if it was a number, I just wouldn't put a value in ... that is ... the stored procedure would not have been called. So - how do I detect, and act on someone NOT calling the stored procedure and set a flag ? SignaturePinging self [127.0.0.1] with 32 bites of banana cake: Ping statistics for 127.0.0.1: Slices: Sent = 4, Received = 0, Lost = 4 (100% loss), > zigzag...@yahoo.com wrote: > >It has always worked for me. Oracle may have messed up [quoted text clipped - 22 lines] > Ping statistics for 127.0.0.1: > Slices: Sent = 4, Received = 0, Lost = 4 (100% loss), Not sure I fully undesratnd , but I would think that client code has to pass two paremetrs, one indicating whethere value is null or not , and the other the actaul value. You may even consiser an indicator column in the table for this varchar2 column, so you can look at the value of the indicator column to decide whther " msg is not null. > Not sure I fully undesratnd , but I would think that client code has > to pass two paremetrs, one indicating whethere value is null or not , > and the other the actaul value. You may even consiser an indicator > column in the table for this varchar2 column, so you can look at the > value of the indicator column to decide whther " msg is not null. The point is - if the client is calling the procedure, then the message will not be null (but could be ''). That is - it's the uncalled messages are the problem ... I'd have to create a flag for all of them saying with a bit mask of null flags. > zigzag...@yahoo.com wrote: > >It has always worked for me. Oracle may have messed up [quoted text clipped - 16 lines] > So - how do I detect, and act on someone NOT calling the stored > procedure and set a flag ? Perhaps some demo code would make the scenario clearer. If myproc() takes an argument with a default of NULL, you can distinguish between myproc() and myproc(NULL) by using overloading. I'm not sure that is your scenario though. You may also be able to do something with CHAR variables (normally considered evil for this sort of reason). > myproc() > > and > > myproc(NULL) more between myproc('') and myproc(NULL) However, myproc(NULL) never gets called ... only ... there is never the need to tell the system a message has not been sent. SignaturePinging self [127.0.0.1] with 32 bites of banana cake: Ping statistics for 127.0.0.1: Slices: Sent = 4, Received = 0, Lost = 4 (100% loss), > > myproc() > [quoted text clipped - 18 lines] > Ping statistics for 127.0.0.1: > Slices: Sent = 4, Received = 0, Lost = 4 (100% loss), To distinguish between null and '' parameter values, try something like this: CREATE OR REPLACE PROCEDURE distinguish_emptiness ( p_msg VARCHAR2 ) AS v_msg CHAR(50) := p_msg; BEGIN IF v_msg IS NULL THEN DBMS_OUTPUT.PUT_LINE('NULL'); ELSE DBMS_OUTPUT.PUT_LINE('String with length ' || LENGTH(v_msg)); END IF; END; / SQL> exec distinguish_emptiness(NULL); NULL PL/SQL procedure successfully completed. SQL> exec distinguish_emptiness('') String with length 50 PL/SQL procedure successfully completed. On Aug 17, 10:23 pm, "David Portas" <REMOVE_BEFORE_REPLYING_dpor...@acm.org> wrote: > <zigzag...@yahoo.com> wrote in message > [quoted text clipped - 12 lines] > -- > David Portas Oracle's approach is that there is no empty string. While I can sort of see how a second type of emptiness might sometimes be useful, I'm generally glad that Oracle keeps it simple. There was a long-running debate about this over on OTN forums last year, and I tried to get the poster there to explain why he wasn't concerned about "known no-value" DATEs when "known no-value" strings seemed to drive him crazy, and never got a proper answer there either. As I understand it, first Oracle came along and handled null strings a particular way; then a couple more rival products appeared that handled them in a different way. Because more than one rival product is different to Oracle, Oracle gets called "non-standard". Is that the basis for "Oracle doesn't adhere to SQL standards for the distinction" or am I missing something? I must say I have never come across this requirement in any system I have worked on in 18 years so I would be interested to hear the business rule in more detail. > Because more than one rival product > is different to Oracle, Oracle gets called "non-standard". Is that the > basis for "Oracle doesn't adhere to SQL standards for the distinction" > or am I missing something? The basis for saying that "Oracle doesn't adhere to SQL standards" is the ANSI/ISO/IEC SQL standard document. ISO/IEC 9075-2:2003. Section 4.2.1: "A character string is a sequence of characters. All the characters in a character string are taken from a single character set. A character string has a length, which is the number of characters in the sequence. The length is 0 (zero) or a positive integer." SignatureDavid Portas On Aug 18, 1:28 pm, "David Portas" <REMOVE_BEFORE_REPLYING_dpor...@acm.org> wrote: > > Because more than one rival product > > is different to Oracle, Oracle gets called "non-standard". Is that the [quoted text clipped - 12 lines] > -- > David Portas Thanks - nobody ever managed to produce that quote during the course of the OTN debate and instead seemed to argue from what they were used to, then set theory, Codd and I think Aristotle were dragged in and things degenerated from there. I think however the above definition oversimplifies things by not mentioning nulls. In the case where a missing value stands for "unknown", its length is also unknown and should be reported as null, not 0. (I would expect this also to be true in set theory when we don't know what's in the set.) So now we are asking how or whether we should distinguish between "unknown" and "definitely no value" in the case of strings but not apparently dates, and all the standard can tell us is that string lengths start at 0. Yes that implies that we can use zero-length strings for "definitely no value" values, and that implies that nulls should be implemented differently and that we should have two kinds of emptiness as SQL Server does and Oracle does not, but I wish it would come right out and say so. Oracle could of course implement this by (finally) changing the reserved VARCHAR type, or perhaps inventing a VARCHAR3, but I'm hoping that day will never come as things will get massively more complicated. > I think however the above definition oversimplifies things by not > mentioning nulls. The SQL standard runs to more than 2000 pages and I think it has rarely been accused of oversimplifying things! Nulls are defined elsewhere. > In the case where a missing value stands for > "unknown", its length is also unknown and should be reported as null, [quoted text clipped - 7 lines] > should have two kinds of emptiness as SQL Server does and Oracle does > not, but I wish it would come right out and say so. I think you are missing the point. A zero-length string does not mean "definitely no value" and no such thing is implied. A zero-length string IS a value. Like all values it has the property of being equal to itself - a property that a null does not have because a null is not a value. As is to be expected, the standard is completely silent on any user- ascribed meaning for values. Nor does the standard generally make the mistake of saying that null is to be used to represent "unknown" (except in the unfortunate case of the Boolean type - but we don't need to deal with that in the present discussion). > Oracle could of course implement this by (finally) changing the > reserved VARCHAR type, or perhaps inventing a VARCHAR3, but I'm hoping > that day will never come as things will get massively more complicated. For those who use only the Oracle DBMS, yes. For those who require interoperability and portability things would be simplified. -- David Portas > > I think however the above definition oversimplifies things by not > > mentioning nulls. [quoted text clipped - 33 lines] > For those who use only the Oracle DBMS, yes. For those who require > interoperability and portability things would be simplified. Perhaps I should rephrase "definitely no value". I was trying to describe a value that is known to be absent, like my wife's middle name. It seems some non-Oracle applications make use of empty string semantics to distinguish between that and, say, mine if it hasn't been entered into the system. Although I take your point that the standard doesn't make recommendations on what meaning we should read into such values, nonetheless where it is implemented it would seem to leave us with two kinds of emptiness (assuming character values in general are allowed to be null) and an opportunity to make a business distinction between them. I'm still not convinced that my wife's middle name has a length of zero, or that it can't be said to come under the category of "not applicable", so I'm still not sure what useful purpose an empty but non-null string serves. Perhaps if I worked with SQL Server I would have a better idea of the typical uses for it. William Robertson wrote on 18.08.2007 11:19: > There was a long-running debate about this over on OTN forums last > year, and I tried to get the poster there to explain why he wasn't > concerned about "known no-value" DATEs when "known no-value" strings > seemed to drive him crazy, and never got a proper answer there either. I have never understood that distinction as well. Actually everybody (not only the poster on OTN and here) seems to be screaming about not having a known no-value vor strings but no one ever complained about dates or numbers (and no, I don't think zero is an approriate subsitution for '' in this example). I remember that discussion on OTN and I too wanted to have an answer to that question ;) Thomas > I have never understood that distinction as well. Actually everybody > (not only the poster on OTN and here) seems to be screaming about not [quoted text clipped - 3 lines] > I remember that discussion on OTN and I too wanted to have an answer to > that question ;) This is about elementary algebra: http://en.wikipedia.org/wiki/0_%28number%29 "In set theory, the number zero is the cardinality of the empty set: if one does not have any apples, then one has zero apples. In fact, in certain axiomatic developments of mathematics from set theory, zero is defined to be the empty set. When this is done, the empty set is the Von Neumann cardinal assignment for a set with no elements, which is the empty set. The cardinality function, applied to the empty set, returns the empty set as a value, thereby assigning it zero elements." Ultimately who have to back to Models and interpretations: http://en.wikipedia.org/wiki/Model_theory It's been 16 years since I went through this... suffice to say that: zero is the neutral element with respect to addition an empty string is the neutral element with respect to addition I'm not aware of a matching "date" algebra. When doing math with dates you need intervals. Intervals do have a neutral element. That would be an "instant" (?) I.e. an infinitely short duration. Note that zero is an "empty set". NULL is the absence of the set itself. Look at VARRAY. When you accept a string as a set of characters and you model that as a VARRAY you can clearly see the distinction between an empty VARRAY (of CHAR(1)) and a NULL VARRAY. Cheers Serge SignatureSerge Rielau DB2 Solutions Development IBM Toronto Lab Serge Rielau wrote on 18.08.2007 16:23: > Note that zero is an "empty set". NULL is the absence of the set itself. > Look at VARRAY. > When you accept a string as a set of characters and you model that as a > VARRAY you can clearly see the distinction between an empty VARRAY (of > CHAR(1)) and a NULL VARRAY. I completely agree with you, the explanations you give are pretty good. But still: why is everybody whining about not having this distinction with strings but no one ever complained about not having it for DATES? Why is one data type treated that differently (in terms of expectations) than the other? *That's* what I don't understand. I can fully understand that people might be confused about the unusual way (compared to other products) Oracle treats this. I have come to like it, I actually prefer writing WHERE a IS NULL instead of WHERE a = '' OR a IS NULL to find out those rows where no information is available :) Thomas > But still: why is everybody whining about not having this distinction > with strings but no one ever complained about not having it for DATES? Because they can. It is a case of painting the target after the arrow was fired. I belive Oracle defined NULL and zero-length strings before there was a standard. When competing vendors got together and created "the" standard no doubt some little bit of competitive was seen in defining the "standard" as they did. Oracle has had a clearly defined implementation long enough that it is what it is and for reasons of not breaking everything ever built I expect it will stay that way. One might speculate that after PeopleSoft, Siebel, JD Edwards, Stellant, Retek, and thousands more have this incorporated into their logic the standard will be the way Oracle is doing it as Ellison's boat runs on wind not whine. Wait ... isn't that DB2 (Viper) including new "compatibility" features? Why yes I think it is. <g> SignatureDaniel A. Morgan University of Washington damorgan@x.washington.edu (replace x with u to respond) Puget Sound Oracle Users Group www.psoug.org > Wait ... isn't that DB2 (Viper) including new "compatibility" features? > Why yes I think it is. <g> For the record: I did _not_ mention DB2... But since you bring it up... DB2 Viper 2 has indeed compatibility features. We are very careful though to allow customers to pick which features to enable. What's mathematically right (which is the debate started by _Oracle_users_ here) has little to do with what's popular (in the same sense as one-party elections popular). Further note that I at no point stated: "It's wrong because the SQL standard says so". W.r.t. the arrow and the target the underlying math is a tad older that Larry I dare say. The standard fixed his mathematical error. Sometimes Oracle chooses to follow (like on outer join syntax), sometimes not. You could say Oracle has shot their arrow, barely hit the target and over time redefined the bulls eye.... Lastly: I speak three languages, so does DB2 My national pride has not been hurt by learning English. My professional pride has not been hurt by teaching DB2 CONNECT BY and (+). In fact I have enjoyed it very much. I chuckle on both fronts... Cheers Serge SignatureSerge Rielau DB2 Solutions Development IBM Toronto Lab >> Wait ... isn't that DB2 (Viper) including new "compatibility" features? >> Why yes I think it is. <g> [quoted text clipped - 3 lines] > We are very careful though to allow customers to pick which features to > enable. Seems reasonable. Perhaps you could teach that trick to Microsoft. > What's mathematically right (which is the debate started by > _Oracle_users_ here) has little to do with what's popular (in the same > sense as one-party elections popular). The conversation was about strings ... not math. A zero byte string may or may not be NULL depending on the product. But there are no differences when it comes to math and dates. > Further note that I at no point stated: > "It's wrong because the SQL standard says so". No need to be defensive ... at least with respect to this thread. Anytime you want to bring DB2 to a single set of specifications for basics like the number of bytes in an object name though you can be as defensive as you wish. <g> > W.r.t. the arrow and the target the underlying math is a tad older that > Larry I dare say. The standard fixed his mathematical error. > Sometimes Oracle chooses to follow (like on outer join syntax), > sometimes not. > You could say Oracle has shot their arrow, barely hit the target and > over time redefined the bulls eye.... Math may be older than Larry ... he may have a different opinion. <g> But string concatenation and string length is not. It is a problem that is quite recent. > Lastly: > I speak three languages, so does DB2 > My national pride has not been hurt by learning English. But I wonder if it could survive my French? <g> > My professional pride has not been hurt by teaching DB2 CONNECT BY and > (+). In fact I have enjoyed it very much. > I chuckle on both fronts... > > Cheers > Serge Are you coming to OpenWorld? <g> SignatureDaniel A. Morgan University of Washington damorgan@x.washington.edu (replace x with u to respond) Puget Sound Oracle Users Group www.psoug.org >> Seems reasonable. Perhaps you could teach that trick to Microsoft. I came across this biased bull sh1t -> http://www.psoug.org/reference/sqlserver.html which I'm starting to readdress your obvious total lack of any experience or knowledge of SQL Server. Anyway, that aside, with have the configuration manager for one that enables step by step enabling of various features of the product via a GUI or through the command line via T-SQL. I know I'm talking to a wall while saying that but your answer may prove amusing. Tony. SignatureTony Rogerson, SQL Server MVP http://sqlblogcasts.com/blogs/tonyrogerson [Ramblings from the field from a SQL consultant] http://sqlserverfaq.com [UK SQL User Community] >>> Seems reasonable. Perhaps you could teach that trick to Microsoft. > [quoted text clipped - 11 lines] > > Tony. Any time you find something you don't agree with send me a correction and a link where it can be verified. Glad to fix it. SignatureDaniel A. Morgan University of Washington damorgan@x.washington.edu (replace x with u to respond) Puget Sound Oracle Users Group www.psoug.org Great, I'll send stuff over thru the next couple of weeks. Start with these two... "Translate character string using character set" We use CAST which I believe is ANSI standard as well; Oracle uses TREAT which I believe isn't ANSI standard. CAST( <expression> AS <type> ) "Table Level Triggers" "statement level" I don't believe Oracle has proper statement level triggers, by that I mean the new and old values for ALL rows modified by the statement are available. In SQL Server we have the inserted and deleted tables; I don't believe there is Oracle equiv that gives the complete set of rows as a table that are being inserted/deleted/updated. Row level triggers, we just use a cursor against the "inserted" and/or "deleted" system materialised views. INSTEAD OF is a before trigger in SQL Server. I'll send the rest via email once you have updated the current document to reflect the above. Many thanks, Tony. SignatureTony Rogerson, SQL Server MVP http://sqlblogcasts.com/blogs/tonyrogerson [Ramblings from the field from a SQL consultant] http://sqlserverfaq.com [UK SQL User Community] >>>> Seems reasonable. Perhaps you could teach that trick to Microsoft. >> [quoted text clipped - 14 lines] > Any time you find something you don't agree with send me a correction > and a link where it can be verified. Glad to fix it. > Great, I'll send stuff over thru the next couple of weeks. > [quoted text clipped - 6 lines] > > CAST( <expression> AS <type> ) What? <g> http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/functions016.htm# SQLRF00613 http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/functions198.htm# SQLRF06148 Please look at TREAT (second link) and explain what you intend. I am confused by what appears to be a misunderstanding of what TREAT is and does. Now look at this: http://msdn2.microsoft.com/en-us/library/ms187928.aspx CAST in Oracle and CAST in TransactSQL are different in what way? I am missing your point. > "Table Level Triggers" > "statement level" > > I don't believe Oracle has proper statement level triggers, by that I > mean the new and old values for ALL rows modified by the statement are > available. You are correct and I would really like to see this dealt with in 11gR2. But the point of my web page is not that each and every statement, left and right, has identical functionality. Were that the intent you couldn't equate anything between the products. The point of the page is to give students transitioning from one to the other an idea of what to look up. Anyone expecting identical functionality with anything more complex than SELECT * FROM t is going to be disappointed. BTW for anyone not following this thread the relevant link is: http://www.psoug.org/reference/sqlserver.html > Row level triggers, we just use a cursor against the "inserted" and/or > "deleted" system materialised views. Every product has workarounds. That isn't the point of my page. I teach at the University of Washington just two miles from the main Microsoft campus and many of my students, most actually, are experts on SQL Server or Sybase. The page is there to help them find rough equivalences. Nothing substitutes for reading the docs. > INSTEAD OF is a before trigger in SQL Server. Your point being? <g> If you look at my page I indicate that both products have instead-of triggers. I don't offer any information other than that. It is up to those interested to see in what ways they are similar and in what ways they differ. Be thankful I haven't upgraded the page from 10.2 to 11.1. When I do that the disparities with SS2005 grows larger still. Consider this for example. In a US medical environment with patient data HIPAA, federal law, requires that SELECT statements be audited. We have been able to do that in Oracle for years. In SQL Server it is still impossible. Something that won't be fixed until at least 2009. If I tried to put together a page of all differences I would need months just to catalog the differences. > I'll send the rest via email once you have updated the current document > to reflect the above. I really do appreciate it. But please include links. I need to be able to read the docs to verify statements. The one place I know I am weak is on OLAP functionality so if you know any of that please include it. Thanks. SignatureDaniel A. Morgan University of Washington damorgan@x.washington.edu (replace x with u to respond) Puget Sound Oracle Users Group www.psoug.org > Consider this for example. In a US medical environment with patient data > HIPAA, federal law, requires that SELECT statements be audited. At the risk of changing the topic.. You appear to be an expert on HIPAA. Does it actually state "SELECT statement"? Or does it say "data access"? Just wondering. You are always very SPECIFIC about exactly HOW any HIPAAA, SOX, .. requirement needs to be implemented. In my limited experience the HOW is typically open to interpretation (and rightly so)... Cheers Serge SignatureSerge Rielau DB2 Solutions Development IBM Toronto Lab >> Consider this for example. In a US medical environment with patient data >> HIPAA, federal law, requires that SELECT statements be audited. [quoted text clipped - 6 lines] > Cheers > Serge Every request for record(s), by who, to where, and which records were retrieved. That is always implemented as a SELECT somewhere in the database. It will likely be from a third-party application or a reporting tool such as Crystal, people rarely play with medical data using SQL*Plus, but it will be a SELECT and it must, by law, be audited in that the medical facility must be able to prove who had access to the data on a record-by-record, patient-by-patient basis. This is what the hospitals and medical centers out here are using as their internal guide. One medical center I work with has now enforced a printer access rule to keep unauthorized people from getting physical access to a printer that "might" contain patient information to which they are not entitled. In the US the rules are made by lawyers for the benefit of lawyers. And we've got a lot of 'em. SignatureDaniel A. Morgan University of Washington damorgan@x.washington.edu (replace x with u to respond) Puget Sound Oracle Users Group www.psoug.org > In the US the rules are made by lawyers for the benefit of lawyers. And > we've got a lot of 'em. Amen! SignatureSerge Rielau DB2 Solutions Development IBM Toronto Lab > Please look at TREAT (second link) and explain what you intend. I am > confused by what appears to be a misunderstanding of what TREAT is and > does. I quoted the wrong line. Your page says this "Change declared type of an expression" In Oracle you say use TREAT. In SQL Server, to "Change declared type of an expression" we would use CAST. So, please change your statement of "no equivalent" to "CAST" There are so many things wrong on your page is laughable, nor more than attempt by you to diss the product 'period', you've lost all objectivety; in fact, I may use it as a crib sheet to assit people migrating from Oracle to SQL Server - thanks; that will be useful for people. You say we don't have date arithmetic in one part of your page and then in another you say we do - make your mind up; you say we can't add months - we can, DATEADD deals with milisecond, second, minute, hour, day, month and year. You say we don't have hierarchical operators - we've implemented the SQL Standard CTE's. You say we don't have IN-OUT parameters, when we do - see CREATE PROC in bol. You say we don't have materialised views - we do, "indexed views". This is all literally in 5 minutes of scanning your page. This is all in public documentation, books online - check here: http://msdn2.microsoft.com/en-us/library/ms130214.aspx; out of interest, is there an Oracle equivalent where I can view your product documentation without installing/downloading anything? It will just help me to "correct" your diss... As soon as you correct your document to be factual around trigger usage then I'll help you "correct" your document with examples; I'll do that anyway on my blog - it will be a good source of content (thanks) - can I have your permission to use your original page? Finally, I'm not aware of a release in 2009, SQL Server 2008 (Katmai) yes, the launch event is spring next year (2008), probably rtm code available mid year - all public info. SignatureTony Rogerson, SQL Server MVP http://sqlblogcasts.com/blogs/tonyrogerson [Ramblings from the field from a SQL consultant] http://sqlserverfaq.com [UK SQL User Community] >> Great, I'll send stuff over thru the next couple of weeks. >> [quoted text clipped - 75 lines] > on OLAP functionality so if you know any of that please include it. > Thanks. Tony Rogerson wrote on 20.08.2007 19:26: > out of interest, is there an Oracle equivalent where I can view your product > documentation without installing/downloading anything? http://www.oracle.com/pls/db102/homepage A lot faster than msdn ;) Thomas Thanks Thomas. Not very intuative compared to the SQL Server books online and MSDN, no menu on the left so it's hard to find stuff with masses and masses of scrolling - oh well. It does give me a point of reference to more understand how DA has gone wrong on his dissing of SQL Server paper he's wrote. INSTEAD OF trigger makes a good read - only available on views and you can't write the old / new values; I guess that's another thing he'll have to change - SQL 2000 + has instead of triggers on either a view or a table and you can write to anything you want. Tony. SignatureTony Rogerson, SQL Server MVP http://sqlblogcasts.com/blogs/tonyrogerson [Ramblings from the field from a SQL consultant] http://sqlserverfaq.com [UK SQL User Community] > Tony Rogerson wrote on 20.08.2007 19:26: >> out of interest, is there an Oracle equivalent where I can view your [quoted text clipped - 4 lines] > > Thomas > Thanks Thomas. > [quoted text clipped - 20 lines] >> >> Thomas To the 20 or so previous posters: PLEASE TAKE A LOOK AT THE ORIGINAL POST... Shakespeare > Thanks Thomas. > [quoted text clipped - 11 lines] > > Tony. I find that menu on the left a bloody pain. But as long as you have something new to whine about ... enjoy this: http://www.oracle.com/pls/db111/homepage SignatureDaniel A. Morgan University of Washington damorgan@x.washington.edu (replace x with u to respond) Puget Sound Oracle Users Group www.psoug.org >> Please look at TREAT (second link) and explain what you intend. I am >> confused by what appears to be a misunderstanding of what TREAT is and [quoted text clipped - 10 lines] > > So, please change your statement of "no equivalent" to "CAST" You are still misreading the page. The entry for TREAT: "Change declared type of an expression" is under the heading String Handling Functions and has nothing to do with what CAST does. TREAT is used specifically to deal with changing the type of expression with the word "type" referring to user defined types. Under "Conversion Functions" I very clearly list Transact SQL's CONVERT and CAST as being roughly equivalent to Oracle's CAST. But this is using CAST as a conversion function which is different. Further down the page, under Date-Time you will note that I show the fact that TSQL's CAST can be used in a manner similar to Oracle's TO_DATE. > There are so many things wrong on your page is laughable, I'm not laughing and neither have a lot of people who work for Microsoft. So when you calm down send specific examples and links. > You say we don't have date arithmetic in one part of your page and then > in another you say we do - make your mind up; No I don't. At least not on the page that is on my website. I very clearly indicate that DATEADD is equivalent to +. And please knock of the "we" stuff. I'm not Oracle and you aren't Bill Gates. > You say we don't have hierarchical operators - we've implemented the SQL > Standard CTE's. And the link is where? > You say we don't have IN-OUT parameters, when we do - see CREATE PROC in > bol. "bol" being what? If you want me to fix the page try actually being helpful. > You say we don't have materialised views - we do, "indexed views". http://www.microsoft.com/technet/prodtechnol/sql/2005/impprfiv.mspx "With SQL Server 2000, the functionality of SQL Server views was expanded to provide system performance benefits. It is possible to create a unique clustered index on a view" "In SQL Server 2000 and 2005, a view that has a unique clustered index is referred to as an indexed view." and you think that is equivalent to this? CREATE MATERIALIZED VIEW mv_complete TABLESPACE uwdata REFRESH COMPLETE START WITH SYSDATE NEXT SYSDATE + 1 AS SELECT s.srvr_id, i.installstatus, COUNT(*) FROM servers s, serv_inst i WHERE s.srvr_id = i.srvr_id GROUP BY s.srvr_id, i.installstatus; and this is a very simplistic example. Want to discuss refresh groups? Want to discuss query rewrite? It is becoming increasingly obvious that other than being offended by the appearance of dissing SQL Server you really don't know the product as well as you think you do. And certainly not nearly as well as do most of my students. > As soon as you correct your document to be factual around trigger usage > then I'll help you "correct" your document with examples; Correct what? I see nothing to correct. You made a comment about instead-of triggers irrelevant to what is on the page. And your point about statement triggers is that they are different. Whoopee. COMMIT is different too. Want me to point say that SQL Server has no equivalent to Oracle's COMMIT WRITE WAIT BATCH? I will make changes were there are things that are factually incorrect. I will not make superfluous changes or changes that alter the nature of why the page was created and its value to my students. So far you are batting 0 for 2. SignatureDaniel A. Morgan University of Washington damorgan@x.washington.edu (replace x with u to respond) Puget Sound Oracle Users Group www.psoug.org Like I said, the public documentation is here: http://msdn2.microsoft.com/en-us/library/ms130214.aspx; Like I said, you've lost all objectivety and are 100% blinded by bias. An example is the triggers - you state no equiv to before statement, when in fact you don't actually do real statement triggers..., an example is the Indexed Views which are the equiv offering of materialised view not the "no equiv" you state. Anyway, your biased rubbish gives me some content for a number of blog posts in the coming weeks - thanks. Like I said, it's like talking to a brick wall you; the page would be a good point of reference if you actually took the time to do an unbiased review. SignatureTony Rogerson, SQL Server MVP http://sqlblogcasts.com/blogs/tonyrogerson [Ramblings from the field from a SQL consultant] http://sqlserverfaq.com [UK SQL User Community] > Like I said, the public documentation is here: > http://msdn2.microsoft.com/en-us/library/ms130214.aspx; [quoted text clipped - 3 lines] > An example is the triggers - you state no equiv to before statement, > when in fact you don't actually do real statement triggers..., So if you don't think this is a statement trigger then tell me precisely what it is? CREATE OR REPLACE TRIGGER test BEFORE UPDATE ON orders DECLARE vMsg VARCHAR2(30) := 'Trigger Fired'; BEGIN dbms_output.put_line(vMsg); END statement_level; / If I update 1 row in the table it fires once. If I update 1,000,000,000 rows in the table it fires once. Stop, for a second, throwing mud and do something constructive. Tell me what this trigger is? And while you are doing that read this: http://download.oracle.com/docs/cd/B14117_01/server.101/b10759/ap_standard_sql001.htm Look I don't want to play some game with you but please take a second to review what I do for a living. You can't baffle me with BS so if you want to make a point do some research and learn something about the subject. There is one part of the ANSI definition of statement level triggers that Oracle doesn't support ... that being the :OLD and :NEW tables. That does not make the trigger something other than a statement level trigger. I am trying very hard to take you seriously but so far you've done little to support that proposition. SignatureDaniel A. Morgan University of Washington damorgan@x.washington.edu (replace x with u to respond) Puget Sound Oracle Users Group www.psoug.org > Look I don't want to play some game with you but please take a second to > review what I do for a living. You can't baffle me with BS so if you > want to make a point do some research and learn something about the > subject. RFLOL You do Oracle for a living, not SQL Server; your page is so biased and unprofessional, it makes you look like a little kid; do you really think that if you say something long enough it will become true? Let's look at other stuff you'll probably also stick your head in the sand with.... "System Triggers" and your statement "no equiv" CREATE TRIGGER safety ON DATABASE FOR DROP_TABLE, ALTER_TABLE AS PRINT 'You must disable Trigger "safety" to drop or alter tables!' ROLLBACK ; The above is an example of a DATABASE scoped trigger for the events DROP_TABLE and ALTER_TABLE. HERE IS THE REFERENCE: http://msdn2.microsoft.com/en-us/library/ms189799.aspx Here is one for SERVER scoped AFTER LOGON where you also say there is no equiv... CREATE TRIGGER connection_limit_trigger ON ALL SERVER WITH EXECUTE AS 'login_test' FOR LOGON AS BEGIN IF ORIGINAL_LOGIN()= 'login_test' AND (SELECT COUNT(*) FROM sys.dm_exec_sessions WHERE is_user_process = 1 AND original_login_name = 'login_test') > 3 ROLLBACK; END; I'll not continue, best I save my effort for my blog so I can go into detail. Thanks for the amusement though DA, does the DA stand for Denial Again with a slient 'in'? SignatureTony Rogerson, SQL Server MVP http://sqlblogcasts.com/blogs/tonyrogerson [Ramblings from the field from a SQL consultant] http://sqlserverfaq.com [UK SQL User Community] >> Look I don't want to play some game with you but please take a second to >> review what I do for a living. You can't baffle me with BS so if you [quoted text clipped - 9 lines] > Let's look at other stuff you'll probably also stick your head in the > sand with.... No. Lets stick with the fact that so far everything you've written has been incorrect as proven by Microsoft's own documentation and as I am about to demonstrate again below. > "System Triggers" and your statement "no equiv" > [quoted text clipped - 5 lines] > ROLLBACK > ; Brilliant except that what you just wrote is NOT a system trigger. If you look under DDL Triggers (DROP and ALTER TABLE are DDL) you will find that I have it absolutely correct. Look I'd really like to humor you but this nonsense is totally off-topic and your rant is about my web page and that has nothing to do with c.d.o.server. Also, it seems pointless to contribute to your further attempts to dig that hole you are in any deeper. The page is again proven correct and you are again shown to be displaying your ignorance of both Oracle and SQL Server. So either get on-topic or contact me off-line: Otherwise you will be ignored. SignatureDaniel A. Morgan University of Washington damorgan@x.washington.edu (replace x with u to respond) Puget Sound Oracle Users Group www.psoug.org I see you chose to ignore this one as well... CREATE TRIGGER connection_limit_trigger ON ALL SERVER WITH EXECUTE AS 'login_test' FOR LOGON AS BEGIN IF ORIGINAL_LOGIN()= 'login_test' AND (SELECT COUNT(*) FROM sys.dm_exec_sessions WHERE is_user_process = 1 AND original_login_name = 'login_test') > 3 ROLLBACK; END; We use CREATE TRIGGER, there are hundreds of events that are captured using the CREATE TRIGGER, LOGON is just one of many 'system' level events. I'll make it easy, here is the reference now update your biased rubbish: http://msdn2.microsoft.com/en-us/library/ms189799.aspx The fact you don't want to says it all, SignatureTony Rogerson, SQL Server MVP http://sqlblogcasts.com/blogs/tonyrogerson [Ramblings from the field from a SQL consultant] http://sqlserverfaq.com [UK SQL User Community] >> Like I said, the public documentation is here: >> http://msdn2.microsoft.com/en-us/library/ms130214.aspx; [quoted text clipped - 36 lines] > trigger. I am trying very hard to take you seriously but so far you've > done little to support that proposition. Will the trigger fire if no row is updated? A statement trigger should (because the statement executed) It is my understanding that in Oracle a statement trigger is a row-trigger than fires only for the first row... Not taking sides here btw. SQL Servers INSTEAD OF triggers on tables are just a workaround for before triggers and I'd love to see how a SQL Server statement trigger performs row-actions when the primary key was updated (lining up those INSERTED and DELETED rows may prove difficult...). Having to do a JOIN on the transition tables to get to rows is just nastily inefficient and can be outright wrong. Cheers Serge SignatureSerge Rielau DB2 Solutions Development IBM Toronto Lab > Server statement trigger performs row-actions when the primary key was > updated (lining up those INSERTED and DELETED rows may prove > difficult...). Having to do a JOIN on the transition tables to get to rows > is just nastily inefficient and can be outright wrong. A reason why the majority of SQL Server folks use a surrogate key in way of IDENTITY - imutable. SignatureTony Rogerson, SQL Server MVP http://sqlblogcasts.com/blogs/tonyrogerson [Ramblings from the field from a SQL consultant] http://sqlserverfaq.com [UK SQL User Community] > > Server statement trigger performs row-actions when the primary key was > > updated (lining up those INSERTED and DELETED rows may prove [quoted text clipped - 8 lines] > [Ramblings from the field from a SQL consultant]http://sqlserverfaq.com > [UK SQL User Community] Ah, that is why you like those so much Tony. You are forced to use a crutch and have grown to like it. You have my deepest sympathies. Ed > Ah, that is why you like those so much Tony. You are forced to use a > crutch and have grown to like it. You have my deepest sympathies. Unlike yourself Ed I realise the natural key may change; and unlike yourself Ed I prefer to protect myself from the concurrency problem caused by a disconnected application archiecture - basically, the web broweser as an example; surrogate keys are a known, tried and tested solution for that. That class room door you hide behind needs oiling. SignatureTony Rogerson, SQL Server MVP http://sqlblogcasts.com/blogs/tonyrogerson [Ramblings from the field from a SQL consultant] http://sqlserverfaq.com [UK SQL User Community] >> Ah, that is why you like those so much Tony. You are forced to use a >> crutch and have grown to like it. You have my deepest sympathies. > > Unlike yourself Ed I realise the natural key may change; That statement just earned you a place in my class at the university. Would you like to reconsider it before you earn a place of infamy? Natural keys can not change. If it can change IT IS NOT A KEY it is just a value. Good grief! > and unlike > yourself Ed I prefer to protect myself from the concurrency problem [quoted text clipped - 3 lines] > > That class room door you hide behind needs oiling. Look who's talking. And while you are reconsidering, and you should, your response to Ed consider this: ID FNAME LNAME 1 Daniel Morgan 2 Jack Cline 3 Daniel Morgan That surrogate key accomplished precisely what? Amazing! SignatureDaniel A. Morgan University of Washington damorgan@x.washington.edu (replace x with u to respond) Puget Sound Oracle Users Group www.psoug.org > That statement just earned you a place in my class at the university. > Would you like to reconsider it before you earn a place of infamy? > Natural keys can not change. If it can change IT IS NOT A KEY it is > just a value. These are some examples of a natural key that have changed in the past 10 years ... ISBN number ISO country code Telephone number here in London if you where stupid enough to have used it as a natural key (it is afterall unique). Country name if you where stupid enough not to use the ISO code, how many country name changes have there been in the last 10 years? > Good grief! Yes, I'm shocked you didn't know these industry keys you should be using as your natural keys in database design have changed. > ID FNAME LNAME > 1 Daniel Morgan > 2 Jack Cline > 3 Daniel Morgan > > That surrogate key accomplished precisely what? And where is your natural key? Also, you obviously haven't worked or designed a CRM system where the above happens except you have other attributes to better identify the entity you've modelled, but again - you'd have a form of natural key or if not you'd have to make one using an artifical key in which case you'd probably not need a surrogate because the artificial would not change. Let me pose a question [QUESTION TO ANSWER SO NO DISTRACTION AND IGNORING IT]. Given natural keys change, eg. ISBN and ISO country code. If the user browses to your site and gets the data then he currently holds the natural key 12345 which is the link back into the database and data; the user is disconnected (it's a browser - IE for instance), you have not used a surrogate key - just a natural key. How on earth do you reconnect the user with the data, which in the database the natural key changed to 12345-02? The answer is you can't without using a surrogate key, or is there some magic I don't know about - remember, without using a surrogate key? All you have shown here is yet another example that you are an objectional idiot who is more bent on being 'right' in his own mind then being 'right' along with his peers; the page where you inaccurately rubbish SQL Server v Oracle is also an example. If you didn't know natural keys changed then what are you doing teaching? Obviously you have just learn't syntax and have no real industrial experience like somebody else I know. PS. I'm awaiting your email reply to why this isn't an equiv "system trigger" in SQL Server compared to your Oracle LOGON trigger.... Link: http://technet.microsoft.com/en-us/library/ms189799.aspx CREATE TRIGGER connection_limit_trigger ON ALL SERVER WITH EXECUTE AS 'login_test' FOR LOGON AS BEGIN IF ORIGINAL_LOGIN()= 'login_test' AND (SELECT COUNT(*) FROM sys.dm_exec_sessions WHERE is_user_process = 1 AND original_login_name = 'login_test') > 3 ROLLBACK; END; SignatureTony Rogerson, SQL Server MVP http://sqlblogcasts.com/blogs/tonyrogerson [Ramblings from the field from a SQL consultant] http://sqlserverfaq.com [UK SQL User Community] >>> Ah, that is why you like those so much Tony. You are forced to use a >>> crutch and have grown to like it. You have my deepest sympathies. [quoted text clipped - 26 lines] > > Amazing! >> ID FNAME LNAME >> 1 Daniel Morgan [quoted text clipped - 4 lines] > > And where is your natural key? The point was to demonstrate the surrogate keys are worthless for purposes of data integrity with most data. What ends up happening is that unique constraints are then built, on top of the surrogate key, in an attempt to do what the surrogate can not. SignatureDaniel A. Morgan University of Washington damorgan@x.washington.edu (replace x with u to respond) Puget Sound Oracle Users Group www.psoug.org > >> ID FNAME LNAME > >> 1 Daniel Morgan [quoted text clipped - 15 lines] > damor...@x.washington.edu (replace x with u to respond) > Puget Sound Oracle Users Groupwww.psoug.org Could you clowns just take this offline? Ok you have both already proven you are both just incredibly brilliant people. We get it already ... > Could you clowns just take this offline? > > Ok you have both already proven you are both just incredibly brilliant > people. We get it already ... Just put me on your spam list or don't read my post, this is afterall a NNTP usenet forum which by it's very protocol is threaded. Stop trying to dictate who posts and what they post to what is a public forum. You have a choice, choose not to read my post - period. SignatureTony Rogerson, SQL Server MVP http://sqlblogcasts.com/blogs/tonyrogerson [Ramblings from the field from a SQL consultant] http://sqlserverfaq.com [UK SQL User Community] > Could you clowns just take this offline? > > Ok you have both already proven you are both just incredibly brilliant > people. We get it already ... Thank you Mr. Moderator. <g> SignatureDaniel A. Morgan University of Washington damorgan@x.washington.edu (replace x with u to respond) Puget Sound Oracle Users Group www.psoug.org > The point was to demonstrate the surrogate keys are worthless > for purposes of data integrity with most data. What ends up > happening is that unique constraints are then built, on top of > the surrogate key, in an attempt to do what the surrogate can > not. Oh really? Then why say this "Natural keys can not change. If it can change IT IS NOT A KEY it is just a value.". You seem to be under the impression that "Natural keys can not change". On another point, I'm STILL awaiting your email reply to why this isn't an equiv "system trigger" in SQL Server compared to your Oracle LOGON trigger.... Link: http://technet.microsoft.com/en-us/library/ms189799.aspx CREATE TRIGGER connection_limit_trigger ON ALL SERVER WITH EXECUTE AS 'login_test' FOR LOGON AS BEGIN IF ORIGINAL_LOGIN()= 'login_test' AND (SELECT COUNT(*) FROM sys.dm_exec_sessions WHERE is_user_process = 1 AND original_login_name = 'login_test') > 3 ROLLBACK; END; SignatureTony Rogerson, SQL Server MVP http://sqlblogcasts.com/blogs/tonyrogerson [Ramblings from the field from a SQL consultant] http://sqlserverfaq.com [UK SQL User Community] >>> ID FNAME LNAME >>> 1 Daniel Morgan [quoted text clipped - 10 lines] > the surrogate key, in an attempt to do what the surrogate can > not. > Then why say this "Natural keys can not change. If it can change IT IS > NOT A KEY it is just a value.". > > You seem to be under the impression that "Natural keys can not change". If it CAN change it is NOT a key. That is the definition. Let me give you an example. From the standpoint of my bank and my bank account number ... they can never change. I may close an account ... I may open an account. But each account is uniquely defined by that account number. If you need additional help with the concept please ask. SignatureDaniel A. Morgan University of Washington damorgan@x.washington.edu (replace x with u to respond) Puget Sound Oracle Users Group www.psoug.org > If it CAN change it is NOT a key. That is the definition. Let me give > you an example. That's just Oracle speek in the Oracle environment because your candidate key cannot be changed -> http://orafaq.com/node/24. It's not how the real world works which brings us full circle back to the original thread - NULL behaviour, I love your comment about a proper design having all NOT NULL to get round the weakness in the product; that's just not possible in the real world. > If you need additional help with the concept please ask. No need and believe me it wouldn't be you, you appear to have problems with understanding database theory, that's the problem teaching syntax day in day out. SignatureTony Rogerson, SQL Server MVP http://sqlblogcasts.com/blogs/tonyrogerson [Ramblings from the field from a SQL consultant] http://sqlserverfaq.com [UK SQL User Community] >> If it CAN change it is NOT a key. That is the definition. Let me give >> you an example. > > That's just Oracle speek in the Oracle environment because your > candidate key cannot be changed -> http://orafaq.com/node/24. No that is university level database speak for students studying the work of Date, Codd, and Celko. Where did you receive your degree in computer science? SignatureDaniel A. Morgan University of Washington damorgan@x.washington.edu (replace x with u to respond) Puget Sound Oracle Users Group www.psoug.org > > If it CAN change it is NOT a key. That is the definition. Let me give > > you an example. > > That's just Oracle speek in the Oracle environment because your candidate > key cannot be changed ->http://orafaq.com/node/24. Tony, try dropping into comp.database.theory I doubt you will last long in that forum. > It's not how the real world works which brings us full circle back to the > original thread - NULL behaviour, I love your comment about a proper design > having all NOT NULL to get round the weakness in the product; that's just > not possible in the real world. Earlier I asked this question: If I have a user filling out an electronic form and they enter nothing into one of the fields (they TAB right past it), I would load a NULL in the DB. Would you really try loading an empty string? (a related question: How would you fill out the form on paper?) The issue of not having a middle name and not having the middle name entered on a form (ie NULL) is a special case. Names are a very special case. The entire name ideally should be entered into one field. What we call Last names are really Surnames. For example in oriental culture it is often the Surname that comes FIRST. And the use of shortened versions (Ed Edward, Eddie) and spelling/pronunciation variations (Eduard, others?) makes names a poor choice for PK and other DB concept discussions. (but you think you have all this solved by using surrogate keys. enjoy your bliss until the day an update programming error happens and knocks some of your keys out of sync. Hopefully you find out the day it happens and can recover.) > > If you need additional help with the concept please ask. > > No need and believe me it wouldn't be you, you appear to have problems with > understanding database theory, that's the problem teaching syntax day in day > out. On the contrary you seem to have the problem with understanding relational DB theory. That's the problem with only coding all day and not getting out to talk (just talk, not argue) to other DB people using other products. You need to learn to not be so dogmatic. Ed > > > If it CAN change it is NOT a key. That is the definition. Let me give > > > you an example. [quoted text clipped - 6 lines] > try dropping into comp.database.theory > I doubt you will last long in that forum. :-) http://groups.google.com/group/comp.databases.theory/msg/24a358b989f29903?dmode= source jg -- @home.com is bogus. How about some aversion therapy? http://www.signonsandiego.com/uniontrib/20070827/news_1n27benny.html PS. I'm awaiting your email reply to why this isn't an equiv "system trigger" in SQL Server compared to your Oracle LOGON trigger.... Link: http://technet.microsoft.com/en-us/library/ms189799.aspx CREATE TRIGGER connection_limit_trigger ON ALL SERVER WITH EXECUTE AS 'login_test' FOR LOGON AS BEGIN IF ORIGINAL_LOGIN()= 'login_test' AND (SELECT COUNT(*) FROM sys.dm_exec_sessions WHERE is_user_process = 1 AND original_login_name = 'login_test') > 3 ROLLBACK; END; Note - it's not a DDL trigger - no DATA DEFINITION in there; it's a LOGON trigger. What's up DA? in Denial Again? Do you really want to make your SQL - ORacle comparison page accurate? [I guess not] SignatureTony Rogerson, SQL Server MVP http://sqlblogcasts.com/blogs/tonyrogerson [Ramblings from the field from a SQL consultant] http://sqlserverfaq.com [UK SQL User Community] >> Then why say this "Natural keys can not change. If it can change IT IS >> NOT A KEY it is just a value.". [quoted text clipped - 9 lines] > > If you need additional help with the concept please ask. > PS. I'm awaiting your email reply to why this isn't an equiv "system > trigger" in SQL Server compared to your Oracle LOGON trigger.... [quoted text clipped - 18 lines] > What's up DA? in Denial Again? Do you really want to make your SQL - > ORacle comparison page accurate? [I guess not] The page has been updated. Thanks. Now stuff the attitude it is really quite unflattering. When I find errors I make corrections. The attitude is suitable for a teenager not an adult professional. SignatureDaniel A. Morgan University of Washington damorgan@x.washington.edu (replace x with u to respond) Puget Sound Oracle Users Group www.psoug.org At last, given the attitude you have to get this one corrected I guess the dozens of other errors won't get fixed, here are another two random ones... One character set to another no equivalent ORacle uses CONVERT SQL Server uses COLLATE -> <character data> COLLATE <new collation> Unicode string to a string no equivalent DECOMPOSE We use CAST( <unicode> AS <ascii> ) eg. CAST( N'asadasasd' AS varchar(50) ) Infact many of your 'Conversion Functions' are tackled with CAST. Anyway, you grow tiresome; I'll just work through the corrections myself and post it up when I've finished. Where on earth did you learn SQL Server from? A packet of corn flakes? SignatureTony Rogerson, SQL Server MVP http://sqlblogcasts.com/blogs/tonyrogerson [Ramblings from the field from a SQL consultant] http://sqlserverfaq.com [UK SQL User Community] > At last, given the attitude you have to get this one corrected I guess > the dozens of other errors won't get fixed, here are another two random [quoted text clipped - 3 lines] > ORacle uses CONVERT > SQL Server uses COLLATE -> <character data> COLLATE <new collation> Again no links. Which of these definitions supports your statement? http://msdn2.microsoft.com/en-us/library/aa258237(SQL.80).aspx (do not respond here) > Unicode string to a string no equivalent DECOMPOSE > We use CAST( <unicode> AS <ascii> ) eg. CAST( N'asadasasd' AS > varchar(50) ) We? You are not Bill Gates and you are not a Microsoft employee and this is NOT a SQL Server forum. Take it off-line. I will no longer respond here as you are totally off-topic. And again ... if you respond do it off-line as this is not the proper place for you to vent your angst about my webpage: Last warning. SignatureDaniel A. Morgan University of Washington damorgan@x.washington.edu (replace x with u to respond) Puget Sound Oracle Users Group www.psoug.org >> One character set to another no equivalent >> ORacle uses CONVERT [quoted text clipped - 3 lines] > http://msdn2.microsoft.com/en-us/library/aa258237(SQL.80).aspx > (do not respond here) <Quote from books online from the link you provided> Casting the collation of an expression. You can use the COLLATE clause to cast a character expression to a certain collation. Character literals and variables are assigned the default collation of the current database. Column references are assigned the definition collation of the column. For the collation of an expression, see Collation Precedence. </> We don't call them character sets, we call them collations - same thing just called differently - why didn't you know that, you've created this wonderful you feel 100% accurate page and you don't know these fundementals? >> Unicode string to a string no equivalent DECOMPOSE >> We use CAST( <unicode> AS <ascii> ) eg. CAST( N'asadasasd' AS >> varchar(50) ) Again, update your document - it's so full of holes. And I use "We" in the context of "We who use SQL Server", using "We" makes sense because there is more than one and I'm in a collective group.... SignatureTony Rogerson, SQL Server MVP http://sqlblogcasts.com/blogs/tonyrogerson [Ramblings from the field from a SQL consultant] http://sqlserverfaq.com [UK SQL User Community] >And I use "We" in the context of "We who use SQL Server", using "We" makes >sense because there is more than one and I'm in a collective group.... You mean you are part of a group of arrogant trolls 'promoting' sqlserver this way? SignatureSybrand Bakker Senior Oracle DBA >> And I use "We" in the context of "We who use SQL Server", using "We" makes >> sense because there is more than one and I'm in a collective group.... > > You mean you are part of a group of arrogant trolls 'promoting' > sqlserver this way? Interesting ... "We" who use Oracle aren't so rude as to go to the SQL Server usenet group and behave in such an uncivilized manner. I appreciate that Microsoft for years has taught, by example, bad coding practices. I wasn't aware that bad manners were part of it too until I remembered that little chair throwing incident of Steve Ballmers. I guess some people learn best by example. SignatureDaniel A. Morgan University of Washington damorgan@x.washington.edu (replace x with u to respond) Puget Sound Oracle Users Group www.psoug.org > Interesting ... "We" who use Oracle aren't so rude as to go to the > SQL Server usenet group and behave in such an uncivilized manner. > I appreciate that Microsoft for years has taught, by example, bad > coding practices. I wasn't aware that bad manners were part of it too > until I remembered that little chair throwing incident of Steve Ballmers. Interesting, "We" in the SQL Server usenet group treat people with respect unless they don't deserve it and you can count them on one hand, I notice it's just you and sybrand that have an unprofessional bully attitude on this forum. Other people as well as myself notice just how rude, arrogant and condecending you and sybrand are too people who post to c.o.s. I also think it's highly unprofessional for somebody (you) to post a product comparison that is so far from accurate and so full of mistakes and when people who are recognised product experts in the SQL Server field (whether you like it or not) point out the faults you provide a kidified response and put your head in the sand - very unprofessional, very childish and speaks volumes for the type of person you are and the obvious biased approach you take to the database field in general, no wonder you can only get a job in education, nobody in industry would touch you. > I guess some people learn best by example. Yes, and some people just don't learn or accept reality is reality even when shown a reference. SignatureTony Rogerson, SQL Server MVP http://sqlblogcasts.com/blogs/tonyrogerson [Ramblings from the field from a SQL consultant] http://sqlserverfaq.com [UK SQL User Community] >> The point was to demonstrate the surrogate keys are worthless >> for purposes of data integrity with most data. What ends up [quoted text clipped - 7 lines] > NOT A KEY it is > just a value.". You seem to have it backwards - the mantra is "if keys can change, it is likely an attribute, not a key". You prove Daniels point, by demonstrating natural keys change (and *thus* *should* not have been used as keys) > You seem to be under the impression that "Natural keys can not change". The should not, when used in programs. This is different from the fact they change. Loosely quoted, you stated "ISO country codes are not keys, as they change". Well - if you knew that from the start (and you should) - if used in a system, that is not expected to cope with those changes, it is acceptable. For Customs, I'd advise not to use it as a key, but just as a value. It seems you gentlemen are in agreement, you are just looking at the question at hand from two different angles. - -- Regards, Frank van Bortel Top-posting is one way to shut me up... > Loosely quoted, you stated "ISO country codes are not keys, as > they change". Loosely? Made up more like. I said and I quote properly... <START QUOTE> "These are some examples of a natural key that have changed in the past 10 years ... ISBN number ISO country code " <END QUOTE> And you added this "are not keys, as they change" People tell us to use industry standard codes for natural keys - aka country, isbn etc... these have changed, unless you used a surrogate or artifical key of your own then you had to change the data. Consider a book 'The XYZ of blah', pre ISBN change it might of been x123456 and post change it is now both x123456 and z654321 - the single title "The XYZ of blah" has two natural keys (two uniquely identifyable, verifyable keys). DA stated this <START QUOTE> Natural keys can not change. If it can change IT IS NOT A KEY it is just a value. <END QUOTE> The Natural key to a book is it's ISBN number, that has changed over the past 10 years. The only thing I can interprit from this is that you guys create your own artifical key probably based on a auto incrementing number or do you like the rest of use correctly use ISBN as the natural key but create a surrogate as well and use the surrogate for table and application plumbing (foreign keys, internal values passed between the app and the db - eg. the VALUE property of the <SELECT> HTML tag. SignatureTony Rogerson, SQL Server MVP http://sqlblogcasts.com/blogs/tonyrogerson [Ramblings from the field from a SQL consultant] http://sqlserverfaq.com [UK SQL User Community] > People tell us to use industry standard codes for natural keys - aka And you believe them? Time to do your own thinking again - -- Regards, Frank van Bortel Top-posting is one way to shut me up... >> People tell us to use industry standard codes for natural keys - aka > > And you believe them? > > Time to do your own thinking again I see you are self taught. SignatureTony Rogerson, SQL Server MVP http://sqlblogcasts.com/blogs/tonyrogerson [Ramblings from the field from a SQL consultant] http://sqlserverfaq.com [UK SQL User Community] >>> People tell us to use industry standard codes for natural keys - aka >> [quoted text clipped - 3 lines] > > I see you are self taught. If true: Better than untaught. SignatureDaniel A. Morgan University of Washington damorgan@x.washington.edu (replace x with u to respond) Puget Sound Oracle Users Group www.psoug.org >>>> People tell us to use industry standard codes for natural keys - aka >>> [quoted text clipped - 5 lines] > > If true: Better than untaught. By an unteacher? Shakespeare (Climbing in the ranks of frequent posters in this group .... by following the leaders adding non-contributing contributions in discussions.... ;-)))))) > The Natural key to a book is it's ISBN number, that has changed over the > past 10 years. No. If the key changes, it is a new book! Keys can not change! (Want a new car? Get a new licence plate!) I worked for a school that changed their codes for certain classes, which where used as keys in a (gov) reporting system. Now they are confonted with the fact that over 50% of their students do not graduate.... because no one graduated on the old coded classes. Cost them a lot of money and their good name. So far for changing natural keys! Shakespeare (What's in a key?) > No. If the key changes, it is a new book! Keys can not change! (Want a new > car? Get a new licence plate!) So, a membership database - my user group (sqlserverfaq.com) for instance; I only capture this information because it's all I need and by law in the UK I can only capture what I need... Full Name Email Address Company Country of residence What's the natural key? Email Address is unique at a point in time. Are you trying to tell me email address does not change? What happens when you move companies? You'd want to update your email address, in your data model you aren't allowed to do that so I guess you can't use Oracle. What I do to get round this problem is to create a surrogate key - an auto generating number, that is fixed and never changes. The natural key is still email address. What is your solution? Is it an artifical key? But, why? The natural key (email) can only be unique, you cannot have two members with the same email address. Or, would you create a composite key of start_date, end_date, email_address? Wow, the whole world suddenly gets more complicated then it needs to be. SignatureTony Rogerson, SQL Server MVP http://sqlblogcasts.com/blogs/tonyrogerson [Ramblings from the field from a SQL consultant] http://sqlserverfaq.com [UK SQL User Community] >> No. If the key changes, it is a new book! Keys can not change! (Want a >> new car? Get a new licence plate!) In the state I live in, when you get a new car, you put your old license plates on the new car (assuming you traded your old car in on the new purchase). > So, a membership database - my user group (sqlserverfaq.com) for > instance; I only capture this information because it's all I need and by [quoted text clipped - 13 lines] > your data model you aren't allowed to do that so I guess you can't use > Oracle. Don't let the politician's in the US hear about this one. Next thing you know, they'll pass a law that says you can take your email address with you...similar to the law for cell phone numbers. Cheers, Brian Signature=================================================================== Brian Peasland dba@nospam.peasland.net http://www.peasland.net Remove the "nospam." from the email address to email me. "I can give it to you cheap, quick, and good. Now pick two out of the three" - Unknown -- Posted via a free Usenet account from http://www.teranews.com >>> No. If the key changes, it is a new book! Keys can not change! (Want a >>> new car? Get a new licence plate!) > > In the state I live in, when you get a new car, you put your old license > plates on the new car (assuming you traded your old car in on the new > purchase). Then it must be the same car! ;-) (In my country, plates belong to the car, not to the driver and stay on the car when sold) >> So, a membership database - my user group (sqlserverfaq.com) for >> instance; I only capture this information because it's all I need and by [quoted text clipped - 16 lines] > know, they'll pass a law that says you can take your email address with > you...similar to the law for cell phone numbers. Yeah, Transportable Email Addresses! By the way, this is a real problem: I registered for some sites, support etc. with my company email address and switched jobs. Lost all my electronic certificates on Oracle Education for example..... I hate sites using email address as a user id. Shakespeare > Cheers, > Brian > >>> No. If the key changes, it is a new book! Keys can not change! (Want a > >>> new car? Get a new licence plate!) [quoted text clipped - 5 lines] > Then it must be the same car! ;-) (In my country, plates belong to the > car, not to the driver and stay on the car when sold) In California it works both ways. If you just get a regular series license plate, it goes with the car. If you get a special series, such as a vanity plate (a number of varieties), handicapped, environmental, olympic, horseless carriage (my friends have one on a '67 big-block Vette), shortwave radio (and a bunch of others), you own the plate and it goes with you. With a more recently enacted exception about keeping plates not on a car (some people were hoarding, just as some people sat on domain names). And you can also buy old plates and put them on cars, as long as you have a pair. Oddly, it is illegal to restore damaged plates, although there are a number of businesses that do. All these things and more helped contribute to a famous high-cost computer upgrade failure. Later, there was a scandal because Oracle wanted to sell a site-license to the entire state government (which would have been a great idea, in my opinion). > >> So, a membership database - my user group (sqlserverfaq.com) for > >> instance; I only capture this information because it's all I need and by [quoted text clipped - 22 lines] > certificates on Oracle Education for example..... I hate sites using email > address as a user id. Funnily enough, my @home address was given to me years ago when I signed up for cable, and then went away when the home.com domain went to Japan. I was too stubborn to stop using it... :-) Since I have my own domains, I've taken to signing up for things with the name of the site or worse. That really confuses support people at times. Even my latest SR had a comment about a bad email address... Oracle seems to be able to send some things to my domain but not others. And I just caught costco spamming long after unsubscribing from something, I know because I used fuckyou in the address. After complaining, I got a "Dear fuckyou" letter from their support. jg -- @home.com is bogus. "Royalty check is here, honey!" - Billy the Mountain by Frank Zappa http://www.signonsandiego.com/uniontrib/20070831/news_1b31tech.html >> No. If the key changes, it is a new book! Keys can not change! (Want a >> new car? Get a new licence plate!) [quoted text clipped - 28 lines] > email_address? Wow, the whole world suddenly gets more complicated then it > needs to be. Hope you did not miss the ironic character of my answer .... Shakespeare >> The Natural key to a book is it's ISBN number, that has changed over the >> past 10 years. > > No. If the key changes, it is a new book! Keys can not change! (Want a new > car? Get a new licence plate!) Depends on where you live - our neighbors eastward have a personal plate, it moves with *you* not the car. Love to live in the county of Borken and get me a "TEL-1" plate. Some sources say, our government is contemplating a similar move. - -- Regards, Frank van Bortel Top-posting is one way to shut me up... PS - the plate would read BOR-TEL-1 > -----BEGIN PGP SIGNED MESSAGE----- > Hash: SHA1 [quoted text clipped - 41 lines] > =iDob > -----END PGP SIGNATURE----- ISO Country Codes are a perfectly valid natural key in a lookup table where each code uniquely identifies a country. When then put into another table, and validated via a referential constraint, they are not a natural key: Natural or unnatural. SignatureDaniel A. Morgan University of Washington damorgan@x.washington.edu (replace x with u to respond) Puget Sound Oracle Users Group www.psoug.org >>> Ah, that is why you like those so much Tony. You are forced to use a >>> crutch and have grown to like it. You have my deepest sympathies. [quoted text clipped - 26 lines] > > Amazing! Good example! Two Daniel Morgans MUST be a TERRIBLE bug in the system <g> Shakespeare >>> That class room door you hide behind needs oiling. >> Look who's talking. And while you are reconsidering, and you should, [quoted text clipped - 12 lines] > > Shakespeare Some people think one is too many. SignatureDaniel A. Morgan University of Washington damorgan@x.washington.edu (replace x with u to respond) Puget Sound Oracle Users Group www.psoug.org > Will the trigger fire if no row is updated? > > Cheers > Serge Yes! Connected to: Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 With the Partitioning, OLAP and Data Mining options SQL> CREATE TABLE t ( 2 testcol VARCHAR2(5)); Table created. SQL> INSERT INTO t VALUES ('AAA'); 1 row created. SQL> CREATE OR REPLACE TRIGGER testtrig 2 BEFORE UPDATE 3 ON t 4 5 DECLARE 6 vMsg VARCHAR2(30) := 'Trigger Fired'; 7 BEGIN 8 dbms_output.put_line(vMsg); 9 END testtrig; 10 / Trigger created. SQL> set serveroutput on SQL> UPDATE t 2 SET testcol = 'BBB' 3 WHERE testcol = 'CCC'; Trigger Fired <================ note that the trigger fired 0 rows updated. SQL> SignatureDaniel A. Morgan University of Washington damorgan@x.washington.edu (replace x with u to respond) Puget Sound Oracle Users Group www.psoug.org >> Will the trigger fire if no row is updated? >> [quoted text clipped - 36 lines] > > 0 rows updated. Tx, I was misinformed then. SignatureSerge Rielau DB2 Solutions Development IBM Toronto Lab >>> Will the trigger fire if no row is updated? >>> [quoted text clipped - 37 lines] >> 0 rows updated. > Tx, I was misinformed then. You really do need to come out here and take my class. <g> SignatureDaniel A. Morgan University of Washington damorgan@x.washington.edu (replace x with u to respond) Puget Sound Oracle Users Group www.psoug.org >Like I said, you've lost all objectivety and are 100% blinded by bias. Pott <-> Kettle <-> Black You are 300 percent blinded by bias. SignatureSybrand Bakker Senior Oracle DBA >> Like I said, you've lost all objectivety and are 100% blinded by bias. > > Pott <-> Kettle <-> Black > You are 300 percent blinded by bias. Two weeks of him working to find something on the page I got incorrect and he finally found something. ~600 items on the page, one omission, and you'd think I had been caught in an church committing a felony. To keep this from degenerating further I won't post any excerpts from what I've been receiving off-line but suffice it to say he's been kill-filed. And no one at Microsoft, I can find, knows who the H he is so your evaluation of him seems to be spot on. SignatureDaniel A. Morgan University of Washington damorgan@x.washington.edu (replace x with u to respond) Puget Sound Oracle Users Group www.psoug.org > And no one at Microsoft, I can find, knows who the H he is > so your evaluation of him seems to be spot on. Then you are asking the wrong people, Tony was one of the first 9 SQL Server MVPs I first interacted with him 1998 when I was converting from Oracle apps to SQL Server apps. Any member of the MVP Management Team within Customer Support Services and most of the senior members of the SQL Server Development Team from David Campbell (SQL Server Technical Fellow) down will confirm that. You really do need to stop overselling your MS contacts here, they have yet to come through for you in an argument. -Euan >> And no one at Microsoft, I can find, knows who the H he is >> so your evaluation of him seems to be spot on. [quoted text clipped - 10 lines] > > -Euan And he knows so little SQL Server he puts up a trigger on DROP TABLE as a system trigger? That explains a lot about the quality of what you award MVP too: Perhaps too much. SignatureDaniel A. Morgan University of Washington damorgan@x.washington.edu (replace x with u to respond) Puget Sound Oracle Users Group www.psoug.org > And he knows so little SQL Server he puts up a trigger on DROP TABLE > as a system trigger? That explains a lot about the quality of what > you award MVP too: Perhaps too much. Really DA? And just where did I do that (Google groups URL for ease?)? Talking BS again, inventing stuff again, in Denial Again eh Morgan? Have you updated your biased rubbish for unicode and collate yet? Do you not see a pattern yet? Perhaps you should have stuck to Oracle instead of entering into and bashing territories you know very little about. SignatureTony Rogerson, SQL Server MVP http://sqlblogcasts.com/blogs/tonyrogerson [Ramblings from the field from a SQL consultant] http://sqlserverfaq.com [UK SQL User Community] >>> And no one at Microsoft, I can find, knows who the H he is >>> so your evaluation of him seems to be spot on. [quoted text clipped - 14 lines] > as a system trigger? That explains a lot about the quality of what > you award MVP too: Perhaps too much. > Two weeks of him working to find something on the page I got incorrect > and he finally found something. ~600 items on the page, one omission, > and you'd think I had been caught in an church committing a felony. Approx 60 - 70% are wrong, some partially but some just dam right wrong. What's the problem? If you where professional about doing a proper comparison you'd be objective rather than Objective. I believe a number of other SQL SERver experts known in the SQL community have tried to help you make this list accurate but you refuse. in D ... A... Morgan? > kill-filed. And no one at Microsoft, I can find, knows who the H he is > so your evaluation of him seems to be spot on. Lol, you do live in a very small universe; just highlights the fact you don't actually know many if anybody in MS. But I guess, if you keep asking the XBox team SQL Server questions.... SignatureTony Rogerson, SQL Server MVP http://sqlblogcasts.com/blogs/tonyrogerson [Ramblings from the field from a SQL consultant] http://sqlserverfaq.com [UK SQL User Community] > Consider this for example. In a US medical environment with patient data > HIPAA, federal law, requires that SELECT statements be audited. We have > been able to do that in Oracle for years. InSQL Serverit is still > impossible. Something that won't be fixed until at least 2009. If I > tried to put together a page of all differences I would need months just > to catalog the differences. I'm sorry you lose again, I suggest you review SQL Trace with the C2 option which has been available since SQL Server 2000. You can also checkout http://www.lumigent.com/products/auditdb.html which surprise surprise contains support for SQL Server as well as Oracle. Ok so time for you to educate me for real, its been a long time since I use oracle daily and the audit issue is something you throw out a lot so I am intrigued as to how Oracle solves a common app problem. Thats the one where the middle tier logs into the database as a single user that is an app role? -Euan >> Consider this for example. In a US medical environment with patient data >> HIPAA, federal law, requires that SELECT statements be audited. We have [quoted text clipped - 5 lines] > I'm sorry you lose again, I suggest you review SQL Trace with the C2 > option which has been available since SQL Server 2000. Try again: "while SQL Server 2000 can indicate which user might have updated a table, it cannot capture the actual content of the update. Still, SQL Server 2000 does conform with the C2-level auditing required by certain government users." http://www.itworld.com/AppDev/136/ITW010305talmage/ Next go to: http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/sqlc2.mspx#EJGAC and scroll down to: "List of Auditable Events" If you are going to comment on a product ... take the time to learn something about it. According to Microsoft it can not meet HIPAA based on what is published on their website. SignatureDaniel A. Morgan University of Washington damorgan@x.washington.edu (replace x with u to respond) Puget Sound Oracle Users Group www.psoug.org > euan.gar...@gmail.com wrote: > >> Consider this for example. In a US medical environment with patient data [quoted text clipped - 25 lines] > damor...@x.washington.edu (replace x with u to respond) > Puget Sound Oracle Users Groupwww.psoug.org These are always such fun. You said: "...requires that SELECT statements be audited. We have been able to do that in Oracle for years. InSQL Serverit is still impossible..." And now you want to have a conversation about updates. I never said that C2 was the answer to HIPAA I said it was the answer to select audits. If you want to discuss a HIPAA solution, thats going to involve trace and some other technologies, its a bit ugly depending on the implementation and in fact C2 would likey be overkill for HIPAA but as I am not a HIPAA expert I can't comment. There is however http://www.lumigent.com/products/auditdb.html which is an elegant solution for SQL Server, Oracle and others (which I noticed you dropped in your reply) and mentions HIPAA specifically. Needless to say there are plenty of orgs using SQL Server that are subject to HIPAA, just as I am sure there are plenty using Oracle, DB2 and other database platforms. I'm willing to acknowledge that Oracle and IBM have a place in the database industry and that my lack of knowledge and experience with them in recent years means I should refrain from commenting deeply on their functionality/solution areas, it seems once again you are unwilling to take the same position regarding SQL Server. As always looking forward to the next round. -Euan > And now you want to have a conversation about updates. I never said > that C2 was the answer to HIPAA I said it was the answer to select > audits. And I posted the link to Microsoft's own docs where they say that this isn't true. So who's correct? You or Microsoft? Perhaps the problem here is that you don't understand what HIPAA means with respect to auditing SELECT statements. It isn't who issued it. That is not the issue. It is which records, with which specific values, were returned to which users? If you believe otherwise then please provide a link to the doc that demonstrates that this capability exists in any database product other than Oracle. SignatureDaniel A. Morgan University of Washington damorgan@x.washington.edu (replace x with u to respond) Puget Sound Oracle Users Group www.psoug.org > And I posted the link to Microsoft's own docs where they say that this > isn't true. So who's correct? You or Microsoft? Hmmm, you posted 2 links, one was a write up by an MVP(from 6 years ago) which documented that C2 in SQL Server 2000 would tell you who made a change(update) but not what the change was. The second link you sent was the to C2 summary, in that doc it lists the following; "...End User Activity (for example, all SQL commands, logins, and logouts).." Which I think is the bucket that selects are going to come under, I agree we could have made this clearer but the doc was not written with HIPAAs more exacting/different requirements in mind. Given that C2 has been superceded as a std and SQL Server is currently undergoing common criteria certification I don't think there is much chance of getting the page updated. Now trace was not that well documented in SQL 6.5/7/2000 so I am going to reference SQL2005 docs, most of what I reference also applies on older versions. Here is the list of events that can be audited by trace in 2005; http://msdn2.microsoft.com/en-us/library/ms175481.aspx To save time, here is the category of events that include all sql statements, hence would include select statements; http://msdn2.microsoft.com/en-us/library/ms177488.aspx > Perhaps the problem here is that you don't understand what HIPAA means > with respect to auditing SELECT statements. It isn't who issued it. That > is not the issue. It is which records, with which specific values, were > returned to which users? Ah ok now that makes sense, as I said I am no HIPAA expert so I was not aware of that requirement. > If you believe otherwise then please provide a link to the doc that > demonstrates that this capability exists in any database product other > than Oracle. I'm not aware of it existing in SQL Server at this time, I'm not going to comment on other DBs as I don't know them. However I have another question about HIPAA at this point, I thought that HIPAA was an end to end requirement, which means while Oracle makes this possible on the back end through built in features(I presume this is done through versioning somehow? How long is the audit trail kept btw) if the app tier does something thats not auditable then from a compliance perspective its a bust? -Euan >> And I posted the link to Microsoft's own docs where they say that this >> isn't true. So who's correct? You or Microsoft? [quoted text clipped - 49 lines] > > -Euan That is the statement ... but not what was returned. The statement, itself, in the context of HIPAA is meaningless because the data changes. A month later, during an audit, it is impossible to tell if 0, 1, or 1000 records were returned and which ones. It may make a marketer happy to claim it as auditing but it does not comply with the law being discussed. SignatureDaniel A. Morgan University of Washington damorgan@x.washington.edu (replace x with u to respond) Puget Sound Oracle Users Group www.psoug.org > euan.gar...@gmail.com wrote: > >> And I posted the link to Microsoft's own docs where they say that this [quoted text clipped - 63 lines] > damor...@x.washington.edu (replace x with u to respond) > Puget Sound Oracle Users Groupwww.psoug.org Funny how items like the original posting in this thread get coerced and turned into flame wars. At least the most popular url in cdos gets posted a few hundred more times. If you people want to continue this at least start a new thread and leave this one. > If you people want to continue this at least start a new thread and > leave this one. If you have something to contribute to this thread I've no doubt we would all appreciate your participation. If you see your job as moderator then perhaps you've got the wrong forum. SignatureDaniel A. Morgan University of Washington damorgan@x.washington.edu (replace x with u to respond) Puget Sound Oracle Users Group www.psoug.org >Funny how items like the original posting in this thread get coerced >and turned into flame wars. At least the most popular url in cdos >gets posted a few hundred more times. Funny your *only* contribution to cdos nowadays consists of flaming Daniel Morgan. Did you yet consider to flame Jonathan Lewis too, for including information about his book in his sig? Or, more likely, are you just a big coward, so you don't dare to attack Jonathan Lewis as you attack Daniel Morgan? Either: you attack both Daniel Morgan AND Jonathan Lewis or you keep shut. Better still: find a different playground, maybe Oracle-l. SignatureSybrand Bakker Senior Oracle DBA > No need to be defensive ... at least with respect to this thread. > Anytime you want to bring DB2 to a single set of specifications for > basics like the number of bytes in an object name though you can be > as defensive as you wish. <g> No need to be shy: 128 Cheers Serge SignatureSerge Rielau DB2 Solutions Development IBM Toronto Lab |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2010 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.