Re: FrontBase SQL: date formatting, „local variable“?
Re: FrontBase SQL: date formatting, „local variable“?
- Subject: Re: FrontBase SQL: date formatting, „local variable“?
- From: OC <email@hidden>
- Date: Fri, 11 Mar 2016 22:06:41 +0100
Chuck,
On 11. 3. 2016, at 21:59, Chuck Hill <email@hidden> 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=email@hidden on behalf of OC" <webobjects-dev-bounces+chill=email@hidden on behalf of email@hidden> 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 (email@hidden)
>> Help/Unsubscribe/Update your Subscription:
>>
>> This email sent to email@hidden
_______________________________________________
Do not post admin requests to the list. They will be ignored.
Webobjects-dev mailing list (email@hidden)
Help/Unsubscribe/Update your Subscription:
This email sent to email@hidden