OraFAQ Forum: SQL & PL/SQL » Setting to single row.

Home » SQL & PL/SQL » SQL & PL/SQL » Setting to single row.

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Setting to single row. [message #227906]

Fri, 30 March 2007 02:31

choks
Messages: 4
Registered: March 2007
Location: PJ,Malaysia

Junior Member

Hi all,

i have a problem in query, which the is the query :

set head off;
set feedback off;
set pagesize 0;
set linesize 105;
set space 0;
column serv_no format a16;
column ext_cust_id format a14;
column cust_id format a13;
column pricing_plan_id format a8;
select c.EXT_CUST_ID, c.cust_id, a.ACCT_NO,a.SERV_NO, 
to_char(b.pricing_plan_id,'FM99999999'), 
to_char(b.EFF_START_DT,'DDMMYYYYHH24MISS'), 
to_char(b.EFF_END_DT,'DDMMYYYYHH24MISS')
from 
pp_serv_assoc a, 
pp_assoc b, 
account c,
acct_serv_link d
where 
a.pp_assoc_id = b.pp_assoc_id and
b.eff_end_dt is null and
a.acct_no = c.acct_no and
a.acct_no = d.acct_no and
a.serv_no = d.serv_no and
d.serv_end_date is null and
serv_type in ('TEL', 'CDA','DID') and
dat_code in ('DEL','FCT','CTX','KTS','PAX','LOC','FPH','ITF','UFN','DFN','OTF','CON');

And it should produce the file after spool like this:

0000000120901329      00052187     D209733300110 00907430170     1342    01052005000000(for one record)

After sometime, i run this script again, the result was it continuing and make a new row below it, such as above, this will make awk script to convert a row,assume the new row was the second record. I try to figure where the problem was and if i spool above query until:

select c.EXT_CUST_ID ,c.cust_id,a.ACCT_NO, a.SERV_NO

outpu was correct,

20064942      00050688     D209802881208 00905686442
20064942      00050688     D209802881208 00905686442                                                     
20064942      00050688     D209802881208 00905686442

And if i continue to the "to_char(b.pricing_plan_id,'FM99999999'),
to_char(b.EFF_START_DT,'DDMMYYYYHH24MISS'),
to_char(b.EFF_END_DT,'DDMMYYYYHH24MISS')"
it will result as i told before:

20901329      00052187     D209733300110 00907430170                            
1342                                                                            
01052005000000                                                                  
                                                                                
                                                                                
20004396      00051990     H212339832509 00907483989                            
1342                                                                            
22042005000000                    

actually it was two records and should be two rows

sorry for long explanation, hope some body have solutions
regard,
mie.

[Updated on: Fri, 30 March 2007 02:33]

Report message to a moderator