RE: [sqr-users] MAX() Query

["Turner, Ivan" <Ivan.Turner@qwest.com>]

I am not getting the 10 highest bal_amt values from the database.  

I could probably remove the rownum < 11 and use the sqr LOOPS=10.  

I really want to know how to write a query returning 10 rows of the
highest amounts that I could execute in sqr OR outside of sqr.

The highest value in the db is $51,185,837.30


SELECT  a.BAL_amt, C.BAL_TOTAL, C.BAL_CURRENT
FROM PS_CUST_DATA A, PS_CUSTOMER B, PS_DAILY_AG_VW_L C
WHERE A.BUSINESS_UNIT = 'LATIS'
AND A.CUST_ID = B.CUST_ID
AND A.CUST_ID = C.CUST_ID
AND substr(B.SEGMENT_ID,1,1) NOT IN ('W','A')
AND  rownum < 11
order by a.bal_amt DESC
SQL> /

   BAL_AMT  BAL_TOTAL BAL_CURRENT
---------- ---------- -----------
     284.7      284.7       284.7
      61.4       61.4       24.72
     56.95      56.95       30.19
     54.26      54.26        46.4
     27.46      27.46       27.46
      21.5       21.5        21.5
      1.38       1.38        1.38
         1          1           1
     -1.45      -1.45           0
    -507.7     -507.7      -507.7

10 rows selected.


select max(bal_amt) from PS_CUST_DATA
SQL> /

MAX(BAL_AMT)
------------
  51185837.3


-----Original Message-----
From: sqr-users-bounces+ivan.turner=qwest.com@sqrug.org
[mailto:sqr-users-bounces+ivan.turner=qwest.com@sqrug.org] On Behalf Of
James Womeldorf
Sent: Wednesday, November 24, 2004 3:13 PM
To: 'This list is for discussion about the SQR database reporting
languagefrom Hyperion Solutions.'
Subject: RE: [sqr-users] MAX() Query

I think your first query is close, but you need to do a DESC to put the
largest values first in the return set:

SELECT  a.BAL_amt, A.CUST_ID, B.SEGMENT_ID, B.NAME1, C.BAL_TOTAL,
C.BAL_CURRENT
FROM PS_CUST_DATA A, PS_CUSTOMER B, PS_DAILY_AG_VW_L C
WHERE A.BUSINESS_UNIT = 'LATIS'
AND A.CUST_ID = B.CUST_ID
AND A.CUST_ID = C.CUST_ID
AND substr(B.SEGMENT_ID,1,1) NOT IN ('W','A')
AND  rownum < 11
order by a.bal_amt DESC


-----Original Message-----
From: sqr-users-bounces+jwomeldo=fastenal.com@sqrug.org
[mailto:sqr-users-bounces+jwomeldo=fastenal.com@sqrug.org]On Behalf Of
Turner, Ivan
Sent: Wednesday, November 24, 2004 2:05 PM
To: This list is for discussion about the SQR database reportinglanguage
from Hyperion Solutions.
Subject: [sqr-users] MAX() Query


I am trying to get the ten highest balance amounts from the db.  None of
these queries do the job.  Does anyone know how I can do this?

SELECT  a.BAL_amt, A.CUST_ID, B.SEGMENT_ID, B.NAME1, C.BAL_TOTAL,
C.BAL_CURRENT
FROM PS_CUST_DATA A, PS_CUSTOMER B, PS_DAILY_AG_VW_L C
WHERE A.BUSINESS_UNIT = 'LATIS'
AND A.CUST_ID = B.CUST_ID
AND A.CUST_ID = C.CUST_ID
AND substr(B.SEGMENT_ID,1,1) NOT IN ('W','A')
AND  rownum < 11
and  a.BAL_amt <= (select max(bal_amt) from ps_cust_data)
order by a.bal_amt

SELECT A.CUST_ID, max(a.bal_amt), B.SEGMENT_ID, B.NAME1, C.BAL_TOTAL,
C.BAL_CURRENT
FROM PS_CUST_DATA A, PS_CUSTOMER B, PS_DAILY_AG_VW_L C
WHERE A.CUST_ID = B.CUST_ID
AND A.CUST_ID = C.CUST_ID
AND A.BUSINESS_UNIT = C.BUSINESS_UNIT
AND A.BUSINESS_UNIT = 'LATIS'
AND substr( B.SEGMENT_ID,1,1) NOT IN ('W','A')
AND  rownum < 11
group by A.CUST_ID, B.SEGMENT_ID, B.NAME1, C.BAL_TOTAL, C.BAL_CURRENT,
C.BAL_30, C.BAL_60, C.BAL_90, C.BAL_120, C.BAL_150, C.BAL_180
/

SELECT A.CUST_ID, max(a.bal_amt), B.SEGMENT_ID, B.NAME1, C.BAL_TOTAL,
C.BAL_CURRENT
FROM PS_CUST_DATA A, PS_CUSTOMER B, PS_DAILY_AG_VW_L C
WHERE A.CUST_ID = B.CUST_ID
AND A.CUST_ID = C.CUST_ID
AND A.BUSINESS_UNIT = C.BUSINESS_UNIT
AND A.BUSINESS_UNIT = 'LATIS'
AND substr( B.SEGMENT_ID,1,1) NOT IN ('W','A')
AND  rownum < 11
group by A.CUST_ID, B.SEGMENT_ID, B.NAME1, C.BAL_TOTAL, C.BAL_CURRENT,
C.BAL_30, C.BAL_60, C.BAL_90, C.BAL_120, C.BAL_150, C.BAL_180
having max(a.bal_amt) > 100000
/

_______________________________________________
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