Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

creating database view

Status
Not open for further replies.
CrystalUser1

CrystalUser1

Programmer
Sep 16, 2004
138
US
Hi,

I have to create a database view in oracle 9i. The view should show the total counts.

The data should display like this:

criteria_name count

abc 100
xyz 209
aaa 100

the count is based on the criteria_name field.

along with those above fields, the view should also display the other fields. the other fields are:

dept,division,section,source_name

the final view should have the following fields:

criteria_name count dept division section source_name

can anybody guide me how to create this view having counts for each criteria name and also display other fields.

thanks
 
Sort by date Sort by votes
CU,

What code have you tried so far? Have you created a query that does what you want? ...Do you not have in-house developers to help, or do you need us to code this for you?

Let us know.

[santa]Mufasa
(aka Dave of Sandy, Utah, USA)
[ Providing low-cost remote Database Admin services]
Click here to join Utah Oracle Users Group on Tek-Tips if you use Oracle in Utah USA.
 
Upvote 0 Downvote
Hi Mufasa,

thank you for your reply. i tried the following query.

select criteria_name,count,dept,division,section,source_name

from criteria group by criteria_name,dept,division,section,source_name.

the results are like this:

criteria_name count dept division section source_name

abc 12 aa aa aa aa
abc 13 ab ab ab ab

i want the count total count for each criteria_name. may be my query is wrong. can you please help me.

thanks


 
Upvote 0 Downvote
Hi Mufasa,

thank you for your reply. i tried the following query.

select criteria_name,count,dept,division,section,source_name

from criteria group by criteria_name,dept,division,section,source_name.

the results are like this:

criteria_name count dept division section source_name

abc 12 aa aa aa aa
abc 13 ab ab ab ab

i want the count total count for each criteria_name. may be my query is wrong. can you please help me.

thanks in advance.


 
Upvote 0 Downvote
CU,

I cannot tell from your description(s) what the relationship is amongst dept, division, section, source_name and criteria_name, so I don't know how close this solution will come to meeting your needs. But since you apparently want the count only to be for each separate criteria_name, but to then associate those data with the other columns, perhaps this will get you started:
Code: 
Select x.criteria_name
      ,y.cnt
      ,x.dept
      ,x.division
      ,x.section
      ,x.section_name
  from criteria x
      ,(select count(*) cnt, criteria_name crit_name
          from criteria
         group by criteria_name) y
 where x.criteria_name = y.crit_name
-- group by <SELECT expressions> if your need indicates
/
In the above code, the "(select..." in the FROM clause is an Oracle "in-line" VIEW. As a result of this construct, I rarely create traditional VIEWs as you are requesting. If, however, you do need to create a VIEW, just append in front of the SELECT the syntax:
Code: 
CREATE OR REPLACE VIEW <view_name> AS SELECT...

Let us know if all this gets you closer to your resolution.

[santa]Mufasa
(aka Dave of Sandy, Utah, USA)
[ Providing low-cost remote Database Admin services]
Click here to join Utah Oracle Users Group on Tek-Tips if you use Oracle in Utah USA.
 
Upvote 0 Downvote
Thank you very much mufasa.

I am trying to create the following view based on your advice. I am getting the following error:

Y.CNT IS NOT A GROUP FUNCTION.

CREATE OR REPLACE VIEW TEST_VIEW
AS
SELECT
A.CRITERIA_NAME,Y.CNT,
A.DEPT ,
A.DIVISION ,
A.SECTION ,
A.SECTION_NAME

FROM CRITERIA A,DEPT_TABLE B,DIVISION_TABLE C,SECTION_TABLE D,SECTION_NAME E,

(SELECT COUNT(*) CNT,CRITERIA_NAME FROM CRITERIA GROUP BY CRITERIA_NAME) Y
WHERE A.CRITERIA_NAME=Y.CRITERIA_NAME AND
A.ORG_ID=B.ORG_ID(+) AND A.DEPT_ID=C.DEPT_ID(+) AND A.SECTION_ID=D.SECTION_ID(+) AND
A.SECTION_NAME=E.SECTION_NAME(+) GROUP BY A.CRITERIA_NAME,
A.DEPT ,
A.DIVISION ,
A.SECTION ,
A.SECTION_NAME

I am doing misake somewhere. Please advice.

thanks in advance.
 
Upvote 0 Downvote
Your syntax, above, I believe, is much more complicated than it needs to be. I'm sure we can simplify it if you can provide us with clear specifications of what you want to display. Please specify exactly:

1) Which rows you want the COUNT(*) to be counting?
2) What data do you want on the displayed rows? (Do you want "details" rows of data or do you want "grouped" rows, i.e., 1 representative row for multiple detail rows.)
3) Do you really have separate tables for "CRITERIA A,DEPT_TABLE B,DIVISION_TABLE C,SECTION_TABLE D,SECTION_NAME E?"

[santa]Mufasa
(aka Dave of Sandy, Utah, USA)
[ Providing low-cost remote Database Admin services]
Click here to join Utah Oracle Users Group on Tek-Tips if you use Oracle in Utah USA.
 
Upvote 0 Downvote
hi,

1. the count(*) should count the each criteria_name
2. i want grouped rows as well as details rows. for example, criteria abc has total count of 100. it should display like this:

criteria count other fields...

abc 100

or atleast the best possible way.

3. i have different tables that needs to be joined.

thanks
 
Upvote 0 Downvote
hi mufasa,

do you need any more clarifications? please help me.

thanks in advance.
 
Upvote 0 Downvote
CU,

Try this slight change (that does not impact your results) to your code:
Code: 
SELECT 
  A.CRITERIA_NAME,Y.CNT,
  A.DEPT  ,
  A.DIVISION  ,
  A.SECTION  ,
  A.SECTION_NAME
FROM  CRITERIA A,DEPT_TABLE B,DIVISION_TABLE C,SECTION_TABLE D,SECTION_NAME E,

(SELECT COUNT(*) CNT,CRITERIA_NAME FROM CRITERIA GROUP BY CRITERIA_NAME) Y
WHERE A.CRITERIA_NAME=Y.CRITERIA_NAME AND 
A.ORG_ID=B.ORG_ID(+) AND  A.DEPT_ID=C.DEPT_ID(+) AND A.SECTION_ID=D.SECTION_ID(+) AND
 A.SECTION_NAME=E.SECTION_NAME(+) GROUP BY A.CRITERIA_NAME,
  A.DEPT  ,
  A.DIVISION  ,
  A.SECTION  ,
  A.SECTION_NAME,[B]
  Y.CRITERIA_NAME,
  Y.CNT[/B]
Let us know your results.


[santa]Mufasa
(aka Dave of Sandy, Utah, USA)
[ Providing low-cost remote Database Admin services]
Click here to join Utah Oracle Users Group on Tek-Tips if you use Oracle in Utah USA.
 
Upvote 0 Downvote
BTW, CU,

If the code you posted represents your entire SQL statement, what effect/benefit do you expect from outer joining to tables that you never reference except in the WHERE clause?

[santa]Mufasa
(aka Dave of Sandy, Utah, USA)
[ Providing low-cost remote Database Admin services]
Click here to join Utah Oracle Users Group on Tek-Tips if you use Oracle in Utah USA.
 
Upvote 0 Downvote
hi mufasa,

I am not getting the correct results when i use the above. but i did in a different way, i am getting the correct results. see the below query:

SELECT
A.CRITERIA_NAME,count(*),
A.DEPT ,
A.DIVISION ,
A.SECTION ,
A.SECTION_NAME
FROM CRITERIA A,DEPT_TABLE B,DIVISION_TABLE C,SECTION_TABLE D,SECTION_NAME E
WHERE A.CRITERIA_NAME=Y.CRITERIA_NAME AND
A.ORG_ID=B.ORG_ID(+) AND A.DEPT_ID=C.DEPT_ID(+) AND A.SECTION_ID=D.SECTION_ID(+) AND
A.SECTION_NAME=E.SECTION_NAME(+) GROUP BY A.CRITERIA_NAME,
A.DEPT ,
A.DIVISION ,
A.SECTION ,
A.SECTION_NAME


thanks
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

Aung Thurein Tun
  • Locked
  • Question
opera_mail.AuthenticateCredential error ORA-20001 in Oracle Database
Replies
0
Views
149
Aung Thurein Tun
Aung Thurein Tun
BJoy
  • Locked
  • Question
Bulk insert from C# into Oracle Database
Replies
4
Views
230
karluk
karluk
billo102
  • Locked
  • Question
stats collection on DG affects standby database
Replies
0
Views
133
billo102
billo102
tekpr00
  • Locked
  • Question
Database Capacity Trend
Replies
0
Views
58
tekpr00
tekpr00
marcq
  • Locked
  • Question
Restored database with RMAN in the state which I supposed will be different.
Replies
4
Views
98
marcq
marcq

Part and Inventory Search

Sponsor

Back
Top