Case sensitivity error!

Hi,
1] I have a domain user group 'Domain_name \my group' added into my SQL
Server.
2] When I execute the following code ..
if not exists (select * from master.dbo.syslogins where loginname = N'Domain_name\My Group')
exec sp_grantlogin N'Domain_name\My Group'
exec sp_defaultdb N'Domain_name\My Group', N'master'
exec sp_defaultlanguage N'Domain_name\My Group', N'us_english'
GO
I receive a error..
Error 15401
Windows NT user or group 'Domain_name\My Group not found.
Check the name again.
I just changed the code where ever *My Group* was there to *my group* then
the query was success.
I have gone through the article below but it dint answer my doubt.
http://support.microsoft.com/kb/q245768/
The server collation is Latin1_General_BIN
What is happening and what is the other way if I dont have to modify the code?
Thanks
ReddiYou have a binary collation (very unusual, btw) which is also case sensitive.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Reddi" <Reddi@.discussions.microsoft.com> wrote in message
news:D0ACD235-3F0D-4D56-BAA5-562E88C22939@.microsoft.com...
> Hi,
> 1] I have a domain user group 'Domain_name \my group' added into my SQL
> Server.
> 2] When I execute the following code ..
> if not exists (select * from master.dbo.syslogins where loginname => N'Domain_name\My Group')
> exec sp_grantlogin N'Domain_name\My Group'
> exec sp_defaultdb N'Domain_name\My Group', N'master'
> exec sp_defaultlanguage N'Domain_name\My Group', N'us_english'
> GO
> I receive a error..
> Error 15401
> Windows NT user or group 'Domain_name\My Group not found.
> Check the name again.
> I just changed the code where ever *My Group* was there to *my group* then
> the query was success.
> I have gone through the article below but it dint answer my doubt.
> http://support.microsoft.com/kb/q245768/
> The server collation is Latin1_General_BIN
> What is happening and what is the other way if I dont have to modify the code?
> Thanks
> Reddi
>|||> The server collation is Latin1_General_BIN
I'm not completely sure about this, but it looks as if the BIN collation
means that you're dealing with a SQLServer instance which is case sensitive.
It's rare that this is required, as Windows isn't case sensitive.
As for turning SQLServer from case sensitive to case insensitive, I've never
tried it. I'd be surprised if it were possible though...
Griff|||> As for turning SQLServer from case sensitive to case insensitive, I've never
> tried it. I'd be surprised if it were possible though...
You need to rebuild the system databases using rebuildm.exe, which means you lose all stuff in the
system databases. Also, this doesn't change the collation for the user databases.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Griff" <Howling@.The.Moon> wrote in message news:uiyxm0w8EHA.3988@.TK2MSFTNGP10.phx.gbl...
>> The server collation is Latin1_General_BIN
> I'm not completely sure about this, but it looks as if the BIN collation
> means that you're dealing with a SQLServer instance which is case sensitive.
> It's rare that this is required, as Windows isn't case sensitive.
> As for turning SQLServer from case sensitive to case insensitive, I've never
> tried it. I'd be surprised if it were possible though...
> Griff
>|||Hi Tibor,
Thanks for the response. Correct me if i am off track. If the issue is with
collaltion (case sensitivity), SQL server should not have allowed adding the
Windows NT group with wrong case in the first place as pointed by me in the
URL.
I have other server with same collation settings but it has Windows NT group
as Domain_name\My Group. I need not have to make any code change as what is
mentioned in code is matching with the group.
Thanks
Reddi
"Tibor Karaszi" wrote:
> > As for turning SQLServer from case sensitive to case insensitive, I've never
> > tried it. I'd be surprised if it were possible though...
> You need to rebuild the system databases using rebuildm.exe, which means you lose all stuff in the
> system databases. Also, this doesn't change the collation for the user databases.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> http://www.sqlug.se/
>
> "Griff" <Howling@.The.Moon> wrote in message news:uiyxm0w8EHA.3988@.TK2MSFTNGP10.phx.gbl...
> >> The server collation is Latin1_General_BIN
> >
> > I'm not completely sure about this, but it looks as if the BIN collation
> > means that you're dealing with a SQLServer instance which is case sensitive.
> >
> > It's rare that this is required, as Windows isn't case sensitive.
> >
> > As for turning SQLServer from case sensitive to case insensitive, I've never
> > tried it. I'd be surprised if it were possible though...
> >
> > Griff
> >
> >
>
>|||You could try rewriting your code to make it case-insensitive. How about
the following (not tested at all):
DECLARE @.ln nvarchar(100)
select @.ln = loginname from master.dbo.syslogins where loginname COLLATE
SQL_Latin1_General_CP1_CI_AS = N'Domain_name\My Group'
if not @.ln is null
BEGIN
exec sp_grantlogin @.ln
exec sp_defaultdb @.ln, N'master'
exec sp_defaultlanguage @.ln, N'us_english'
END
Note that I added BEGIN and END because in the original the second and third
lines were being executed unconditionally.
HTH,
Mike
"Reddi" <Reddi@.discussions.microsoft.com> wrote in message
news:D0ACD235-3F0D-4D56-BAA5-562E88C22939@.microsoft.com...
> Hi,
> 1] I have a domain user group 'Domain_name \my group' added into my SQL
> Server.
> 2] When I execute the following code ..
> if not exists (select * from master.dbo.syslogins where loginname => N'Domain_name\My Group')
> exec sp_grantlogin N'Domain_name\My Group'
> exec sp_defaultdb N'Domain_name\My Group', N'master'
> exec sp_defaultlanguage N'Domain_name\My Group', N'us_english'
> GO
> I receive a error..
> Error 15401
> Windows NT user or group 'Domain_name\My Group not found.
> Check the name again.
> I just changed the code where ever *My Group* was there to *my group* then
> the query was success.
> I have gone through the article below but it dint answer my doubt.
> http://support.microsoft.com/kb/q245768/
> The server collation is Latin1_General_BIN
> What is happening and what is the other way if I dont have to modify the
> code?
> Thanks
> Reddi
>

Case sensitivity error!

Hi,
1] I have a domain user group 'Domain_name \my group' added into my SQL
Server.
2] When I execute the following code ..
if not exists (select * from master.dbo.syslogins where loginname =
N'Domain_name\My Group')
exec sp_grantlogin N'Domain_name\My Group'
exec sp_defaultdb N'Domain_name\My Group', N'master'
exec sp_defaultlanguage N'Domain_name\My Group', N'us_english'
GO
I receive a error..
Error 15401
Windows NT user or group 'Domain_name\My Group not found.
Check the name again.
I just changed the code where ever *My Group* was there to *my group* then
the query was success.
I have gone through the article below but it dint answer my doubt.
http://support.microsoft.com/kb/q245768/
The server collation is Latin1_General_BIN
What is happening and what is the other way if I dont have to modify the code?
Thanks
Reddi
You have a binary collation (very unusual, btw) which is also case sensitive.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Reddi" <Reddi@.discussions.microsoft.com> wrote in message
news:D0ACD235-3F0D-4D56-BAA5-562E88C22939@.microsoft.com...
> Hi,
> 1] I have a domain user group 'Domain_name \my group' added into my SQL
> Server.
> 2] When I execute the following code ..
> if not exists (select * from master.dbo.syslogins where loginname =
> N'Domain_name\My Group')
> exec sp_grantlogin N'Domain_name\My Group'
> exec sp_defaultdb N'Domain_name\My Group', N'master'
> exec sp_defaultlanguage N'Domain_name\My Group', N'us_english'
> GO
> I receive a error..
> Error 15401
> Windows NT user or group 'Domain_name\My Group not found.
> Check the name again.
> I just changed the code where ever *My Group* was there to *my group* then
> the query was success.
> I have gone through the article below but it dint answer my doubt.
> http://support.microsoft.com/kb/q245768/
> The server collation is Latin1_General_BIN
> What is happening and what is the other way if I dont have to modify the code?
> Thanks
> Reddi
>
|||> The server collation is Latin1_General_BIN
I'm not completely sure about this, but it looks as if the BIN collation
means that you're dealing with a SQLServer instance which is case sensitive.
It's rare that this is required, as Windows isn't case sensitive.
As for turning SQLServer from case sensitive to case insensitive, I've never
tried it. I'd be surprised if it were possible though...
Griff
|||> As for turning SQLServer from case sensitive to case insensitive, I've never
> tried it. I'd be surprised if it were possible though...
You need to rebuild the system databases using rebuildm.exe, which means you lose all stuff in the
system databases. Also, this doesn't change the collation for the user databases.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Griff" <Howling@.The.Moon> wrote in message news:uiyxm0w8EHA.3988@.TK2MSFTNGP10.phx.gbl...
> I'm not completely sure about this, but it looks as if the BIN collation
> means that you're dealing with a SQLServer instance which is case sensitive.
> It's rare that this is required, as Windows isn't case sensitive.
> As for turning SQLServer from case sensitive to case insensitive, I've never
> tried it. I'd be surprised if it were possible though...
> Griff
>
|||Hi Tibor,
Thanks for the response. Correct me if i am off track. If the issue is with
collaltion (case sensitivity), SQL server should not have allowed adding the
Windows NT group with wrong case in the first place as pointed by me in the
URL.
I have other server with same collation settings but it has Windows NT group
as Domain_name\My Group. I need not have to make any code change as what is
mentioned in code is matching with the group.
Thanks
Reddi
"Tibor Karaszi" wrote:

> You need to rebuild the system databases using rebuildm.exe, which means you lose all stuff in the
> system databases. Also, this doesn't change the collation for the user databases.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> http://www.sqlug.se/
>
> "Griff" <Howling@.The.Moon> wrote in message news:uiyxm0w8EHA.3988@.TK2MSFTNGP10.phx.gbl...
>
>
|||You could try rewriting your code to make it case-insensitive. How about
the following (not tested at all):
DECLARE @.ln nvarchar(100)
select @.ln = loginname from master.dbo.syslogins where loginname COLLATE
SQL_Latin1_General_CP1_CI_AS = N'Domain_name\My Group'
if not @.ln is null
BEGIN
exec sp_grantlogin @.ln
exec sp_defaultdb @.ln, N'master'
exec sp_defaultlanguage @.ln, N'us_english'
END
Note that I added BEGIN and END because in the original the second and third
lines were being executed unconditionally.
HTH,
Mike
"Reddi" <Reddi@.discussions.microsoft.com> wrote in message
news:D0ACD235-3F0D-4D56-BAA5-562E88C22939@.microsoft.com...
> Hi,
> 1] I have a domain user group 'Domain_name \my group' added into my SQL
> Server.
> 2] When I execute the following code ..
> if not exists (select * from master.dbo.syslogins where loginname =
> N'Domain_name\My Group')
> exec sp_grantlogin N'Domain_name\My Group'
> exec sp_defaultdb N'Domain_name\My Group', N'master'
> exec sp_defaultlanguage N'Domain_name\My Group', N'us_english'
> GO
> I receive a error..
> Error 15401
> Windows NT user or group 'Domain_name\My Group not found.
> Check the name again.
> I just changed the code where ever *My Group* was there to *my group* then
> the query was success.
> I have gone through the article below but it dint answer my doubt.
> http://support.microsoft.com/kb/q245768/
> The server collation is Latin1_General_BIN
> What is happening and what is the other way if I dont have to modify the
> code?
> Thanks
> Reddi
>

Case sensitivity error!

Hi,
1] I have a domain user group 'Domain_name \my group' added into my SQL
Server.
2] When I execute the following code ..
if not exists (select * from master.dbo.syslogins where loginname =
N'Domain_name\My Group')
exec sp_grantlogin N'Domain_name\My Group'
exec sp_defaultdb N'Domain_name\My Group', N'master'
exec sp_defaultlanguage N'Domain_name\My Group', N'us_english'
GO
I receive a error..
Error 15401
Windows NT user or group 'Domain_name\My Group not found.
Check the name again.
I just changed the code where ever *My Group* was there to *my group* then
the query was success.
I have gone through the article below but it dint answer my doubt.
http://support.microsoft.com/kb/q245768/
The server collation is Latin1_General_BIN
What is happening and what is the other way if I dont have to modify the cod
e?
Thanks
ReddiYou have a binary collation (very unusual, btw) which is also case sensitive
.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Reddi" <Reddi@.discussions.microsoft.com> wrote in message
news:D0ACD235-3F0D-4D56-BAA5-562E88C22939@.microsoft.com...
> Hi,
> 1] I have a domain user group 'Domain_name \my group' added into my SQL
> Server.
> 2] When I execute the following code ..
> if not exists (select * from master.dbo.syslogins where loginname =
> N'Domain_name\My Group')
> exec sp_grantlogin N'Domain_name\My Group'
> exec sp_defaultdb N'Domain_name\My Group', N'master'
> exec sp_defaultlanguage N'Domain_name\My Group', N'us_english'
> GO
> I receive a error..
> Error 15401
> Windows NT user or group 'Domain_name\My Group not found.
> Check the name again.
> I just changed the code where ever *My Group* was there to *my group* then
> the query was success.
> I have gone through the article below but it dint answer my doubt.
> http://support.microsoft.com/kb/q245768/
> The server collation is Latin1_General_BIN
> What is happening and what is the other way if I dont have to modify the c
ode?
> Thanks
> Reddi
>|||> The server collation is Latin1_General_BIN
I'm not completely sure about this, but it looks as if the BIN collation
means that you're dealing with a SQLServer instance which is case sensitive.
It's rare that this is required, as Windows isn't case sensitive.
As for turning SQLServer from case sensitive to case insensitive, I've never
tried it. I'd be surprised if it were possible though...
Griff|||> As for turning SQLServer from case sensitive to case insensitive, I've nevern">
> tried it. I'd be surprised if it were possible though...
You need to rebuild the system databases using rebuildm.exe, which means you
lose all stuff in the
system databases. Also, this doesn't change the collation for the user datab
ases.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Griff" <Howling@.The.Moon> wrote in message news:uiyxm0w8EHA.3988@.TK2MSFTNGP10.phx.gbl...[vb
col=seagreen]
> I'm not completely sure about this, but it looks as if the BIN collation
> means that you're dealing with a SQLServer instance which is case sensitiv
e.
> It's rare that this is required, as Windows isn't case sensitive.
> As for turning SQLServer from case sensitive to case insensitive, I've nev
er
> tried it. I'd be surprised if it were possible though...
> Griff
>[/vbcol]|||Hi Tibor,
Thanks for the response. Correct me if i am off track. If the issue is with
collaltion (case sensitivity), SQL server should not have allowed adding the
Windows NT group with wrong case in the first place as pointed by me in the
URL.
I have other server with same collation settings but it has Windows NT group
as Domain_name\My Group. I need not have to make any code change as what is
mentioned in code is matching with the group.
Thanks
Reddi
"Tibor Karaszi" wrote:

> You need to rebuild the system databases using rebuildm.exe, which means y
ou lose all stuff in the
> system databases. Also, this doesn't change the collation for the user dat
abases.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> http://www.sqlug.se/
>
> "Griff" <Howling@.The.Moon> wrote in message news:uiyxm0w8EHA.3988@.TK2MSFTN
GP10.phx.gbl...
>
>|||You could try rewriting your code to make it case-insensitive. How about
the following (not tested at all):
DECLARE @.ln nvarchar(100)
select @.ln = loginname from master.dbo.syslogins where loginname COLLATE
SQL_Latin1_General_CP1_CI_AS = N'Domain_name\My Group'
if not @.ln is null
BEGIN
exec sp_grantlogin @.ln
exec sp_defaultdb @.ln, N'master'
exec sp_defaultlanguage @.ln, N'us_english'
END
Note that I added BEGIN and END because in the original the second and third
lines were being executed unconditionally.
HTH,
Mike
"Reddi" <Reddi@.discussions.microsoft.com> wrote in message
news:D0ACD235-3F0D-4D56-BAA5-562E88C22939@.microsoft.com...
> Hi,
> 1] I have a domain user group 'Domain_name \my group' added into my SQL
> Server.
> 2] When I execute the following code ..
> if not exists (select * from master.dbo.syslogins where loginname =
> N'Domain_name\My Group')
> exec sp_grantlogin N'Domain_name\My Group'
> exec sp_defaultdb N'Domain_name\My Group', N'master'
> exec sp_defaultlanguage N'Domain_name\My Group', N'us_english'
> GO
> I receive a error..
> Error 15401
> Windows NT user or group 'Domain_name\My Group not found.
> Check the name again.
> I just changed the code where ever *My Group* was there to *my group* then
> the query was success.
> I have gone through the article below but it dint answer my doubt.
> http://support.microsoft.com/kb/q245768/
> The server collation is Latin1_General_BIN
> What is happening and what is the other way if I dont have to modify the
> code?
> Thanks
> Reddi
>

case and group by problem

Hi I have this code below it gives me the right results but there are load o
f
null values. Is there a way to group by on a case statement to elimiinate
these nulls to smarten up ther report
select ,c.name ,c.custno,c.salesno, case catno
when 'n' then sum(qtyshp* netprice) end as 'Cat-N', case catno
when 'L' then sum(qtyshp* netprice) end as 'Cat-L', case catno
when 'G' then sum(qtyshp* netprice) end as 'Cat-G', case catno
when 'I' then sum(qtyshp* netprice) end as 'Cat-I'
from ius_cust c
inner join ius_detail d
on d.custno=c.custno
inner join ius_prods p
on p.prod=d.prod
where invdate > '2005-01-01'
group by c.name,c.custno,c.salesno,p.catno
thanks for any help
Sammychange the case catno
when 'L' then sum(qtyshp* netprice) end as
to case catno
when 'L' then sum(qtyshp* netprice) else 0 end as
put else 0 in between
http://sqlservercode.blogspot.com/
Sammy wrote:
> Hi I have this code below it gives me the right results but there are load
of
> null values. Is there a way to group by on a case statement to elimiinate
> these nulls to smarten up ther report
> select ,c.name ,c.custno,c.salesno, case catno
> when 'n' then sum(qtyshp* netprice) end as 'Cat-N', case catno
> when 'L' then sum(qtyshp* netprice) end as 'Cat-L', case catno
> when 'G' then sum(qtyshp* netprice) end as 'Cat-G', case catno
> when 'I' then sum(qtyshp* netprice) end as 'Cat-I'
> from ius_cust c
> inner join ius_detail d
> on d.custno=c.custno
> inner join ius_prods p
> on p.prod=d.prod
> where invdate > '2005-01-01'
> group by c.name,c.custno,c.salesno,p.catno
>
> thanks for any help
> Sammy|||Sammy
Can you show us your table's structure?
create table #test
(
col1 int,
col2 int,
col3 char(1)
)
insert into #test values (1,10,'h')
insert into #test values (1,40,'h')
insert into #test values (1,20,'s')
insert into #test values (2,20,'h')
insert into #test values (2,10,'h')
insert into #test values (2,850,'a')
select col1,sum(case when col3='h' then col2 end),
sum(case when col3='h' then col2 end)
from #test
group by col1
"Sammy" <Sammy@.discussions.microsoft.com> wrote in message
news:CE89E103-FD29-4B44-A12B-D10C7D836A13@.microsoft.com...
> Hi I have this code below it gives me the right results but there are load
> of
> null values. Is there a way to group by on a case statement to elimiinate
> these nulls to smarten up ther report
> select ,c.name ,c.custno,c.salesno, case catno
> when 'n' then sum(qtyshp* netprice) end as 'Cat-N', case catno
> when 'L' then sum(qtyshp* netprice) end as 'Cat-L', case catno
> when 'G' then sum(qtyshp* netprice) end as 'Cat-G', case catno
> when 'I' then sum(qtyshp* netprice) end as 'Cat-I'
> from ius_cust c
> inner join ius_detail d
> on d.custno=c.custno
> inner join ius_prods p
> on p.prod=d.prod
> where invdate > '2005-01-01'
> group by c.name,c.custno,c.salesno,p.catno
>
> thanks for any help
> Sammy|||The nulls are from empty result sets, so try try this to get zero
amounts
SUM (CASE cat_no WHEN 'n'
THEN (qty_shp* net_price)
ELSE 0.00 END) AS cat-N
Do not put the alias names in single quotes since that is proprietary
syntax that will screw up other tools and portability.|||On Wed, 21 Dec 2005 08:23:03 -0800, Sammy wrote:

>Hi I have this code below it gives me the right results but there are load
of
>null values. Is there a way to group by on a case statement to elimiinate
>these nulls to smarten up ther report
>select ,c.name ,c.custno,c.salesno, case catno
>when 'n' then sum(qtyshp* netprice) end as 'Cat-N', case catno
>when 'L' then sum(qtyshp* netprice) end as 'Cat-L', case catno
>when 'G' then sum(qtyshp* netprice) end as 'Cat-G', case catno
>when 'I' then sum(qtyshp* netprice) end as 'Cat-I'
>from ius_cust c
>inner join ius_detail d
>on d.custno=c.custno
>inner join ius_prods p
>on p.prod=d.prod
>where invdate > '2005-01-01'
>group by c.name,c.custno,c.salesno,p.catno
Hi Sammy,
Try if this works:
SELECT c.name, c.custno, c.salesno,
SUM(CASE catno WHEN 'n' THEN qtyshp * netprice ELSE 0 END) AS
"Cat-N",
SUM(CASE catno WHEN 'L' THEN qtyshp * netprice ELSE 0 END) AS
"Cat-L",
SUM(CASE catno WHEN 'G' THEN qtyshp * netprice ELSE 0 END) AS
"Cat-G",
SUM(CASE catno WHEN 'I' THEN qtyshp * netprice ELSE 0 END) AS
"Cat-I"
FROM ius_cust AS c
INNER JOIN ius_detail AS d
ON d.custno = c.custno
INNER JOIN ius_prods AS p
ON p.prod = d.prod
WHERE invdate > '20050101' -- Note the recommended format
GROUP BY c.name, c.custno, c.salesno, p.catno
(untested - see www.aspfaq.com/5006 if you prefer a tested reply)
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||On 21 Dec 2005 08:33:07 -0800, --CELKO-- wrote:

>The nulls are from empty result sets, so try try this to get zero
>amounts
> SUM (CASE cat_no WHEN 'n'
> THEN (qty_shp* net_price)
> ELSE 0.00 END) AS cat-N
>Do not put the alias names in single quotes since that is proprietary
>syntax that will screw up other tools and portability.
Hi Joe,
The alternative you recommend is portable - it results in an error on
ALL platforms. ;->
Try enclosing it in double quotes: ... AS "cat-N"
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)

CASE alias in WHERE

Hello,

I found members of this group very helpful for my last queries.
Have one problem with CASE. I can use the column name alias in Order By Clause
but unable to use it in WHERE CLAUSE.
PLS TELL ME IF IT IS POSSIBLE TO USE IT IN WHERE CLAUSE AND SOME ALTERNATIVE.

QUERY:

SELECT
M.SECS =
CASE
WHEN NO_OF_SEC IS NULL THEN -1
WHEN NO_OF_SEC =0 THEN 1
ELSE NO_OF_SEC
END
FROM DOWNLOAD_MASTER M
WHERE M.SECS < 100
ORDER BY M.SECS

Hoping for a immediate reply.
thanks in advanceReferences to column aliases are only valid in the ORDER BY clause. You can
work around this by putting the expression into a derived table:

SELECT secs
FROM
(SELECT secs =
CASE
WHEN no_of_sec IS NULL THEN -1
WHEN no_of_sec = 0 THEN 1
ELSE no_of_sec
END
FROM DOWNLOAD_MASTER) AS M
WHERE secs < 100
ORDER BY secs

--
David Portas
SQL Server MVP
--|||On 5 Jul 2004 04:02:38 -0700, A.V.C. wrote:

>Hello,
>I found members of this group very helpful for my last queries.
>Have one problem with CASE. I can use the column name alias in Order By Clause
>but unable to use it in WHERE CLAUSE.
>PLS TELL ME IF IT IS POSSIBLE TO USE IT IN WHERE CLAUSE AND SOME ALTERNATIVE.
>QUERY:
>SELECT
>M.SECS =
>CASE
>WHEN NO_OF_SEC IS NULL THEN -1
>WHEN NO_OF_SEC =0 THEN 1
>ELSE NO_OF_SEC
>END
>FROM DOWNLOAD_MASTER M
>WHERE M.SECS < 100
>ORDER BY M.SECS
>
>Hoping for a immediate reply.
>thanks in advance

Hi A.V.C.,

Before answering your question, one remark about your query. I advise you
to remove "M." before "SECS". You are using an alias; not a column name.
The name "M.SECS" looks as if you're referring to a column named SECS in
the table named (or aliased) M. I expect the above query to throw an error
because column SECS can't be found in the table DOWNLOAD_MASTER. The order
by clause will probably not throw an error, but that is only because table
names (or aliases) are largely mostly disregarded by SQL Server when
evaluating an roder by clause.

To answer your question: no, this is not possible. To understand why, it
helps to know how an SQL query gets evaluated. Note that this is a
conceptual description; a good RDBMS will change the order of operation to
optimize; as long as the results remain the same that is not a problem.

Step 1: Evaluate FROM clause, build intermediate table from all rows in
the tables used, joined together on the conditions given. If old style
join syntax is used (with the ON conditions in the WHERE clause), this
step will yield the full carthesian product of all tables used.

Step 2: Evaluate WHERE clause, remove rows that don't match the criteria
from intermediate table.

Step 3: Evaluate GROUP BY clause, group rows together according to the
specified arguments.

Step 4: Evaluate HAVING clause, remove groups that don't match the
criteria from intermediate table.

Step 5: Evaluate SELECT clause, build result set to be returned from the
data in the intermediate table.

Step 6: Evaluate ORDER BY, perform sorting.

Officially, columns that are not included in the SELECT clause are not
available for sorting. Many products (like SQL Server) do allow this, but
it is a non-standard extension of the ISO/ANSI SQL-92 specification (and I
don't think that later SQL specifications included this).

Since the alias of a columns or expression is only effective from step 5
but the WHERE clause is evaluated as step 2, it is clear that an alias
can't be used in the WHERE clause.

You also ask for alternatives. In your case, you could try either "WHERE
COALESCE(NO_OF_SEC, -1) < 100" or "WHERE NO_OF_SEC < 100 OR NO_OF_SEC IS
NULL". In more complex cases, you might have to repeat the CASE expression
in the WHERE clause. If you dislike that redundancy, you can always use
the derived table approach. For your query, the equivalent with a derived
table would look like this:

SELECT SECS
FROM (SELECT SECS = CASE
WHEN NO_OF_SEC IS NULL THEN -1
WHEN NO_OF_SEC = 0 THEN 1
ELSE NO_OF_SEC
END
FROM DOWNLOAD_MASTER) AS D
WHERE SECS < 100
ORDER BY SECS
(untested)

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)|||Thank you David Portas and Hugo Kornelis

I agree with the point(M.) mentioned by Hugo Kornelis.
I appreciate your way of writing descriptive answers.

Thanks once again.

cascade combo boxes

I follow sql coding for cascading combo boxes that populates them
if the first one populates the cdname the second one should populate the cd group and the third one the composers with the songs or the samthing with music hymnals. I am trying the steps they aren't populating. Where are simple books on this?

mikevds@.optonline.netI follow sql coding for cascading combo boxes that populates them
if the first one populates the cdname the second one should populate the cd group and the third one the composers with the songs or the samthing with music hymnals. I am trying the steps they aren't populating. Where are simple books on this?

mikevds@.optonline.net

what language are you using??

CASCADE !

I am creating two tables category_group & category.
category references category_group.
When a category group is deleted i want to delete all associated categories
also.
Is what i have below the correct way to do this?
/* Tables to manage categories; Applies to users and events*/
CREATE TABLE category_group
(category_group_id INT IDENTITY(1,1) PRIMARY KEY CLUSTERED,
category_group_name VARCHAR(30))
CREATE TABLE category
(category_id INT IDENTITY(1,1) PRIMARY KEY CLUSTERED,
category_name VARCHAR(30),
category_group INT NOT NULL REFERENCES category_group(category_group_id)
ON DELETE CASCADE)
Help Appreciated!!
AJ
Hi
Defining a cascading Foreign Key (as you have) will do this for you. If you
want to know more see
the subject "Creating and Modifying FOREIGN KEY Constraints" in Books
online.
I prefer to name the keys so my DDL and example data would be like:
CREATE TABLE category_group
(category_group_id INT IDENTITY(1,1) CONSTRAINT PK_Category_Group PRIMARY
KEY CLUSTERED,
category_group_name VARCHAR(30))
CREATE TABLE category
(category_id INT IDENTITY(1,1) CONSTRAINT PK_Category PRIMARY KEY
CLUSTERED,
category_name VARCHAR(30),
category_group INT NOT NULL CONSTRAINT FK_Category_Category_Group
REFERENCES category_group(category_group_id)
ON DELETE CASCADE)
INSERT INTO Category_group (category_group_name) VAlUES ( 'Group 1' )
INSERT INTO Category_group (category_group_name) VAlUES ( 'Group 2' )
INSERT INTO Category_group (category_group_name) VAlUES ( 'Group 3' )
INSERT INTO category ( category_name, category_group)
VALUES ( 'Cat 1' , 1 )
INSERT INTO category ( category_name, category_group)
VALUES ( 'Cat 2' , 2 )
INSERT INTO category ( category_name, category_group)
VALUES ( 'Cat 3' , 3 )
INSERT INTO category ( category_name, category_group)
VALUES ( 'Cat 4' , 2 )
INSERT INTO category ( category_name, category_group)
VALUES ( 'Cat 5' , 1 )
BEGIN TRANSACTION
SELECT * FROM Category_Group
SELECT * FROM Category
DELETE FROM Category_Group WHERE category_group_id = 1
SELECT * FROM Category_Group
SELECT * FROM Category
DELETE FROM Category_Group WHERE category_group_id = 2
SELECT * FROM Category_Group
SELECT * FROM Category
ROLLBACK TRANSACTION
John
CREATE TABLE category
> (category_id INT IDENTITY(1,1) PRIMARY KEY CLUSTERED,
> category_name VARCHAR(30),
> category_group INT NOT NULL REFERENCES
category_group(category_group_id)
> ON DELETE CASCADE)
"Anthony Judd" <adam.jknight@.optusnet.com.au> wrote in message
news:uYAHCV4oEHA.3876@.TK2MSFTNGP15.phx.gbl...
> I am creating two tables category_group & category.
> category references category_group.
> When a category group is deleted i want to delete all associated
categories
> also.
> Is what i have below the correct way to do this?
>
> /* Tables to manage categories; Applies to users and events*/
> CREATE TABLE category_group
> (category_group_id INT IDENTITY(1,1) PRIMARY KEY CLUSTERED,
> category_group_name VARCHAR(30))
> CREATE TABLE category
> (category_id INT IDENTITY(1,1) PRIMARY KEY CLUSTERED,
> category_name VARCHAR(30),
> category_group INT NOT NULL REFERENCES
category_group(category_group_id)
> ON DELETE CASCADE)
> Help Appreciated!!
> AJ
>
|||In addition to John's reply, don't forget to declare proper keys. You have
nullable columns and no natural key in either table:
CREATE TABLE category_group
(category_group_id INT IDENTITY(1,1) PRIMARY KEY CLUSTERED,
category_group_name VARCHAR(30) NOT NULL UNIQUE)
CREATE TABLE category
(category_id INT IDENTITY(1,1) PRIMARY KEY CLUSTERED,
category_name VARCHAR(30) NOT NULL UNIQUE,
category_group INT NOT NULL REFERENCES category_group(category_group_id)
ON DELETE CASCADE)
David Portas
SQL Server MVP

CASCADE !

I am creating two tables category_group & category.
category references category_group.
When a category group is deleted i want to delete all associated categories
also.
Is what i have below the correct way to do this?
/* Tables to manage categories; Applies to users and events*/
CREATE TABLE category_group
(category_group_id INT IDENTITY(1,1) PRIMARY KEY CLUSTERED,
category_group_name VARCHAR(30))
CREATE TABLE category
(category_id INT IDENTITY(1,1) PRIMARY KEY CLUSTERED,
category_name VARCHAR(30),
category_group INT NOT NULL REFERENCES category_group(category_group_id)
ON DELETE CASCADE)
Help Appreciated!!
AJHi
Defining a cascading Foreign Key (as you have) will do this for you. If you
want to know more see
the subject "Creating and Modifying FOREIGN KEY Constraints" in Books
online.
I prefer to name the keys so my DDL and example data would be like:
CREATE TABLE category_group
(category_group_id INT IDENTITY(1,1) CONSTRAINT PK_Category_Group PRIMARY
KEY CLUSTERED,
category_group_name VARCHAR(30))
CREATE TABLE category
(category_id INT IDENTITY(1,1) CONSTRAINT PK_Category PRIMARY KEY
CLUSTERED,
category_name VARCHAR(30),
category_group INT NOT NULL CONSTRAINT FK_Category_Category_Group
REFERENCES category_group(category_group_id)
ON DELETE CASCADE)
INSERT INTO Category_group (category_group_name) VAlUES ( 'Group 1' )
INSERT INTO Category_group (category_group_name) VAlUES ( 'Group 2' )
INSERT INTO Category_group (category_group_name) VAlUES ( 'Group 3' )
INSERT INTO category ( category_name, category_group)
VALUES ( 'Cat 1' , 1 )
INSERT INTO category ( category_name, category_group)
VALUES ( 'Cat 2' , 2 )
INSERT INTO category ( category_name, category_group)
VALUES ( 'Cat 3' , 3 )
INSERT INTO category ( category_name, category_group)
VALUES ( 'Cat 4' , 2 )
INSERT INTO category ( category_name, category_group)
VALUES ( 'Cat 5' , 1 )
BEGIN TRANSACTION
SELECT * FROM Category_Group
SELECT * FROM Category
DELETE FROM Category_Group WHERE category_group_id = 1
SELECT * FROM Category_Group
SELECT * FROM Category
DELETE FROM Category_Group WHERE category_group_id = 2
SELECT * FROM Category_Group
SELECT * FROM Category
ROLLBACK TRANSACTION
John
CREATE TABLE category
> (category_id INT IDENTITY(1,1) PRIMARY KEY CLUSTERED,
> category_name VARCHAR(30),
> category_group INT NOT NULL REFERENCES
category_group(category_group_id)
> ON DELETE CASCADE)
"Anthony Judd" <adam.jknight@.optusnet.com.au> wrote in message
news:uYAHCV4oEHA.3876@.TK2MSFTNGP15.phx.gbl...
> I am creating two tables category_group & category.
> category references category_group.
> When a category group is deleted i want to delete all associated
categories
> also.
> Is what i have below the correct way to do this?
>
> /* Tables to manage categories; Applies to users and events*/
> CREATE TABLE category_group
> (category_group_id INT IDENTITY(1,1) PRIMARY KEY CLUSTERED,
> category_group_name VARCHAR(30))
> CREATE TABLE category
> (category_id INT IDENTITY(1,1) PRIMARY KEY CLUSTERED,
> category_name VARCHAR(30),
> category_group INT NOT NULL REFERENCES
category_group(category_group_id)
> ON DELETE CASCADE)
> Help Appreciated!!
> AJ
>|||In addition to John's reply, don't forget to declare proper keys. You have
nullable columns and no natural key in either table:
CREATE TABLE category_group
(category_group_id INT IDENTITY(1,1) PRIMARY KEY CLUSTERED,
category_group_name VARCHAR(30) NOT NULL UNIQUE)
CREATE TABLE category
(category_id INT IDENTITY(1,1) PRIMARY KEY CLUSTERED,
category_name VARCHAR(30) NOT NULL UNIQUE,
category_group INT NOT NULL REFERENCES category_group(category_group_id)
ON DELETE CASCADE)
--
David Portas
SQL Server MVP
--

Capture Grouping Values

I have two groupings defined in my table. I group by Owner and then by Priority. I'm struggling with the expression to capture the string values at the appropriate scope. Using the table below, I want to capture "Critical, High" for "Jack Daniels" and "Medium, Low, Informational" for "Jim Beam". The table has "grpOwner" and "grpPriority" defined on Fields!Owner.Value and Fields!Priority.Value respectively. Any help would be greatly appreciated!

Owner

Priority

Jack Daniels

Critical

High

Jim Beam

Medium

Low

Informational

You should be able to concatenate the values in code as explained here.|||I opened a ticket with MSFT and they recommended creating a custom assembly with a function that retrieves the values based on the grouping level.

Capacity plannaing (Was Re: Algorithm Question)

[Cross-posting to a more relevant group with title changed.]
Peter Kim
This posting is provided "AS IS" with no warranties, and confers no rights.
"Poch Reyes" <pochreyes@.hotmail.com> wrote in message
news:e6S0%23oz1DHA.2480@.TK2MSFTNGP10.phx.gbl...

quote:

---

> Hi Peter,
> Thanks for this info. We have a client here in the Philippines. Can you
> direct me to good SQL Server data mining sites outside of the MSFT site?
> Also, I would also like to ask for your comments on capacity planning
> against current hardware solution of our client. Please see below the
> details:
> System Capacity Requirements
> - 1 million transactions per day
> - Daily refresh of reports, reports available by 9am every day
> - Keep up to 2 years of historical data
> - Recommendation system should be able to process a minimum of 150
> transactions per second
> Solution should run on the following hardware/software platform
> - 2 x Dual Processor Intel PIII 1.13GHz Servers with
> - 512 MB Memory
> - 500 GB Shared Storage
> - Windows 2003 Server
> What do you think, will these type of server with specs indicated hold?
> Thanks,
> Poch
> "Peter Kim [MS]" <peterkim@.online.microsoft.com> wrote in message
> news:e8DUXet1DHA.2000@.TK2MSFTNGP11.phx.gbl...
> need
the[QUOTE]
cases[QUOTE]
> calculated
may[QUOTE]
more.[QUOTE]
> rights.
used[QUOTE]
> probability,
> Confidence
>

---

I think the current question is about the capacity issue outlined below?
Are the server specs good enough? It really depends on how taxing those 1
million per day transaction are... are they expensive or cheap statments?
It's reasonable to think a dual box as described below could handle the
job. But there's no way to know without better understanding the transaction
mix... also depends on how well tuned (indexes, etc...) the db is...
Brian Moran
Principal Mentor
Solid Quality Learning
SQL Server MVP
http://www.solidqualitylearning.com
"Peter Kim [MS]" <peterkim@.online.microsoft.com> wrote in message
news:%23pDhO8g2DHA.1720@.TK2MSFTNGP10.phx.gbl...

quote:

---

> [Cross-posting to a more relevant group with title changed.]
> --
> Peter Kim
> This posting is provided "AS IS" with no warranties, and confers no

---

rights.

quote:

---

> "Poch Reyes" <pochreyes@.hotmail.com> wrote in message
> news:e6S0%23oz1DHA.2480@.TK2MSFTNGP10.phx.gbl...
if[QUOTE]
but[QUOTE]
> the
> cases
> may
> more.
to[QUOTE]
the[QUOTE]
> used
>

---