Case statement

Hi

I am having a few problem with a case statement.

I have a table with customer data in and I need to bring back all the rows
which have data missing from 2 different columns.

customer table
CustomerID Name SoftwareVersion
1001 TEST1 V5
1002 TEST2 V5
3 TEST3 V5
004 TEST4 V6
ect..

CustomerID Table
ID
1001
1002
1003
1004
ect...

I have a need customerid table and I need to list all the customers who are
not in this table or whos id does not match the new listings,
alos there is a softwareVersion Table list all the newest software so the same
will need to be done for this.

Thanks

Rich

You need to create the join batween the two table with CustomerID & ID, with not equal operator, yuor query may look like this

SELECT A.CustomerID FROM customer A , customerID B

WHERE A.CustomerID != B.ID

Gurpreet S. Gill

|||

Hi

I would like to use the case statement because where there is an error, I would like the cell to display

ERROR: 'then customerid'

Thanks

Rich

|||

I dont get you, what you want to do ? tell me in more detail.

|||

SELECT A.CustomerID FROM customer A , customerID B

WHERE A.CustomerID != B.ID

This won't work, you're just going to get every Customer joined to every customer except themselves.

You need to use an outer join something like this

SELECT A.CustomerID
FROM Customer A
LEFT JOIN CustomerID B ON A.CustomerID = B.[ID]
WHERE B.ID IS NULL

|||I assume you mean that the CustomerID table in your example above is really the result of a query you want to write. The problem is you haven't specified what "all the customers who are not in this table or whos id does not match the new listings" means. How do you know if a customer is not in that table or if they do not match the new listings, is that info in another table?|||

yes SnMSDN

he didnt make much clear what he wants, that why i did post that sample code. But he not responding, might he had solved the problem

sql

Case Sensitive Flag

I have a customer with MS-SQL7 they have to have the case sensitive flag on
for a database they purchased some time ago. Can I attach a database to the
same SQL server and some how remove the case sensitive flag for just this DB
?Not possible - this is set at the server level and affects all databases.
"Dan C" <anonymous@.discussions.microsoft.com> wrote in message
news:8D10190E-A01E-4950-B2AE-F62BCBD2C6A9@.microsoft.com...

quote:

---

> I have a customer with MS-SQL7 they have to have the case sensitive flag

---

on for a database they purchased some time ago. Can I attach a database to
the same SQL server and some how remove the case sensitive flag for just
this DB?|||Hi Dan,
You (or your customer) is out of luck there. The collation (which includes
among other things the setting for case sensitivity) is a server wide
setting on SQL Server 7. You either need a separate server for that
database, or you need to upgrade to SQL Server 2000, or you can install a
named instance of SQL Server 2000 (with the appropriate collation) on the
same machine as SQL Server 7 is running on.
Jacco Schalkwijk
SQL Server MVP
"Dan C" <anonymous@.discussions.microsoft.com> wrote in message
news:8D10190E-A01E-4950-B2AE-F62BCBD2C6A9@.microsoft.com...

quote:

---

> I have a customer with MS-SQL7 they have to have the case sensitive flag

---

on for a database they purchased some time ago. Can I attach a database to
the same SQL server and some how remove the case sensitive flag for just
this DB?

Case Sensitive Flag

I have a customer with MS-SQL7 they have to have the case sensitive flag on for a database they purchased some time ago. Can I attach a database to the same SQL server and some how remove the case sensitive flag for just this DB?Not possible - this is set at the server level and affects all databases.
"Dan C" <anonymous@.discussions.microsoft.com> wrote in message
news:8D10190E-A01E-4950-B2AE-F62BCBD2C6A9@.microsoft.com...
> I have a customer with MS-SQL7 they have to have the case sensitive flag
on for a database they purchased some time ago. Can I attach a database to
the same SQL server and some how remove the case sensitive flag for just
this DB?|||Hi Dan,
You (or your customer) is out of luck there. The collation (which includes
among other things the setting for case sensitivity) is a server wide
setting on SQL Server 7. You either need a separate server for that
database, or you need to upgrade to SQL Server 2000, or you can install a
named instance of SQL Server 2000 (with the appropriate collation) on the
same machine as SQL Server 7 is running on.
--
Jacco Schalkwijk
SQL Server MVP
"Dan C" <anonymous@.discussions.microsoft.com> wrote in message
news:8D10190E-A01E-4950-B2AE-F62BCBD2C6A9@.microsoft.com...
> I have a customer with MS-SQL7 they have to have the case sensitive flag
on for a database they purchased some time ago. Can I attach a database to
the same SQL server and some how remove the case sensitive flag for just
this DB?sql

Cascading Updates / Delete Problem

I have 3 tables set up in the typical customer - order - order item setup. I
have cascading updates & deletes on the customer and order tables. Primary &
secondary keys are correct.
When I delete, or update the customer table key field, it takes about 60
seconds to cascade to the child tables. If I execute manually the delete
operation startting at the order item table, then the orders, and finally the
customer table it only takes a second.
Why is the cascading taking so long, when doing the same task manually takes
a fraction of the time?If you do the manual delete, do you have the cascading foreign leys in place
or not? If you don't, you can probably fix the issue by creating indexes on
the foreign key columns.
--
Jacco Schalkwijk
SQL Server MVP
"Howard Carr" <HowardCarr@.discussions.microsoft.com> wrote in message
news:AEEEB81C-C1F2-4AD1-B2EE-39E4B8C88F32@.microsoft.com...
>I have 3 tables set up in the typical customer - order - order item setup.
>I
> have cascading updates & deletes on the customer and order tables. Primary
> &
> secondary keys are correct.
> When I delete, or update the customer table key field, it takes about 60
> seconds to cascade to the child tables. If I execute manually the delete
> operation startting at the order item table, then the orders, and finally
> the
> customer table it only takes a second.
> Why is the cascading taking so long, when doing the same task manually
> takes
> a fraction of the time?|||All keys are in place.
"Jacco Schalkwijk" wrote:
> If you do the manual delete, do you have the cascading foreign leys in place
> or not? If you don't, you can probably fix the issue by creating indexes on
> the foreign key columns.
> --
> Jacco Schalkwijk
> SQL Server MVP
>
> "Howard Carr" <HowardCarr@.discussions.microsoft.com> wrote in message
> news:AEEEB81C-C1F2-4AD1-B2EE-39E4B8C88F32@.microsoft.com...
> >I have 3 tables set up in the typical customer - order - order item setup.
> >I
> > have cascading updates & deletes on the customer and order tables. Primary
> > &
> > secondary keys are correct.
> > When I delete, or update the customer table key field, it takes about 60
> > seconds to cascade to the child tables. If I execute manually the delete
> > operation startting at the order item table, then the orders, and finally
> > the
> > customer table it only takes a second.
> > Why is the cascading taking so long, when doing the same task manually
> > takes
> > a fraction of the time?
>
>|||"Howard Carr" <HowardCarr@.discussions.microsoft.com> wrote in message
news:232641B7-91F9-439E-83EE-3ECB623432F8@.microsoft.com...
> All keys are in place.
>
Then you'll need to post a repro.
David|||What about indexes on the foreign key columns?
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Howard Carr" <HowardCarr@.discussions.microsoft.com> wrote in message
news:232641B7-91F9-439E-83EE-3ECB623432F8@.microsoft.com...
> All keys are in place.
> "Jacco Schalkwijk" wrote:
>> If you do the manual delete, do you have the cascading foreign leys in place
>> or not? If you don't, you can probably fix the issue by creating indexes on
>> the foreign key columns.
>> --
>> Jacco Schalkwijk
>> SQL Server MVP
>>
>> "Howard Carr" <HowardCarr@.discussions.microsoft.com> wrote in message
>> news:AEEEB81C-C1F2-4AD1-B2EE-39E4B8C88F32@.microsoft.com...
>> >I have 3 tables set up in the typical customer - order - order item setup.
>> >I
>> > have cascading updates & deletes on the customer and order tables. Primary
>> > &
>> > secondary keys are correct.
>> > When I delete, or update the customer table key field, it takes about 60
>> > seconds to cascade to the child tables. If I execute manually the delete
>> > operation startting at the order item table, then the orders, and finally
>> > the
>> > customer table it only takes a second.
>> > Why is the cascading taking so long, when doing the same task manually
>> > takes
>> > a fraction of the time?
>>

Cascading Updates / Delete Problem

I have 3 tables set up in the typical customer - order - order item setup. I
have cascading updates & deletes on the customer and order tables. Primary &
secondary keys are correct.
When I delete, or update the customer table key field, it takes about 60
seconds to cascade to the child tables. If I execute manually the delete
operation startting at the order item table, then the orders, and finally the
customer table it only takes a second.
Why is the cascading taking so long, when doing the same task manually takes
a fraction of the time?
If you do the manual delete, do you have the cascading foreign leys in place
or not? If you don't, you can probably fix the issue by creating indexes on
the foreign key columns.
Jacco Schalkwijk
SQL Server MVP
"Howard Carr" <HowardCarr@.discussions.microsoft.com> wrote in message
news:AEEEB81C-C1F2-4AD1-B2EE-39E4B8C88F32@.microsoft.com...
>I have 3 tables set up in the typical customer - order - order item setup.
>I
> have cascading updates & deletes on the customer and order tables. Primary
> &
> secondary keys are correct.
> When I delete, or update the customer table key field, it takes about 60
> seconds to cascade to the child tables. If I execute manually the delete
> operation startting at the order item table, then the orders, and finally
> the
> customer table it only takes a second.
> Why is the cascading taking so long, when doing the same task manually
> takes
> a fraction of the time?
|||All keys are in place.
"Jacco Schalkwijk" wrote:

> If you do the manual delete, do you have the cascading foreign leys in place
> or not? If you don't, you can probably fix the issue by creating indexes on
> the foreign key columns.
> --
> Jacco Schalkwijk
> SQL Server MVP
>
> "Howard Carr" <HowardCarr@.discussions.microsoft.com> wrote in message
> news:AEEEB81C-C1F2-4AD1-B2EE-39E4B8C88F32@.microsoft.com...
>
>
|||"Howard Carr" <HowardCarr@.discussions.microsoft.com> wrote in message
news:232641B7-91F9-439E-83EE-3ECB623432F8@.microsoft.com...
> All keys are in place.
>
Then you'll need to post a repro.
David
|||What about indexes on the foreign key columns?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Howard Carr" <HowardCarr@.discussions.microsoft.com> wrote in message
news:232641B7-91F9-439E-83EE-3ECB623432F8@.microsoft.com...[vbcol=seagreen]
> All keys are in place.
> "Jacco Schalkwijk" wrote:

Cascading Updates / Delete Problem

I have 3 tables set up in the typical customer - order - order item setup. I
have cascading updates & deletes on the customer and order tables. Primary &
secondary keys are correct.
When I delete, or update the customer table key field, it takes about 60
seconds to cascade to the child tables. If I execute manually the delete
operation startting at the order item table, then the orders, and finally th
e
customer table it only takes a second.
Why is the cascading taking so long, when doing the same task manually takes
a fraction of the time?If you do the manual delete, do you have the cascading foreign leys in place
or not? If you don't, you can probably fix the issue by creating indexes on
the foreign key columns.
Jacco Schalkwijk
SQL Server MVP
"Howard Carr" <HowardCarr@.discussions.microsoft.com> wrote in message
news:AEEEB81C-C1F2-4AD1-B2EE-39E4B8C88F32@.microsoft.com...
>I have 3 tables set up in the typical customer - order - order item setup.
>I
> have cascading updates & deletes on the customer and order tables. Primary
> &
> secondary keys are correct.
> When I delete, or update the customer table key field, it takes about 60
> seconds to cascade to the child tables. If I execute manually the delete
> operation startting at the order item table, then the orders, and finally
> the
> customer table it only takes a second.
> Why is the cascading taking so long, when doing the same task manually
> takes
> a fraction of the time?|||All keys are in place.
"Jacco Schalkwijk" wrote:

> If you do the manual delete, do you have the cascading foreign leys in pla
ce
> or not? If you don't, you can probably fix the issue by creating indexes o
n
> the foreign key columns.
> --
> Jacco Schalkwijk
> SQL Server MVP
>
> "Howard Carr" <HowardCarr@.discussions.microsoft.com> wrote in message
> news:AEEEB81C-C1F2-4AD1-B2EE-39E4B8C88F32@.microsoft.com...
>
>|||"Howard Carr" <HowardCarr@.discussions.microsoft.com> wrote in message
news:232641B7-91F9-439E-83EE-3ECB623432F8@.microsoft.com...
> All keys are in place.
>
Then you'll need to post a repro.
David|||What about indexes on the foreign key columns?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Howard Carr" <HowardCarr@.discussions.microsoft.com> wrote in message
news:232641B7-91F9-439E-83EE-3ECB623432F8@.microsoft.com...[vbcol=seagreen]
> All keys are in place.
> "Jacco Schalkwijk" wrote:
>

Cascading Delete Problem

I have three tables that equate to a customer - order - order items
scenario.
The Customer table has 100,000 rows
The Order table has 3 million rows
The Order items has 30 million rows.
I have the primary and foreign keys all defind and the appropriate indexes.
I have cascading deletes on the foreign keys between all three tables.
It takes over a minute to delete a customer. If I remove the cacading
delete, and delete from the order items then orders then customers it takes
seconds.
Why does the cascading deletes take so long?
Using SQL2000 sp3a
Thanks for any insight!
On Thu, 03 Feb 2005 19:35:25 GMT, Howard Carr wrote:

>I have three tables that equate to a customer - order - order items
>scenario.
>The Customer table has 100,000 rows
>The Order table has 3 million rows
>The Order items has 30 million rows.
>I have the primary and foreign keys all defind and the appropriate indexes.
>I have cascading deletes on the foreign keys between all three tables.
>It takes over a minute to delete a customer. If I remove the cacading
>delete, and delete from the order items then orders then customers it takes
>seconds.
>Why does the cascading deletes take so long?
Hi Howard,
Is your primary key (or unique) constraint for the OrderItems table
defined as (OrderID, CustomerID)?
Try changing it to (CustomerID, OrderID). It will speed up the deletion of
a customer, but it will slow down the deletion of an order. If both
deletion happens frequently enough to need good performance, you might try
adding an extra index, so that both orders are present.
Note that changing indexes might affect performance of all other queries
as well.
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)

Cascading Delete Problem

I have three tables that equate to a customer - order - order items
scenario.
The Customer table has 100,000 rows
The Order table has 3 million rows
The Order items has 30 million rows.
I have the primary and foreign keys all defind and the appropriate indexes.
I have cascading deletes on the foreign keys between all three tables.
It takes over a minute to delete a customer. If I remove the cacading
delete, and delete from the order items then orders then customers it takes
seconds.
Why does the cascading deletes take so long?
Using SQL2000 sp3a
Thanks for any insight!On Thu, 03 Feb 2005 19:35:25 GMT, Howard Carr wrote:

>I have three tables that equate to a customer - order - order items
>scenario.
>The Customer table has 100,000 rows
>The Order table has 3 million rows
>The Order items has 30 million rows.
>I have the primary and foreign keys all defind and the appropriate indexes.
>I have cascading deletes on the foreign keys between all three tables.
>It takes over a minute to delete a customer. If I remove the cacading
>delete, and delete from the order items then orders then customers it takes
>seconds.
>Why does the cascading deletes take so long?
Hi Howard,
Is your primary key (or unique) constraint for the OrderItems table
defined as (OrderID, CustomerID)?
Try changing it to (CustomerID, OrderID). It will speed up the deletion of
a customer, but it will slow down the deletion of an order. If both
deletion happens frequently enough to need good performance, you might try
adding an extra index, so that both orders are present.
Note that changing indexes might affect performance of all other queries
as well.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)

Cascading Delete Problem

I have three tables that equate to a customer - order - order items
scenario.
The Customer table has 100,000 rows
The Order table has 3 million rows
The Order items has 30 million rows.
I have the primary and foreign keys all defind and the appropriate indexes.
I have cascading deletes on the foreign keys between all three tables.
It takes over a minute to delete a customer. If I remove the cacading
delete, and delete from the order items then orders then customers it takes
seconds.
Why does the cascading deletes take so long?
Using SQL2000 sp3a
Thanks for any insight!On Thu, 03 Feb 2005 19:35:25 GMT, Howard Carr wrote:
>I have three tables that equate to a customer - order - order items
>scenario.
>The Customer table has 100,000 rows
>The Order table has 3 million rows
>The Order items has 30 million rows.
>I have the primary and foreign keys all defind and the appropriate indexes.
>I have cascading deletes on the foreign keys between all three tables.
>It takes over a minute to delete a customer. If I remove the cacading
>delete, and delete from the order items then orders then customers it takes
>seconds.
>Why does the cascading deletes take so long?
Hi Howard,
Is your primary key (or unique) constraint for the OrderItems table
defined as (OrderID, CustomerID)?
Try changing it to (CustomerID, OrderID). It will speed up the deletion of
a customer, but it will slow down the deletion of an order. If both
deletion happens frequently enough to need good performance, you might try
adding an extra index, so that both orders are present.
Note that changing indexes might affect performance of all other queries
as well.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)

Carriage return in column values

Hi,
How can I find out where the carriage returns are in a column value in a
table? For example, we have an Address column in a Customer table. This
column value can have a maximum of 255 characters where carriage returns are
allowed. How can I find out where those carriage returns are?
Thank you in advance,
DeeOne way is using CHARINDEX, if you only want to first position:
USE tempdb
CREATE TABLE t(c1 int identity, c2 varchar(2000))
INSERT INTO t VALUES('Hello
there')
INSERT INTO t VALUES('Hi everybody
I''ts Doctor Nick')
SELECT CHARINDEX(CHAR(13) + CHAR(10), c2) FROM t
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"bpdee" <bpdee@.discussions.microsoft.com> wrote in message
news:0EF8E157-C008-4370-A355-DB9E43A3F741@.microsoft.com...
> Hi,
> How can I find out where the carriage returns are in a column value in a
> table? For example, we have an Address column in a Customer table. This
> column value can have a maximum of 255 characters where carriage returns are
> allowed. How can I find out where those carriage returns are?
> Thank you in advance,
> Dee|||Thank you, Tibor, for your response. Let's put a different twist to it now.
Let's say
I want to identify customer records where the address has 1 or more lines in
the Address column that are greater than 40 characters. Obviously, the text
before each carriage return is considered as one line in the Address column.
"Tibor Karaszi" wrote:
> One way is using CHARINDEX, if you only want to first position:
> USE tempdb
> CREATE TABLE t(c1 int identity, c2 varchar(2000))
> INSERT INTO t VALUES('Hello
> there')
> INSERT INTO t VALUES('Hi everybody
> I''ts Doctor Nick')
> SELECT CHARINDEX(CHAR(13) + CHAR(10), c2) FROM t
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "bpdee" <bpdee@.discussions.microsoft.com> wrote in message
> news:0EF8E157-C008-4370-A355-DB9E43A3F741@.microsoft.com...
> > Hi,
> >
> > How can I find out where the carriage returns are in a column value in a
> > table? For example, we have an Address column in a Customer table. This
> > column value can have a maximum of 255 characters where carriage returns are
> > allowed. How can I find out where those carriage returns are?
> >
> > Thank you in advance,
> > Dee
>|||Dealing with unknowns (like unknown number of address lines), I'd suggest writing a scalar user
defined function for this.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"bpdee" <bpdee@.discussions.microsoft.com> wrote in message
news:1F0AF68D-40DA-4F7A-9C84-C086922573FE@.microsoft.com...
> Thank you, Tibor, for your response. Let's put a different twist to it now.
> Let's say
> I want to identify customer records where the address has 1 or more lines in
> the Address column that are greater than 40 characters. Obviously, the text
> before each carriage return is considered as one line in the Address column.
> "Tibor Karaszi" wrote:
>> One way is using CHARINDEX, if you only want to first position:
>> USE tempdb
>> CREATE TABLE t(c1 int identity, c2 varchar(2000))
>> INSERT INTO t VALUES('Hello
>> there')
>> INSERT INTO t VALUES('Hi everybody
>> I''ts Doctor Nick')
>> SELECT CHARINDEX(CHAR(13) + CHAR(10), c2) FROM t
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://sqlblog.com/blogs/tibor_karaszi
>>
>> "bpdee" <bpdee@.discussions.microsoft.com> wrote in message
>> news:0EF8E157-C008-4370-A355-DB9E43A3F741@.microsoft.com...
>> > Hi,
>> >
>> > How can I find out where the carriage returns are in a column value in a
>> > table? For example, we have an Address column in a Customer table. This
>> > column value can have a maximum of 255 characters where carriage returns are
>> > allowed. How can I find out where those carriage returns are?
>> >
>> > Thank you in advance,
>> > Dee
>>

Capturing events in a DataGrid

I'm trying to add functionality to a VB 6 application allowing
customer service to add a customer number to a new customer.
Customers are added to the database by sales personnel, and a
prospective customer may have multiple rows due to projected orders
for multiple products. Customer numbers are assigned when a new
customer makes their first order.

I'm using a DataGrid connected to an ADO Data Control. The data
control is connected to a view in SQL Server using the 'distinct'
directive (I know it's not updatable) to show only one line per new
customer. What I wish to do is capture the update event (probably
through the FieldChangeComplete routine of the data control), manually
assign the newly entered customer number to all appropriate rows in
the database, and cancel the update event with no notifications.

I'm having two problems:

1. I can't capture the newly entered customer number. The Text
property of the DataGrid returns the old value of the cell instead of
the newly entered value. How do I get the edited value?

2. Even though I set adStatus = adStatusCancel in the
FieldChangeComplete routine, I get a Microsoft DataGrid Control dialog
stating 'Operation was Canceled'. How do I avoid this notification?Bill Reynolds (TruckeeBill@.ltol.com) writes:
> I'm trying to add functionality to a VB 6 application allowing
> customer service to add a customer number to a new customer.
> Customers are added to the database by sales personnel, and a
> prospective customer may have multiple rows due to projected orders
> for multiple products. Customer numbers are assigned when a new
> customer makes their first order.
> I'm using a DataGrid connected to an ADO Data Control. The data
> control is connected to a view in SQL Server using the 'distinct'
> directive (I know it's not updatable) to show only one line per new
> customer. What I wish to do is capture the update event (probably
> through the FieldChangeComplete routine of the data control), manually
> assign the newly entered customer number to all appropriate rows in
> the database, and cancel the update event with no notifications.
> I'm having two problems:
> 1. I can't capture the newly entered customer number. The Text
> property of the DataGrid returns the old value of the cell instead of
> the newly entered value. How do I get the edited value?
> 2. Even though I set adStatus = adStatusCancel in the
> FieldChangeComplete routine, I get a Microsoft DataGrid Control dialog
> stating 'Operation was Canceled'. How do I avoid this notification?

Sounds like you should ask in an ADO group. Or maybe even a pure VB
group. If you insist on asking here, we will only tell you to use stored
procedures and not relying on things happening behind the scenes. :-)

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp