Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> comp.databases.theory -> Re: Break up a field into multiple rows

Re: Break up a field into multiple rows

From: Tibor Karaszi <tibor_not_pressed_ham_.karaszi_at_cornerstone.se>
Date: Wed, 30 Oct 2002 18:51:27 GMT
Message-ID: <PSVv9.2238$1r1.66227@newsc.telia.net>


I assume that you don't use the query builder for this. There are so many constructs that the query builder doesn't support...

Most probable reason is that the column where you have the LEFT and RIGHT expressions aren't given a name. And tables/views can't have columns with no name. Try:

,LEFT(territories, CHARINDEX(' ', territories)) AS yourcolname ,RIGHT(territories, CHARINDEX(' ', territories)) AS yourcolname

(In SQL Server, the column name in a UNION is picked from the first query. In ANSI SQL it is, as I recall it, undefined in case of conflict; which is why I prefer to be consistent across the SELECTs in the UNION.)

--
Tibor Karaszi


"Todd Benson" <toddb_at_spectralogic.com> wrote in message
news:97b61782.0210301000.75832c9b_at_posting.google.com...

> Thanks, this makes sense. My only problem is that I cannot write this
> as a view in MS SQL server. Any one know why? It simply errors out
> and complains that there are no output columns or from clause.
>
> Todd
>
> "Tibor Karaszi" <tibor_not_pressed_ham_.karaszi_at_cornerstone.se> wrote in
message news:<Ewvu9.1454$1r1.44981_at_newsc.telia.net>...
> > Todd,
> >
> > Here's a "brute force" method, using some string manipulation and a
UNION.
> > Dialect is MS Transact-SQL. The CHARINDEX function find the position of
the
> > first expression within the second expression.
> >
> > SELECT
> > order_number
> > ,sales_rep
> > ,item
> > ,quantity
> > ,LEFT(territories, CHARINDEX(' ', territories))
> > FROM sales_orders
> > UNION ALL
> > SELECT
> > order_number
> > ,sales_rep
> > ,item
> > ,quantity
> > ,RIGHT(territories, CHARINDEX(' ', territories))
> > FROM sales_orders
> >
> > --
> > Tibor Karaszi
Received on Wed Oct 30 2002 - 12:51:27 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US