Adding the sum of column to use as alias

Chumley,
I've double-checked the syntax of the statements I posted, and they're OK.
Have you altered it them any way? Post the exact statement you're executing
and I'll have a look.
Also, which version of SQL Server are you working in?
Thanks
Damien
"Chumley Walrus" wrote:

> Damien, I now get an "Invalid syntax near SUM " error from the sql
> string you have outlined (pointing to the HAVING SUM line). I don't
> understand,a s I know there's data in there meeting this criteria.
>Right,
I'll spare you the 'dynamic sql is bad' stuff, because if you read this
group regularly, you already know.
You are missing a plus sign after your GROUP BY clause, and you cannot use
an alias in your HAVING. In the SQL I posted for you, I put:
SELECT salesperson, SUM( saleamount ) AS allsales
FROM #transactions
--WHERE thedate In ('20050106', '20050206')
GROUP BY salesperson
HAVING SUM( saleamount ) > 0
So you can see, you don't use 'allsales', you use SUM ( saleamount ).
Being rude to Joe is not going to help you. He's earned his right to make
comments like that be being one of the leading authorities in SQL in the
entire world. Even if you don't agree with his point of view, you've at
least got to respect it.
Let me know how you get on with that SQL.
Damien
"Chumley Walrus" wrote:

> I have inner joins (they all work, as the various IDs are related, and
> do fine in my main sql string), I would post the DDL, but I know the
> datatypes are absolutely accurate (saleamount is a money datatype)
> SELECT ticket.Salesperson_ID, employ.LName + ', ' + tblSalesRep.FName
> AS Salesperson, " +
> "ticket.ID, employ.ID, " +
> "transaction.thedate, " +
> "SUM(transactions.saleamount) AS allsales,
> transactions.ticket_ID,transactions.thedate " +
> "FROM ticket " +
> "INNER JOIN employ ON ticket.Salesperson_ID = employ.ID " +
> "INNER JOIN transactions ON ticket.ID=transactions.ticket_ID " +
> "WHERE transactions.theddate IN ('6/1/2005', '6/2/2005')" +
> "GROUP BY Salesperson"
> "HAVING SUM(allsales) > 0 ";
> once again, I get an "Invalid syntax error by SUM" on HAVING
> SUM(allsales) line.
> '
> chumley
>

adding new column

ALTER TABLE BFTITLE ADD COLUMN [RATETYPE] [NUMERIC] (10) NOT NULL ;
syntax is not working so can any one tell the right syntaxalter Table Bftitle Add [ratetype] [numeric] (10) Not Null|||Read BOL (SQL Server Books On Line) carefully. If a table already has rows in it, it isn't possible to add a NOT NULL column unless you also give the column a default value too (if you think about it, this only makes sense). I'm guessing on this, but I'd suggest trying:ALTER TABLE BFTITLE
ADD COLUMN [RATETYPE] [NUMERIC] (10) NOT NULL
DEFAULT 0-PatP|||ALTER TABLE BFTITLE
ADD COLUMN [RATETYPE] [NUMERIC] (10) NOT NULL
DEFAULT 0
-PatP

Close. This worked for me:

ALTER TABLE BFTITLE
ADD [RATETYPE] [NUMERIC] (10) NOT NULL
DEFAULT 0

I just removed "COLUMN" from your statement. Funny, I did not think it would be possible to add a non-nullable column, but you're right it can be done as long as you define a default value.

Following is proof:

create table foo (bar int)
go

insert foo (bar) values (1)
go
insert foo (bar) values (2)
go
insert foo (bar) values (3)
go
insert foo (bar) values (4)
go

ALTER TABLE foo
ADD [RATETYPE] [NUMERIC] (10) NOT NULL
DEFAULT 0

select * from foo

sp_columns foo

Adding locationID's to detail records using SQL syntax

SQLserver 2K
Table A (Customer Master)
CustID
CustName
Table B (Customer Location)
CustID
LocationID
LocationOrderID
LocationName
What's the syntax to automatically add LocationOrderID in increment of 1 for
table B where B.CustID = A.CustID ?
For example,
A.CustID = 100
and there are 5 B records having B.CustID = 100
I need to insert LocationOrderID starting with 1 to B.LocationOrderID based
on the order of B.LocationName
Any help is greatly appreciated.
BillTry,
update tableB
set LocationOrderID = (select count(*) from tableB as a where a.CustID =
tableB.CustID and a.LocationName <= tableB.LocationName)
go
AMB
"Bill Nguyen" wrote:

> SQLserver 2K
> Table A (Customer Master)
> CustID
> CustName
> Table B (Customer Location)
> CustID
> LocationID
> LocationOrderID
> LocationName
> What's the syntax to automatically add LocationOrderID in increment of 1 f
or
> table B where B.CustID = A.CustID ?
> For example,
> A.CustID = 100
> and there are 5 B records having B.CustID = 100
> I need to insert LocationOrderID starting with 1 to B.LocationOrderID base
d
> on the order of B.LocationName
>
> Any help is greatly appreciated.
> Bill
>
>|||Alejandro;
This works great!
Thanks
Bill
"Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in message
news:9E0D3718-1900-409C-BAFB-885F32212EB9@.microsoft.com...
> Try,
> update tableB
> set LocationOrderID = (select count(*) from tableB as a where a.CustID =
> tableB.CustID and a.LocationName <= tableB.LocationName)
> go
>
> AMB
> "Bill Nguyen" wrote:
>

Adding custom code in LocalReport

Hi,
I have a LocalReport to which I'm trying to add some code to (Report
Properties -> Code).
The code is fine syntax wise, but when I attempt to use it in the
Expression editor, the function never appears. Other examples seem to
just add the function into the code window and it just shows up in the
expression editor after typing Code.
Any ideas what I'm doing wrong?
Thanks
AndyOn Jun 28, 2:36 pm, Andy <a...@.med-associates.com> wrote:
> I have a LocalReport to which I'm trying to add some code to (Report
> Properties -> Code).
> The code is fine syntax wise, but when I attempt to use it in the
> Expression editor, the function never appears. Other examples seem to
> just add the function into the code window and it just shows up in the
> expression editor after typing Code.
No one has any idea what would prevent my function from being usable?

Adding Columns

What is the SQL syntax for adding a column to an already created DB table?ALTER TABLE <your table> ADD <your column> <column definition>

e.g.
ALTER TABLE tbl ADD col VARCHAR(20) NULL|||Originally posted by waspfish
ALTER TABLE <your table> ADD <your column> <column definition>

e.g.
ALTER TABLE tbl ADD col VARCHAR(20) NULL

What is with the "NULL"?

And, another question, how would you edit the name of an existing column?|||Open SQL Server Books Online and search the keywords of "ALTER TABLE" in the tab Index. You can get more information about modifications of a table definition by altering, adding, or dropping columns and constraints, or by disabling or enabling constraints and triggers.|||...or you can do it the lazy, cheating way:

1) Open the table and make the changes you want.
2) Click the "Save changes script" button in the icon bar.
3) Copy the resulting script.
4) Close your table WITHOUT saving changes.

blindman

adding column using t-sql

How can I add a column to the begining of the table using
t-sql? I know the syntax, but it adds it as the last
column on the table.
TIA,
Vic
Hi,
No, you cant do that. Using TSQL statements (ALTER table) you cant insert a
column inbetween or first. It
always add the new column in the end.
Using Enterprise manager we can insert columns inbetween, but enterprise
manager does this in the below way
1. Export data out
2. Drop the table
3. Create the new table with new column added
4. Loads back the data.
This will not be a good mechanism if your data volume in your table is high.
Thanks
Hari
SQL Server MVP
"Vic" <vduran@.specpro-inc.com> wrote in message
news:080601c53f17$3e349bc0$a501280a@.phx.gbl...
> How can I add a column to the begining of the table using
> t-sql? I know the syntax, but it adds it as the last
> column on the table.
> TIA,
> Vic
|||> This will not be a good mechanism if your data volume in your table is
high.
Also, you should not rely on column order. In a relation, column and row
order is not defined. You should always specify column names in your DML
statements.
Dejan Sarka, SQL Server MVP
Associate Mentor
www.SolidQualityLearning.com

adding column using t-sql

How can I add a column to the begining of the table using
t-sql? I know the syntax, but it adds it as the last
column on the table.
TIA,
VicHi,
No, you cant do that. Using TSQL statements (ALTER table) you cant insert a
column inbetween or first. It
always add the new column in the end.
Using Enterprise manager we can insert columns inbetween, but enterprise
manager does this in the below way
1. Export data out
2. Drop the table
3. Create the new table with new column added
4. Loads back the data.
This will not be a good mechanism if your data volume in your table is high.
Thanks
Hari
SQL Server MVP
"Vic" <vduran@.specpro-inc.com> wrote in message
news:080601c53f17$3e349bc0$a501280a@.phx.gbl...
> How can I add a column to the begining of the table using
> t-sql? I know the syntax, but it adds it as the last
> column on the table.
> TIA,
> Vic|||> This will not be a good mechanism if your data volume in your table is
high.
Also, you should not rely on column order. In a relation, column and row
order is not defined. You should always specify column names in your DML
statements.
--
Dejan Sarka, SQL Server MVP
Associate Mentor
www.SolidQualityLearning.com

adding column using t-sql

How can I add a column to the begining of the table using
t-sql? I know the syntax, but it adds it as the last
column on the table.
TIA,
VicHi,
No, you cant do that. Using TSQL statements (ALTER table) you cant insert a
column inbetween or first. It
always add the new column in the end.
Using Enterprise manager we can insert columns inbetween, but enterprise
manager does this in the below way
1. Export data out
2. Drop the table
3. Create the new table with new column added
4. Loads back the data.
This will not be a good mechanism if your data volume in your table is high.
Thanks
Hari
SQL Server MVP
"Vic" <vduran@.specpro-inc.com> wrote in message
news:080601c53f17$3e349bc0$a501280a@.phx.gbl...
> How can I add a column to the begining of the table using
> t-sql? I know the syntax, but it adds it as the last
> column on the table.
> TIA,
> Vic|||> This will not be a good mechanism if your data volume in your table is
high.
Also, you should not rely on column order. In a relation, column and row
order is not defined. You should always specify column names in your DML
statements.
Dejan Sarka, SQL Server MVP
Associate Mentor
www.SolidQualityLearning.com