Home » Other » Client Tools » SQL Output
SQL Output [message #451767] Fri, 16 April 2010 13:04 Go to next message
KingDoofus
Messages: 17
Registered: April 2010
Junior Member
Using SQL*Plus and having trouble getting each record to output on to one line. The fields I'm querying are:

TEMPLATEID NOT NULL NUMBER(10)
EXPERIMENTID NOT NULL NUMBER(10)
NAME VARCHAR2(200)
CREATEDATE DATE

Obviously, it's the NAME field causing me problems? Have tried using FORMAT, but no change in look of output.
COLUMN TEMPLATEID FORMAT 999999
COLUMN NAME FORMAT A40 WORD_WRAPPED
COLUMN EXPERIMENTID FORMAT 999999

Any suggestions greatly appreciated.
Re: SQL Output [message #451768 is a reply to message #451767] Fri, 16 April 2010 13:06 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
SQL> SET LINESIZE 250
Re: SQL Output [message #451770 is a reply to message #451768] Fri, 16 April 2010 13:10 Go to previous messageGo to next message
KingDoofus
Messages: 17
Registered: April 2010
Junior Member
Thanks for that quick reply. That worked great to put everything on one line, but I can only see the first 2 columns, not the last 2.

Any other suggestions?

If it helps any, here's the .sql I'm trying to run to produce a report:

SET PAGESIZE 80
SET LINESIZE 250

COLUMN TEMPLATEID HEADING TMPL_ID
COLUMN NAME HEADING TMPL_TITLE
COLUMN CREATEDATE HEADING TMPL_CREATED
COLUMN EXPERIMENTID HEADING NUM_EXPERIMENTS

COLUMN TEMPLATEID FORMAT 999999
COLUMN NAME FORMAT A40 WORD_WRAPPED
COLUMN EXPERIMENTID FORMAT 999999

SPOOL U:\TMMfiles\SQL_Projects\Template_Usage.TXT
SELECT A.TEMPLATEID, substr(rtrim(A.NAME),1,55), A.CREATEDATE, COUNT(B.EXPERIMENTID)
FROM EE.TEMPLATE A, EE.TEMPLATEBYEXPERIMENT B, EE.TEMPLATE_BUSINESSUNIT_JOIN C
WHERE A.TEMPLATEID = B.TEMPLATEID
AND B.TEMPLATEID = C.TEMPLATEID
AND C.BUSINESSUNITID = 3
AND A.STATUSID IN (13,15)
GROUP BY A.TEMPLATEID, substr(rtrim(A.NAME),1,55), A.CREATEDATE
ORDER BY A.TEMPLATEID;
SPOOL OFF

[Updated on: Fri, 16 April 2010 13:12]

Report message to a moderator

Re: SQL Output [message #451772 is a reply to message #451770] Fri, 16 April 2010 13:16 Go to previous messageGo to next message
Littlefoot
Messages: 21806
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Quote:
but I can only see the first 2 columns, not the last 2.
Scroll right.
Re: SQL Output [message #451773 is a reply to message #451770] Fri, 16 April 2010 13:20 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
>I can only see the first 2 columns, not the last 2.

I can't see anything because I am not standing behind you and you decided to not use CUT & PASTE

>COLUMN NAME FORMAT A40 WORD_WRAPPED
remove line above.

It is a BAD idea to use KEYWORD (NAME) as column_name.

It would be helpful if you followed Posting Guidelines - http://www.orafaq.com/forum/t/88153/0/
Re: SQL Output [message #451774 is a reply to message #451772] Fri, 16 April 2010 13:20 Go to previous messageGo to next message
KingDoofus
Messages: 17
Registered: April 2010
Junior Member
Won't let me scroll right, but by the looks of the scroll bar there must be info over there.

Perhaps I should ask how do I get the NAME field to display only 40 or 50 characters and word_wrap the rest? The longest record in this field is only 55 characters so I really don't need to be showing 200.
Re: SQL Output [message #451775 is a reply to message #451773] Fri, 16 April 2010 13:24 Go to previous messageGo to next message
KingDoofus
Messages: 17
Registered: April 2010
Junior Member
"It is a BAD idea to use KEYWORD (NAME) as column_name."
I agree, but I didn't create the datbase, tables, or column names. - Can I give it an alias? Would that help in formatting?

Thanks for the guideline link, will try to familiarize myself with them and apologize if I offended anyone with my initial post(s). I'm kinda new to all this.

[Updated on: Fri, 16 April 2010 13:25]

Report message to a moderator

Re: SQL Output [message #451781 is a reply to message #451775] Fri, 16 April 2010 13:45 Go to previous message
Littlefoot
Messages: 21806
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
column temp_id format 999999
column sub_name format a55
column cre_date format a10
column cnt format 999999

--     values                       Aliases
--     ---------------------------  --------
SELECT A.TEMPLATEID                 temp_id, 
       substr(rtrim(A.NAME), 1, 55) sub_name, 
       A.CREATEDATE                 cre_date, 
       COUNT(B.EXPERIMENTID)        cnt
FROM ...

Written as above, it seems that it should all fit within 80 characters, so you *must* see all of it.
Note that your COLUMN commands weren't correctly written. It is useless to
COLUMN NAME FORMAT A40
and then
select substr(rtrim(A.NAME), 1, 55)
as it won't do anything. You have to create an alias and format that alias.
Previous Topic: Display option in sqlplus
Next Topic: Semicolon ; is not interpreted correctly.. :(
Goto Forum:
  


Current Time: Fri Mar 29 10:05:20 CDT 2024