StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>I would, for a start, not coalesce a per-row function like <code>date()</code>. You're better off testing the actual field for NULL as in the following example:</p> <pre><code>create table a (podate varchar(8)); insert into a (podate) values ('20090101'); insert into a (podate) values ('20090102'); insert into a (podate) values ('20090103'); insert into a (podate) values (null); commit; select case when podate is null then date('2000-01-01') else date(substr(podate,1,4) || '-' || substr(podate,5,2) || '-' || substr(podate,7,2)) end as mydate from a; drop table a; commit; </code></pre> <p>which outputs:</p> <pre><code>--------- MYDATE ---------- 2009-01-01 2009-01-02 2009-01-03 2000-01-01 DSNE610I NUMBER OF ROWS DISPLAYED IS 4 </code></pre> <p>In your case, the code would look something like:</p> <pre><code>select case when podate is null then date('12/31/99') else date (substr (podate,1,2) || char ('/') || substr (podate,3,2) || char ('/') || substr (podate,5,2) end </code></pre> <p>I haven't tested your specific case since my DB/2 is not set up for US date formats. That's something else to look out for as well. This may not work that well on platforms not using US date formats.</p> <p>Ideally, dates should be stored in the database as dates, not strings. In other words, the conversion costs should be <em>once</em> when the data enters a table, not the <em>millions of times</em> you're going to extract it. Then your code becomes a simpler and faster:</p> <pre><code>select case when podate is null then date('12/31/99') else podate end </code></pre> <p>or, better if you're never going to have that sentinel date as a real date, don't store <code>NULL</code>s at all. Instead, convert them to <code>date('12/31/99')</code> as they go into the tables, and use:</p> <pre><code>select podate </code></pre> <p>It doesn't get much simpler or faster than that (assuming you're comfortable with a non-NULL sentinel).</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload