Home » SQL & PL/SQL » SQL & PL/SQL » SQL Top N Analysis
| SQL Top N Analysis [message #597566] |
Sun, 06 October 2013 13:14  |
 |
Hometown1
Messages: 23
Registered: March 2011
|
Junior Member |
|
|
Oracle8 Enterprise Edition Release 8.0.5.0.0 - Production
PL/SQL Release 8.0.5.0.0 - Production
CORE Version 4.0.5.0.0 - Production
TNS for 32-bit Windows: Version 8.0.5.0.0 - Production
NLSRTL Version 3.3.2.0.0 - Production
With reference to above oracle version. I am trying to retrieve top 300 donors from the table, below is my query.
Select * FROM
(select
distinct(d.contact_code),
c.contact_title||' '||c.contact_name,
c.contact_address,
c.contact_pcode,
ci.city_name,
cy.country_name,
to_char(d.donation_date,'DD-MM-RRRR'),
sum(cd.category_amount)
from
tbl_donation d,
tbl_contact c,
tbl_city ci,
tbl_country cy,
tbl_category_detail cd
where
d.donation_code = cd.donation_code
and
d.contact_code = c.contact_code
and
c.city_code = ci.city_code
and
ci.country_code = cy.country_code
and
d.currency_code = 1
and
d.contact_code NOT IN (3713,3714)
and
c.contact_name IS NOT NULL
and
c.contact_address IS NOT NULL
and
c.contact_pcode IS NOT NULL
and
ci.city_code IS NOT NULL
and
ci.country_code IS NOT NULL
and
ci.country_code = 1
group by
d.contact_code,
c.contact_title||' '||c.contact_name,
c.contact_address,
c.contact_pcode,
ci.city_name,
cy.country_name,
to_char(d.donation_date,'DD-MM-RRRR'),
cd.category_amount
order by d.contact_code,sum(cd.category_amount) DESC)
WHERE ROWNUM <= 301
order by d.contact_code,sum(cd.category_amount) DESC)
*
ERROR at line 50:
ORA-00907: missing right parenthesis
what is incorrect with this query. If I remove the main query then this query gives 76223 rows is correct, but I only want top 300 donors. How do I resolve this
|
|
|
|
 Re: SQL Top N Analysis [message #597567 is a reply to message #597566] |
Sun, 06 October 2013 13:36  |
 |
Michel Cadot
Messages: 68643
Registered: March 2007
Location: Nanterre, France, http://...
|
Senior Member
Account Moderator |
|
|
1/ A query that is not formatted is very hard to read and understand, use SQL Formatter.
2/ We have not your tables and so can't reproduce what you did, Use SQL*Plus and copy and paste your session, it will point to the line and column where the error is.
3/ As far as I remember, in 8.0 you couldn't use ORDER BY in a subquery.
Do you also still use Windows NT 3.51?
[Updated on: Sun, 06 October 2013 13:40] Report message to a moderator |
|
|
|
| Re: SQL Top N Analysis [message #597569 is a reply to message #597566] |
Sun, 06 October 2013 14:03 |
|
Hometown1
Messages: 23
Registered: March 2011
|
Junior Member |
|
|
SELECT DISTINCT( d.contact_code ),
c.contact_title
||' '
||c.contact_name,
c.contact_address,
c.contact_pcode,
ci.city_name,
cy.country_name,
To_char(d.donation_date, 'DD-MM-RRRR'),
SUM(cd.category_amount)
FROM tbl_donation d,
tbl_contact c,
tbl_city ci,
tbl_country cy,
tbl_category_detail cd
WHERE d.donation_code = cd.donation_code
AND d.contact_code = c.contact_code
AND c.city_code = ci.city_code
AND ci.country_code = cy.country_code
AND d.currency_code = 1
AND d.contact_code NOT IN ( 3713, 3714 )
AND c.contact_name IS NOT NULL
AND c.contact_address IS NOT NULL
AND c.contact_pcode IS NOT NULL
AND ci.city_code IS NOT NULL
AND ci.country_code IS NOT NULL
AND ci.country_code = 1
AND ROWNUM <= 300
GROUP BY d.contact_code,
c.contact_title
||' '
||c.contact_name,
c.contact_address,
c.contact_pcode,
ci.city_name,
cy.country_name,
To_char(d.donation_date, 'DD-MM-RRRR'),
cd.category_amount
ORDER BY d.contact_code,
SUM(cd.category_amount) DESC
SQL> desc tbl_donation
Name Null? Type
------------------------------- -------- ----
DONATION_CODE NOT NULL NUMBER(8)
DONATION_DATE DATE
DONATION_RECEIPT_NO NUMBER(6)
CONTACT_CODE NUMBER(5)
PURPOSE_CODE NUMBER(5)
COUNTRY_CODE NUMBER(5)
DONATION_AMOUNT NUMBER(10,2)
CATEGORY_CODE NUMBER(5)
DONATION_PAYMENT_MODE CHAR(2)
APPEAL_CODE NUMBER(5)
DONATION_SR_NO NUMBER(5)
QURBANI_TYPE CHAR(1)
CURRENCY_CODE NUMBER(5)
CARD_NO VARCHAR2(25)
ISSUE_DATE DATE
EXPIRY_DATE DATE
ISSUE_NO NUMBER(2)
CARD_TYPE CHAR(2)
DONATION_BOOK_NO NUMBER(3)
DONATION_MACHINE_NO NUMBER(3)
DONATION_TYPE CHAR(2)
DONATION_DESCRIPTION VARCHAR2(200)
DONATION_CLAIM CHAR(1)
CAMPAIGN_CODE NUMBER(5)
CREATION_DATE DATE
PROGRAM_CODE NUMBER(5)
CANVASSER_NAME VARCHAR2(45)
STUDENT_CODE NUMBER(5)
LAST_UPDATE_DATE DATE
UPDATED_FOR VARCHAR2(50)
LAST_UPDATED_BY VARCHAR2(45)
PLEDGE_CODE NUMBER(3)
GA_CLAIM_BATCH VARCHAR2(20)
CANVASSER_CODE NUMBER(5)
INCOME_SOURCE CHAR(2)
SQL> desc tbl_contact
Name Null? Type
------------------------------- -------- ----
CONTACT_CODE NOT NULL NUMBER(5)
CONTACT_NAME VARCHAR2(100)
CONTACT_ADDRESS VARCHAR2(300)
CONTACT_PCODE VARCHAR2(15)
CONTACT_DOB DATE
CONTACT_PH_RES VARCHAR2(25)
CONTACT_PH_OFF VARCHAR2(25)
CONTACT_PH_MOB VARCHAR2(25)
CONTACT_FAX VARCHAR2(25)
CONTACT_EMAIL VARCHAR2(50)
CONTACT_TYPE CHAR(2)
CONTACT_DONOR CHAR(1)
CONTACT_TAX_PAYER CHAR(1)
CITY_CODE NUMBER(5)
TOWN_CODE NUMBER(5)
QUALIFICATION_CODE NUMBER(5)
PROFESSION_CODE NUMBER(5)
ACTIVE CHAR(1)
CONTACT_GENDER CHAR(1)
CONTACT_YEAR CHAR(4)
CONTACT_SPONSOR_NO NUMBER(2)
COUNTRY_CODE NUMBER(5)
BOY_SPONSOR NUMBER(2)
GIRL_SPONSOR NUMBER(2)
SPONSOR_AMOUNT NUMBER(12,2)
CURRENCY_CODE NUMBER(5)
CONTACT_TAX_SIGNED CHAR(1)
CONTACT_MONTHLY_SPONSOR CHAR(1)
CONTACT_QUARTERLY_SPONSOR CHAR(1)
CONTACT_HALF_YEARLY_SPONSOR CHAR(1)
CONTACT_YEARLY_SPONSOR CHAR(1)
CONTACT_TITLE VARCHAR2(5)
SEND_MAIL CHAR(1)
CONTACT_ORG CHAR(1)
CREATION_DATE DATE
LAST_UPDATE_DATE DATE
LAST_UPDATED_BY VARCHAR2(40)
DECEASED CHAR(1)
DO_NOT_CALL CHAR(1)
SQL> desc tbl_city
Name Null? Type
------------------------------- -------- ----
CITY_CODE NOT NULL NUMBER(5)
CITY_NAME VARCHAR2(45)
CITY_SHORT_NAME VARCHAR2(5)
COUNTRY_CODE NUMBER(5)
ACTIVE CHAR(1)
SQL> desc tbl_country
Name Null? Type
------------------------------- -------- ----
COUNTRY_CODE NUMBER(5)
COUNTRY_NAME VARCHAR2(45)
COUNTRY_SHORT_NAME VARCHAR2(5)
ACTIVE CHAR(1)
SQL> desc tbl_category_detail
Name Null? Type
------------------------------- -------- ----
DONATION_CODE NUMBER(5)
CATEGORY_CODE NUMBER(5)
CATEGORY_AMOUNT NUMBER(12,2)
So what is the other way around, Yes Michael unfortunately we still use the outdated version, in the process of upgradation
|
|
|
|
|
|
|
|
| Re: SQL Top N Analysis [message #597573 is a reply to message #597571] |
Sun, 06 October 2013 15:43 |
|
Michel Cadot
Messages: 68643
Registered: March 2007
Location: Nanterre, France, http://...
|
Senior Member
Account Moderator |
|
|
Michel Cadot wrote on Sun, 06 October 2013 20:361/ A query that is not formatted is very hard to read and understand, use SQL Formatter.
2/ We have not your tables and so can't reproduce what you did, Use SQL*Plus and copy and paste your session, it will point to the line and column where the error is.
3/ As far as I remember, in 8.0 you couldn't use ORDER BY in a subquery.
Do you also still use Windows NT 3.51?
You have to search on the web as I think no one here has an 8.0 version to test any query.
[Updated on: Sun, 06 October 2013 15:43] Report message to a moderator |
|
|
|
| Re: SQL Top N Analysis [message #597782 is a reply to message #597569] |
Tue, 08 October 2013 08:00 |
|
Hometown1
Messages: 23
Registered: March 2011
|
Junior Member |
|
|
Hi Lalit
Thank you for your hint. After having applied the same I am getting this error. I do not know whether I have perceived your suggestion correctly, or could you suggest other workaround, as I believe my current version does not permit's the intended result.
Please advice
1 SELECT *
2 FROM (SELECT DISTINCT( v.contact_code ),
3 c.contact_title
4 ||' '
5 ||c.contact_name,
6 c.contact_address,
7 c.contact_pcode,
8 c.send_mail,
9 ci.city_name,
10 cy.country_name,
11 To_char(v.donation_date, 'DD-MM-RRRR'),
12 SUM(v.category_amount)
13 FROM vew_donation v,
14 vew_contact c,
15 vew_city ci,
16 vew_country cy
17 WHERE v.contact_code = c.contact_code
18 AND c.city_code = ci.city_code
19 AND c.country_code = cy.country_code
20 AND v.currency_code = 1
21 AND v.contact_code NOT IN ( 3713, 3714 )
22 AND c.contact_name IS NOT NULL
23 AND c.contact_address IS NOT NULL
24 AND c.contact_pcode IS NOT NULL
25 AND ci.city_code IS NOT NULL
26 AND ci.country_code IS NOT NULL
27 AND ci.country_code = 1
28 GROUP BY v.contact_code,
29 c.contact_title
30 ||' '
31 ||c.contact_name,
32 c.contact_address,
33 c.contact_pcode,
34 c.send_mail,
35 ci.city_name,
36 cy.country_name,
37 To_char(v.donation_date, 'DD-MM-RRRR')
38 ORDER BY v.contact_code,
39 v.category_amount DESC
40* WHERE ROWNUM <= 301
SQL> /
ORDER BY v.contact_code,
*
ERROR at line 38:
ORA-00907: missing right parenthesis
|
|
|
|
| Re: SQL Top N Analysis [message #597783 is a reply to message #597782] |
Tue, 08 October 2013 08:03 |
|
Michel Cadot
Messages: 68643
Registered: March 2007
Location: Nanterre, France, http://...
|
Senior Member
Account Moderator |
|
|
Michel Cadot wrote on Sun, 06 October 2013 22:43Michel Cadot wrote on Sun, 06 October 2013 20:361/ A query that is not formatted is very hard to read and understand, use SQL Formatter.
2/ We have not your tables and so can't reproduce what you did, Use SQL*Plus and copy and paste your session, it will point to the line and column where the error is.
3/ As far as I remember, in 8.0 you couldn't use ORDER BY in a subquery.
Do you also still use Windows NT 3.51?
You have to search on the web as I think no one here has an 8.0 version to test any query.
[Updated on: Tue, 08 October 2013 08:04] Report message to a moderator |
|
|
|
|
|
|
|
|
|
| Re: SQL Top N Analysis [message #597788 is a reply to message #597782] |
Tue, 08 October 2013 08:20 |
joy_division
Messages: 4963
Registered: February 2005
Location: East Coast USA
|
Senior Member |
|
|
Hometown1 wrote on Tue, 08 October 2013 09:00Hi Lalit
33 c.contact_pcode,
34 c.send_mail,
35 ci.city_name,
36 cy.country_name,
37 To_char(v.donation_date, 'DD-MM-RRRR')
38 ORDER BY v.contact_code,
39 v.category_amount DESC
40* WHERE ROWNUM <= 301
SQL> /
ORDER BY v.contact_code,
*
ERROR at line 38:
ORA-00907: missing right parenthesis
You cannot have a WHERE clause after an ORDER BY, which is what your query is being that you left off the parenthesis to close out the subquery.
But just to reiterate, we think that ORDER BY is not valid in subquery in v8.
|
|
|
|
| Re: SQL Top N Analysis [message #597821 is a reply to message #597787] |
Tue, 08 October 2013 09:57 |
|
Hometown1
Messages: 23
Registered: March 2011
|
Junior Member |
|
|
Hi Pabloee
Applied your supplied link's logic, works but does not gives the desired result. Please see the code below
SELECT Max(category_amount)
FROM vew_donation
WHERE currency_code = 1;
MAX(CATEGORY_AMOUNT)
--------------------
40000
1 select sum(category_amount),currency_code
2 from vew_donation
3 where rownum < 10
4 group by
5 category_amount,currency_code
6 order by
7* category_amount DESC
SQL> /
SUM(CATEGORY_AMOUNT) CURRENCY_CODE
-------------------- -------------
400 1
100 1
70 1
60 1
100 1
50 1
|
|
|
|
Goto Forum:
Current Time: Tue Apr 23 14:16:39 CDT 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
