Results 1 to 4 of 4
  1. #1
    Join Date
    Jun 2004
    Posts
    12

    Unanswered: Denormalization query

    Hi all,
    I've got a table structured as follow where the code field identify a particular parameter. Is it possible with a single query to retrieve more than just one parameter at a time? I mean can I get a recordset with the first field representing the date, the second field holding the values of parameter code [001], the third field the values of parameter code [002] taking into account NULL values as well??

    Table structure
    CREATE TABLE test (
    date TIMESTAMP NOT NULL ,
    code VARCHAR(3) NOT NULL ,
    value REAL
    );
    INSERT INTO test VALUES ('01/01/2005 00:00:00', '001', 89);
    INSERT INTO test VALUES ('01/01/2005 00:00:00', '002', 1);
    INSERT INTO test VALUES ('01/01/2005 01:00:00', '001', 76);
    INSERT INTO test VALUES ('01/01/2005 01:00:00', '002', NULL);
    INSERT INTO test VALUES ('01/01/2005 02:00:00', '001', 67);
    INSERT INTO test VALUES ('01/01/2005 02:00:00', '002', 7);
    INSERT INTO test VALUES ('01/01/2005 03:00:00', '001', 46);
    INSERT INTO test VALUES ('01/01/2005 03:00:00', '002', NULL);
    INSERT INTO test VALUES ('01/01/2005 04:00:00', '001', 43);
    INSERT INTO test VALUES ('01/01/2005 04:00:00', '002', 3);

    ecodms=# select * from test;
    date | code | value
    ---------------------+------+-------
    2005-01-01 00:00:00 | 001 | 89
    2005-01-01 00:00:00 | 002 | 1
    2005-01-01 01:00:00 | 001 | 76
    2005-01-01 01:00:00 | 002 |
    2005-01-01 02:00:00 | 001 | 67
    2005-01-01 02:00:00 | 002 | 7
    2005-01-01 03:00:00 | 001 | 46
    2005-01-01 03:00:00 | 002 |
    2005-01-01 04:00:00 | 001 | 43
    2005-01-01 04:00:00 | 002 | 3
    (10 rows)

    ecodms=#

    Final Recordset should be like this:
    date | value_cod1 | value_cod2
    ---------------------+------------+------------
    2005-01-01 00:00:00 | 89 | 1
    2005-01-01 01:00:00 | 76 |
    2005-01-01 02:00:00 | 67 | 7
    2005-01-01 03:00:00 | 46 |
    2005-01-01 04:00:00 | 43 | 3

    Thank you very much!
    Paolo Saudin

  2. #2
    Join Date
    Jun 2004
    Posts
    57

    Here is the query

    select tt.date, t.value, tt.value
    from test tt inner join test t on tt.date = t.date
    where tt.code = (select max(code) from test where date = tt.date)
    and t.code = (select min(code) from test where date = tt.date);

  3. #3
    Join Date
    Jun 2004
    Posts
    12
    Thank you very much AStefan, is it possible to join a second table that contains only data values to get NULLs when no records are found in the test table ?

    test table
    date | code | value
    ---------------------+------+-------
    2005-01-01 00:00:00 | 001 | 89
    2005-01-01 00:00:00 | 002 | 1
    No data
    No data
    2005-01-01 02:00:00 | 001 | 67
    2005-01-01 02:00:00 | 002 | 7
    2005-01-01 03:00:00 | 001 | 46
    2005-01-01 03:00:00 | 002 |
    2005-01-01 04:00:00 | 001 | 43
    2005-01-01 04:00:00 | 002 | 3

    master table
    date
    --------------------
    2005-01-01 00:00:00
    2005-01-01 01:00:00
    2005-01-01 02:00:00
    2005-01-01 03:00:00
    2005-01-01 04:00:00

    Final Recordset should be like this:
    date | value_cod1 | value_cod2
    ---------------------+------------+------------
    2005-01-01 00:00:00 | 89 | 1
    2005-01-01 01:00:00 |NULL| NULL
    2005-01-01 02:00:00 | 67 | 7
    2005-01-01 03:00:00 | 46 | NULL
    2005-01-01 04:00:00 | 43 | 3

    Thank again,
    Paolo

  4. #4
    Join Date
    Jun 2004
    Posts
    57

    Query

    The query will be the following:

    select master.date, selectedvalues.value_cod1, selectedvalues.value_cod2
    from master left outer join
    (select tt.date, t.value as value_cod1, tt.value as value_cod2
    from test tt inner join test t on tt.date = t.date
    where tt.code = (select max(code) from test where date = tt.date)
    and t.code = (select min(code) from test where date = tt.date) ) as selectedvalues
    on master.date = selectedvalues .date;

    The problem is if it will be more than 2 codes. In this case, you cannot use min and max and you must use another way of getting results.
    The pleasure was mine in helping you.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •