Chuck, On 11. 3. 2016, at 21:59, Chuck Hill <ch...@gevityinc.com> wrote:
> It is supposed to be SQL92 compliant. If standard SQL can’t do it, you might > be out of luck. I am no SQL guru myself; the google results look like there is a terrible mess of standard- and server-specific tools. Eg., there seems to be DATE_FORMAT(date,'format') -- which does not work in FrontBase. There seems to be LPAD(string,width,padding) -- which does not work there either. I did not succeed to find any decent SQL92 description; the best I have found is http://www.contrib.andrew.cmu.edu/~shadow/sql/sql1992.txt and that one's pretty messy. Anyhow, so far, I haven't been able to find a solution there either. Still, it can be solvable (perhaps even easily) (a) in standard SQL92 which I don't know well enough (b) in some FrontBase-specific extension which is why I am asking. > This is not something you can do client-side? Not easily; I would rather need to implement it as a modelled derived attribute. If it proves impossible, I can mangle the data client-side, but it would get pretty messy: those are still the rawrows, no entity code there to help. Thanks a lot, OC > n 2016-03-11, 12:47 PM, > "webobjects-dev-bounces+chill=gevityinc....@lists.apple.com on behalf of OC" > <webobjects-dev-bounces+chill=gevityinc....@lists.apple.com on behalf of > o...@ocs.cz> wrote: > >> Hello there, >> >> any FrontBase SQL gurus on-line? For a derived attribute, I would need >> >> -- (a) to format a TIMESTAMP as “%Y%n%e” >> >> So far the best I have found is >> >> CAST(EXTRACT(year FROM DATE) AS VARCHAR(4)) || >> CAST(EXTRACT(month FROM DATE) AS VARCHAR(2)) || >> CAST(EXTRACT(day FROM DATE) AS VARCHAR(2)) >> >> but this does not zero-pad, ie., instead of the desired “20160302” I get >> improper result “201632”. >> >> How to zero-pad the month and day? >> >> -- (b) to define and use a “local variable” >> >> The problem is that the “DATE” above is not a column, but a SELECT >> sub-expression. If I repeat it again and again and again, it does work all >> right, but the SQL is really ugly (and, I suppose, also inefficient). I >> would need something like >> >> VAR mydate=( SELECT C_START_DATE FROM T_TIME_CHUNK WHERE T_TIME_CHUNK.C_UID >> = C_TIME_CHUNK_UID ) >> CASE >> WHEN mydate IS NULL THEN 'fallback-value' >> ELSE >> CAST(EXTRACT(year FROM mydate) AS VARCHAR(4)) || >> CAST(EXTRACT(month FROM mydate) AS VARCHAR(2)) || >> ... >> END >> >> Can this be done anyhow? >> >> Thanks a big lot, >> OC >> >> >> _______________________________________________ >> Do not post admin requests to the list. They will be ignored. >> Webobjects-dev mailing list (Webobjects-dev@lists.apple.com) >> Help/Unsubscribe/Update your Subscription: >> https://lists.apple.com/mailman/options/webobjects-dev/chill%40gevityinc.com >> >> This email sent to ch...@gevityinc.com _______________________________________________ Do not post admin requests to the list. They will be ignored. Webobjects-dev mailing list (Webobjects-dev@lists.apple.com) Help/Unsubscribe/Update your Subscription: https://lists.apple.com/mailman/options/webobjects-dev/archive%40mail-archive.com This email sent to arch...@mail-archive.com
