Home » SQL & PL/SQL » SQL & PL/SQL » Views
Views [message #188542] Sat, 19 August 2006 03:45 Go to next message
eqqqqqq
Messages: 30
Registered: August 2006
Member
Hi!
I need to create a view which first orders/shows the customers who were invoiced, (+ number of invoices) and the ones who where not invoiced, as "not invoiced". How do I group them???

CREATE OR REPLACE VIEW invoices(ID, NAME, quantity) AS
SELECT ID, NAME, count(nvl(invoice_nr,'Not invoiced')as quantity)
FROM CUSTOMER, INVOICE
WHERE ID=CUSTOMER_ID(+)
Order by quantity;

Then I need to create view with an empty space(an empty column)
Furthest down in the view I would like the headlines;
Summary:
Number of authors:
Number of books:


Re: Views [message #188545 is a reply to message #188542] Sat, 19 August 2006 06:05 Go to previous messageGo to next message
a_developer
Messages: 194
Registered: January 2006
Senior Member
For your first view:
SQL> select * from customer order by customer_id;

NAME             CUSTOMER_ID                                      
---------------- -----------                                      
Cust1            1                                      
Cust2            2                                      
Cust3            3                                      
Cust4            4                                      
Cust5            5                                      
Cust6            6                                      
Cust7            7                                      
Cust8            8                                      
Cust9            9                                      

9 rows selected.

SQL> select * from sample_invoice order by invoice_id;

INVOICE_NR               INVOICE_ID         
------------------------ ----------         
101                      1         
102                      1         
                         2         
                         2         
103                      3         
104                      3         
105                      4         
106                      4         
107                      4         
108                      4         
                         5         
                         5         
                         6         
                         6         
                         7         
                         7         
                         7         
109                      7         
110                      7         
                         11         
                         11         
111                      12         
112                      12         

23 rows selected.

SQL> SELECT Invoice_ID, NAME, count(nvl( invoice_nr,'NOT_INVOICED')) as quantity
  2  FROM CUSTOMER, sample_INVOICE
  3  WHERE invoice_ID=CUSTOMER_ID(+)
  4  group by invoice_id, name
  5  Order by quantity;

INVOICE_ID NAME          QUANTITY       
---------- ------------- ----------       
1          Cust1         2       
2          Cust2         2       
3          Cust3         2       
6          Cust6         2       
4                        2       
11                       2       
12                       2       
5          Cust5         2       
4          Cust4         4
7          Cust7         5

10 rows selected.


I did not get what you want for your second view.. Please explain it further in detail.

[Updated on: Sat, 19 August 2006 06:45]

Report message to a moderator

Re: Views [message #188548 is a reply to message #188542] Sat, 19 August 2006 06:55 Go to previous message
eqqqqqq
Messages: 30
Registered: August 2006
Member
Thank you... actually, And how do I add the text "not invoiced" where quantity zero.DECODE(QUANTITY, NULL, "not invoiced", QUANTITY)???

CREATE OR REPLACE VIEW invoiced(ID, NAME, quantity) AS
SELECT ID, NAME, count(INvoice_nr) as quantity
FROM CUSTOMER, INVOICE
WHERE ID=CUSTOMER_ID(+)
group by id, name
Order by quantity DESC;

Second view;
CREATE OR REPLACE VIEW authors(AUTHOR, "empty column", NR_BOOK) AS
SELECT NAME, sum(IN_STOCK)
FROM AUTHOR, BOOK
WHERE ID=AUTHOR_ID
GROUP BY ROLLUP(NAME);

How do i do these headlines on last rows??
On the last row of column1 just headline "Summary:"
Column2 "Number of authors:"
Column3 "Number of books:"
Previous Topic: Data migration from XML source to Oracle
Next Topic: storage of tables
Goto Forum:
  


Current Time: Sat Dec 03 20:04:48 CST 2016

Total time taken to generate the page: 0.10450 seconds