On Tue, Sep 14, 2010 at 2:07 AM, Gerhard Wiesinger <lists@wiesinger.com> wrote:
> Hello Merlin,
>
> Seems to be a feasible approach. On problem which might be that when
> multiple rows are returned that they are not ordered in each subselect
> correctly. Any idea to solve that?
>
> e.g.
> Raumsolltemperatur | Raumisttemperatur
> Value from time 1 | Value from time 2
> Value from time 2 | Value from time 1
>
> but should be
> Raumsolltemperatur | Raumisttemperatur
> Value from time 1 | Value from time 1
> Value from time 2 | Value from time 2
>
> But that might be solveable by first selecting keys from the log_details
> table and then join again.
>
> I will try it in the evening and I have to think about in detail.
>
> But thank you for the new approach and opening the mind :-)
Using subquery in that style select (<subquery>), ... is limited to
results that return 1 row, 1 column. I assumed that was the case...if
it isn't in your view, you can always attempt arrays:
CREATE OR REPLACE VIEW log_entries AS
SELECT
l.id AS id,
l.datetime AS datetime,
l.tdate AS tdate,
l.ttime AS ttime,
array(select value from log_details ld join key_description kd on
ld.fk_keyid = kd.keyid where ld.fk_id = l.id and description =
'Raumsolltemperatur' order by XYZ) AS Raumsolltemperatur,
[...]
arrays might raise the bar somewhat in terms of dealing with the
returned data, or they might work great. some experimentation is in
order.
XYZ being the ordering condition you want. If that isn't available
inside the join then we need to think about this some more. We could
probably help more if you could describe the schema in a little more
detail. This is solvable.
merlin