<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML xmlns="http://www.w3.org/TR/REC-html40" xmlns:v =
"urn:schemas-microsoft-com:vml" xmlns:o =
"urn:schemas-microsoft-com:office:office" xmlns:w =
"urn:schemas-microsoft-com:office:word" xmlns:st1 =
"urn:schemas-microsoft-com:office:smarttags"><HEAD><TITLE>Message</TITLE>
<META content="MSHTML 6.00.2900.2722" name=GENERATOR><!--[if !mso]>
<STYLE>v\:* {
BEHAVIOR: url(#default#VML)
}
o\:* {
BEHAVIOR: url(#default#VML)
}
w\:* {
BEHAVIOR: url(#default#VML)
}
shape {
BEHAVIOR: url(#default#VML)
}
</STYLE>
<![endif]--><o:SmartTagType
namespaceuri="urn:schemas-microsoft-com:office:smarttags" name="City"
downloadurl="http://www.5iamas-microsoft-com:office:smarttags"></o:SmartTagType><o:SmartTagType
namespaceuri="urn:schemas-microsoft-com:office:smarttags" name="place"
downloadurl="http://www.5iantlavalamp.com/"></o:SmartTagType><!--[if !mso]>
<STYLE>st1\:* {
BEHAVIOR: url(#default#ieooui)
}
</STYLE>
<![endif]-->
<STYLE>@font-face {
font-family: Tahoma;
}
@page Section1 {size: 612.0pt 792.0pt; margin: 72.0pt 90.0pt 72.0pt 90.0pt; }
P.MsoNormal {
FONT-SIZE: 12pt; MARGIN: 0cm 0cm 0pt; FONT-FAMILY: "Times New Roman"
}
LI.MsoNormal {
FONT-SIZE: 12pt; MARGIN: 0cm 0cm 0pt; FONT-FAMILY: "Times New Roman"
}
DIV.MsoNormal {
FONT-SIZE: 12pt; MARGIN: 0cm 0cm 0pt; FONT-FAMILY: "Times New Roman"
}
A:link {
COLOR: blue; TEXT-DECORATION: underline
}
SPAN.MsoHyperlink {
COLOR: blue; TEXT-DECORATION: underline
}
A:visited {
COLOR: blue; TEXT-DECORATION: underline
}
SPAN.MsoHyperlinkFollowed {
COLOR: blue; TEXT-DECORATION: underline
}
P {
FONT-SIZE: 12pt; MARGIN-LEFT: 0cm; MARGIN-RIGHT: 0cm; FONT-FAMILY: "Times New Roman"; mso-margin-top-alt: auto; mso-margin-bottom-alt: auto
}
PRE {
FONT-SIZE: 10pt; MARGIN: 0cm 0cm 0pt; FONT-FAMILY: "Courier New"
}
SPAN.EmailStyle19 {
COLOR: navy; FONT-FAMILY: Arial; mso-style-type: personal-reply
}
DIV.Section1 {
page: Section1
}
</STYLE>
</HEAD>
<BODY lang=EN-US vLink=blue link=blue>
<DIV><SPAN class=313541800-05102005><FONT face=Arial color=#0000ff
size=2>However, I did get it to work using ...</FONT></SPAN></DIV>
<DIV><SPAN class=313541800-05102005><FONT face=Arial color=#0000ff
size=2></FONT></SPAN> </DIV>
<DIV><SPAN class=313541800-05102005><FONT face=Arial color=#0000ff size=2>where
cast(0 as date) = ebh.date_processed</FONT></SPAN></DIV>
<DIV><SPAN class=313541800-05102005><FONT face=Arial color=#0000ff
size=2></FONT></SPAN> </DIV>
<DIV><SPAN class=313541800-05102005><FONT face=Arial color=#0000ff size=2>Thanks
anyway everyone.</FONT></SPAN></DIV>
<DIV><SPAN class=313541800-05102005></SPAN> </DIV>
<DIV></DIV>
<DIV class=OutlookMessageHeader lang=en-us dir=ltr align=left><FONT face=Tahoma
size=2>-----Original Message-----<BR><B>From:</B> Fernando Olmos
<BR><B>Sent:</B> Wednesday, October 05, 2005 10:18 AM<BR><B>To:</B>
'atla38@dsl.pipex.com'; powerh-l@lists.sowder.com<BR><B>Subject:</B> RE: Date
data types and NULL or ZERO<BR><BR></FONT></DIV>
<DIV><SPAN class=721071800-05102005><FONT face=Arial color=#0000ff
size=2>Nope.</FONT></SPAN></DIV>
<DIV><SPAN class=721071800-05102005><FONT face=Arial color=#0000ff
size=2></FONT></SPAN> </DIV>
<DIV><SPAN class=721071800-05102005><FONT face=Arial color=#0000ff size=2>I get
this ...</FONT></SPAN></DIV>
<DIV><SPAN class=721071800-05102005><FONT face=Arial color=#0000ff
size=2></FONT></SPAN> </DIV>
<DIV><SPAN class=721071800-05102005><FONT face=Arial color=#0000ff size=2>>
> > > > > > > > > > > > > > *E* Error
parsing SQL statement.<BR>DMS-E-GENERAL, A general exception has occurred during
operation 'prepare<BR>request'.<BR>DMS-E-RGE_BIFNA, Builtin function TO_CHAR is
not available.</FONT></SPAN></DIV>
<DIV><SPAN class=721071800-05102005></SPAN> </DIV>
<DIV></DIV>
<DIV class=OutlookMessageHeader lang=en-us dir=ltr align=left><FONT face=Tahoma
size=2>-----Original Message-----<BR><B>From:</B> atla38@dsl.pipex.com
[mailto:atla38@dsl.pipex.com] <BR><B>Sent:</B> Wednesday, October 05, 2005 10:16
AM<BR><B>To:</B> Fernando Olmos; powerh-l@lists.sowder.com<BR><B>Subject:</B>
RE: Date data types and NULL or ZERO<BR><BR></FONT></DIV>
<DIV class=Section1>
<P class=MsoNormal><FONT face=Arial color=navy size=2><SPAN
style="FONT-SIZE: 10pt; COLOR: navy; FONT-FAMILY: Arial">how about “ where
to_char(</SPAN></FONT><FONT face=Arial size=2><SPAN
style="FONT-SIZE: 10pt; FONT-FAMILY: Arial">DATE_PROCESSED) = ‘0’ “<FONT
color=navy><SPAN style="COLOR: navy"><o:p></o:p></SPAN></FONT></SPAN></FONT></P>
<DIV>
<P><FONT face="Times New Roman" color=navy size=3><SPAN
style="FONT-SIZE: 12pt; COLOR: navy">Regards, Joe. </SPAN></FONT><o:p></o:p></P>
<DIV style="TEXT-ALIGN: center" align=center><FONT face="Times New Roman"
size=3><SPAN style="FONT-SIZE: 12pt">
<HR tabIndex=-1 align=center width="100%" SIZE=3>
</SPAN></FONT></DIV></DIV>
<DIV>
<P class=MsoNormal><B><FONT face=Tahoma size=2><SPAN
style="FONT-WEIGHT: bold; FONT-SIZE: 10pt; FONT-FAMILY: Tahoma">From:</SPAN></FONT></B><FONT
face=Tahoma size=2><SPAN style="FONT-SIZE: 10pt; FONT-FAMILY: Tahoma">
powerh-l-bounces+atla38=dsl.pipex.com@lists.sowder.com
[mailto:powerh-l-bounces+atla38=dsl.pipex.com@lists.sowder.com] <B><SPAN
style="FONT-WEIGHT: bold">On Behalf Of
</SPAN></B>fernando.olmos@hpa.com.au<BR><B><SPAN
style="FONT-WEIGHT: bold">Sent:</SPAN></B> 05 October 2005 00:37<BR><B><SPAN
style="FONT-WEIGHT: bold">To:</SPAN></B> powerh-l@lists.sowder.com<BR><B><SPAN
style="FONT-WEIGHT: bold">Subject:</SPAN></B> Date data types and NULL or
ZERO</SPAN></FONT><o:p></o:p></P></DIV>
<P class=MsoNormal><FONT face="Times New Roman" size=3><SPAN
style="FONT-SIZE: 12pt"><o:p> </o:p></SPAN></FONT></P>
<P><FONT face=Arial size=2><SPAN style="FONT-SIZE: 10pt; FONT-FAMILY: Arial">Hi
guys,</SPAN></FONT> <o:p></o:p></P>
<P><FONT face=Arial size=2><SPAN style="FONT-SIZE: 10pt; FONT-FAMILY: 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_PROCESSED is NULL. Her query
does not return anything.</SPAN></FONT><o:p></o:p></P>
<P><FONT face=Arial size=2><SPAN style="FONT-SIZE: 10pt; FONT-FAMILY: 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?</SPAN></FONT><o:p></o:p></P>
<P><FONT face=Arial size=2><SPAN style="FONT-SIZE: 10pt; FONT-FAMILY: Arial">She
tried DATE_PROCESSED = 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.</SPAN></FONT><o:p></o:p></P>
<P><FONT face=Arial size=2><SPAN style="FONT-SIZE: 10pt; FONT-FAMILY: 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.</SPAN></FONT><o:p></o:p></P>
<P><FONT face=Arial size=2><SPAN
style="FONT-SIZE: 10pt; FONT-FAMILY: Arial">Cheers</SPAN></FONT> <o:p></o:p></P>
<P><I><FONT face=Arial color=blue size=4><SPAN
style="FONT-SIZE: 13.5pt; COLOR: blue; FONT-STYLE: italic; FONT-FAMILY: Arial">Fernando
Olmos</SPAN></FONT></I> <BR><B><FONT face=Arial size=2><SPAN
style="FONT-WEIGHT: bold; FONT-SIZE: 10pt; FONT-FAMILY: Arial">M.I.S.</SPAN></FONT></B>
<o:p></o:p></P>
<P><B><FONT face=Arial color=blue size=2><SPAN lang=EN-AU
style="FONT-WEIGHT: bold; FONT-SIZE: 10pt; COLOR: blue; FONT-FAMILY: Arial">HPA</SPAN></FONT></B><FONT
face=Arial><SPAN style="FONT-FAMILY: Arial"><BR></SPAN></FONT><FONT face=Arial
color=black size=2><SPAN lang=EN-AU
style="FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: Arial">Direct: 03 9217
5411</SPAN></FONT><BR><st1:City w:st="on"><st1:place w:st="on"><FONT face=Arial
color=black size=2><SPAN lang=EN-AU
style="FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: Arial">Mobile</SPAN></FONT></st1:place></st1:City><FONT
face=Arial color=black size=2><SPAN lang=EN-AU
style="FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: Arial">:
0410 382 857</SPAN></FONT><FONT face=Arial><SPAN
style="FONT-FAMILY: Arial"><BR></SPAN></FONT><FONT face=Arial color=black
size=2><SPAN lang=EN-AU
style="FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: Arial">Fax:
03 9217 5716</SPAN></FONT><SPAN lang=EN-AU> </SPAN><o:p></o:p></P>
<P><FONT face="Times New Roman" size=3><SPAN style="FONT-SIZE: 12pt"><A
href="file:///H:\Appdata\Microsoft\Signatures\www.hpa.com.au"><B><FONT
face=Arial size=2><SPAN lang=EN-AU
style="FONT-WEIGHT: bold; FONT-SIZE: 10pt; FONT-FAMILY: Arial">www.hpa.com.au</SPAN></FONT></B></A>
<o:p></o:p></SPAN></FONT></P>
<P class=MsoNormal><FONT face="Times New Roman" size=3><SPAN
style="FONT-SIZE: 12pt"><o:p> </o:p></SPAN></FONT></P><PRE><FONT face="Courier New" size=2><SPAN style="FONT-SIZE: 10pt">**********************************************************************<o:p></o:p></SPAN></FONT></PRE><PRE><FONT face="Courier New" size=2><SPAN style="FONT-SIZE: 10pt">IMPORTANT<o:p></o:p></SPAN></FONT></PRE><PRE><FONT face="Courier New" size=2><SPAN style="FONT-SIZE: 10pt">The contents of this e-mail and its attachments are confidential and intended<o:p></o:p></SPAN></FONT></PRE><PRE><FONT face="Courier New" size=2><SPAN style="FONT-SIZE: 10pt">solely for the use of the individual or entity to whom they are addressed. If<o:p></o:p></SPAN></FONT></PRE><PRE><FONT face="Courier New" size=2><SPAN style="FONT-SIZE: 10pt">you received this e-mail in error, please notify the HPA Postmaster, postmaster@hpa.com.au,<o:p></o:p></SPAN></FONT></PRE><PRE><FONT face="Courier New" size=2><SPAN style="FONT-SIZE: 10pt">then delete the e-mail.<o:p></o:p></SPAN></FONT></PRE><PRE><FONT face="Courier New" size=2><SPAN style="FONT-SIZE: 10pt">This footnote also confirms that this e-mail message has been swept for the<o:p></o:p></SPAN></FONT></PRE><PRE><FONT face="Courier New" size=2><SPAN style="FONT-SIZE: 10pt">presence of computer viruses by Ironport. Before opening or using any<o:p></o:p></SPAN></FONT></PRE><PRE><FONT face="Courier New" size=2><SPAN style="FONT-SIZE: 10pt">attachments, check them for viruses and defects.<o:p></o:p></SPAN></FONT></PRE><PRE><FONT face="Courier New" size=2><SPAN style="FONT-SIZE: 10pt">Our liability is limited to resupplying any affected attachments.<o:p></o:p></SPAN></FONT></PRE><PRE><FONT face="Courier New" size=2><SPAN style="FONT-SIZE: 10pt">HPA collects personal information to provide and market our services. For more<o:p></o:p></SPAN></FONT></PRE><PRE><FONT face="Courier New" size=2><SPAN style="FONT-SIZE: 10pt">information about use, disclosure and access see our Privacy Policy at<o:p></o:p></SPAN></FONT></PRE><PRE><FONT face="Courier New" size=2><SPAN style
="FONT-SIZE: 10pt">www.hpa.com.au<o:p></o:p></SPAN></FONT></PRE><PRE><FONT face="Courier New" size=2><SPAN style="FONT-SIZE: 10pt">**********************************************************************<o:p></o:p></SPAN></FONT></PRE></DIV><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>