[Date Prev][Date Next][Thread Prev][Thread Next]
[Author Index]
[Date Index]
[Thread Index]
[SQR-USERS Info]
[SQRUG Home Page]
RE: [sqr-users] MAX() Query
- Subject: RE: [sqr-users] MAX() Query
- From: Bob Stone <bstone@fastenal.com>
- Date: Thu, 2 Dec 2004 09:52:11 -0600
- Delivery-date: Thu, 02 Dec 2004 10:53:25 -0500
- List-id: "This list is for discussion about the SQR database reportinglanguage from Hyperion Solutions." <sqr-users.sqrug.org>
I'm not big on vernacular...but sure. I think its called an in-line query.
That is a:
select * from (select a.a, a.b, b.c from table a, b where a.d = b.d) where
[$where_clause]
select * from (inner select) where some 2ndary clause
-----Original Message-----
From: sqr-users-bounces+bstone=fastenal.com@sqrug.org
[mailto:sqr-users-bounces+bstone=fastenal.com@sqrug.org]On Behalf Of
Turner, Ivan
Sent: Thursday, December 02, 2004 9:31 AM
To: This list is for discussion about the SQR database reportinglanguage
from Hyperion Solutions.
Subject: RE: [sqr-users] MAX() Query
Thanks. These queries do exactly as expected.
One question. Is this called an in-line query?
-----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
Bob Stone
Sent: Wednesday, December 01, 2004 2:55 PM
To: 'This list is for discussion about the SQR database reporting
languagefrom Hyperion Solutions.'
Subject: RE: [sqr-users] MAX() Query
Jim is correct on this one. The problem with the original one (which we
all
sent you) was that the rownum was in the original query....this means
that
it grabs the first 10 rows it sees in the database, then orders them by
balance. You want it to grab ALL the rows, order them, then only get
the
first 10 of those. so yeah...
select * from
( select ****everything you say except 'and rownum < 11'...order by
a.bal_amt desc )
where rownum < 11
-----Original Message-----
From: sqr-users-bounces+bstone=fastenal.com@sqrug.org
[mailto:sqr-users-bounces+bstone=fastenal.com@sqrug.org]On Behalf Of
James Womeldorf
Sent: Wednesday, December 01, 2004 1:44 PM
To: 'This list is for discussion about the SQR database reporting
language from Hyperion Solutions.'
Subject: RE: [sqr-users] MAX() Query
My mistake. Sorry. try this:
SELECT x.BAL_amt, x.BAL_TOTAL, x.BAL_CURRENT
(SELECT a.BAL_amt BAL_amt, C.BAL_TOTAL BAL_TOTAL, C.BAL_CURRENT
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')
order by a.bal_amt DESC) x
where rownum < 11
Jim
-----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, December 01, 2004 1:39 PM
To: This list is for discussion about the SQR database reportinglanguage
from Hyperion Solutions.
Subject: RE: [sqr-users] MAX() Query
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
_______________________________________________
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