<html><head>
<style><!-- /* Font Definitions */ @font-face {font-family:"Cambria Math"; panose-1:2 4 5 3 5 4 6 3 2 4;} @font-face {font-family:Calibri; panose-1:2 15 5 2 2 2 4 3 2 4;} @font-face {font-family:"Lucida Console"; panose-1:2 11 6 9 4 5 4 2 2 4;} /* Style Definitions */ p.MsoNormal, li.MsoNormal, div.MsoNormal {margin:0cm; margin-bottom:.0001pt; font-size:11.0pt; font-family:"Calibri",sans-serif; mso-fareast-language:EN-US;} a:link, span.MsoHyperlink {mso-style-priority:99; color:#0563C1; text-decoration:underline;} a:visited, span.MsoHyperlinkFollowed {mso-style-priority:99; color:#954F72; text-decoration:underline;} span.EmailStyle17 {mso-style-type:personal-compose; font-family:"Calibri",sans-serif; color:windowtext;} .MsoChpDefault {mso-style-type:export-only; font-family:"Calibri",sans-serif;
mso-fareast-language:EN-US;} @page WordSection1 {size:612.0pt 792.0pt; margin:72.0pt 72.0pt 72.0pt 72.0pt;} div.WordSection1 {page:WordSection1;} --></style><!--[if gte mso 9]><xml> <o:shapedefaults v:ext="edit" spidmax="1026" /> </xml><![endif]--><!--[if gte mso 9]><xml> <o:shapelayout v:ext="edit"> <o:idmap v:ext="edit" data="1" /> </o:shapelayout></xml><![endif]-->
</head>
<body>
<div style="font-size: 10pt;"><div dir="auto">Morning</div><div dir="auto"><br></div><div dir="auto">If you use zero instead of 1 do you get the same?</div><div dir="auto"><br></div><div dir="auto">I must admit i have not seen that 'when a is integer/float' syntax before.</div><div dir="auto"><br></div><div dir="auto">But then i use openroad so our syntax is about 20 years behind.</div><div dir="auto"><br></div><div dir="auto">Adrian</div></div><div style="font-size: 10pt;"><div id="LGEmailHeader" dir="auto"><div dir="auto"><br></div><div
dir="auto">------ Original message------</div><div dir="auto"><b>From: </b>Martin Bowes<martin.bowes@ndph.ox.ac.uk></martin.bowes@ndph.ox.ac.uk></div><div dir="auto"><b>Date: </b>Wed, 29 Jul 2020 08:01</div><div dir="auto"><b>To: </b>Ingres lists;</div><div dir="auto"><b>Cc: </b></div><div dir="auto"><b>Subject:</b>[Info-ingres] why is an empty string an integer</div><div dir="auto"><br></div></div><div dir="auto">
<div class="WordSection1">
<p class="MsoNormal">Hi All,<o:p></o:p></p>
<p class="MsoNormal"><o:p> </o:p></p>
<p class="MsoNormal">I may have come across this before, but it’s early and I haven’t had my coffee.<o:p></o:p></p>
<p class="MsoNormal"><o:p> </o:p></p>
<p class="MsoNormal" style="text-autospace:none"><span style="font-size:10.0pt;font-family:"Lucida Console"">declare global temporary table fred(<o:p></o:p></span></p>
<p class="MsoNormal" style="text-autospace:none"><span style="font-size:10.0pt;font-family:"Lucida
Console""> a varchar(20)<o:p></o:p></span></p>
<p class="MsoNormal" style="text-autospace:none"><span style="font-size:10.0pt;font-family:"Lucida Console"">) on commit preserve rows with norecovery;<o:p></o:p></span></p>
<p class="MsoNormal" style="text-autospace:none"><span style="font-size:10.0pt;font-family:"Lucida Console"">Executing . . .<o:p></o:p></span></p>
<p class="MsoNormal" style="text-autospace:none"><span style="font-size:10.0pt;font-family:"Lucida Console""><o:p> </o:p></span></p>
<p class="MsoNormal" style="text-autospace:none"><span style="font-size:10.0pt;font-family:"Lucida Console"">continue<o:p></o:p></span></p>
<p class="MsoNormal" style="text-autospace:none"><span style="font-size:10.0pt;font-family:"Lucida Console"">* * insert into fred values (null), (''), ('1'), ('13-jan-2020');<o:p></o:p></span></p>
<p class="MsoNormal" style="text-autospace:none"><span
style="font-size:10.0pt;font-family:"Lucida Console"">Executing . . .<o:p></o:p></span></p>
<p class="MsoNormal" style="text-autospace:none"><span style="font-size:10.0pt;font-family:"Lucida Console""><o:p> </o:p></span></p>
<p class="MsoNormal" style="text-autospace:none"><span style="font-size:10.0pt;font-family:"Lucida Console"">(4 rows)<o:p></o:p></span></p>
<p class="MsoNormal" style="text-autospace:none"><span style="font-size:10.0pt;font-family:"Lucida Console"">continue<o:p></o:p></span></p>
<p class="MsoNormal" style="text-autospace:none"><span style="font-size:10.0pt;font-family:"Lucida Console"">* * * * * * * * select case when a is null then 'null'<o:p></o:p></span></p>
<p class="MsoNormal" style="text-autospace:none"><span style="font-size:10.0pt;font-family:"Lucida Console""> when a = '' then 'empty string'<o:p></o:p></span></p>
<p
class="MsoNormal" style="text-autospace:none"><span style="font-size:10.0pt;font-family:"Lucida Console""> else a end as data,<o:p></o:p></span></p>
<p class="MsoNormal" style="text-autospace:none"><span style="font-size:10.0pt;font-family:"Lucida Console""> case when a is integer then 'integer' else 'not integer' end as is_integer,<o:p></o:p></span></p>
<p class="MsoNormal" style="text-autospace:none"><span style="font-size:10.0pt;font-family:"Lucida Console""> case when a is float then 'float' else 'not float' end as is_float,<o:p></o:p></span></p>
<p class="MsoNormal" style="text-autospace:none"><span style="font-size:10.0pt;font-family:"Lucida Console""> case when a is ansidate then 'ansidate' else 'not ansidate' end as is_ansidate<o:p></o:p></span></p>
<p
class="MsoNormal" style="text-autospace:none"><span style="font-size:10.0pt;font-family:"Lucida Console"">from fred;<o:p></o:p></span></p>
<p class="MsoNormal" style="text-autospace:none"><span style="font-size:10.0pt;font-family:"Lucida Console"">Executing . . .<o:p></o:p></span></p>
<p class="MsoNormal" style="text-autospace:none"><span style="font-size:10.0pt;font-family:"Lucida Console""><o:p> </o:p></span></p>
<p class="MsoNormal" style="text-autospace:none"><span style="font-size:10.0pt;font-family:"Lucida Console""><o:p> </o:p></span></p>
<p class="MsoNormal" style="text-autospace:none"><span style="font-size:10.0pt;font-family:"Lucida Console"">┌────────────────────┬───────────┬─────────┬────────────┐<o:p></o:p></span></p>
<p class="MsoNormal" style="text-autospace:none"><span style="font-size:10.0pt;font-family:"Lucida Console"">│data
│is_integer │is_float │is_ansidate │<o:p></o:p></span></p>
<p class="MsoNormal" style="text-autospace:none"><span style="font-size:10.0pt;font-family:"Lucida Console"">├────────────────────┼───────────┼─────────┼────────────┤<o:p></o:p></span></p>
<p class="MsoNormal" style="text-autospace:none"><span style="font-size:10.0pt;font-family:"Lucida Console"">│null │not integer│not float│not ansidate│<o:p></o:p></span></p>
<p class="MsoNormal" style="text-autospace:none"><span style="font-size:10.0pt;font-family:"Lucida Console"">│empty string │<span style="background:yellow;mso-highlight:yellow">integer │float</span> │not ansidate│<o:p></o:p></span></p>
<p class="MsoNormal" style="text-autospace:none"><span
style="font-size:10.0pt;font-family:"Lucida Console"">│1 │integer │float │not ansidate│<o:p></o:p></span></p>
<p class="MsoNormal" style="text-autospace:none"><span style="font-size:10.0pt;font-family:"Lucida Console"">│13-jan-2020 │not integer│not float│ansidate │<o:p></o:p></span></p>
<p class="MsoNormal" style="text-autospace:none"><span style="font-size:10.0pt;font-family:"Lucida Console"">└────────────────────┴───────────┴─────────┴────────────┘<o:p></o:p></span></p>
<p class="MsoNormal" style="text-autospace:none"><span style="font-size:10.0pt;font-family:"Lucida Console"">(4 rows)<o:p></o:p></span></p>
<p class="MsoNormal" style="text-autospace:none"><span style="font-size:10.0pt;font-family:"Lucida
Console"">continue<o:p></o:p></span></p>
<p class="MsoNormal"><o:p> </o:p></p>
<p class="MsoNormal">Now the tests with nulls behave as I expect. But the empty string cases I did not expect. Is this a bug? Documented behaviour?<o:p></o:p></p>
<p class="MsoNormal"><o:p> </o:p></p>
<p class="MsoNormal">Martin Bowes<o:p></o:p></p>
</div>
</div></div>
</body></html>