I'm working on something for a client. The query below basically
returns 1 record for the symbol he inputs. All the tables basically
contain 1 record for each symbol except the history table, that
contains many per symbol hence the DENSE_RANK and limiting it to the
first item based on the DATE, ordered by.

Now, the first column is RANK. What the customer now wants is to
compare that rank with the immediate previous one (based on the
ordered date) and have returned a string: 'UP','DOWN', or 'NONE'.

Not sure that can be done in a query anymore.......thoughts? gonna
try some serious analytical functions here.

SELECT rank, recommendation, estimate, ind_rank, comp_ind,
last_report_file, rank_count, ind_code, last_rank_date
FROM (
SELECT zrh.m_ticker,
NVL(dzr.z_rank_d, -9999) rank,
NVL(DECODE(zrc.cur_rec_value, 1, 'BUY', 3, 'HOLD', 5, 'SELL'),
'N/A') recommendation,
NVL(tpec.value, -9999) estimate,
NVL(ia.ord_rank_d_rank, -9999) ind_rank,
NVL(ci.ord_rank_d_rank, -9999) comp_ind,
NVL(zrc.last_report_file, 'N/A') last_report_file,
NVL(ia.count_rank, -9999) rank_count,
NVL(ia.ind_code, -9999) ind_code,
NVL(TO_CHAR(zrh.time,'MM/DD/YYYY'),NULL) last_rank_date,
DENSE_RANK()
OVER (PARTITION BY zrh.m_ticker ORDER BY zrh.time DESC) max_date
FROM master_table mt,
daily_zacks_rank dzr,
zacks_rank_history zrh,
zr_recom_cur zrc,
trg_price_est_cur tpec,
comp_ind ci,
industry_agg ia
WHERE mt.m_ticker = dzr.m_ticker (+)
AND mt.m_ticker = zrc.m_ticker (+)
AND mt.m_ticker = zrh.m_ticker (+)
AND mt.m_ticker = tpec.master (+)
AND mt.m_ticker = ci.m_ticker(+)
AND ci.ind_code = ia.ind_code (+)
AND zrh.m_ticker = 'IBM')
WHERE max_date = 1;

On Feb 5, 1:39*pm, Mtek <m... (AT) mtekusa (DOT) com> wrote:
Well, I guess one option is an INLINE function of sorts.....

On Feb 5, 1:39*pm, Mtek <m... (AT) mtekusa (DOT) com> wrote:
If you're running 9.2.0.8 or later you can use either the LEAD() or LAG
() function. Examples follow.

SQL> create table parts(
2 idpart number,
3 mydate date,
4 idcategory number,
5 hours number
6 );

Table created.

SQL>
SQL> insert all
2 into parts
3 values (1, to_date('01/01/2001','MM/DD/YYYY'), 27, 7)
4 into parts
5 values (1, to_date('01/01/2001','MM/DD/YYYY'), 27, 5)
6 into parts
7 values (1, to_date('02/01/2001','MM/DD/YYYY'), 27, 2)
8 into parts
9 values (2, to_date('03/01/2001','MM/DD/YYYY'), 15, 8)
10 into parts
11 values (1, to_date('04/01/2001','MM/DD/YYYY'), 27, 5)
12 into parts
13 values (1, to_date('05/01/2001','MM/DD/YYYY'), 27, 6)
14 select * From dual;

6 rows created.

SQL>
SQL> commit;

Commit complete.

SQL>
SQL> select mydate, max(enddate), idcategory, ttlhrs
2 from
3 (select p.mydate, lead(p.mydate) over (order by p.mydate,
p.idpart, p.idcategory) enddate, p.idcategory, d.ttlhrs
4 from parts p, (select mydate, sum(hours) ttlhrs from parts
group by mydate) d
5 where d.mydate = p.mydate)
6 having max(enddate) is not null
7 group by mydate, idcategory, ttlhrs
8 /

MYDATE MAX(ENDDA IDCATEGORY TTLHRS
--------- --------- ---------- ----------
01-JAN-01 01-FEB-01 27 12
01-FEB-01 01-MAR-01 27 2
01-APR-01 01-MAY-01 27 5
01-MAR-01 01-APR-01 15 8

SQL>
SQL> -- with get_hrs as(
SQL> -- select idpart, mydate, idcategory, sum(hours) ttlhrs
SQL> -- from parts
SQL> -- group by idpart, mydate, idcategory
SQL> -- )
SQL> -- select get_hrs.mydate
SQL>
SQL> create table yakima(
2 username varchar2(30),
3 start_date date,
4 end_date date
5 );

Table created.

SQL>
SQL> insert all
2 into yakima
3 values('scott', to_date('01-sep-2008','dd-mon-rrrr'), to_date('30-
sep-2008','dd-mon-rrrr'))
4 into yakima (username)
5 values('tiger')
6 select * from dual;

2 rows created.

SQL>
SQL> commit;

Commit complete.

SQL>
SQL> select username,
2 case when start_date is null then lag(end_date) over (order
by end_date) else start_date end start_date,
3 end_date
4 from yakima;

USERNAME START_DAT END_DATE
------------------------------ --------- ---------
scott 01-SEP-08 30-SEP-08
tiger 30-SEP-08

SQL>
SQL> create table gronkenflotz(
2 product_id number,
3 product_name varchar2(35),
4 to_mkt_dt date,
5 off_mkt_dt date,
6 repl_prod number
7 );

Table created.

SQL>
SQL> insert all
2 into gronkenflotz
3 values(47, 'Poodle Buffer', to_date('01-jun-1978','dd-mon-rrrr'),
to_date('29-mar-1978','dd-mon-rrrr'), null)
4 into gronkenflotz
5 values(71, 'Raisin De-Wrinkler', to_date('01-jun-1979','dd-mon-
rrrr'), to_date('27-feb-1980','dd-mon-rrrr'), 347)
6 into gronkenflotz
7 values(347, 'Noodle Straightener', to_date('01-jun-1967','dd-mon-
rrrr'), to_date('28-feb-1968','dd-mon-rrrr'), 47)
8 into gronkenflotz
9 values(973, 'Macaroni De-Elbowifier', null, null, 71)
10 select * From dual;

4 rows created.

SQL>
SQL> commit;

Commit complete.

SQL>
SQL> select product_id, product_name,
2 to_mkt_dt,
3 off_mkt_dt,
4 repl_prod
5 from
6 gronkenflotz
7 connect by repl_prod = prior product_id
8 start with product_id = 47;

PRODUCT_ID PRODUCT_NAME TO_MKT_DT OFF_MKT_D
REPL_PROD
---------- ----------------------------------- --------- ---------
----------
47 Poodle Buffer 01-JUN-78 29-MAR-78
347 Noodle Straightener 01-JUN-67 28-
FEB-68 47
71 Raisin De-Wrinkler 01-JUN-79 27-
FEB-80 347
973 Macaroni De-
Elbowifier 71

SQL>
SQL> select product_id, product_name,
2 case when to_mkt_dt is null then lag(off_mkt_dt) over
(order by repl_prod desc) else to_mkt_dt end to_mkt_dt,
3 off_mkt_dt,
4 repl_prod
5 from
6 (select product_id, product_name,
7 to_mkt_dt,
8 off_mkt_dt,
9 repl_prod
10 from
11 gronkenflotz
12 connect by repl_prod = prior product_id
13 start with product_id = 47);

PRODUCT_ID PRODUCT_NAME TO_MKT_DT OFF_MKT_D
REPL_PROD
---------- ----------------------------------- --------- ---------
----------
47 Poodle Buffer 01-JUN-78 29-MAR-78
71 Raisin De-Wrinkler 01-JUN-79 27-
FEB-80 347
973 Macaroni De-Elbowifier 27-
FEB-80 71
347 Noodle Straightener 01-JUN-67 28-
FEB-68 47

SQL>

Hopefully you'll get the idea.


David Fitzjarrell

On Feb 5, 2:11*pm, ddf <orat... (AT) msn (DOT) com> wrote:

Yeah, my fault. 10g.

I'll review and test your query.....thanks!

On Feb 5, 2:11*pm, ddf <orat... (AT) msn (DOT) com> wrote:

Yeah, my fault. 10g.

I'll review and test your query.....thanks!

On 05.02.2009 22:25, Mtek wrote:
Erm, do you really have to post this multiple times?

robert

On Feb 5, 2:11*pm, ddf <orat... (AT) msn (DOT) com> wrote:

Yeah, my fault. 10g.

I'll review and test your query.....thanks!

On Feb 5, 3:37*pm, Robert Klemme <shortcut... (AT) googlemail (DOT) com> wrote:

It is weird. I make a post, refresh my screen and when I refresh it
re-posts the previous post. Like I need to close and re-open the
window....

Mtek schreef:
Which we told you a zillion times. Get another news reader!

Shakespeare

On Feb 9, 11:10*am, Shakespeare <what... (AT) xs4all (DOT) nl> wrote:
It appears he's using google. Which newsreader would you recommend
with similar features?

jg
--
@home.com is bogus.
http://ma.tt/2009/01/google-account-takeover/