add column to exiting table

I have a table that has the following columns:
ID SSN LastName FirstName TypeCodeID
With keeping the integrity of the information can I create a new column that
is between FIRSTNAME and TypeCodeID named Active? I am hoping not having to
drop table and rather use the Alter table command. If so, how?
This would be new table format.
ID SSN LastName FirstName Active TypeCodeIDYou cannot add a column in the middle of a table. When you do an ALTER
TABLE to add a column, it is added at the and of the table.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"Big D" <BigDaddy@.newsgroup.nospam> wrote in message
news:eXsabilYFHA.1868@.TK2MSFTNGP14.phx.gbl...
I have a table that has the following columns:
ID SSN LastName FirstName TypeCodeID
With keeping the integrity of the information can I create a new column that
is between FIRSTNAME and TypeCodeID named Active? I am hoping not having to
drop table and rather use the Alter table command. If so, how?
This would be new table format.
ID SSN LastName FirstName Active TypeCodeID|||Big D wrote:

> I have a table that has the following columns:
> ID SSN LastName FirstName TypeCodeID
> With keeping the integrity of the information can I create a new column
that
> is between FIRSTNAME and TypeCodeID named Active? I am hoping not having
to
> drop table and rather use the Alter table command. If so, how?
>
> This would be new table format.
> ID SSN LastName FirstName Active TypeCodeID
Hi,
Yes, you use ALTER TABLE to add a column to an existing table, but columns
do not have any order, just as you have no control over the order of rows.
You can specify any order you desire for columns when you retrieve rows with
a SELECT statement.
Richard
Microsoft MVP Scripting and ADSI
Hilltop Lab web site - http://www.rlmueller.net
--|||Short answer, you shouldn't care. This:
> ID SSN LastName FirstName Active TypeCodeID
is equivalent to:
> ID SSN LastName FirstName TypeCodeID Active
It has the same data, and the same properties. When you query the data, you
order the columns how you want, as you should never do:
select *
from table
Other than when you are testing.
That having been said, I know what you mean and sometimes you want to
reorder the columns for "ease of use." For this, you have to drop and
recreate the table in the order you want the columns.
----
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
"Big D" <BigDaddy@.newsgroup.nospam> wrote in message
news:eXsabilYFHA.1868@.TK2MSFTNGP14.phx.gbl...
>I have a table that has the following columns:
> ID SSN LastName FirstName TypeCodeID
> With keeping the integrity of the information can I create a new column
> that is between FIRSTNAME and TypeCodeID named Active? I am hoping not
> having to drop table and rather use the Alter table command. If so, how?
>
> This would be new table format.
> ID SSN LastName FirstName Active TypeCodeID
>
>|||An idea may be using Enterprise Manager for such sort of work. Thru EM you
can do such thing as I did many times previously.
"Big D" <BigDaddy@.newsgroup.nospam> wrote in message
news:eXsabilYFHA.1868@.TK2MSFTNGP14.phx.gbl...
>I have a table that has the following columns:
> ID SSN LastName FirstName TypeCodeID
> With keeping the integrity of the information can I create a new column
> that is between FIRSTNAME and TypeCodeID named Active? I am hoping not
> having to drop table and rather use the Alter table command. If so, how?
>
> This would be new table format.
> ID SSN LastName FirstName Active TypeCodeID
>
>|||However, Big didn't want to drop and re-create the table, which is exactly w
hat EM does...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Wayne Right" <serdar@.senkronyazilim.com> wrote in message
news:%239C55roYFHA.1152@.tk2msftngp13.phx.gbl...
> An idea may be using Enterprise Manager for such sort of work. Thru EM you
can do such thing as I
> did many times previously.
> "Big D" <BigDaddy@.newsgroup.nospam> wrote in message news:eXsabilYFHA.1868
@.TK2MSFTNGP14.phx.gbl...
>|||Thanks everyone who helped.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:%23vF52XpYFHA.3616@.TK2MSFTNGP15.phx.gbl...
> However, Big didn't want to drop and re-create the table, which is exactly
> what EM does...
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Wayne Right" <serdar@.senkronyazilim.com> wrote in message
> news:%239C55roYFHA.1152@.tk2msftngp13.phx.gbl...
>

Add Cases to Select Statment

I need to add some cases to the select statment for cpeorderstatus:

Here is my Select statement:

"SELECT O.ORDERID, C.FIRSTNAME, C.LASTNAME, O.CLIENTORDERID AS CRMORDERID, TO_CHAR(O.ORDERDATE, 'YYYYMMDD')
AS CPEORDERDATE, TO_CHAR(O.SHIPDATE, 'YYYYMMDD') AS SHIPDATE, O.TRACKINGNBR AS TRACKINGNUMBER, O.SHIPNAME AS CARRIER,
OI.ITEM AS CPEORDERTYPE, OI.QTY,
O.STATUS AS CPEORDERSTATUS, OSN.ORD_SERIAL_NO AS SERIALNUMBER, C.BTN AS BTN, C.FIRSTNAME AS FIRST, C.LASTNAME AS LAST,
C.SHIPADDR1 AS ADDRESSLINE1, C.SHIPADDR2 AS ADDRESSLINE2, C.CITY AS CITY, C.STATE AS STATE, C.ZIP AS ZIP, TO_CHAR(R.ISSUEDATE,
'YYYYMMDD') AS ISSUEDATE, R.RMA_ID AS RMANUMBER, R.RMA_REASON AS REASON, TO_CHAR(R.RETURNDATE, 'YYYYMMDD') AS RETURNDATE
FROM SELF.ORDERS O, SELF.CUSTOMER C, SELF.ORDERITEM OI, SELF.ORD_SERIAL_NUMBER OSN, SELF.RMA R
WHERE O.CUSTID = C.CUSTID AND O.ORDERID = OI.ORDERID AND O.ORDERID = OSN.ORDER_ID (+) AND O.ORDERID = R.ORDER_ID (+) AND
(C.CUSTID IN (SELECT C.CUSTID FROM SELF.CUSTOMER C WHERE C.BTN='{0}')) ORDER BY O.ORDERDATE DESC"

I need to add multiple cases to cpeorderstatus, five different cases. Cane anyonye HELP

This is really unclear. It looks like you're selecting

O.STATUS AS CPEORDERSTATUS

but you're not doing anything with it other than returning it. Why not simply adding 5 different values to whatever inserting into the orders table?