formula column

[Billy Cormic]

Hi,
I would like to create a calculated column using the formula
section for a table. I am having some trouble doing this.

The table's name is ReportParameter . The calculated column's name is
tbcalculatedcol umn and tb1 and tb2 are boolean columns in the table.
I would like to use an If then statement such as the following (in
psuedo code):

If tb1 = 1 then tbcalculatedcol umn = 1
Elseif tb2 = 1 then tbcalculatedcol umn = 2
Endif

Thanks for the help,
Bill

[John Bell]

Re: formula column

Hi

Maybe something like:

CREATE TABLE MyTable ( tb1 bit, tb2 bit, tbcalculatedcol umn AS CASE WHEN
tb1 = 1 then 1
WHEN tb2 = 1 then 2
END )


INSERT INTO MyTable ( tb1, tb2 ) VALUES ( 1,1 )
INSERT INTO MyTable ( tb1, tb2 ) VALUES ( 1,0 )
INSERT INTO MyTable ( tb1, tb2 ) VALUES ( 0,1 )

SELECT * FROM MyTable
/*
tb1 tb2 tbcalculatedcol umn
---- ---- ------------------
1 1 1
1 0 1
0 1 2
*/

John
"Billy Cormic" <billy_cormic@h otmail.com> wrote in message
news:dd2f7565.0 310010526.3ce4b e47@posting.goo gle.com...[color=blue]
> Hi,
> I would like to create a calculated column using the formula
> section for a table. I am having some trouble doing this.
>
> The table's name is ReportParameter . The calculated column's name is
> tbcalculatedcol umn and tb1 and tb2 are boolean columns in the table.
> I would like to use an If then statement such as the following (in
> psuedo code):
>
> If tb1 = 1 then tbcalculatedcol umn = 1
> Elseif tb2 = 1 then tbcalculatedcol umn = 2
> Endif
>
> Thanks for the help,
> Bill[/color]

[David Portas]

Re: formula column

> I would like to create a calculated column using the formula
Why? Why not just put a CASE expression in a query or view rather than
create an extra redundant column.
[color=blue]
> tbcalculatedcol umn and tb1 and tb2 are boolean columns in the table.[/color]
There is no Boolean data type in SQLServer. You mean a numeric column
(presumably BIT).

--
David Portas
------------
Please reply only to the newsgroup
--

"Billy Cormic" <billy_cormic@h otmail.com> wrote in message
news:dd2f7565.0 310010526.3ce4b e47@posting.goo gle.com...[color=blue]
> Hi,
> I would like to create a calculated column using the formula
> section for a table. I am having some trouble doing this.
>
> The table's name is ReportParameter . The calculated column's name is
> tbcalculatedcol umn and tb1 and tb2 are boolean columns in the table.
> I would like to use an If then statement such as the following (in
> psuedo code):
>
> If tb1 = 1 then tbcalculatedcol umn = 1
> Elseif tb2 = 1 then tbcalculatedcol umn = 2
> Endif
>
> Thanks for the help,
> Bill[/color]

[--CELKO--]

Re: formula column

Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, datatypes, etc. in
your schema are. Sample data is also a good ideas, along with clear
specifications.
[color=blue][color=green]
>> The table's name is ReportParameter . <<[/color][/color]

That is not a table name; a table is an entity or a relationship. It
is also too vague to be a data element. You need to read a book about
database design.
[color=blue][color=green]
>> The calculated column's name is "tbcalculatedco lumn" ...<<[/color][/color]

Please tell me that "tbl-" is not a silly redundant prefix; and there
is always a better name than "calculated_col umn" for a calculated
column -- what extactly did you you compute? Interest? discounts? To
be is to be something in particular; to be nothing in particular is to
be nothing.
[color=blue][color=green]
>> and tb1 and tb2 are boolean columns in the table. <<[/color][/color]

There are no BOOLEAN variables in SQL; it would destroy the 3VL. Look
up the CASE expression in any pf the books on SQL you clearly have
never read.

[Christian Maslen]

Re: formula column

> There are no BOOLEAN variables in SQL; it would destroy the 3VL.

Aren't Booleans defined in SQL99?

[David Portas]

Re: formula column

Yes. Joe goes into denial when confronted with SQL99 ;-)

--
David Portas
------------
Please reply only to the newsgroup
--

"Christian Maslen" <christian.masl en@techie.com> wrote in message
news:b9c8cfba.0 310021514.11d51 a@posting.googl e.com...[color=blue][color=green]
> > There are no BOOLEAN variables in SQL; it would destroy the 3VL.[/color]
>
> Aren't Booleans defined in SQL99?[/color]

[--CELKO--]

Re: formula column

>> Joe goes into denial when confronted with SQL99 <<

So does everyone else who worked on the draft documents :)

They had to re-define the foundation to get them into SQL-99 and this
is oneof many reasons nobody is gallopping to SQL-99. The US
government requires SQL-92 and refers to it as "a standard in
progress" in their bid forms.

The problem is that a data type in SQL must be NULL-able; a NULL
doesnto have a data type itself, but holds a place for a value which
may or may not be determined later.

1) The fundamental rule of a NULL is that it propagates.

2) The fundamental rule of 3VL is that your have TRUE, FALSE and
UNKNOWN as the only possible values.

These fundamentals don't go together if you can have a column with a
3VL datatype. The "solution" was to make NULL = UNKNOWN but only in a
BOOLEAN column and then worry about null propagation. This screws up
3VL operators in some pretty awful ways that can drive an SQL engine
nuts:

FALSE OR UNKNOWN = UNKNOWN -- definition of OR
FALSE OR NULL = NULL = UNKNOWN -- null propagation
TRUE OR UNKNOWN = TRUE -- definition of OR
TRUE OR NULL = NULL = UNKNOWN -- null propagation

likewise,

TRUE AND UNKNOWN = UNKNOWN -- definition of AND
TRUE AND NULL = NULL = UNKNOWN -- null propagation
FALSE AND UNKNOWN = FALSE -- definition of AND
FALSE AND NULL = NULL = UNKNOWN -- null propagation