Re: Query help required please.

Steve,

I'd propose to create a function-based index like

  CREATE INDEX <index_name> ON <table_name>( to_char(hirestart,'yyyymm') );

It will be used if you query

  ... to_char(hirestart,'yyyymm') = to_char(sysdate,'yyyymm')

Regards,
Ingo

"Dirk Tschentscher" <dirk.tschentscher_at_volkswagen.de> wrote in message news:<apofof$2oe5_at_doiweb4.volkswagen.de>...
> Hi Steve,
> when I understand you right then
> b) and to_char(hirestart,'yyyymm') =
> (select
> decode(to_char(sysdate,'mm'),'01',to_char(sysdate,'yyyy') -1 ||'12',
> to_char(sysdate,'yyyymm')-1)
> from dual)
> c) and to_char(hirestart,'yyyymm') = to_char(sysdate,'yyyymm')
>
> maybe not the best, but it should work for smaller tables...
> or you add an indexed column with only the 'yyyymm' of the hirestart column
>
> I had a similar problem some time ago, has any "guru" a good idea ??
>
> regards
> Dirk
>
> "Steve" <hunt.s_at_blueyonder.co.uk> schrieb im Newsbeitrag
> news:0cVu9.37062$862.19867_at_news-binary.blueyonder.co.uk...
> > I am attempting to find which Customers (Names) have commenced a hire
> > contract as follows:-
> >
> > a) Ever.
> > b) Anytime last month and,
> > c) Anytime this month.
> >
> > My tables are as follows:
> >
> > ClientId Name Parent ClientType
> > -------- ------------------------------ ------ ----------
> > 1500 Sydney Symphony Orchestra T
> > 1501 Melbourne symphony Orchestra T
> > 1502 Daves Didjeridoo Duo P
> > 1503 Southern Cross String Quartet 1501 P
> > 1504 Barcaldine Barbershop Quartet 1501 P
> > 1505 Blue Room Quintet 1500 P
> > 1506 Wallerawang Whistlers P
> >
> > ContractId ClientId HireStart HireReturn
> > ---------- -------- --------- ----------
> > MU15 1505 10-AUG-02 16-SEP-02
> > MU16 1504 21-AUG-02 27-SEP-02
> > MU17 1503 04-SEP-02 30-SEP-02
> > MU18 1501 01-OCT-02 15-OCT-02
> > MU19 1500 07-OCT-02 31-OCT-02
> > MU20 1506 18-NOV-02 30-NOV-02
> > MU21 1503 20-NOV-02 01-DEC-02
> > MU22 1505 15-NOV-02 05-DEC-02
> > MU23 1506 01-DEC-02 14-DEC-02
> > MU24 1500 02-DEC-02 21-DEC-02
> > MU25 1503 16-DEC-02 02-JAN-03
> > MU26 1501 17-DEC-02 10-JAN-03
> > MU27 1503 03-JAN-03 31-JAN-03
> > MU28 1504 10-JAN-03 24-JAN-03
> >
> > I have tried the query below but it lists all matches. Is there a way of
> > avoiding displaying duplicates?
> >
> > Select Clients.Name,
> > HireContracts.ClientId
> > from Clients, HireContracts
> > where Clients.ClientId = HireContracts.ClientId;
> >
> > Any help with b) and c) very much welcome.
> >
> > Many thanks in advance.
> >
> > Mailto: hunt.s_at_blueyonder.co.uk
> >
> >
Received on Thu Oct 31 2002 - 05:10:32 CST