Re: SQL Help

[rob@xxxxxxxxx]

SQL does not directly support conversion from that format.  However, RPGLE 
does.  You could
1-Create a simple RPGLE service program that converts that.
2-Wrap that service program as a UDF or User Defined Function
3-Embed that UDF into your view.
An example of this is at
http://faq.midrange.com/data/cache/185.html
The example goes all the way into embedding the UDF into the view.
Your RPG UDF could return the following:

monitor;
return %date(bsdate:*CYMD0);
On-error 00112;
  // bsdate is malformed, return a default date;
  return defaultDate;
EndMon;

Views are nice, especially for use by query tools such as query/400 or GUI 
client server based tools.  However, performance wise, if you're imbedding 
this it may be better to convert your input variable from a real date to 
this goofy alpha.  This way the sql index advisor can use any access paths 
built over the goofy date.  Then again, it would be better if the 
underlying table used a real date field in the first place but we all have 
to deal with legacy stuff.
What I was saying is that

Select * from mytable
where bsdate=:DateAsAlpha

will perform better than

Select * from mytable
where MyUDF(bsdate)=:DateAsDate

Because the first one can use an index over date.


Rob Berendt