Home » SQL & PL/SQL » SQL & PL/SQL » tabular query
tabular query [message #227954] Fri, 30 March 2007 07:14 Go to next message
rzkhan
Messages: 345
Registered: March 2005
Senior Member
EX > select * from bill;

a)

MEMBER_    ITEM_ID     AMOUNT M            Y                                                        
------- ---------- ---------- --- ----------                                                        
2006001          1        100 APR       2007
2006001         12         56 APR       2007
2006001         14      33.33 APR       2007
2006001         15        900 APR       2007
2006001         16      500.1 APR       2007
2006002         12         56 APR       2007
2006002         14      33.33 APR       2007
2006003         12         56 APR       2007
2006003         14      33.33 APR       2007
2006004         12         56 APR       2007
2006004         14      33.33 APR       2007
2006005         12         56 APR       2007
2006005         14      33.33 APR       2007
2006006         12         56 APR       2007
2006006         14      33.33 APR       2007                                             

15 rows selected.



b)

 select b.member_id,i.item,b.amount,b.m,b.y
 from bill b, items i
 where b.item_id=i.item_id
 order by 1

MEMBER_ ITEM                                                   AMOUNT M            Y
------- -------------------------------------------------- ---------- --- ----------
2006001 FRESH MILK                                                100 APR       2007
2006001 NEWSPAPER                                                  56 APR       2007
2006001 ELECTRICITY                                             33.33 APR       2007
2006001 MESSING                                                   900 APR       2007
2006001 GAS                                                     500.1 APR       2007
2006002 NEWSPAPER                                                  56 APR       2007
2006002 ELECTRICITY                                             33.33 APR       2007
2006003 NEWSPAPER                                                  56 APR       2007
2006003 ELECTRICITY                                             33.33 APR       2007
2006004 NEWSPAPER                                                  56 APR       2007
2006004 ELECTRICITY                                             33.33 APR       2007
2006005 NEWSPAPER                                                  56 APR       2007
2006005 ELECTRICITY                                             33.33 APR       2007
2006006 NEWSPAPER                                                  56 APR       2007
2006006 ELECTRICITY                                             33.33 APR       2007

15 rows selected.


c)

EX > select * from net;

MEMBER_ CURRENT_BILL    ARREARS  SURCHARGE         GT       RECD    BALANCE M            Y          
------- ------------ ---------- ---------- ---------- ---------- ---------- --- ----------          
2006001      1589.43          0          0       1590        123       1467 APR       2007          
2006002        89.33          0          0         90        203       -113 APR       2007          
2006003        89.33          0          0         90       1245      -1155 APR       2007          
2006004        89.33          0          0         90                    90 APR       2007          
2006005        89.33          0          0         90                    90 APR       2007          
2006006        89.33          0          0         90                    90 APR       2007          

6 rows selected.

EX > spool off



How can I embedd the above data in on query. Specially if we need it in the following format.
using query (b) and (c)

MEMBER_ ITEM                                                   AMOUNT M            Y
2006001 FRESH MILK                                                100 APR       2007
2006001 NEWSPAPER                                                  56 APR       2007
2006001 ELECTRICITY                                             33.33 APR       2007
2006001 MESSING                                                   900 APR       2007
2006001 GAS                                                     500.1 APR       2007
2006001	CURRENT_BILL						1589.43
2006001	ARREARS							0
2006001	SURCHARGE						0	
2006001	GT							1590
2006001	RECD							123
2006001	BALANCE							1467




15 rows selected.


Re: tabular query [message #228985 is a reply to message #227954] Wed, 04 April 2007 15:38 Go to previous messageGo to next message
nmacdannald
Messages: 460
Registered: July 2005
Location: Stockton, California - US...
Senior Member
Try 'order by' with the qurry.
Re: tabular query [message #229012 is a reply to message #227954] Thu, 05 April 2007 01:01 Go to previous message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You likely can, if only you explain a little bit what do you want with words not only the final result.
It's a bit mysterious why some lines has data in M and Y and others none or why there are 15 lines in the final result and so on.

Regards
Michel

Previous Topic: ORA-00937: not a single-group group function
Next Topic: PLS-00925 native compilation failed: Unable to create C file (string string)
Goto Forum:
  


Current Time: Sat Dec 03 18:06:13 CST 2016

Total time taken to generate the page: 0.07206 seconds