Re: Which SQL query should I go for?

"Michel Cadot" <micadot{at}altern{dot}org> wrote in message news:<41191c1d$0$23886$626a14ce_at_news.free.fr>...
> "Turkbear" <john.g_at_dot.spamfree.com> a écrit dans le message de
> news:5i6ih0961fh1btsn6r2ulflj2pcjpeah8i_at_4ax.com...
> > "Michel Cadot" <micadot{at}altern{dot}org> wrote:
> >
> > >
> > >"Turkbear" <john.g_at_dot.spamfree.com> a écrit dans le message de
> > >news:qq2ih0h374c2f5snbf0hmj1p611c71qob5_at_4ax.com...
> > >> "Michel Cadot" <micadot{at}altern{dot}org> wrote:
> > >>
> > >> >
> > >> >"Dimitri" <jose.cuervo_at_lycos.co.uk> a écrit dans le message de
> > >> >news:2452bd93.0408100049.46217571_at_posting.google.com...
> > >> >> Hello everyone,
> > >> >>
> > >> >> I was wondering if anyone could help me with a decision I have to
> > >> >> make.
> > >> >> I need to write a query inside an Oracle stored procedure that draws
> > >> >> its data from just one very large table called POSITION containing
> > >> >> hundrends of thousands of records.
> > >> >> The table contains records for various dates.
> > >> >> The query needs to return columns from one date (T), together with
> > >> >> columns from a previous date (T-1) in each record.
> > >> >> I have come up with the following two alternatives but I can't decide
> > >> >> which one is best in terms of efficiency and maintainability:
> > >> >>
> > >> >>
> > >> >> CHOICE 1:
> > >> >>
> > >> >> SELECT t.strategy_dim_key, t.POSITION_ID, t.account_hier_dim_key,
> > >> >> t_1.POSITION_ID ... (possibly more fields from date T or date T-1)
> > >> >> FROM POSITION t, POSITION t_1
> > >> >> WHERE t.cob_date = to_date('20040423','yyyymmdd')
> > >> >> AND t_1.cob_date = to_date('20040422','yyyymmdd')
> > >> >> AND t.strategy_dim_key = t_1.strategy_dim_key
> > >> >> AND t.account_hier_dim_key = t_1.account_hier_dim_key
> > >> >> ______________________________________________________________
> > >> >>
> > >> >> CHOICE 2:
> > >> >>
> > >> >> SELECT T.strategy_dim_key, T.position_id, T.account_hier_dim_key,
> > >> >> T_1.position_id FROM
> > >> >> (
> > >> >> SELECT n.strategy_dim_key,n.position_id,n.account_hier_dim_key,n.adjustment_dim_key
> > >> >> FROM POSITION n WHERE n.cob_date = to_date('20040423','yyyymmdd')
> > >> >> ) T,
> > >> >> (
> > >> >> SELECT n.strategy_dim_key,n.position_id,n.account_hier_dim_key,n.adjustment_dim_key
> > >> >> FROM POSITION n WHERE n.cob_date = to_date('20040422','yyyymmdd')
> > >> >> ) T_1
> > >> >> WHERE T.Strategy_dim_key = T_1.Strategy_dim_key
> > >> >> AND T.account_hier_dim_key = T_1.account_hier_dim_key
> > >> >>
> > >> >> _____________________________________________________________
> > >> >> columns strategy_dim_key and account_hier_dim_key form a composite
> > >> >> key.
> > >> >>
> > >> >> My worry is that choice 1, could result into a cartesian query
> > >> >> depending on how the optimiser is configured but I'm not a database
> > >> >> administrator so I don't know much about how or in what order the
> > >> >> optimiser chooses to run statements.
> > >> >> Choice 2 looks a bit long-winded to me.
> > >> >> Any help would be appreciated.
> > >> >>
> > >> >> Dimitri
> > >> >
> > >> >Have a look at lead/lag and analytic functions:
> > >> >
> > >> >http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96540/functions56a.htm#83619
> > >> >http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96540/functions2a.htm#81409
> > >>
> > >> Maybe I'm missing something ( not a rare event) but would not this work:
> > >>
> > >> SELECT t.strategy_dim_key, t.POSITION_ID, t.account_hier_dim_key,
> > >> t.POSITION_ID ...
> > >> FROM POSITION t
> > >> WHERE
> > >> (t.cob_date = to_date('20040423','yyyymmdd') OR t.cob_date = to_date('20040422','yyyymmdd') )
> > >> AND t.strategy_dim_key = t_1.strategy_dim_key
> > >> AND t.account_hier_dim_key = t_1.account_hier_dim_key
> > >>
> > >> With an index on cob_date it should be fairly efficient...
> > >>
> > >>
> > >
> > >What t_1 stand for?
> >
> > Sorry, bad editing..should be t. like the others..
> >
> > AND t.strategy_dim_key = t.strategy_dim_key
> > AND t.account_hier_dim_key = t.account_hier_dim_key
> >
> >
>
> And what is the meaning of these ones?
> strategy_dim_key is not null and account_hier_dim_key is not null
>
> Btw, the OP question is to get *in the same row* the values for one day and its previous one.
> That is what lag function is made for. Have a look at the first link i sent.

Michel understood the issue well. I want to have in the same row, data for one date and its previous one. T and T-1 are just the aliases of two instances of the same table called "POSITION". It look like the function Michel is suggesting would do the job, but the problem is that the link you gave me Michel requires registration and the page to set up a new account says I already have an account although I've never registered there in my life. If the page is not too big could you email it to me please? I would appreciate it greatly.

Thanks
Dimitri Received on Wed Aug 11 2004 - 09:25:54 CDT