RE: [sqr-users] fetch value in array

["George Jansen" <GJANSEN@aflcio.org>]

If you are using Oracle Enterprise Edition, 8i and up, this is a lightweight 
query:

SELECT emplid, company, xcount
FROM (SELECT emplid, company, xcount,
           Row_Number() OVER (PARTITION by emplid ORDER BY xcount DESC) AS rn
           FROM yr_table)
WHERE rn = 1

(xcount because "count" is a reserved word). Otherwise


SELECT emplid, company, xcount
FROM your_table a
WHERE NOT EXISTS
   (SELECT 1 FROM your_table b
    WHERE b.emplid = a.emplid AND
         (b.xcount > a.xcount OR (b.xcount = a.xcount AND b.rowid < a.rowid)))



>>> gkrishan@bigpond.net.au 01/25/06 5:53 AM >>>
With EDS APIs, you can implement on-break logic when processing data from
array.
EDS (Event Driven SQRs) is part of SqrPlus software. It provides a
simplified way of implementing   on-break logic that can be very complex and
time consuming otherwise.

Gopal.

-----Original Message-----
From: sqr-users-bounces+gkrishan=bigpond.net.au@sqrug.org 
[mailto:sqr-users-bounces+gkrishan=bigpond.net.au@sqrug.org]On Behalf Of
Steve Cavill
Sent: Wednesday, January 25, 2006 8:41 PM
To: 'This list is for discussion about the SQR database
reportinglanguagefrom Hyperion Solutions.'
Subject: RE: [sqr-users] fetch value in array


Load the table into a temp table and use on-break to identify the last
record on each employee group.
Steve.

-----Original Message-----
From: sqr-users-bounces+steve.cavill=infoclarity.com.au@sqrug.org 
[mailto:sqr-users-bounces+steve.cavill=infoclarity.com.au@sqrug.org] On
Behalf Of Bob Stone
Sent: Saturday, 7 January 2006 3:59 AM
To: This list is for discussion about the SQR database reporting
languagefrom Hyperion Solutions.
Subject: RE: [sqr-users] fetch value in array

wow.  convoluted example....nice use of the same 3 letters all caps for
5 different employees...real readable.

So, I'm going to pretend the first guy is ABC, second is LMN, and third is
XYZ.  Or maybe we got Jones, Smith, and Grady.  ...XYZ
ZYX YZX YXZ, not so good.

select *
from YOURTABLE a
where (a.emplid, a.company, a.count) in
       (select b.emplid, b.company, b.count from yourtable b
        where a.emplid = b.emplid and a.company = b.company
          and b.count = ( select max(c.count) from YOURTABLE c where

                      b.emplid = c.emplid and b.company = c.company)

Is there an easier way?  probably.  do you have to join 3 times?  maybe not.
It's just what I would try.


-----Original Message-----
From: sqr-users-bounces+bstone=fastenal.com@sqrug.org 
[mailto:sqr-users-bounces+bstone=fastenal.com@sqrug.org] On Behalf Of
prashanth reddy
Sent: Friday, January 06, 2006 10:43 AM
To: sqr-users@sqrug.org 
Subject: [sqr-users] fetch value in array

Hi,

 I'm using array in sqr and data in array looks like
this:

Emplid  Company Count
XYZ       A      1
XYZ       A      2
XYZ       A      3
YXZ       B      1
YXZ       B      2
YXZ       B      3
ZYX       A      1
YZX       B      1

>From this array I'm supposed to get max count for each
emplid, i.e for emplid XYZ have to get line 3 (XYZ   A
 3), for emplid YXZ (YXZ  B 3) and for emplid ZYX (ZYX  A  1).

When i compare row value, I'm not able to get the correct results.
Any help or suggestion would be appreciated.

Thanks,
Prashanth


_______________________________________________
sqr-users mailing list
sqr-users@sqrug.org 
http://www.sqrug.org/mailman/listinfo/sqr-users 



_______________________________________________
sqr-users mailing list
sqr-users@sqrug.org 
http://www.sqrug.org/mailman/listinfo/sqr-users 


_______________________________________________
sqr-users mailing list
sqr-users@sqrug.org 
http://www.sqrug.org/mailman/listinfo/sqr-users


_______________________________________________
sqr-users mailing list
sqr-users@sqrug.org
http://www.sqrug.org/mailman/listinfo/sqr-users