column name can't include character '-'

[Niy]

why?

[Mark C. Stock]

Re: column name can't include character '-'


"Niy" <niy38@hotmail. com> wrote in message
news:55dd405a.0 402181456.59042 58c@posting.goo gle.com...
| why?

that's the rules

also, '-' has special meaning to the parser

however, just like Access and VB use [] delimiters, Oracle (and I believe
this is standard ANSI SQL) uses "" delimiters to allow 'illegal' column (and
other object) names -- but I wouldn't recommend this

just use _ instead of -, and life with oracle will be just fine


;-{) mcs

[x]

Re: column name can't include character '-'

IF you had a tale with columns

a number
b number
a-b number

what would return

select a-b from x

?

[Mark C. Stock]

Re: column name can't include character '-'

your question is not clear, but it looks like you need to do research on
expressions -- take some time reading the Oracle SQL Reference manual, and
running some tests

you will find that oracle does not support derived columns in table
definitions, but it does in views

;-{ mcs

"x" <x@x.hr> wrote in message news:c11pqa$u56 $1@ls219.htnet. hr...
| IF you had a tale with columns
|
| a number
| b number
| a-b number
|
| what would return
|
| select a-b from x
|
| ?
|
|

[Mark D Powell]

Re: column name can't include character '-'

"x" <x@x.hr> wrote in message news:<c11pqa$u5 6$1@ls219.htnet .hr>...[color=blue]
> IF you had a tale with columns
>
> a number
> b number
> a-b number
>
> what would return
>
> select a-b from x
>
> ?[/color]

Obviously a minus b:

SQL> @test
SQL> create table marktest3 (
2 a number, b number, "a-b" number);

Table created.

SQL> desc marktest3
Name Null? Type
----------------------------------------- --------
----------------------------
A NUMBER
B NUMBER
a-b NUMBER


SQL>
SQL> insert into marktest3 values (6,4,9);

1 row created.

SQL> commit;

Commit complete.

SQL>
SQL> select a, b, a-b, "a-b" from marktest3;

A B A-B a-b
---------- ---------- ---------- ----------
6 4 2 9

SQL>
SQL> drop table marktest3;

Table dropped.

If you need a col "a-b" it would be better to name it a_minus_b,
diff_a_b, or some such than use double quotes to create potential
problems for users.

HTH -- Mark D Powell --

[Mark C. Stock]

Re: column name can't include character '-'

| SQL> select a, b, a-b, "a-b" from marktest3;
|
| A B A-B a-b
| ---------- ---------- ---------- ----------
| 6 4 2 9
|
| SQL>
| SQL> drop table marktest3;
|
| Table dropped.
|
| If you need a col "a-b" it would be better to name it a_minus_b,
| diff_a_b, or some such than use double quotes to create potential
| problems for users.
|
| HTH -- Mark D Powell --

unless there's a specific identified processing advantage to storing a
derived value, the "a-b" or a_minus_b column really should be implemented
via a view, not included in the table.
(function-based indexes would be an alternative if searches are often done
on the derived value, if an index would be effective in those searches)

-- mcs

[Mark D Powell]

Re: column name can't include character '-'

"Mark C. Stock" <mcstockX@Xenqu ery .com> wrote in message news:<OLWdnaELQ vHqVKndRVn-uA@comcast.com> ...[color=blue]
> | SQL> select a, b, a-b, "a-b" from marktest3;
> |
> | A B A-B a-b
> | ---------- ---------- ---------- ----------
> | 6 4 2 9
> |
> | SQL>
> | SQL> drop table marktest3;
> |
> | Table dropped.
> |
> | If you need a col "a-b" it would be better to name it a_minus_b,
> | diff_a_b, or some such than use double quotes to create potential
> | problems for users.
> |
> | HTH -- Mark D Powell --
>
> unless there's a specific identified processing advantage to storing a
> derived value, the "a-b" or a_minus_b column really should be implemented
> via a view, not included in the table.
> (function-based indexes would be an alternative if searches are often done
> on the derived value, if an index would be effective in those searches)
>
> -- mcs[/color]

Mark, very true. I was just trying to make a point about meaningful
names and should have thought of that also.

Saving calculated values should only be done where either the
variables of the calculation are not retained with the result such as
in a history table OR the cost of calculating the result is too
expensive. Our electronics and prototype lab calculates values that
often take significant time per calculation so even it the variable
values are saved it is not feasible to recalculate the results for
display.

-- Mark D Powell --

[Niy]

Re: column name can't include character '-'

Thanks for the reply.

In v$transaction, you can see
DSCN-B, DSCN-W columns, how
to select them?

[Mark C. Stock]

Re: column name can't include character '-'


"Niy" <niy38@hotmail. com> wrote in message
news:55dd405a.0 402191936.584f4 b8e@posting.goo gle.com...
| Thanks for the reply.
|
| In v$transaction, you can see
| DSCN-B, DSCN-W columns, how
| to select them?


very observant -- you have to (always) use double-quotes to select these
columns, and type them in upper case, because oracle (why, oh, why?) used
non-conforming column names here

the only exception to 'always' is when you do a SELECT *

select addr, "DSCN-B", "DSCN-W"
from v$transaction

;-{ mcs

[Mark C. Stock]

Re: column name can't include character '-'


"Mark D Powell" <Mark.Powell@ed s.com> wrote in message
news:2687bb95.0 402191741.50904 bbd@posting.goo gle.com...
| "Mark C. Stock" <mcstockX@Xenqu ery .com> wrote in message
news:<OLWdnaELQ vHqVKndRVn-uA@comcast.com> ...
| > | SQL> select a, b, a-b, "a-b" from marktest3;
| > |
| > | A B A-B a-b
| > | ---------- ---------- ---------- ----------
| > | 6 4 2 9
| > |
| > | SQL>
| > | SQL> drop table marktest3;
| > |
| > | Table dropped.
| > |
| > | If you need a col "a-b" it would be better to name it a_minus_b,
| > | diff_a_b, or some such than use double quotes to create potential
| > | problems for users.
| > |
| > | HTH -- Mark D Powell --
| >
| > unless there's a specific identified processing advantage to storing a
| > derived value, the "a-b" or a_minus_b column really should be
implemented
| > via a view, not included in the table.
| > (function-based indexes would be an alternative if searches are often
done
| > on the derived value, if an index would be effective in those searches)
| >
| > -- mcs
|
| Mark, very true. I was just trying to make a point about meaningful
| names and should have thought of that also.
|
| Saving calculated values should only be done where either the
| variables of the calculation are not retained with the result such as
| in a history table OR the cost of calculating the result is too
| expensive. Our electronics and prototype lab calculates values that
| often take significant time per calculation so even it the variable
| values are saved it is not feasible to recalculate the results for
| display.
|
| -- Mark D Powell --

point well made, and good real-world example

;-{ mcs

[x]

Re: column name can't include character '-'

Thank you for your answers, but I didn't ask what would select a-b return, I
just answered to 'why column name can't include character '-''

My point is that in that cese select a-b would be confusing. select "a-b" is
something else.