formatting output from a column in sql plus

I have read the documentation of the column command in sql plus version 10.1
and i still don't understand how to format the data field as follows.
I have tried different ways to use the column command, without success,
believe me...

Questions:
1) How to format a varchar2 field so that it is "right justified"?
2) How do i format a date field as follows 'YYYY-MM-DD' with the help of the
column command, if possible?

Thank you


0
amwi
7/7/2004 6:52:53 AM
comp.databases.oracle.misc 8448 articles. 0 followers. Post Follow

3 Replies
100 Views

Similar Articles

[PageSpeed] 28
"amwi" <amwi@yahoo.com> wrote in message news:<95NGc.98019$dP1.327068@newsc.telia.net>...
> I have read the documentation of the column command in sql plus version 10.1
> and i still don't understand how to format the data field as follows.
> I have tried different ways to use the column command, without success,
> believe me...
> 
> Questions:
> 1) How to format a varchar2 field so that it is "right justified"?
> 2) How do i format a date field as follows 'YYYY-MM-DD' with the help of the
> column command, if possible?
> 
> Thank you

1) select lpad(<your expression>,<max length>) ....
2) the column command supports strings and numbers. Dates are treated
as strings. You'll need to_char(<date>,'YYYY-MM-DD') in your select
statement.

Sybrand Bakker
Senior Oracle DBA
0
sybrandb
7/7/2004 11:32:50 AM
"amwi" <amwi@yahoo.com> wrote in message news:<95NGc.98019$dP1.327068@newsc.telia.net>...
> I have read the documentation of the column command in sql plus version 10.1
> and i still don't understand how to format the data field as follows.
> I have tried different ways to use the column command, without success,
> believe me...
> 
> Questions:
> 1) How to format a varchar2 field so that it is "right justified"?
> 2) How do i format a date field as follows 'YYYY-MM-DD' with the help of the
> column command, if possible?
> 
> Thank you

The column command doesn't do either of those things, unfortunately
for you.
1) Use LPAD(col,<width>) in the SQL
2) Use TO_CHAR(col,'YYYY-MM-DD') in the SQL, or SET NLS_DATE_FORMAT =
'YYYY-MM-DD' if you want the same format for ALL columns.
0
andrewst
7/7/2004 11:35:50 AM
"amwi" <amwi@yahoo.com> wrote in message news:<95NGc.98019$dP1.327068@newsc.telia.net>...
> I have read the documentation of the column command in sql plus version 10.1
> and i still don't understand how to format the data field as follows.
> I have tried different ways to use the column command, without success,
> believe me...
> 
> Questions:
> 1) How to format a varchar2 field so that it is "right justified"?
> 2) How do i format a date field as follows 'YYYY-MM-DD' with the help of the
> column command, if possible?
> 
> Thank you

You normally format dates in the select list of the query using the
to_char function as in to_char(date_col,'YYYY-MM-DD') as label_name

HTH -- Mark D Powell --
0
Mark
7/7/2004 1:47:40 PM
Reply:
Similar Artilces:

Format inside a format
Hi everyone, I have a list of codes which I want formatted with a string for each code. But I don't have a definition for every single code, so I would like to have a generic "Other" value which keeps the code in it. Something like "Other (2323)" for the undefined code 2323. Is this possible to achieve using formats or should I write a datastep for that. Thanks a lot for your help. Guillermo Ramos wrote: > Hi everyone, > I have a list of codes which I want formatted with a string for each > code. But I don't have a definition for every single code, so I ...

ruby string format kicks in when doign wild card in activerecord
when i was doing a LIKE statement in my wild card query. Ruby thinks that the % is a String Format charactor and throws exception. what are my options. >> Rawdata.find(:all , :conditions=>["processed_indicator like 'LF%'"]) ArgumentError: malformed format string - %' from e:/ruby/lib/ruby/gems/1.8/gems/activerecord-1.15.5/lib/ active_record/base.rb:1421:in `%' from e:/ruby/lib/ruby/gems/1.8/gems/activerecord-1.15.5/lib/ active_record/base.rb:1421:in `sanitize_sql_array' from e:/ruby/lib/ruby/gems/1.8/gems/activerecord-1.15.5/lib/...

Re: Date Formats... #2
On Tue, 5 May 2009 13:42:40 -0400, SUBSCRIBE SAS-L Joe H. Smith <peesari.mahesh@GMAIL.COM> wrote: >Hi all; > >I have the following code here..... > data want; > x=date(); > y=date(); > format x dtdate9. y ddmmyy10.; > run; > >MY O/P is ... x y > 01Jan1960 05/05/2009 > >Can you explain me why there is a difference in date values,when i am >using the same date function for both the variables..only my format type >is changing,if i want x to have a value of 05May2009,How can i get >this .My desi...

supress output header in CLP for CALL statement
Hi, I see that I always get on CLP for CALL stmt: Value of output parameters -------------------------- Parameter Name : Parameter Value : How can I only return parameter value from CLP for further script to process it. Regards, Suri rnbjrnbj@gmail.com wrote: > Hi, I see that I always get on CLP for CALL stmt: > Value of output parameters > -------------------------- > Parameter Name : Parameter Value : > > How can I only return parameter value from CLP for further script to > process it. > Regards, > Suri You don't list your p...

Re: Reading MS SQL Server files #3
On Wed, 28 May 2008 11:36:01 -0700, Friar Broccoli <EliasRK@GMAIL.COM> wrote: >In my work I am more and more frequently receiving data in >"MS SQL Server" files which usually have the extension *.mdf. > IF <Got access to SAS/ACCESS?> THEN LIBNAME libref sqlsvr <connection-options> <LIBNAME-options>; And you can read the files directly. For options goto to the online doc. Example from doc.: libname mydblib sqlsvr noprompt="uid=testuser; pwd=testpass; dsn=sqlservr;" stringdates=yes; proc print data=mydblib.customers; wher...

Proc Format vs Format
I need to create 10 point breaks on values ranging from 0 - 999. I was wondering which technique is better. Create a proc format that groups the values or just code a new variable in a data step. Nicole: How much data do you have? Will you be doing this often? Do you have a library in which to save formats? In general, I believe in Leaving the Data Alone (tm). That means: format. Avoids a data step. Doesn't require explaining where that new data set came from. If you don't feel like typing 100 lines, you can 1)--use Excel's Fill Series feature to do most of the typing for yo...

SSIS2008 and Cannot retrieve the column code page info from the OLE DB Provider
Is there a solution for this warning from SSIS 2008? Cannot retrieve the column code page info from the OLE DB Provider Setting AlwaysUseDefaultCodePage to TRUE didn't fix it. -- John Hardin KA7OHZ Senior Applications Developer, BI Specialist EPICOR Retail web: http://www.epicor.com voice: (425) 245-1800 fax: (425) 670-1810 email: <jhardin@epicor.com> 20818 44th Ave. W., Suite 270 Lynnwood, WA 98036 USA Worldwide Headquarters 18200 Von Karman, Suite 1000, Irvine CA 92612 USA ------------------------------------------------------------------------ The firs...

Re: date format conversion #2
Hey thanks so much! That worked very nicely. I was unaware of that "datepart" function. ...

*.wmv -> *.mpg (or any qt-format, any format fcp can read)
Hi, anybody an idea how can I convert *.wmv-files to something QuickTime (especially FinalCut Pro, DVD Studio Pro) can use. I'm a Macuser and I don't like M$-Progs on my mashine. THX berkhoff In article <1113752038_20868@news-east.n>, Berkhoff <berkhoff@freemail.hu> wrote: > Hi, > anybody an idea how can I convert *.wmv-files to something QuickTime > (especially FinalCut Pro, DVD Studio Pro) can use. > > I'm a Macuser and I don't like M$-Progs on my mashine. > > THX Depending upon what codec/version was used to create the .wmv file, y...

Annoying output
I often copy the output of calulation into a text editor. In Matlab, the output in the command window of a column vector is just a column data. I can just highlight and copy them. However, in Scilab, the output will look like ! 16. ! ! 17. ! ! 18. ! ! 19. ! ! 24. ! ! 25. ! ! 26. ! ! 27. ! ! 36. ! ! 37. ! ! 38. ! ! 39. ! ! 40. ! ! 41. ! ! 42. ! ! 43. ! ! 60. ! ! 61. ! ! 62. ! ! 63. ! ! 128. ! ! 129. ! ! 130. ! [More (y or n ) ?] Firstly, I have to delete each ! sign. Secondly I have to delete each ...

RAW image data format
I just got a digital SLR, and one of the recording formats is RAW. The resolution isn't noticeably better than its extra fine jpeg, but it ought to be easier to process. I'm pretty happy using GIMP (Gnu Image Manipulation Program, obviously free) on my digital images, but it doesn't know Minolta's RAW format. I have a program that can convert it to tiff, but GIMP, while it displays the image, finds tags it doesn't understand. Instead of asking naive questions, I simply request advice. One bit of processing I want to do is averaging. The "film speed" can be ...

output to spreadsheet
I use OpenOffice but I suppose similar formatting rules apply to MS Excel. For example, to format a cell for two digit numbering, the format code is: #,##0.00 I can use formulas (=F$temp + 5) and there's likely a way to write formatting strings in the output code, I just need a little help. Any ideas? My code is fairly typical to export to spreadsheet: //get data from database header("Content-type: application/octet-stream"); header("Content-Disposition: attachment; filename=\"my-data.xls\""); $data = ""; $temp = 0; foreach($r as $row) { $tem...

Re: V9 format
Stat, I don't know what code you used to get you dataset with yor format names so i will skip that now on to your error you might want to invest some time into reading the online docs on FMTSEACRCH= : FMTSEARCH= System Option -------------------------------------------------------------------------------- Controls the order in which format catalogs are searched Valid in: configuration file, SAS invocation, OPTIONS statement, SAS System Options window Category: Environment control: Files PROC OPTIONS GROUP= ENVFILES ------------------------------------------------------------------...

printf doesn't show any output under cygwin bash shell
Hi Why trying to print out messages using printf does't produce any output under bash/cygwin (it works though under cmd.exe). Printing with "cout << " works anywhere. Is there something I can fix on my PC (WinXP) or it's a known problem? I started to see the problem when my clisp output didn't print anything in emacs (which uses bash shell), I took a look a the clisp code and saw that they use printf to print out. Thank you, Andrei "Trastabuga" <lispercat@gmail.com> writes: > Why trying to print out messages using printf does't produce any...

what does debug outputs mean ?
How are you? Please anybody tell me that what those debug outputs mean,. For example, when I read below debug outputs, I understand that "src" is source, and "dst" is destination. "debug arp" *Mar 1 00:06:09.619: IP ARP: creating incomplete entry for IP address: 192.1.1.1 interface Ethernet0/1 *Mar 1 00:06:09.619: IP ARP: sent req src 172.16.39.3 00b0.64cb.eab1, dst 192.1.1.1 0000.0000.0000 Ethernet0/1 *Mar 1 00:06:09.635: IP ARP rep filtered src 192.1.1.1 0010.7b81.0b19, dst 172.16.39.3 00b0.64cb.eab1 wrong cable, interface Ethernet0/1 wha...

Question Regarding Output To An IFrame
I'm having problems getting an html file to display within an IFrame. I would like to have unchanging top and left hand side information with changes occurring within an IFrame. When I use a Select List to try to open the html file Nike.html within the IFrame it always opens a new full size window. Can anyone enlighten me how to direct the display to the IFrame? The directory structure is localhost/Test01 Below are two files. localhost/Test01/Test01.htm and localhost/Test01/Nike.html Thanks in advance Douglas Test01.html ----------------------------------------------------------- <S...

On the conversion of Mapinfo format into Arcinfo format
Hello everyone here, I am mainly working with ArcMap. However recently I have to read a map in the format of Mapinfo. Since I do not have access to a Mapinfo software, anyone have an idea of converting the Mapinfo map (tab file) into the ArcMap format (shape file) without using Mapinfo? Thanks in advance Jianhua JIanhua Guo wrote: > Hello everyone here, > > I am mainly working with ArcMap. However recently I have to read a map in > the format of Mapinfo. Since I do not have access to a Mapinfo software, > anyone have an idea of converting the Mapinfo map...

WWW::Search produces no output
HI, I wonder why the following code produces no output on my XP ?Modules seem to be properly installed. =============================== require WWW::Search; my $sQuery = 'postscript'; my $oSearch = new WWW::Search('AltaVista'); $oSearch->native_query(WWW::Search::escape_query($sQuery)); while (my $oResult = $oSearch->next_result()) { print $oResult->url, "\n"; sleep 1; } # while On 05/27/2007 07:50 AM, MoshiachNow wrote: > HI, > > I wonder why the following code produces no output on my XP ?Modules > seem to be properly install...

Upgrade or format/clean install
1. I am a n00b 2. Macbook pro (3-4 years old) 3. Running SL 4. Want to go to 10.7. - haven't done this yet 5. Don't want to f... up - I usually do, the simpler things are, more prone am I to f... just the same So, any good web resources to read through before doing this? I read some forum threads claiming that a clean install is "smarter" than upgrading ... What is your experience? Any tips? Tnx, and sorry for a n00b question. -- oNe buTTon keyBOARd To RuLe tHeM All In article <PM0004C99CB7468E51@miso-mudrics-macbook-pro.unknown.dom>, Marlock...

Analyzing Python GC output
I'm printing out each entry in "gc.garbage" after a garbage collection in DEBUG_LEAK mode, and I'm seeing many entries like <cell at 0x00F7C170: function object at 0x00FDD6B0> That's the output of "repr". Are "cell" objects created only from external C libraries, or can regular Python code generate them? Is there any way to find out what the 'function object' is from within Python? (I'm looking for a possible memory leak, obviously.) John Nagle John Nagle <nagle@animats.com> wrote: > I'm printing out each ent...

How to use the merge block for enablesubsystem with vector-inputs and -outputs
Hi, my friends, I want to realize a datafusion subsystem. The inputs are from two radarsensors. Each of the inputs is a 7-element-vector (after bus creator)and is fed to a enablesubsystem. The two vectors should be read by the merge block, but only one vector as output. After the merge block, I want to use the elements of the vectors separately, namely be divided by a bus selector. But now the problem is, the inputs signal of the bus selector can be not found!!! It seems that the merge can not transfer two vectors! Please help me! Thank you very much in advance! yours yoyo ...

Formatted Input
I have a file structure like this.. Variable Description StartingColumn EndingColumn Data Type Name Name 1 15 Char Acct Account number 16 20 Char Balance Acct balance 21 26 Num Rate Interest rate 27 30 Num and Text file is like this Philip Jones V1234 4322.32 Nathan Philips V1399 15202.45 Shu Lu W8892 451233.45 Betty Boop V7677 50002.78 My problem is i need to read this file by using formatted input. i tried with follo...

a function to output multiple variable names, getting the name of a var into a string,
Hi, $mice = array('mickey','minni','mighty'); $pets = array('cow'=>'bessie','dog'=>'fido','mice' => $mice); $config = 'some setting'; function showvars($message){ $args = func_get_args(); echo"<pre>"; print_r($args);echo"</pre>"; echo "$message <BR>"; foreach ($args as $k=>$v) { $name = var_name($k,get_defined_vars()); echo '$'.$k.' = '.$v."<br>"; } } function var_name (&$iVar, &$aDefinedVars){ foreach ($aD...

sql rdd for BCC
I am useing xHarbour ver 1.0. A few month ago I have tested SqlRdd. There was sql.lib for ver 1.0 with some errors. The proper lib was sent to me from xHarbour. Later, I bought SqRdd and there wasn't the usage for the ver 1.0 as well. XHarbour. com answered for my complain that I should address my request to the newsletter. Is is the proper place to indicate the producer thet the product is invalid? I was in hurry so I exchanged for my previously received lib. After two months my software has stopedd working. I have sent an e-mail to Patrick Mast and he suggested that I should annouce th...

Re: %sysfunc and date format #2
On Wed, 5 Sep 2007 09:13:42 -0700, a@MAILINATOR.COM wrote: >this works but not in the proc sql > >%let a=%sysfunc(putn(%sysfunc(date()) -16, yymmdd10.)); >%let b=%sysfunc(translate(%sysfunc(putn(%sysfunc(date()) -16, >yymmdd10.)),"/","-")); >%put &a &b; > >2007-08-20 2007/08/20 > >..and date = > ("%sysfunc(translate(%sysfunc(putn(%sysfunc(date()) -16, >yymmdd10.)),"/","-"))") > >produces error in sas log > >Column '2007/08/20' not found hi, with my 9.1.3 sp4 on my win xp, i get...