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 23:57:34 +0100
Well...
On 11. 3. 2016, at 22:06, OC <email@hidden> wrote:
> 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.
... without better knowledge the SQL used to derive the attribute looks like this. Yes, it is _terribly_ ugly, but I can't see how to simplify it whilst keeping the functionality. I would appreaciate if anybody sees and advices how to simplify this brute, especially if there's a way to get rid of the repeated nested SELECTs:
===
CASE
WHEN ( SELECT C_SHORTCUT FROM T_MARKET WHERE T_MARKET.C_UID = C_MARKET_ID ) IS NULL
THEN
'Market(' ||
CAST(C_MARKET_ID as VARCHAR(18)) ||
')'
ELSE ( SELECT C_SHORTCUT FROM T_MARKET WHERE T_MARKET.C_UID = C_MARKET_ID )
END ||
'-' ||
CASE
WHEN ( SELECT C_START_DATE FROM T_TIME_CHUNK WHERE T_TIME_CHUNK.C_UID = C_TIME_CHUNK_UID ) IS NULL THEN '0'
ELSE
CAST(EXTRACT(year FROM ( SELECT C_START_DATE FROM T_TIME_CHUNK WHERE T_TIME_CHUNK.C_UID = C_TIME_CHUNK_UID )) AS VARCHAR(4)) ||
CASE
WHEN EXTRACT(month FROM ( SELECT C_START_DATE FROM T_TIME_CHUNK WHERE T_TIME_CHUNK.C_UID = C_TIME_CHUNK_UID ))<10 THEN '0'
ELSE ''
END ||
CAST(EXTRACT(month FROM ( SELECT C_START_DATE FROM T_TIME_CHUNK WHERE T_TIME_CHUNK.C_UID = C_TIME_CHUNK_UID )) AS VARCHAR(2)) ||
CASE
WHEN EXTRACT(day FROM ( SELECT C_START_DATE FROM T_TIME_CHUNK WHERE T_TIME_CHUNK.C_UID = C_TIME_CHUNK_UID ))<10 THEN '0'
ELSE ''
END ||
CAST(EXTRACT(day FROM ( SELECT C_START_DATE FROM T_TIME_CHUNK WHERE T_TIME_CHUNK.C_UID = C_TIME_CHUNK_UID )) AS VARCHAR(2))
END ||
'-' ||
CAST(C_AUCTION_SEQ as VARCHAR(18))
===
Thanks and all the best,
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