Out PUt in SQL*Plus

[sammybee]

Hi All,

I need some help, need to do an output report that shows a title, then an Id for grouping then show the results of select statement per group - how do I do this?

many thanks

SAm

 

[lewisp]

Chcek the following commands:

TTITLE will set a title for your sql output.
BREAK will produce a break report on your groups.
COLUMN can be used to format your columns.

 

[sammybee]

Is there any way I can highlight the id as a subtitle before the group details?

Many thanks for your help

Sam

 

[lewisp]

Try something like this

COLUMN your_column NEW_VALUE col_val NOPRINT

TTITLE LEFT col_val SKIP 2
BREAK ON your_column SKIP PAGE

Then in your sql you will be SELECTing your_column as one of the columns.

 

[sammybee]

Hi All,

Below is my script, I'm sorry but I'm not too familiar with output scripts,

1.set lines 999
2.set pages 0
3.set feed off
4.column sb_per_lh_forename, column sb_per_lh_surname newline
5.column sb_lh_address newline
6.column sb_bal_cf_date noprint newline
7.column sb_pay_ref newline
8.column sb_prop_address newline
9.column sb_bal_bf_amount
10.select sbt_tra_date, sbt_tra_dr, sbt_tra_cr
11.from sb_prop_accounts, sb_prop_trans
12.where sb_pay_Ref = sbt_pay_ref (+) and sb_pay_ref = 10072896

running in sql plus after row 4. I get the error:-

sp2-0158: unknown column option "column"

then after row 9. the error:-

sp2-0046 column 'sb_bal_bf_amount' not defined.

Then I get the results of the select statement, I'm expecting it to show the results of those columns it hasn't errored on before this. What am I doing wrong?

Many thanks

Sam

 

[SantaMufasa]

Sam,

Your syntax errors result for these reasons:

4.column sb_per_lh_forename, column sb_per_lh_surname newline
sp2-0158: unknown column option "column"

You must specify each "column" definition on its own line and without a comma; in your case you specified them on the same line separated by a comma.

9.column sb_bal_bf_amount
sp2-0046 column 'sb_bal_bf_amount' not defined.

This is an incomplete "column" definition; you must specify at least on column attribute such as "newline" or "format $999,999.99" or "heading 'Balance|Amount'" et cetera. Otherwise SQL*Plus considers your column definition to be "not defined".

Let us know if these tips resolve your problems.

Mufasa
(aka Dave of Sandy, Utah, USA)
@ 16:36 (05Nov04) UTC (aka "GMT" and "Zulu"),
@ 09:36 (05Nov04) Mountain Time