query problem

I have a table (Product) and a Column (ReleaseDate)

and trying to run this query

select Product =
CASE ReleaseDate

WHEN datediff(d,ReleaseDate,getutcdate()) < 30 THEN 'New'
WHEN datediff(d,ReleaseDate,getutcdate()) < 90 THEN 'Recent'
ELSE 'Normal' END

and get this error

Server: Msg 170, Level 15, State 1, Line 4
Line 4: Incorrect syntax near '<'.

any ideas

cheers!!!

Change to:

select Product=

CASEWHENdatediff(d,ReleaseDate,getutcdate())< 30THEN'New'

WHENdatediff(d,ReleaseDate,getutcdate())< 90THEN'Recent'

ELSE'Normal'END

FROM product

|||Thanks for that.. i got it sorted

Query prob?

Hi,

I got table that contains prodcode, qty and Box, I want to display all product and their total QTY. like

Item

prodcode

qty

box

1

ADEN100550003006

2

000001

2

ADEN100550003006

3

000001

3

ADEN100550003006

6

000001

4

ADEN100550003006

12

000001

5

ADEN100550003006

15

000001

6

ADEN100550003006

18

000001

7

ADEN100550003006

21

000001

8

ADEN100550003006

24

000001

9

ADEN100550003006

36

000001

10

ADEN100550003006

48

000001

11

ADEN100550003006

72

000001

12

ADEN100550003006

144

000001

13

ADEN100550003006

157

000001

14

ADEN100550003006

360

000001

*** all thses product got same bar code so I want to sum all these QTY and display the total QTY.
I am executing this query right nw.

select WHBOXDET.prodcode,WHBOXDET.qty,WHBOXDET.box ,sum (WHBOXDET.qty) from WHBOXDET where prodcode = 'ADEN100550003006' GROUP BY prodcode,box,qty ;

But it Displays result like :

Created: 26/07/2007 12:55:03

Item

prodcode

qty

box

EXPR

1

ADEN100550003006

2

000001

20

2

ADEN100550003006

3

000001

15

3

ADEN100550003006

6

000001

54

4

ADEN100550003006

12

000001

60

5

ADEN100550003006

15

000001

15

6

ADEN100550003006

18

000001

18

7

ADEN100550003006

21

000001

21

8

ADEN100550003006

24

000001

24

9

ADEN100550003006

36

000001

36

10

ADEN100550003006

48

000001

48

11

ADEN100550003006

72

000001

72

12

ADEN100550003006

144

000001

144

13

ADEN100550003006

157

000001

157

14

ADEN100550003006

360

000001

360

Plz tell me how to do it.
Thx

You need to remove "WHBOXDET.qty" from your select list and "qty" from your group by, otherwise it is factored into the uniqueness of each row. It should look like this:

Code Snippet

SELECT WHBOXDET.prodcode, WHBOXDET.box, sum(WHBOXDET.qty) qty from WHBOXDET where prodcode = 'ADEN100550003006' GROUP BY prodcode, box

|||sorted

Query performance

I have a query as follow

Select product.name, vendor.name from product join vendor on product.vendor_key = vendor_key

Vendor_key is the primary key on Vendor Table

If I want to increase the performance should I use Stored Procedure or create index for the vendor_key on Product Table

The first thing to check is that both tables have indices on the linking columns.

Vendor(Vendor_Key) is a PK, so it is automatically indexed.

Does Product(Vendor_Key) have an index?

|||Product(Vendor_key) has no index|||Performance should improve if you add an Index for Product(Vendor_Key).|||

A sproc will only help if you call the query repeatedly. Also, if you are calling it remotely with ADO and are using a bad cursor type.

It seems that you should have vendor.vendor_key as the last part of that query, right?

Also, if you are doing read-only activity, consider using (NOLOCK) hint for both tables, which provides a small performance gain and also improves concurency.

|||

Also, the index will really help the performance if it has product.name as an Included column.

As always, when creating secondary indexes, you should consider the added cost for insert/update/delete operations.

|||you means to add the Product(name) index or Product(name, vendor_key) index. Is it every time I use the join statement, then I will add the index on the foreign key column to increase the performance.