Formatting reports in SQL PLUS

I having trouble formatting a report for a particular table. The table is quite large and has 20+ columns. Im trying to do a select * from the table, but the output is really bad. I know it has something to do with wrap around, but im not sure how to fix it. Ive increased the LINESIZE and PAGESIZE which helped, but its still impossible to read. The output looks like this:

APP_ID SSN AREA_CODE TEL_NO QUE QUE QUES_LEAVEREASON APPLY_POSITION START_DAT AMTHOURS_
---- ---- ---- ---- -- -- ------- ----- --- -
HIGH_NAME HIGH_CITY HIGH_STATE COLLEGE_NAME COLLEGE_LOCATION COLLEGE_MAJOR COL
------- ------- ----- ------- -------
EXTRA_ACTIVITIES OFFICES_HELD OTHER_SCHOOLING QUE
-------- -------- -------- --
80000 443264455 561 6548890 no no NULL Greeter 04-APR-04 40 yes yes walk-in
Chase HS Bloomfield IL NULL NULL NULL NULL NULL
NULL NULL NULL yes

80001 887651109 772 7817764 no no NULL Manager 12-DEC-05 40 yes yes friend
Broward County HS Palm Beach FL FAU Boca Raton Management Business NULL
NULL NULL NULL yes

80002 198667112 772 5443234 no no NULL Stocker 08-AUG-05 40 yes yes TV
Centiennal Port St. Lucie FL NULL NULL NULL NULL NULL
NULL NULL NULL yes

80003 332690823 561 7734456 no no NULL Security guard 01-JAN-04 40 yes yes
George OLeary Callamajo MD NULL NULL NULL NULL NULL
NULL NULL NULL yes

80004 557751123 772 2601973 no no NULL Cashier 07-JUL-07 30 yes yes friends
St.Lucie County Port St. Lucie FL FAU Boca Raton Dentistry Business NU
NULL NULL NULL yes

80005 554321123 561 7812238 no no NULL Manager 01-JUN-04 40 yes yes newspap
Martin County Stuart FL FAU Boca Raton MIS Business NULL
NULL NULL NULL yes

Quote:

Originally Posted by Mateo

I having trouble formatting a report for a particular table. The table is quite large and has 20+ columns. Im trying to do a select * from the table, but the output is really bad. I know it has something to do with wrap around, but im not sure how to fix it. Ive increased the LINESIZE and PAGESIZE which helped, but its still impossible to read. The output looks like this:

APP_ID SSN AREA_CODE TEL_NO QUE QUE QUES_LEAVEREASON APPLY_POSITION START_DAT AMTHOURS_
---- ---- ---- ---- -- -- ------- ----- --- -
HIGH_NAME HIGH_CITY HIGH_STATE COLLEGE_NAME COLLEGE_LOCATION COLLEGE_MAJOR COL
------- ------- ----- ------- -------
EXTRA_ACTIVITIES OFFICES_HELD OTHER_SCHOOLING QUE
-------- -------- -------- --
80000 443264455 561 6548890 no no NULL Greeter 04-APR-04 40 yes yes walk-in
Chase HS Bloomfield IL NULL NULL NULL NULL NULL
NULL NULL NULL yes

80001 887651109 772 7817764 no no NULL Manager 12-DEC-05 40 yes yes friend
Broward County HS Palm Beach FL FAU Boca Raton Management Business NULL
NULL NULL NULL yes

80002 198667112 772 5443234 no no NULL Stocker 08-AUG-05 40 yes yes TV
Centiennal Port St. Lucie FL NULL NULL NULL NULL NULL
NULL NULL NULL yes

80003 332690823 561 7734456 no no NULL Security guard 01-JAN-04 40 yes yes
George OLeary Callamajo MD NULL NULL NULL NULL NULL
NULL NULL NULL yes

80004 557751123 772 2601973 no no NULL Cashier 07-JUL-07 30 yes yes friends
St.Lucie County Port St. Lucie FL FAU Boca Raton Dentistry Business NU
NULL NULL NULL yes

80005 554321123 561 7812238 no no NULL Manager 01-JUN-04 40 yes yes newspap
Martin County Stuart FL FAU Boca Raton MIS Business NULL
NULL NULL NULL yes

It depends on where and what you want to achieve. If you're in Query Analyzer, go to Query (menu) -> Results in grid. If you use it from some code, you'll have to work it out yourself - depending on what and where you need (sorry). Alternatively select fewer columns by listing the ones the you really need. Screen has limited width... ;-)|||I think you posted at the wrong section.
Sql plus is an oracle tool.
Just run the command from the prompt with the sqlplus.exe and redirect (>) it to an output file.

You should the be able to read the files more clearly (use the pspad viewer, it's free and it can handle long records)sql

Formatting Dates as YYYY/MM/DD for a particular query

Anyone know how to format dates from 10/3/05 to 2005/10/03 in a query?
The only way I know is (cast(year(srecordeddate) as char(4)))+ cast('/'as char) + cast (month(srecordeddate) as char(2))... Which seems so rediculous. There's gotta be a better way!select convert(varchar(10), getdate(), 111)|||Anyone know how to format dates from 10/3/05 to 2005/10/03 in a query?

The only way I know is (cast(year(srecordeddate) as char(4)))+ cast('/'as char) + cast (month(srecordeddate) as char(2))... Which seems so rediculous. There's gotta be a better way!

With the caveat that it's almost ALWAYS better to handle formatting on the client side, you might try:

Convert(varchar(10), [YourDate], 120)

Regards,

hmscott|||select convert(varchar(10), getdate(), 111)
Thanks! You've bailed me out of another toughee...

Formatting a float variable to 2 decimal places

Hey,

I am filling a temp table with various float variables and I need to format one particular column to 2 decimal places.

Does anyone know the correct syntax to do this, and should it be done before filling the temp table or when I select what I needs from the temp table?

ThanksThis should be done at the application level.|||Ok, I'll look into that|||If at all possible, use decimal (x,2) instead of float. Float is considered an approximate datatype and if you have lots of math/rounding it could introduce errors in your application.