Query a dynamic table...HELP!

Hello,

I am trying to write a query to pull data from a table that is system generated daily at midnight. I tried:

declare @.tablename varchar(50) set @.tablename = (select name from sysobjects where crdate = '05/01/2003') select * from @.tablename

But I get an error on 'select * from @.tablename' (must declare @.tablename)

Any assistance is GREATLY APPRECIATED!!!!Please check the answer in

http://forums.databasejournal.com/showthread.php?threadid=31446

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

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.
>

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
>

q; varchar(MAX)

varchar(MAX)
Is there any advantage/disadvantage using varchar(MAX) instead of
varchar(1000)Jim,
No technical disadvantage, but it makes your intent less clear. Datatypes
should ideally indicate the intent of the data. (Rather like someone just
making everything VARCHAR, rather than INT, DATETIME, etc.)
Of course, if you actually go beyond the 8K page boundary in the amount of
data, then there is a natural impact on performance for the extra I/O
involved with the large datatype.
RLF
"JIM.H." <JIMH@.discussions.microsoft.com> wrote in message
news:910C5A5C-7376-4EB1-BF67-7ECE7C26E10E@.microsoft.com...
> varchar(MAX)
> Is there any advantage/disadvantage using varchar(MAX) instead of
> varchar(1000)
>|||On Fri, 28 Sep 2007 04:36:00 -0700, JIM.H.
<JIMH@.discussions.microsoft.com> wrote:
>varchar(MAX)
>Is there any advantage/disadvantage using varchar(MAX) instead of
>varchar(1000)
VARCHAR(MAX) is a wonderful new feature when used correctly.
If your data should not be longer than 1000 characters, and your
programs and reports are written to only handle 1000 characters, then
the major disadvantage to varchar(max) is that you could end up with
data in the table that the code can not handle. There is also some
overhead to handling varchar(max) that is avoided with varchar(1000),
though not much if you keep the data short enough that the (max) was
not needed.
And using varchar(max) when it is not needed is just plain sloppy. To
me it says someone couldn't be bothered to set the length correctly.
The next person who comes along may not realize that there is an
implicit limit. Or they will know there must be one, but not be able
to find what it is.
Roy Harvey
Beacon Falls, CT|||In addition to the other responses, there is an advantage to keep the
defined size below 800 bytes, because values of more than 800 bytes
cannot be indexed or grouped.
--
Gert-Jan
JIM.H. wrote:
> varchar(MAX)
> Is there any advantage/disadvantage using varchar(MAX) instead of
> varchar(1000)