1 2 3 Previous Next 43 Replies Latest reply: Dec 7, 2007 8:48 AM by 494018 Go to original post RSS
      • 15. Re: Using <> or != on a NULL
        cd_2
        Nice one. However, in my example of auditing triggers I needed to find the "<>" cases without the ELSE part of an IF statement.

        C.
        • 16. Re: Using <> or != on a NULL
          cd_2
          @CD: I really like your version, but I agree with
          Laurent. For most developers the the IF THEN ELSE
          version is better. Also consider that keyword
          highlighting helps to improve readability of this
          statement.
          Like I wrote: I'll do this for special occasions, but not because I expect developers to not evolve. With that argument we'd have to stay with IF ELSIF constructs instead of CASE statements, to give another example.
          You mentioned an auditing trigger. I would suggest to
          write a packaged function/procedure for such a
          trigger, that includes the comparison and then
          inserts in the audit table.
          This is an organziational thing, but I don't see where you would gain anything in readability.
          This is not only better
          maintainable, but also faster (saves parsing time).
          In that case I'd probably apply my method inside the package to get the optimum. ;-)

          C.
          • 17. Re: Using <> or != on a NULL
            Sven W.
            This is an organziational thing, but I don't see where you would gain anything in readability.
            case 1
            audit_column(:old.col1, :new.col1);
            audit_column(:old.col2, :new.col2);
            audit_column(:old.col3, :new.col3);
            case 2
            IF NVL(NULLIF(:old.col1, :new.col1), NULLIF(:new.col1, (:old.col1)) IS NOT NULL 
              THEN 
                save_col_value(:old.col1);
            END IF;
            IF NVL(NULLIF(:old.col2, :new.col2), NULLIF(:new.col2, (:old.col2)) IS NOT NULL 
              THEN 
                save_col_value(:old.col2);
            END IF;
            IF NVL(NULLIF(:old.col3, :new.col3), NULLIF(:new.col3, (:old.col3)) IS NOT NULL 
              THEN 
                save_col_value(:old.col3);
            END IF;
            Which case is better to read?

            Message was edited by:
            Sven Weller
            • 18. Re: Using <> or != on a NULL
              cd_2
              We seem to have different concepts auf auditing triggers. In my version, if there's a change in any column of a table, I get a new instance in a history table, like in the following example:
              CREATE OR REPLACE TRIGGER <triggername>
              BEFORE INSERT OR UPDATE OR DELETE ON <table>
              FOR EACH ROW
              BEGIN
                ...
                IF UPDATING THEN
                   IF NVL(NULLIF(:NEW.col1, :OLD.col1), NULLIF(:OLD.col1, :NEW.col1)) IS NOT NULL
                   OR NVL(NULLIF(:NEW.col2, :OLD.col2), NULLIF(:OLD.col2, :NEW.col2)) IS NOT NULL
                   THEN
                      INSERT INTO <history_table> 
                      ...
                   END IF;
                END IF;
                ...
              END <triggername>;
              BTW. You did remember that you have to overload your audit_column function to take different data_types into account, right?

              C.
              • 19. Re: Using <> or != on a NULL
                Laurent Schneider
                at least the first one will be easier to
                understand
                because it contains only <> , OR , AND, IS NULL
                and
                IS NOT NULL. Those operators as available in
                Oracle
                since ages. Very few people know about NULLIF, and
                this Function encapsulation may hurt more than one
                developer. Just my 2 cents ;-)
                With that argument, why ever get a new version, since
                developers could become confused with new commands
                and options, right? Those are my 0.02 EUR.

                C.
                Nice question, quite of topic actually. Probably for Undo tablespace, locally managed tablespace, automatic segment space management, automatic storage management, cost based optimizer, but definitely not for NULLIF ;-)
                • 20. Re: Using <> or != on a NULL
                  Laurent Schneider
                  C, please do not get me wrong, I am the first to use weird things like DATAOBJ_TO_PARTITION, WM_CONCAT and other weird things in my posts. I have really nothing against your code. However, I definitely doubt that it is more maintainable than writing in a more natural language
                  X!=Y OR (X IS NULL AND Y IS NOT NULL) OR (X IS NOT NULL AND Y IS NULL)

                  I had a similar discussion on my blog
                  http://laurentschneider.com/wordpress/2007/07/avoid-unnecessary-updates.html

                  But I am still strongly pretending the condition above is easier to maintain because a human can read it. My 2 Rappen
                  • 21. Re: Using <> or != on a NULL
                    450441
                    I had a similar issue where I needed to identify if 1 and only 1 of X and Y was not null (XOR). I had the brainwave of using NVL(X,Y) = NVL(Y,X) which worked tremendously until I encountered a record where X=Y. Rather than awkwardly add a clause to eliminate that case, I reverted to the tried and tested (X IS NULL AND Y IS NOT NULL) OR (X IS NOT NULL AND Y IS NULL).

                    Much easier for future support monkeys to figure out what I was doing.
                    • 22. Re: Using <> or != on a NULL
                      cd_2
                      Don't worry, Laurent. I'm not using that construct on every possible occasion, it just came in handy on that audit trigger topic, which I gave a sample in my previous posting. At least it gave me something to blog. ;-)

                      Yes, I could have written it with <> and IS/IS NOT NULL, but the way it is right now it already fills the screen (width), and the place I work doesn't usually use 4 letter column names. So you can imagine how a statement with your prefered way would look like. Sure, I could have written a function to do that for me (or several, when counting the overloads), but that could also introduce the possibility of bugs.

                      I wish, DECODE would work in PL/SQL, then we'd both get (some) requirements fulfilled, it's already established and quite readable, at least in my opinion. The funny thing is, that before this "solution", I was actually working with the version that Aketi Jyuuzou posted - unfortunatly I didn't realize in the beginning, that a simple NOT wouldn't give me the result I needed, since I didn't want to check a mismatch in the ELSE clause. So there.

                      C.
                      • 23. Re: Using <> or != on a NULL
                        Laurent Schneider
                        nice to see you blogging! but what are you using for a software?

                        Do not believe what I write, even if I think
                        X!=Y OR (X IS NULL and Y IS NOT NULL) OR (X IS NOT NULL AND Y IS NOT NULL
                        is more maintainable, it does not mean I am using this nor that this is my preferred method.

                        well, just for fun, here is my alternative for plsql
                        if nvl(:x!=:y,nvl(:x,:y) is not null)  then ...
                        Message was edited by:
                        Laurent Schneider

                        I see, you develop your own blogging system. Do you plan to allow comments?
                        • 24. Re: Using <> or != on a NULL
                          damorgan
                          Didn't you mean:
                          if nvl(:x  ~= :y, nvl(:x,:y) is not null)  then
                          ? <g>
                          • 25. Re: Using <> or != on a NULL
                            cd_2
                            nice to see you blogging! but what are you using for
                            a software?
                            APEX 3.0.1 on an XE instance.
                            Do not believe what I write, even if I think
                            X!=Y OR (X IS NULL and Y IS NOT NULL) OR (X IS NOT
                            NULL AND Y IS NOT NULL
                            is more maintainable, it does not mean I am using
                            this nor that this is my preferred method.

                            well, just for fun, here is my alternative for plsql
                            if nvl(:x!=:y,nvl(:x,:y) is not null)  then ...
                            Too tired right now, will take a look at that and Dan's version later on.
                            Message was edited by:
                            Laurent Schneider
                            see, you develop your own blogging system. Do you
                            plan to allow comments?
                            One of the things on my list..Maybe I can integrate it with the forum package that's available in APEX, but as I wrote: everytime I solve one problem I somehow seem to find 2 new requirements. ;-) And since Tyler asked me to to some regex stuff for the wiki project, I think I've got my spare time planned for the next weeks.

                            C.
                            • 26. Re: Using <> or != on a NULL
                              damorgan
                              My version is syntactically valid but was intended as humor as it is a very obscure syntax. Don't use it.
                              • 27. Re: Using <> or != on a NULL
                                MaximDemenko
                                I don't find the ~= operator in documentation, however it can be compiled and seems to be equivalent to not equal ( != ) operator, just curious, can you elaborate?

                                Best regards

                                Maxim
                                • 28. Re: Using <> or != on a NULL
                                  damorgan
                                  For reasons I don't understand Oracle provides three different ways, in PL/SQL, to write the same thing:
                                  <>
                                  !=
                                  ~=
                                  Use ~= to confuse your coworkers and create hard to maintain code. <g>
                                  • 29. Re: Using <> or != on a NULL
                                    cd_2
                                    See, I didn't even register that nice little syntax gem. ;-)

                                    @Laurent: Checked your solution, it's quite nice. The only drawback (in my opinion) is, that you can't test this construct in the SQL engine.

                                    C.

                                    Message was edited by:
                                    cd