Home » SQL & PL/SQL » SQL & PL/SQL » Setting to single row.
Setting to single row. [message #227906] Fri, 30 March 2007 02:31 Go to next message
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, 
pp_serv_assoc a, 
pp_assoc b, 
account c,
acct_serv_link d
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'),
it will result as i told before:
20901329      00052187     D209733300110 00907430170                            
20004396      00051990     H212339832509 00907483989                            

actually it was two records and should be two rows 

sorry for long explanation, hope some body have solutions

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

Report message to a moderator

Re: Setting to single row. [message #227929 is a reply to message #227906] Fri, 30 March 2007 04:02 Go to previous message
vamsi kasina
Messages: 2107
Registered: October 2003
Location: Riyadh, Saudi Arabia
Senior Member
Give the aliases for the columns wrapped by to_char.
Then use column format on them.

Previous Topic: Problems when searching for NVARCHAR2 in ORACLE 9i over ODBC
Next Topic: Registering external objects in PL/SQL
Goto Forum:

Current Time: Sat Jan 21 05:00:27 CST 2017

Total time taken to generate the page: 0.07651 seconds