February 08, 2008

LOV with splitted data

I found an easy technique to visualize data in lov's like this:



Use multiple UNION ALL's to concatenate the data :

select '---new colleagues---' ename, NULL job, NULL hiredate
 from dual
UNION ALL
select ename, job, to_char (hiredate, 'DD.MM.YYYY')
 from emp where hiredate >= to_date ('01.07.1981', 'DD.MM.YYYY')
UNION ALL
select '---before 07/81---' ename, NULL job, NULL hiredate
 from dual
UNION ALL
select ename, job, to_char (hiredate, 'DD.MM.YYYY')
 from emp where hiredate < to_date ('01.07.1981', 'DD.MM.YYYY')

have fun and use it
Gerd

3 comments:

  1. UnknownFebruary 08, 2008

    Wouldn't you have to a bit more to ensure good sorting?

    ReplyDelete
  2. NigelFebruary 08, 2008

    Gerd

    You should include ORDER BY:

    SELECT ename, job, hiredate
    FROM (
    select ...
    UNION ALL
    select ...
    UNION ALL
    select ...
    )
    ORDER BY hiredate

    or order by ename within subquery - your choice.

    And make sure you can't pick the "heading" rows.

    PS I love that "new colleagues" are since 1981 - is it time for the SCOTT company to experience some staff turnover?

    Regards Nigel

    ReplyDelete
  3. Gerd VolbergFebruary 08, 2008

    Ok, sorting is important, I know. In case of my split-select I have to select like this:

    select '---new colleagues---' ename, NULL job, NULL hiredate
    from dual
    UNION ALL
    select ename, job, hiredate
    from (sorted select with where-clause)
    UNION ALL
    select '---before 07/81---' ename, NULL job, NULL hiredate
    from dual
    UNION ALL
    select ename, job, hiredate
    from (sorted select with where-clause)

    ReplyDelete