Joe,
 
When did you try this???
 
Why do I ask?
 
Because this did indeed not work for a while -- releases after REPLACE
was introduced through either 6.0
or 6.3 did not work with NULL as the target value, but releases 7.0 and
later SHOULD work with NULL as
the target value (and maybe 6.3 works I just don't remember when the fix
was introduced).
 
So, it is a version dependent thing and these types of issues need to be
revisited occasionally because
we do indeed fix problems like this when they are identified....
 
Doug Mueller

________________________________

From: Action Request System discussion list(ARSList)
[mailto:[EMAIL PROTECTED] On Behalf Of Joe D'Souza
Sent: Friday, April 11, 2008 9:22 AM
To: [email protected]
Subject: Re: Difference between NULL and ""


** 
Just as an FYI,
 
If you use $NULL$ in REPLACE or functions like REPLACE to do something
like
REPLACE($field$, "XYZ", $NULL$) it wont work. What works is
REPLACE($field$, "XYZ", "")
 
Joe

        -----Original Message-----
        From: Action Request System discussion list(ARSList)
[mailto:[EMAIL PROTECTED] Behalf Of Mueller, Doug
        Sent: Friday, April 11, 2008 10:44 AM
        To: [email protected]
        Subject: Re: Difference between NULL and ""
        
        
        ** 
        Ganga,
         
        This is not a simple answer because the full answer is "it
depends".
         
        Scenario 1 -- You are working through the AR System client
environment (Windows or Web)
         
        "" and NULL are the same thing.  "" is no value, we interpret no
value as NULL.
        In fact "    " and NULL are the same thing.  For any value you
enter in any character field, we string trim it on
        the right as we read it.  So, "    " and "" are the same and ""
and NULL are the same so "    " and NULL are 
        the same.
         
        Why do we do this you might ask?  Well, because you want us to.
If you entered "Fred " vs. "Fred"  (note
        that the first value has a space at the end), do you really want
two different values stored in the name field
        for two different records?  It is really easy to have extra
spaces accidentally entered on the end of character
        fields and you cannot see it without a lot of effort.  By the
way, the system has been working this way for
        about 10 years now so this is not new (OK, except for one
release where it was broken temporarily because
        an engineer saw a bug that we were triming and changing the
value and he "fixed" it which took about 3
        seconds for customers to report that behavior had changed and
they DID NOT LIKE IT and the engineer was
        educated about the difference between being pure and correct and
doing the right thing and what works best
        for the customer).
         
        Scenario 2 -- You are working through the AR System API
         
        If going through the API, you are in charge.  You control the
data values that are specified.  We figure that
        you have written the code and you have done what you want to do.
So, we do no interpretation of any value,
        we accept it as you give it to us.
         
        This means that "" is a value and NULL is a DIFFERENT value.
         
        At this point, we have to leave Scenario 2 because the rest of
the answer is in "SPECIAL DB NOTE" below.
         
        Scenario 3 -- You are using direct SQL through the AR System API
         
        If using direct SQL, we are not involved in the operation other
than being the container through which it is
        issued.  This means that whatever you enter in SQL is what you
enter.  That means the command will
        contain '' (remember that SQL puts single quotes around strings)
for "" and NULL for NULL.
         
        At this point, we have to leave Scenario 3 because the rest of
the answer is in "SPECIAL DB NOTE" below.
         
        Scenario 4 -- You are doing things from OUTSIDE the AR System
         
        If using SQL to get at the data from outside SQL, we are not
involved in the operation in any way and it is
        whatever you specify.
         
        At this point, we have to leave Scenario 4 because the rest of
the answer is in "SPECIAL DB NOTE: below.
         
         
        So far, there is consistency across all the different scenarios.
Things have one way of working and there is
        no difference between environments.  HOWEVER, (and there is
always a however isn't there) there is one
        more thing that we have not discussed -- the database.  And, in
this case it matters:
         
        SPECIAL DB NOTE
         
        The way databases handle an empty string ("" or '' in SQL
syntax) is not consistent.
         
        Some of them treat the empty string ("") as a character string
of 0 length and so a value that is different and
        distinct from NULL.
         
        Some of them treat the empty string ("") as nothing and
equivalent to NULL.
         
        This means that for some databases, a value of an empty string
is a real live value in the character string.
        There is a value.  It is not NULL.  It just happens to be a
string of 0 length.
         
        While for other databases, the value of an empty string is NULL
or the non-existance of a value.
         
        TECHNICALLY -- the empty string as a value of a string with no
length is theorectically correct because that
        is not the non-existance of a value.  It is a very distinct
value.  HOWEVER, some databases have chosen
        to interpret that empty string as NULL as their best practice
implementation.
         
        Off the top of my head, I cannot remember which databases fall
into which camp.  I do know that Sybase
        and MS SQL fall into one and Oracle into the other of the camps.
         
         
        So, a long answer to your question, but there is not a simple
answer as there are many different
        permutations of what is going on.  My suspicion is that you are
being caught by the DB interpretation issue
        of calling or not calling the empty string NULL.
         
        I hope this is useful to the wider team,
         
        Doug Mueller
         

________________________________

        From: Action Request System discussion list(ARSList)
[mailto:[EMAIL PROTECTED] On Behalf Of Ganga Prasad
        Sent: Friday, April 11, 2008 5:40 AM
        To: [email protected]
        Subject: Difference between NULL and ""
        
        
        ** Hi all
        
        Is there any difference between NULL and "" in a character field
??
        My Scenario
        I have a character field. The value to the character field is
supposed to come from a already existing backend from.
        My Requirement.
        On saving I should not be able to save any value in the
character field other than whatever record is present in the BACKEND
Form
        My Solution
        I wrote a filter (RUNIF : character field != NULL) which will go
for a SQL. The SQL will find out the present value in the ticket with
the BACKEND record. Once it is finding, it will make a flag field with
count of records found (Usually 1). If no backend records found then it
will make the flag 0( zero matching records)
        The very next filter will check for the value in the Flag field;
If it is 0 then give a error message
        My problem
        It is working as expected as long as I am opening my Remedy
client and save the ticket.
        But when I am trying to create a ticket in my application
through another application by XML without passing the value to the
character field it is throwing an error to the originating system.One
strange thing is that if in the xml if i am removing the
<characterfield> completly; it is working fine.
        My Approach
        I approached with hit and trial method that before going for SQL
I set the character field NULL if it is "". Now it is working fine.
        My Doubt
        Is this the way remedy should behave ?? is there any diff
between NULL and "" ??
        My Setup
        ARS V 7.0.01 P003
        No ITSM
        Completely Customized appl.
        
        
        -- 
        Thanks and Regards,
        Ganga Prasad Pattnaik,
        ( Remedy Skilled Professional )

__Platinum Sponsor: www.rmsportal.com ARSlist: "Where the Answers Are"
html___ 

_______________________________________________________________________________
UNSUBSCRIBE or access ARSlist Archives at www.arslist.org
Platinum Sponsor: www.rmsportal.com ARSlist: "Where the Answers Are"

Reply via email to