<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 3.2//EN">
<HTML>
<HEAD>
<META NAME="Generator" CONTENT="MS Exchange Server version 6.0.6603.0">
<TITLE>Date data types and NULL or ZERO</TITLE>
</HEAD>
<BODY>
<!-- Converted from text/rtf format -->
<P><FONT SIZE=2 FACE="Arial">Hi guys,</FONT>
</P>
<P><FONT SIZE=2 FACE="Arial">A work colleague of mine is having problems with SQL cursors. She is using a simple SELECT but with a WHERE clause saying DATE_PROCESS is NULL. Her query does not return anything.</FONT></P>
<P><FONT SIZE=2 FACE="Arial">The database is Oracle. I was wondering how would one interrogate Oracle databases using PH CURSORS, where you want to check a date column that is blank?</FONT></P>
<P><FONT SIZE=2 FACE="Arial">She tried DATE_PROCESS = 0 but the parser rejects this as an invalid logical construct. We want to check for ZERO but it looks like we may need to convert the 0 (CAST it) to a date type and not (I assume) a native INTEGER type. Of course if she uses DATE_CONVERT IS NULL the parser accepts this, but we know none of the rows have an actual NULL value.</FONT></P>
<P><FONT SIZE=2 FACE="Arial">In PL/SQL, we physically removed a date value from the column and then used QUIZ to see if it stored the "empty" field as a NULL. We found out using a "DEFINE a = 1 if DATE_PROCESSED is NULL else 0" came back 0 and not 1.</FONT></P>
<P><FONT SIZE=2 FACE="Arial">Cheers</FONT>
</P>
<P><I><FONT COLOR="#0000FF" SIZE=4 FACE="Arial">Fernando Olmos</FONT></I>
<BR><B><FONT SIZE=2 FACE="Arial">M.I.S.</FONT></B>
</P>
<P><SPAN LANG="en-au"><B><FONT COLOR="#0000FF" SIZE=2 FACE="Arial">HPA</FONT></B></SPAN><B><SPAN LANG="en-us"></SPAN></B><SPAN LANG="en-us"></SPAN><SPAN LANG="en-us"></SPAN><SPAN LANG="en-us"><FONT FACE="Arial"><BR>
</FONT></SPAN><SPAN LANG="en-au"></SPAN><SPAN LANG="en-au"><FONT COLOR="#000000" SIZE=2 FACE="Arial">Direct: 03 9217 5411</FONT></SPAN><SPAN LANG="en-us"></SPAN><SPAN LANG="en-us"></SPAN><SPAN LANG="en-us"><BR>
</SPAN><SPAN LANG="en-us"></SPAN><SPAN LANG="en-au"></SPAN><SPAN LANG="en-au"></SPAN><SPAN LANG="en-au"><FONT COLOR="#000000" SIZE=2 FACE="Arial">Mobile: 0410 382 857</FONT></SPAN><SPAN LANG="en-us"></SPAN><SPAN LANG="en-us"></SPAN><SPAN LANG="en-us"><FONT FACE="Arial"><BR>
</FONT></SPAN><SPAN LANG="en-au"></SPAN><SPAN LANG="en-au"><FONT COLOR="#000000" SIZE=2 FACE="Arial">Fax: 03 9217 5716</FONT><FONT FACE="Arial"></FONT></SPAN><SPAN LANG="en-us"></SPAN><SPAN LANG="en-us"> </SPAN>
</P>
<P><SPAN LANG="en-au"></SPAN><A HREF="file:///H:/Appdata/Microsoft/Signatures/www.hpa.com.au"><SPAN LANG="en-au"></SPAN><SPAN LANG="en-au"><U></U></SPAN><SPAN LANG="en-au"><U><B><FONT COLOR="#0000FF" SIZE=2 FACE="Arial">www.hpa.com.au</FONT></B></U></SPAN><SPAN LANG="en-au"></SPAN></A><SPAN LANG="en-au"></SPAN><SPAN LANG="en-au"></SPAN><SPAN LANG="en-us"></SPAN><SPAN LANG="en-us"></SPAN><SPAN LANG="en-us"></SPAN><SPAN LANG="en-us"></SPAN>
</P>
<BR>
<pre>**********************************************************************
IMPORTANT
The contents of this e-mail and its attachments are confidential and intended
solely for the use of the individual or entity to whom they are addressed. If
you received this e-mail in error, please notify the HPA Postmaster, postmaster@hpa.com.au,
then delete the e-mail.
This footnote also confirms that this e-mail message has been swept for the
presence of computer viruses by Ironport. Before opening or using any
attachments, check them for viruses and defects.
Our liability is limited to resupplying any affected attachments.
HPA collects personal information to provide and market our services. For more
information about use, disclosure and access see our Privacy Policy at
www.hpa.com.au
**********************************************************************
</pre></BODY>
</HTML>