| SQL Output [message #451767] |
Fri, 16 April 2010 13:04  |
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 #451770 is a reply to message #451768] |
Fri, 16 April 2010 13:10  |
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 #451774 is a reply to message #451772] |
Fri, 16 April 2010 13:20 |
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 |
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 |
 |
Littlefoot
Messages: 21823
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 toand thenselect substr(rtrim(A.NAME), 1, 55) |
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
