Help with a query

I'm not sure if this is the right place to post this message. I
didn't see a sqlserver.sql newsgroup.
Anyway, we are porting our site from using MySQL to using
SQLServer 2000 and we've run across a problem. In MySQL,
you can have a query that looks like this (using Northwind DB
as an example as that is ubiquitous):
USE [Northwind];
SELECT
ProductName,
IF( UnitPrice < 20, 'Cheap', 'Expensive' ) AS ExpenseLevel
FROM
Products;
Such that it would print out the product name in one column
and either the word 'Cheap' or 'Expensive' on the other column
depending on the unitprice. This doesn't seem to work in
SQLServer 2000 as it's giving me a syntax error at the IF. Is
this possible using SQLServer? If so, how so?
thnx,
ChristophOn Thu, 19 Aug 2004 08:37:50 -0500, Christoph Boget wrote:
>I'm not sure if this is the right place to post this message. I
>didn't see a sqlserver.sql newsgroup.
>Anyway, we are porting our site from using MySQL to using
>SQLServer 2000 and we've run across a problem. In MySQL,
>you can have a query that looks like this (using Northwind DB
>as an example as that is ubiquitous):
>USE [Northwind];
>SELECT
> ProductName,
> IF( UnitPrice < 20, 'Cheap', 'Expensive' ) AS ExpenseLevel
>FROM
> Products;
>Such that it would print out the product name in one column
>and either the word 'Cheap' or 'Expensive' on the other column
>depending on the unitprice. This doesn't seem to work in
>SQLServer 2000 as it's giving me a syntax error at the IF. Is
>this possible using SQLServer? If so, how so?
>thnx,
>Christoph
>
Hi Christoph,
Check out CASE in Books Online. Your example would read:
SELECT ProductName,
CASE
WHEN UnitPrice < 20
THEN 'Cheap'
ELSE 'Expensive'
END AS ExpenseLevel
FROM Products
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Yep:
USE [Northwind];
SELECT
ProductName,
CASE WHEN UnitPrice < 20 THEN 'Cheap' ELSE 'Expensive' END AS ExpenseLevel
FROM
BTW, there is a .programming newsgroup.
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Christoph Boget" <jcboget@.yahoo.com> wrote in message
news:uylq2GfhEHA.4064@.TK2MSFTNGP12.phx.gbl...
I'm not sure if this is the right place to post this message. I
didn't see a sqlserver.sql newsgroup.
Anyway, we are porting our site from using MySQL to using
SQLServer 2000 and we've run across a problem. In MySQL,
you can have a query that looks like this (using Northwind DB
as an example as that is ubiquitous):
USE [Northwind];
SELECT
ProductName,
IF( UnitPrice < 20, 'Cheap', 'Expensive' ) AS ExpenseLevel
FROM
Products;
Such that it would print out the product name in one column
and either the word 'Cheap' or 'Expensive' on the other column
depending on the unitprice. This doesn't seem to work in
SQLServer 2000 as it's giving me a syntax error at the IF. Is
this possible using SQLServer? If so, how so?
thnx,
Christoph|||Hi
USE [Northwind];
SELECT
ProductName,
CASE WHEN UnitPrice < 20 THEN 'Cheap'ELSE 'Expensive' END AS ExpenseLevel
FROM
Products;
"Christoph Boget" <jcboget@.yahoo.com> wrote in message
news:uylq2GfhEHA.4064@.TK2MSFTNGP12.phx.gbl...
> I'm not sure if this is the right place to post this message. I
> didn't see a sqlserver.sql newsgroup.
> Anyway, we are porting our site from using MySQL to using
> SQLServer 2000 and we've run across a problem. In MySQL,
> you can have a query that looks like this (using Northwind DB
> as an example as that is ubiquitous):
> USE [Northwind];
> SELECT
> ProductName,
> IF( UnitPrice < 20, 'Cheap', 'Expensive' ) AS ExpenseLevel
> FROM
> Products;
> Such that it would print out the product name in one column
> and either the word 'Cheap' or 'Expensive' on the other column
> depending on the unitprice. This doesn't seem to work in
> SQLServer 2000 as it's giving me a syntax error at the IF. Is
> this possible using SQLServer? If so, how so?
> thnx,
> Christoph
>

Help with a query

I'm not sure if this is the right place to post this message. I
didn't see a sqlserver.sql newsgroup.
Anyway, we are porting our site from using MySQL to using
SQLServer 2000 and we've run across a problem. In MySQL,
you can have a query that looks like this (using Northwind DB
as an example as that is ubiquitous):
USE [Northwind];
SELECT
ProductName,
IF( UnitPrice < 20, 'Cheap', 'Expensive' ) AS ExpenseLevel
FROM
Products;
Such that it would print out the product name in one column
and either the word 'Cheap' or 'Expensive' on the other column
depending on the unitprice. This doesn't seem to work in
SQLServer 2000 as it's giving me a syntax error at the IF. Is
this possible using SQLServer? If so, how so?
thnx,
Christoph
On Thu, 19 Aug 2004 08:37:50 -0500, Christoph Boget wrote:

>I'm not sure if this is the right place to post this message. I
>didn't see a sqlserver.sql newsgroup.
>Anyway, we are porting our site from using MySQL to using
>SQLServer 2000 and we've run across a problem. In MySQL,
>you can have a query that looks like this (using Northwind DB
>as an example as that is ubiquitous):
>USE [Northwind];
>SELECT
> ProductName,
> IF( UnitPrice < 20, 'Cheap', 'Expensive' ) AS ExpenseLevel
>FROM
> Products;
>Such that it would print out the product name in one column
>and either the word 'Cheap' or 'Expensive' on the other column
>depending on the unitprice. This doesn't seem to work in
>SQLServer 2000 as it's giving me a syntax error at the IF. Is
>this possible using SQLServer? If so, how so?
>thnx,
>Christoph
>
Hi Christoph,
Check out CASE in Books Online. Your example would read:
SELECT ProductName,
CASE
WHEN UnitPrice < 20
THEN 'Cheap'
ELSE 'Expensive'
END AS ExpenseLevel
FROM Products
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)
|||Yep:
USE [Northwind];
SELECT
ProductName,
CASE WHEN UnitPrice < 20 THEN 'Cheap' ELSE 'Expensive' END AS ExpenseLevel
FROM
BTW, there is a .programming newsgroup.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Christoph Boget" <jcboget@.yahoo.com> wrote in message
news:uylq2GfhEHA.4064@.TK2MSFTNGP12.phx.gbl...
I'm not sure if this is the right place to post this message. I
didn't see a sqlserver.sql newsgroup.
Anyway, we are porting our site from using MySQL to using
SQLServer 2000 and we've run across a problem. In MySQL,
you can have a query that looks like this (using Northwind DB
as an example as that is ubiquitous):
USE [Northwind];
SELECT
ProductName,
IF( UnitPrice < 20, 'Cheap', 'Expensive' ) AS ExpenseLevel
FROM
Products;
Such that it would print out the product name in one column
and either the word 'Cheap' or 'Expensive' on the other column
depending on the unitprice. This doesn't seem to work in
SQLServer 2000 as it's giving me a syntax error at the IF. Is
this possible using SQLServer? If so, how so?
thnx,
Christoph
|||Hi
USE [Northwind];
SELECT
ProductName,
CASE WHEN UnitPrice < 20 THEN 'Cheap'ELSE 'Expensive' END AS ExpenseLevel
FROM
Products;
"Christoph Boget" <jcboget@.yahoo.com> wrote in message
news:uylq2GfhEHA.4064@.TK2MSFTNGP12.phx.gbl...
> I'm not sure if this is the right place to post this message. I
> didn't see a sqlserver.sql newsgroup.
> Anyway, we are porting our site from using MySQL to using
> SQLServer 2000 and we've run across a problem. In MySQL,
> you can have a query that looks like this (using Northwind DB
> as an example as that is ubiquitous):
> USE [Northwind];
> SELECT
> ProductName,
> IF( UnitPrice < 20, 'Cheap', 'Expensive' ) AS ExpenseLevel
> FROM
> Products;
> Such that it would print out the product name in one column
> and either the word 'Cheap' or 'Expensive' on the other column
> depending on the unitprice. This doesn't seem to work in
> SQLServer 2000 as it's giving me a syntax error at the IF. Is
> this possible using SQLServer? If so, how so?
> thnx,
> Christoph
>

Help with a query

I'm not sure if this is the right place to post this message. I
didn't see a sqlserver.sql newsgroup.
Anyway, we are porting our site from using mysql to using
SQLServer 2000 and we've run across a problem. In MySQL,
you can have a query that looks like this (using Northwind DB
as an example as that is ubiquitous):
USE [Northwind];
SELECT
ProductName,
IF( UnitPrice < 20, 'Cheap', 'Expensive' ) AS ExpenseLevel
FROM
Products;
Such that it would print out the product name in one column
and either the word 'Cheap' or 'Expensive' on the other column
depending on the unitprice. This doesn't seem to work in
SQLServer 2000 as it's giving me a syntax error at the IF. Is
this possible using SQLServer? If so, how so?
thnx,
ChristophOn Thu, 19 Aug 2004 08:37:50 -0500, Christoph Boget wrote:

>I'm not sure if this is the right place to post this message. I
>didn't see a sqlserver.sql newsgroup.
>Anyway, we are porting our site from using mysql to using
>SQLServer 2000 and we've run across a problem. In MySQL,
>you can have a query that looks like this (using Northwind DB
>as an example as that is ubiquitous):
>USE [Northwind];
>SELECT
> ProductName,
> IF( UnitPrice < 20, 'Cheap', 'Expensive' ) AS ExpenseLevel
>FROM
> Products;
>Such that it would print out the product name in one column
>and either the word 'Cheap' or 'Expensive' on the other column
>depending on the unitprice. This doesn't seem to work in
>SQLServer 2000 as it's giving me a syntax error at the IF. Is
>this possible using SQLServer? If so, how so?
>thnx,
>Christoph
>
Hi Christoph,
Check out CASE in Books Online. Your example would read:
SELECT ProductName,
CASE
WHEN UnitPrice < 20
THEN 'Cheap'
ELSE 'Expensive'
END AS ExpenseLevel
FROM Products
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Yep:
USE [Northwind];
SELECT
ProductName,
CASE WHEN UnitPrice < 20 THEN 'Cheap' ELSE 'Expensive' END AS ExpenseLevel
FROM
BTW, there is a .programming newsgroup.
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Christoph Boget" <jcboget@.yahoo.com> wrote in message
news:uylq2GfhEHA.4064@.TK2MSFTNGP12.phx.gbl...
I'm not sure if this is the right place to post this message. I
didn't see a sqlserver.sql newsgroup.
Anyway, we are porting our site from using mysql to using
SQLServer 2000 and we've run across a problem. In MySQL,
you can have a query that looks like this (using Northwind DB
as an example as that is ubiquitous):
USE [Northwind];
SELECT
ProductName,
IF( UnitPrice < 20, 'Cheap', 'Expensive' ) AS ExpenseLevel
FROM
Products;
Such that it would print out the product name in one column
and either the word 'Cheap' or 'Expensive' on the other column
depending on the unitprice. This doesn't seem to work in
SQLServer 2000 as it's giving me a syntax error at the IF. Is
this possible using SQLServer? If so, how so?
thnx,
Christoph|||Hi
USE [Northwind];
SELECT
ProductName,
CASE WHEN UnitPrice < 20 THEN 'Cheap'ELSE 'Expensive' END AS ExpenseLevel
FROM
Products;
"Christoph Boget" <jcboget@.yahoo.com> wrote in message
news:uylq2GfhEHA.4064@.TK2MSFTNGP12.phx.gbl...
> I'm not sure if this is the right place to post this message. I
> didn't see a sqlserver.sql newsgroup.
> Anyway, we are porting our site from using mysql to using
> SQLServer 2000 and we've run across a problem. In MySQL,
> you can have a query that looks like this (using Northwind DB
> as an example as that is ubiquitous):
> USE [Northwind];
> SELECT
> ProductName,
> IF( UnitPrice < 20, 'Cheap', 'Expensive' ) AS ExpenseLevel
> FROM
> Products;
> Such that it would print out the product name in one column
> and either the word 'Cheap' or 'Expensive' on the other column
> depending on the unitprice. This doesn't seem to work in
> SQLServer 2000 as it's giving me a syntax error at the IF. Is
> this possible using SQLServer? If so, how so?
> thnx,
> Christoph
>

HELP SQLServer will start, SQL Agent won't

We just had a security fix applied to the server.
When we restarted both SQL Server and SQL Server agent
started. However shortly after, the SQLServerAgent stopped
running. They are both using the same account, but when
you try and start up the SQL Server Agent you get the
following error messages...
[165] ODBC Error: 0, Cannot generate SSPI context
[SQLSTATE HY000]
[000] Unable to connect to server '(local)';
SQLServerAgent cannot start
I have read the KB#811889, but was looking for anyone who
has experienced this issue.
Thanks
Fredtake a look at following article:
http://support.microsoft.com/default.aspx?scid=kb;en-
us;811889
hth
>--Original Message--
>We just had a security fix applied to the server.
>When we restarted both SQL Server and SQL Server agent
>started. However shortly after, the SQLServerAgent
stopped
>running. They are both using the same account, but when
>you try and start up the SQL Server Agent you get the
>following error messages...
>[165] ODBC Error: 0, Cannot generate SSPI context
>[SQLSTATE HY000]
>[000] Unable to connect to server '(local)';
>SQLServerAgent cannot start
>I have read the KB#811889, but was looking for anyone who
>has experienced this issue.
>Thanks
>Fred
>.
>

Help SQL CE Query - LOW Performance

Hi!

Sorry for bad english, I'm from Brazil.

In microsoft.public.sqlserver.ce haven't found a way to improve performance
of this query. Thanks for any help or reply!

This used to take almost 6 min !!! With index on E.Produto now takes about 30 sec...

1,909 row table

Ipaq 1950 - Samsung 300 Mhz - 32 MB RAM - Windows Mobile 5.0 - SQL CE
2.0

PK (all multiple columns) - tables:

Lotes - pk(Empresa, Lote, Contagem, Produto)

Contagem - pk(Empresa, Lote, Contagem, Produto)

Produtos - pk(Codigo) // this field also is FK <=> Produto in all other
tables

Estoque - pk(Empresa, Ordem, Produto)

Part of my VB.NET code with SQL:

sql_grd_inv = "SELECT L.Empresa, L.Lote, L.Contagem, L.Produto" _
& ", P.Unidade, P.Descr, P.Ref, P.Embgem, P.Marca" _
& ", Sum(CASE WHEN E.Estoque IS NULL THEN 0 ELSE E.Estoque END)" _
& "AS SomaEstoque, C.Qtde" _
& " FROM (" _
& "(Lotes L " _
& "LEFT JOIN Contagem C ON (L.Empresa = C.Empresa) " _
& "AND (L.Lote = C.Lote) AND (L.Contagem = C.Contagem) " _
& "AND (L.Produto = C.Produto)" _
& ") " _
& "LEFT JOIN Estoque E ON (L.Empresa = E.Empresa) " _
& "AND (L.Produto = E.Produto)" _
& ") " _
& "INNER JOIN Produtos P ON L.Produto = P.Codigo " _
& "GROUP BY L.Empresa, L.Lote, L.Contagem, L.Produto" _
& ", P.Unidade, P.Descr, P.Ref, P.Embgem, P.Marca, C.Qtde " _
& "HAVING (L.Empresa='" & IncEmpresa & "') " _
& "AND (L.Lote='" & Cbo_Lote_Pnl_Invent.Text & "') AND (L.Contagem='" _
& Cbo_Cont_Pnl_Invent.Text & "') " _
& "UNION " _
& "SELECT " _
& "C.Empresa, C.Lote, C.Contagem, C.Produto" _
& ", P.Unidade, P.Descr, P.Ref, P.Embgem, P.Marca" _
& ", Sum(CASE WHEN E.Estoque IS NULL THEN 0 ELSE E.Estoque END)" _
& "AS SomaEstoque, C.Qtde" _
& " FROM (" _
& "(Contagem C " _
& "LEFT JOIN Lotes L ON (C.Empresa = L.Empresa) " _
& "AND (C.Lote = L.Lote) AND (C.Contagem = L.Contagem) " _
& "AND (C.Produto = L.Produto)" _
& ") " _
& "LEFT JOIN Estoque E ON (C.Empresa = E.Empresa) " _
& "AND (C.Produto = E.Produto)" _
& ") " _
& "INNER JOIN Produtos P ON C.Produto = P.Codigo " _
& "GROUP BY C.Empresa, C.Lote, C.Contagem, C.Produto" _
& ", P.Unidade, P.Descr, P.Ref, P.Embgem, P.Marca, C.Qtde" _
& ", L.Empresa, L.Lote, L.Contagem, L.Produto " _
& "HAVING (L.Empresa Is Null) AND (L.Lote Is Null) " _
& "AND (L.Contagem Is Null) AND (L.Produto Is Null) " _
& "AND (C.Empresa='" & IncEmpresa & "') " _
& "AND (C.Lote='" & Cbo_Lote_Pnl_Invent.Text & "') AND (C.Contagem='" _
& Cbo_Cont_Pnl_Invent.Text & "') "

Did anyone have queries with more than one LEFT JOIN and 2,000 records?

I've composite PKs and indexes and I don't know if creating single indexes for each column (additionally or replacing the composite ones?) will improve this query. The query processor only use one index to run the query so I don't know wich one is being used.

I've detected that the first query (before the UNION) is the slowest. The second one most of the times returns no records and takes 1-2 seconds.

Here is a more clean version of the query:

SELECT

L.Empresa, L.Lote, L.Contagem, L.Produto,
P.Descr, Sum(CASE WHEN E.Estoque IS NULL THEN 0 ELSE E.Estoque END) AS SomaDeEstoque,
C.Qtde
FROM (
(Lotes L
LEFT JOIN Contagem C ON (L.Empresa = C.Empresa)
AND (L.Lote = C.Lote) AND (L.Contagem = C.Contagem)
AND (L.Produto = C.Produto)
)
LEFT JOIN Estoque E ON (L.Empresa = E.Empresa)
AND (L.Produto = E.Produto)
)
INNER JOIN Produtos P ON L.Produto = P.Codigo
GROUP BY L.Empresa, L.Lote, L.Contagem, L.Produto, P.Descr, C.Qtde
HAVING (L.Empresa='0001') AND (L.Lote='0001') AND (L.Contagem='1')
UNION
SELECT
C.Empresa, C.Lote, C.Contagem, C.Produto,
P.Descr, Sum(CASE WHEN E.Estoque IS NULL THEN 0 ELSE E.Estoque END) AS SomaDeEstoque,
C.Qtde
FROM (
(Contagem C
LEFT JOIN Lotes L ON (C.Produto = L.Produto)
AND (C.Contagem = L.Contagem) AND
(C.Empresa = L.Empresa) AND (C.Lote = L.Lote)
)
LEFT JOIN Estoque E ON (C.Produto = E.Produto)
AND (C.Empresa = E.Empresa)
)
INNER JOIN Produtos P ON C.Produto = P.Codigo
GROUP BY C.Empresa, C.Lote, C.Contagem, C.Produto, P.Descr, C.Qtde,
L.Empresa, L.Lote, L.Contagem, L.Produto
HAVING (L.Empresa Is Null) AND (L.Contagem Is Null) AND (L.Produto Is Null) AND (L.Lote Is Null)
AND (C.Empresa='0001') AND (C.Lote='0001') AND (C.Contagem='1')