[storm] join and subquery

[akm]

Hi All,

I have a query that looks like this: Is there any way to convert this to storm ?

 SELECT *
 FROM Table1 me
 LEFT JOIN (
                             SELECT MAX(crt_dt) crt_dt,
                                    test_idn
                             FROM Table2
                             WHERE test_cd = 'Completed'
                             GROUP BY test_idn)  inner_query1
                  ON inner_query1.test_idn = me.test_idn
 LEFT JOIN (
                             SELECT MAX(crt_dt) crt_dt,
                                    test_idn
                             FROM Table2
                             WHERE test_cd = 'Closed'
                             GROUP BY test_idn)  inner_query2
                  ON inner_query2.test_idn = me.test_idn



akm at akm-laptop:~$ sqlite3
SQLite version 3.4.2
Enter ".help" for instructions
sqlite> Create table Table1( test_idn numeric(18) );
sqlite> insert into Table1 values (1);
sqlite> insert into Table1 values (2);
sqlite> Create table Table2( test_idn numeric(18), test_cd
varchar(50), crt_dt datetime );
sqlite> insert into Table2 values (1, 'Closed', date('now'));
sqlite> insert into Table2 values (1, 'Completed', date('now'));



akm at akm-laptop:~$ python
Python 2.5.2 (r252:60911, Jul 31 2008, 17:28:52)
[GCC 4.2.3 (Ubuntu 4.2.3-2ubuntu7)] on linux2
Type "help", "copyright", "credits" or "license" for more information.
>>> db =create_database('sqllite:temp')
>>> store = Store(db)
>>> class Table1(object):
...     __storm_table__ = 'Table1'
...     test_idn = Int(primary=True)
>>> class Table2(object):
...     __storm_table__ = 'Table2'
...     test_idn = Int(primary=True)
...     test_cd = RawStr()
...     crt_dt = DateTime()

I have converted the sub-query to storm.

>>>subselect = Select((Max(Table2.crt_dt),
...                           Table2.test_idn),
...                           (Table2.test_cd == 'Closed'),
...                           group_by=Table2.test_idn)


--
Abdul Kader M