Re: Converting fields from Double to String while writing a table view

From: Mark D Powell <Mark.Powell2_at_hp.com>
Date: Fri, 16 Nov 2012 11:56:06 -0800 (PST)
Message-ID: <69e33fe0-20b2-40fc-9d00-2aea20f48321_at_googlegroups.com>



On Friday, November 16, 2012 2:40:01 PM UTC-5, Matt Whitman wrote:
> On Friday, November 16, 2012 12:32:57 PM UTC-5, Harry Tuttle wrote: > Matt Whitman wrote on 16.11.2012 02:49: > Here is a brief example of what I'm trying to do. I've not had any issues creating the view I am >uncertain the best method to change the connection column to string when it is currently a double. > > CREATE VIEW GIS_VIEW AS > SELECT address, > id, > status, > connection > FROM Location > WHERE status = "active" > In SQL (in general as well as in Oracle) character literals (aka strings) need to be enclosed in single quotes. Double quotes are used to denote identifiers (tables, columns, ...) CREATE VIEW GIS_VIEW AS SELECT address, id, status, connection FROM Location WHERE status = 'active'; Btw: that is not PL/SQL, that is simple SQL. PL/SQL is about procedures, functions, triggers and such things. Thanks for the help. Sorry for the lack of clarity. In my original post I should I said was transitioning from working with Access (program) to PL/SQL Developer (program). This transition involves updating my previous Access specific queries to SQL. I am trying to set the column data type because I need to convert it so I can join with a spatial dataset string field. I understand this can be done with to_char and had just wondering if the group had any advice on the best practice. Thanks!

MTT, Generally speaking if you select data in Oracle then since the result of a select is character readable data Oracle will automatically try to convert the internal data type to character for you. If on the other hand you are coding a join or sub-query comparison you will want to take explict control of the conversion. First off you do this to make sure Oracle does not attempt to perform the conversion on the other side of the operator from what you wanted. Second if you need specific formatting then you need to take manual control and specifiy the formatting.

As you mentioned to_char is intended to handle the conversion of numbers and date data types into character representation.

Also note that Oracle by default is case sensitive so = 'active' will not find 'Active' or 'ACTIVE'. Generally speaking you want to save user character input as uppercase. The upper(), lower(), and initcap() functions are available to assist with character case conversion.

You can find most of the single row functions of Oracle documented in the SQL manual.

HTH -- Mark D Powell -- Received on Fri Nov 16 2012 - 20:56:06 CET

Original text of this message