Query ?

Here is the table:
CREATE TABLE [teamstats] (
[name] [varchar] (10) NOT NULL ,
[pos] [varchar] (3) NOT NULL ,
[ab] [numeric](3, 0) NOT NULL ,
[hits] [numeric](4, 0) NOT NULL ,
[walks] [varchar] (5) NOT NULL ,
[singles] [varchar] (7) NOT NULL ,
[doubles] [varchar] (7) NOT NULL ,
[triples] [varchar] (7) NOT NULL ,
[hr] [numeric](2, 0) NOT NULL ,
[so] [varchar] (2) NOT NULL
) ON [PRIMARY]
GO
When I run this:
select sum (singles) "total_singles", sum(doubles) "total_doubles",
sum (triples) "total_triples", sum (hr) "total_hr"
from teamstats
where hits/ab >=.300;
I get this:
Server: Msg 409, Level 16, State 2, Line 1
The sum or average aggregate operation cannot take a varchar data type as an
argument.
Server: Msg 409, Level 16, State 1, Line 1
The sum or average aggregate operation cannot take a varchar data type as an
argument.
Server: Msg 409, Level 16, State 1, Line 1
The sum or average aggregate operation cannot take a varchar data type as an
argument.The error that you receive is a pretty good pointer to the problem.
"The sum or average aggregate operation cannot take a varchar data type =
as an argument."
You cannot perform a SUM on a varchar data type. You need to convert =
the values to int before performing the SUM. This will work as long as =
you have numeric data stored within singles, doubles, and triples.
select sum (CONVERT(int,singles)) AS total_singles, =
sum(CONVERT(int,doubles)) AS total_doubles,
sum (CONVERT(int,triples)) AS total_triples, sum (hr) AS total_hr
from teamstats
where hits/ab >=3D.300
--=20
Keith
"Rmsands" <anonymous@.discussions.microsoft.com> wrote in message =
news:4B521924-DBB1-4262-B1E7-0988FB2F3632@.microsoft.com...
> Here is the table:
>=20
> CREATE TABLE [teamstats] (
> [name] [varchar] (10) NOT NULL ,
> [pos] [varchar] (3) NOT NULL ,
> [ab] [numeric](3, 0) NOT NULL ,
> [hits] [numeric](4, 0) NOT NULL ,
> [walks] [varchar] (5) NOT NULL ,
> [singles] [varchar] (7) NOT NULL ,
> [doubles] [varchar] (7) NOT NULL ,
> [triples] [varchar] (7) NOT NULL ,
> [hr] [numeric](2, 0) NOT NULL ,
> [so] [varchar] (2) NOT NULL=20
> ) ON [PRIMARY]
> GO
>=20
>=20
> When I run this:
>=20
> select sum (singles) "total_singles", sum(doubles) "total_doubles",
> sum (triples) "total_triples", sum (hr) "total_hr"
> from teamstats
> where hits/ab >=3D.300;
>=20
> I get this:
>=20
> Server: Msg 409, Level 16, State 2, Line 1
> The sum or average aggregate operation cannot take a varchar data type =
as an argument.
> Server: Msg 409, Level 16, State 1, Line 1
> The sum or average aggregate operation cannot take a varchar data type =
as an argument.
> Server: Msg 409, Level 16, State 1, Line 1
> The sum or average aggregate operation cannot take a varchar data type =
as an argument.
>

query

hi,

i want to achive the IIF functionality.

example

select

iff(employee.empid is not null,'sdfsdf',iif(employee.deptid is not null,'dssdf','sdfsd')) as 'sdsd'

from employee

how to solve this.

thank you

Hi,

did not understand what you wants to achieve! You may use if and try...catch block or you may use case statements too.

Refer CASE and try...catch for more in BOL.

Regards

Heamntgiri S. Goswami

|||

IFF is not a part of TSQL syntax as far as i know...though this function is provided in sql server 2005 reporting services (thats the place i used it)...

finctionality of IFF is

IFF(mycondition , ValueWhenMyConditionTrue, ValueWhenMyConditionFalse)

IFF can also be nested...so for ValueWhenMyConditionFalse u can write another IFF...as is done in ur case...i hope u'll be able to 'decode' ur actual code now...

|||You can use an IF in SQL.

Its normally shown as

IF EXISTS (SELECT * FROM Orders WHERE OrderNo = @.OrderNo) THEN|||Also you use NotIsNull(Expr1, Expr2)
|||

IIF is not available in T-SQL.

But you can use Case When,

Case When employee.empid is NOT NULL Then 'sdfsdf' Else (Case When employee.deptid is not null Then 'dssdf' Else 'sdfsd' End) End

query

damn touchpads......
I have a query:
SELECT id, name, rank, serial_number from status
this will return many rows...if all values are null for a single row, i need
to be able to assign a unique "id" for that row (PK constraint on another
table) is there a way to do this so that for each row that has NULL values
across all columns. This way I can populate with an incrementing or otherwis
e
unique value (using d1, d2, d3... here)
id name rank serial_number
--
d1 null null null
d2 null null null
34r bill 0-6 4420
d3 null null null
d4 null null null
d5....
TIA!You can try using the following:
NOTE: This recreates the status table (truncate + re-populate)
-- BEGIN SCRIPT
create table status
(id varchar(10),
[name] varchar(10),
rank varchar(10),
serial_number varchar(10))
insert into status
values (null,null,null,null)
insert into status
values ('34r','bill','0-6','4420')
insert into status
values (null,null,null,null)
insert into status
values (null,null,null,null)
insert into status
values (null,null,null,null)
create table #status
(RowID int identity (1,1),
id varchar(10),
[name] varchar(10),
rank varchar(10),
serial_number varchar(10))
create table #status2
(RowID int identity (1,1),
id varchar(10),
[name] varchar(10),
rank varchar(10),
serial_number varchar(10))
insert into #status
select * from status
insert into #status2
select * from status
update #status
set id = 'd'+convert(varchar(10), s.RowID)
from #status s, #status2 s2
where s.RowID = s2.RowID
and s.id is null
truncate table status
insert into status
SELECT id, name, rank, serial_number from #status
select * from status
drop table #status
drop table #status2
Hope it helps
"JMNUSS" wrote:

> damn touchpads......
> I have a query:
> SELECT id, name, rank, serial_number from status
> this will return many rows...if all values are null for a single row, i ne
ed
> to be able to assign a unique "id" for that row (PK constraint on another
> table) is there a way to do this so that for each row that has NULL values
> across all columns. This way I can populate with an incrementing or otherw
ise
> unique value (using d1, d2, d3... here)
> id name rank serial_number
> --
> d1 null null null
> d2 null null null
> 34r bill 0-6 4420
> d3 null null null
> d4 null null null
> d5....
> TIA!
>

query

create table a
(
i varchar(10),
j varchar(11)
)
create table b
(
n varchar(10),
m varchar(11)
)
insert into a values(null, '1')
insert into a values(null, '2')
insert into b values(null, '3')
insert into b values(null, '4')
insert into a values('val1', '5')
insert into a values('val2', '6')
insert into b values('val3', '7')
insert into b values('val4', '8')
select * From a
inner join b on a.i = b.n
and a.i is null and b.n is null
hi i want to join table a and b and return the results
null, 1
null, 2
null, 3
null, 4
but i dont get this output. how do i do that?
thnx
ICHORThe query below should return the result you are after:
SELECT i, j
FROM a
WHERE i IS NULL
UNION
SELECT n, m
FROM b
WHERE n IS NULL
When you perform an INNER JOIN you only return the rows that satisfiy the
join of the first input with the second input. As a NULL is an unknown valu
e
NULL does not equal NULL hence the NULL records do not satisfy the join and
are not returned.
- Peter Ward
WARDY IT Solutions
"ichor" wrote:

> create table a
> (
> i varchar(10),
> j varchar(11)
> )
>
> create table b
> (
> n varchar(10),
> m varchar(11)
> )
> insert into a values(null, '1')
> insert into a values(null, '2')
> insert into b values(null, '3')
> insert into b values(null, '4')
>
> insert into a values('val1', '5')
> insert into a values('val2', '6')
> insert into b values('val3', '7')
> insert into b values('val4', '8')
>
> select * From a
> inner join b on a.i = b.n
> and a.i is null and b.n is null
>
> hi i want to join table a and b and return the results
>
> null, 1
> null, 2
> null, 3
> null, 4
> but i dont get this output. how do i do that?
> thnx
> ICHOR
>
>

QUERY

Using sql 2000 here is my table, query, and error
Table
create table projec
(task varchar(14) not null
startdate datetime
enddate datetime)
Query
SELECT TASK "TASKS_SHORTER_THAN_ONE_MONTH
FROM PROJEC
WHERE ADD_MONTHS (STARTDATE,1) > ENDDATE
Error
Server: Msg 195, Level 15, State 10, Line
'ADD_MONTHS' is not a recognized function nameI think you want this
SELECT TASK as 'TASKS_SHORTER_THAN_ONE_MONTH'
FROM PROJECT
WHERE datediff(m,STARTDATE, ENDDATE) < 1
of if you wanted days use
WHERE datediff(dd,STARTDATE, ENDDATE) < 30
Download BOL, it will help immensely -
http://www.microsoft.com/downloads/details.aspx?FamilyID=a6f79cb1-a420-445f-8a4b-bd77a7da194b&DisplayLang=en
HTH
--
Ray Higdon MCSE, MCDBA, CCNA
--
"RMSANDS" <anonymous@.discussions.microsoft.com> wrote in message
news:48E0A71C-7E83-47D6-8838-C4271F0EB6B8@.microsoft.com...
> Using sql 2000 here is my table, query, and error.
> Table:
> create table project
> (task varchar(14) not null,
> startdate datetime,
> enddate datetime);
>
> Query:
> SELECT TASK "TASKS_SHORTER_THAN_ONE_MONTH"
> FROM PROJECT
> WHERE ADD_MONTHS (STARTDATE,1) > ENDDATE;
> Error:
> Server: Msg 195, Level 15, State 10, Line 3
> 'ADD_MONTHS' is not a recognized function name.
>

QUERY

Using sql 2000 here is my table, query, and error.
Table:
create table project
(task varchar(14) not null,
startdate datetime,
enddate datetime);
Query:
SELECT TASK "TASKS_SHORTER_THAN_ONE_MONTH"
FROM PROJECT
WHERE ADD_MONTHS (STARTDATE,1) > ENDDATE;
Error:
Server: Msg 195, Level 15, State 10, Line 3
'ADD_MONTHS' is not a recognized function name.I think you want this
SELECT TASK as 'TASKS_SHORTER_THAN_ONE_MONTH'
FROM PROJECT
WHERE datediff(m,STARTDATE, ENDDATE) < 1
of if you wanted days use
WHERE datediff(dd,STARTDATE, ENDDATE) < 30
Download BOL, it will help immensely -
http://www.microsoft.com/downloads/...&DisplayLang=en
HTH
Ray Higdon MCSE, MCDBA, CCNA
--
"RMSANDS" <anonymous@.discussions.microsoft.com> wrote in message
news:48E0A71C-7E83-47D6-8838-C4271F0EB6B8@.microsoft.com...
> Using sql 2000 here is my table, query, and error.
> Table:
> create table project
> (task varchar(14) not null,
> startdate datetime,
> enddate datetime);
>
> Query:
> SELECT TASK "TASKS_SHORTER_THAN_ONE_MONTH"
> FROM PROJECT
> WHERE ADD_MONTHS (STARTDATE,1) > ENDDATE;
> Error:
> Server: Msg 195, Level 15, State 10, Line 3
> 'ADD_MONTHS' is not a recognized function name.
>

Querring only non NULL row/column

Hi,
I want to query few rows from a table. I don't want to get a row, where
certian column has a NULL value.
How can I do that?select * from table
where field is not null
"mavrick101" <mavrick101@.discussions.microsoft.com> wrote in message
news:0DAA7CB9-1BF4-4091-A7B0-FCA90581060E@.microsoft.com...
> Hi,
> I want to query few rows from a table. I don't want to get a row, where
> certian column has a NULL value.
> How can I do that?|||SELECT * FROM Table
WHERE column IS NOT NULL
"mavrick101" wrote:

> Hi,
> I want to query few rows from a table. I don't want to get a row, where
> certian column has a NULL value.
> How can I do that?|||SELECT ...
FROM YourTable
WHERE col_x IS NOT NULL
David Portas
SQL Server MVP
--

Queries are not seeing NULL Values in my DB

For some reason my Stored Procs are not recognizing NULL values within my database:
For example:
Select *
From Results
Where
(
home_Phone IS NULL
)
All the home_Phone vaules that are NULL are not being picked up by the query.
Any ideas are appriciated.
Thanks in advance everyone.
RB

Perhaps rather than NULL, the home_Phone is blank (a different thing). That is, rather than NULL, the field is an empty string.|||

Are you sure the values are NULL and not a blank?

Try where home_phone = '' to be sure.

Nick

|||All SQL Server Aggregate Functions ignore NULL except COUNT(*). Hope this helps.|||

Caddre wrote:

All SQL Server Aggregate Functions ignore NULL except COUNT(*).

I am confused. How is that query using an aggregate function?
|||I am not saying the query is using aggregate function I was just telling the user about NULL value behavior in SQL server. I should have explained in detail and did not intend to confuse the user.|||

Thanks everyone for your responses its working very well.

Regards

RB

Queried Parameter Null Values

Hello,

For queried parameters, I need to have the option of using a null value as well. Is there a way to include a null value, or do I have to include it in the query that is returned?

Thanks.

Use DBNull.Value in .NET as the parameter value.

If this does not help you, please clarify your question.

|||

I have a parameter that pulls back data to it. But, this field should be optional, so the user should also be able to select null as another value. How do I allow the user to select null? I can't figure this out... By the way, I tried setting the default value to System.DBNull.Value, and that didn't work... nothing returns, but I did try the query and the query did work. For some reason, this is with RS...

|||

What are you using to send the command to the database? An ObjectDataSource, custom ADO.NET, etc. If you can tell us what you're using, how the users are currently selecting values, etc, it would help.

|||

I'm using a Reporting Services DataSet, standard report... The query works fine, and it has been tested. I can't figure out how to send a null value to the stored procedure parameter. That is what the problem is.

|||

Hello,

Here is the answer that I've found: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1789337&SiteID=1

QRY question: If field1 is null then field2

Is there a way to create a column that will show the
value of field1 unless field1 is null in which case it
will show the value of field2? I do it all the time in
Access but can't seem to figure out if SQL Server can do
it as well. Any help or suggestions are geratly
appreciatd!
Here is how I do it in MS Access:
SELECT IIf([Field1] Is Null,[Field2],[Field1]) AS [Output]
FROM [Table];
Dan,
In SQL Server, you can write
CASE WHEN Field1 IS NULL THEN Field2 ELSE Field1 END AS [Output]
Since this particular need comes up often, there is a shorthand form:
COALESCE(Field1, Field2) AS [Output]
Steve Kass
Drew University
Dan wrote:

>Is there a way to create a column that will show the
>value of field1 unless field1 is null in which case it
>will show the value of field2? I do it all the time in
>Access but can't seem to figure out if SQL Server can do
>it as well. Any help or suggestions are geratly
>appreciatd!
>Here is how I do it in MS Access:
>SELECT IIf([Field1] Is Null,[Field2],[Field1]) AS [Output]
>FROM [Table];
>

Qns on SQL Express Database

Hi

Is it possible to blank/null out all the data in a specific table in the database, executed in a button click event?

What do you mean? Truncate? Delete? Update?

Probably truncate, as a table with all rows filled with blnaks/nulls make no sense to me.

|||

My situation:

I am writing a program to collect real live data like temperature. At the start of my program the tables in the database are blank. The collected real live data will go into the database tables. The user has the option of deleting all the data in the tables/table at one shot (not manually delete row by row) and start the collection of data afresh.

-

So, I think deleting all the data would be suitable. How to go about doing it? Will the number of rows of data affect the deleting process?

BTW, could help me out in my thread here?

Thanks.

|||

Transactions are logged in SQL Server, so the number of rows will defintely affect the performance. If you don′t want to log the deletions of the data you might use the TRUNCATE Statement for that. This is only possible if you don′t use any (or drop them first) Foreign key constraints. If you don′t want to drop the deletions you will have to delete the data in the relational order (first the child records then the parent records) if you did not specify any Cascading DMl operations.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

|||

Hi, am I looking at the correct syntax for the truncate statement?

http://msdn2.microsoft.com/en-us/library/ms177570.aspx

if yes, how do I use it? Don't understand what its trying to tell me. =(

|||Thats quite easy: TRUNCATE TABLE ownerorschema.TableName
and you should be done.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

|||May I know what is owner or schema?|||

Hi,

beginning with Sql Server 2005 the objects are stored in a schema. Schema can be owned by multiple owners. This is a switch in design between the "old" SQL Server 2k version. You might have a look in the BOL (Books Online - the help files of SQL Server) to see which actually changed. There is much information about it.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

QA - Debugger

How do I input a blank but not NULL value into a varchar parameter during
sproc debugging?
It seems that no matter I typed ('') / '' or leave it blank. It still not a
value that I wanted for the params.
LeonardP
Have you tried initializing the parameter to '' when it is declared? If no
value is provided, it uses the '' for it's value.
Ex: @.P1 varchar(10) = '', @.P2 varchar(5), etc...
"Leonard Poon" <leonardpoon@.hotmail.com> wrote in message
news:O$dgcLpTEHA.164@.TK2MSFTNGP12.phx.gbl...
> How do I input a blank but not NULL value into a varchar parameter during
> sproc debugging?
> It seems that no matter I typed ('') / '' or leave it blank. It still not
a
> value that I wanted for the params.
> LeonardP
>