Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]

Groups > comp.lang.java.databases > #338

Re: how to generate the f

Show all headers | View raw

  To: comp.lang.java.databases
In article 
<e1016b06-e37f-41bf-883a-c5ec3cca7856@t18g2000prt.googlegroups.com>,
 skywalker <mail.supratim@gmail.com> wrote:

> select * from mis_outtrack;
> +----------+-------------+---------+------------+------------+
> | pub_code | status_flag | user_id | entry_date | pub_date   |
> +----------+-------------+---------+------------+------------+
> | SND      | RE          | AMY     | 2008-11-01 | 2008-11-01 |
> | SND      | AU          | AMY     | 2008-11-05 | 2008-11-01 |
> | SND      | SE          | AMY     | 2008-11-10 | 2008-11-01 |
> | MND      | RE          | AMY     | 2008-11-03 | 2008-11-02 |
> | MND      | AU          | AMY     | 2008-11-15 | 2008-11-02 |
> | MND      | SE          | AMY     | 2008-11-15 | 2008-11-02 |
> +----------+-------------+---------+------------+------------+
> 
> ------------------------------------------------------------------------
> |  SND    2008-11-01    2008-11-05     2008-11-10    2008-11-01
> |  MND    2008-11-03    2008-11-15     2008-11-15    2008-11-02

[...]
> how can I do this in sql

Start with the easy part, the first and last columns:

  select distinct pub_code, pub_date
  from mis_outtrack
  order by pub_date;

Now add sub-selects for the middle three:

  select distinct pub_code,
    (select entry_date from mis_outtrack
      where status_flag = 'RE' and pub_code = mo.pub_code) re_date,
    (select entry_date from mis_outtrack
      where status_flag = 'AU' and pub_code = mo.pub_code) au_date,
    (select entry_date from mis_outtrack
      where status_flag = 'SE' and pub_code = mo.pub_code) se_date,
    pub_date
  from mis_outtrack mo
  order by pub_date;

OB Java: Use a java.sql.PreparedStatement.

-- 
John B. Matthews
trashgod at gmail dot com
http://home.roadrunner.com/~jbmatthews/

---
 * Synchronet * The Whitehouse BBS --- whitehouse.hulds.com --- check it out free usenet!
--- Synchronet 3.15a-Win32 NewsLink 1.92
Time Warp of the Future BBS - telnet://time.synchro.net:24

Back to comp.lang.java.databases | Previous | Next | Find similar

Thread

Re: how to generate the f "John B. Matthews" <john.b..matthews@THRWHITE.remove-dii-this> - 2011-04-27 15:23 +0000

csiph-web