alias for variable [memory] table?

i have this sql Q:
a delete statement not allowed to use with "as" (delete from table as t
where..),
so when i need "as" i do it in sub q, like this:
delete from customers where 3<(select count(*) from customer t where
t.date=customer.date)
but when table in memory table, I got err:
declare @.t table([id] int, [date] smalldatetime)
delete from @.t where 3<(select count(*) from @.t t where @.t.date=t.date)
I got err:Must declare the scalar variable "@.t"
have a solution for like situations?Indeed, you need to use an alias. Try something like this:
declare @.t table([id] int, [date] smalldatetime)
delete a from @.t a where 3<(select count(*) from @.t t where
a.date=t.date)
Razvan|||Your attempt at inventing syntax makes no sense in terms of the SQL
language model. An alias is supposed to act as it materializes a new
working table with the data from the original table expression in it.
To be consistent, this syntax says that you have done nothing to the
base table.
The next question is why would you use that proprietary in memory table
in the first place? It looks like you are mimicking a scratch tape in a
1950's file system instead of writing SQL. But you did not post enough
for anyone to give you a relatioanl solution.|||Use an alias, but use it where it belongs:
delete <alias>
from <table> [as] <alias>
where <condition>
ML
http://milambda.blogspot.com/|||Here is another way, without aliases:
DELETE @.t WHERE date IN (
SELECT date FROM @.t
GROUP BY date
HAVING COUNT(*)>3
)
Razvan
PS. I hope your real columns have better names...|||to --CELKO--
I realy interest you, but i not understand at all, please explain your
approach!!
for anyone to give you a relatioanl solution.
my example is very clear, i need to delete from this table rec that
appear more
then 3 times. did you have better way?sql

Aggregate returns null - sub value

I have something like the following:
select count(mycolumn) from mytable where...
This works great. The problem is that sometimes, count(mycolumn) returns
null because mycolumn contains no values. In this case, I'd really rather
show the user a '0' rather than the word null.
Any suggestions on how to show the user a 0 (zero) when count returns a null
?
Thanks
--
RandyTry:
select isnull (count(mycolumn), 0)
from mytable where...
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
.
"randy1200" <randy1200@.newsgroups.nospam> wrote in message
news:E73AE804-52AF-4C79-ACEE-59385838204D@.microsoft.com...
I have something like the following:
select count(mycolumn) from mytable where...
This works great. The problem is that sometimes, count(mycolumn) returns
null because mycolumn contains no values. In this case, I'd really rather
show the user a '0' rather than the word null.
Any suggestions on how to show the user a 0 (zero) when count returns a
null?
Thanks
--
Randy|||Try,
select isnull(count(mycolumn), 0) from mytable where ...
go
AMB
"randy1200" wrote:

> I have something like the following:
> select count(mycolumn) from mytable where...
> This works great. The problem is that sometimes, count(mycolumn) returns
> null because mycolumn contains no values. In this case, I'd really rather
> show the user a '0' rather than the word null.
> Any suggestions on how to show the user a 0 (zero) when count returns a nu
ll?
> Thanks
> --
> Randy|||Exactly what I needed. Many thanks
--
Randy
"Tom Moreau" wrote:

> Try:
> select isnull (count(mycolumn), 0)
> from mytable where...
>
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Toronto, ON Canada
> ..
> "randy1200" <randy1200@.newsgroups.nospam> wrote in message
> news:E73AE804-52AF-4C79-ACEE-59385838204D@.microsoft.com...
> I have something like the following:
> select count(mycolumn) from mytable where...
> This works great. The problem is that sometimes, count(mycolumn) returns
> null because mycolumn contains no values. In this case, I'd really rather
> show the user a '0' rather than the word null.
> Any suggestions on how to show the user a 0 (zero) when count returns a
> null?
> Thanks
> --
> Randy
>|||select count(mycolumn) from mytable where mycolumn is not null
?
"randy1200" <randy1200@.newsgroups.nospam> wrote in message
news:E73AE804-52AF-4C79-ACEE-59385838204D@.microsoft.com...
> I have something like the following:
> select count(mycolumn) from mytable where...
> This works great. The problem is that sometimes, count(mycolumn) returns
> null because mycolumn contains no values. In this case, I'd really rather
> show the user a '0' rather than the word null.
> Any suggestions on how to show the user a 0 (zero) when count returns a
null?
> Thanks
> --
> Randy