SQL Statement help! - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

SQL Statement help!

Author

Topic

brendalisalowe
Constraint Violating Yak Guru

269 Posts

Posted - 2004-10-01 : 13:51:20

I need to be able to display all the cases that exist after a certain case number. I don't know how to do it. This is what I have so far:


SELECT * FROM tblCapRec WHERE CaseNumber = '011-364369' 
ORDER BY CaseNumber, PartNumber, LastName, FirstName

I realize that only will pull up one case, but I don't know how else to get that case and all the other cases after that. Anyone know how? Thanks!

Brenda

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-10-01 : 13:52:27

How will you know which ones are after that case? Is there a datetime column for this?

Tara

X002548
Not Just a Number

15586 Posts

Posted - 2004-10-01 : 13:54:00

Well...Order of data in a database is meaningless...

If however you said that the Case number is incremental, or that you had a datetime column that gets add for every new case, or if you had an identity column when a case was added, then yeah, otherwise, no

Brett

8-)

JimL
SQL Slinging Yak Ranger

1537 Posts

Posted - 2004-10-01 : 13:56:26

This is why I do not like char fields for Unique constranints.
Use INT.

Jim
Users <> Logic

brendalisalowe
Constraint Violating Yak Guru

269 Posts

Posted - 2004-10-01 : 14:00:44

It is ordered by case number. Like 011-470866, then 011-470965, then 011-896523, etc...

Right now it will pull up that case, but nothing else. I have an application and when I search for a case it will find that one. I have a NEXT button and it will go through different cases when the search is like this: casenumber LIKE '011-47%' or something like that.

I can't use INT with a dash in the field right?

Anyway, any ideas?

Brenda

JimL
SQL Slinging Yak Ranger

1537 Posts

Posted - 2004-10-01 : 14:39:18

Are the digits to the left of the dash all the same?

Jim
Users <> Logic

brendalisalowe
Constraint Violating Yak Guru

269 Posts

Posted - 2004-10-01 : 14:43:12

NO, it can be multiple things. Like 051-698789, 271-987654, or really any number. Have any ideas yet?

Brenda

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-10-01 : 14:46:52

So does this work:

SELECT *
FROM Table1
WHERE Column1 >= '011-698789'
ORDER BY Column1

?



SET NOCOUNT ON

CREATE TABLE Table1
(
	Column1 VARCHAR(20) NOT NULL,
	Column2 INT NOT NULL
)

INSERT INTO Table1 VALUES('051-698789', 4)
INSERT INTO Table1 VALUES('271-987654', 5)
INSERT INTO Table1 VALUES('011-698789', 3)
INSERT INTO Table1 VALUES('011-123456', 1)
INSERT INTO Table1 VALUES('011-222222', 2)

SELECT * 
FROM Table1
WHERE Column1 >= '011-698789'
ORDER BY Column1

DROP TABLE Table1

Tara

brendalisalowe
Constraint Violating Yak Guru

269 Posts

Posted - 2004-10-01 : 14:53:17

'Greater than' is perfect! Thanks!

But can I do a 'greater than' using LIKE?

For example:
query = "SELECT * FROM tblCapRec WHERE CaseNumber LIKE '011-98%' ORDER BY CaseNumber, PartNumber, LastName, FirstName"

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-10-01 : 14:54:36

WHERE CaseNumber >= '011-984369' AND CaseNumber LIKE '011-98%'

Tara

X002548
Not Just a Number

15586 Posts

Posted - 2004-10-01 : 14:55:52

You might need to get a little slicker with the case number depending of what you deem "order" to mean




USE Northwind
GO

SET NOCOUNT ON
CREATE TABLE myTable99(CaseNumber varchar(25),Col2 int IDENTITY(1,1), Col3 datetime DEFAULT GetDate())
GO

INSERT INTO myTable99(CaseNumber)
SELECT '011-470965' UNION ALL 
SELECT '011-470866' UNION ALL 
SELECT '011-480866' UNION ALL 
SELECT '011-364369' UNION ALL 
SELECT '011-500866' UNION ALL 
SELECT '011-470965'
GO

SELECT TOP 3 * 
      FROM myTable99 
     WHERE CaseNumber >= '011-364369' 
  ORDER BY CaseNumber
GO

SET NOCOUNT OFF
DROP TABLE myTable99
GO

Brett

8-)

brendalisalowe
Constraint Violating Yak Guru

269 Posts

Posted - 2004-10-01 : 15:51:49

My coding where I do the 'greater than' is working great now!

I have a different textbox though where a case number is entered in. I just this behind that box:

SELECT * FROM tblCapRec WHERE CaseNumber LIKE 'txtCaseNumber.Text%' ORDER BY CaseNumber, PartNumber, LastName, FirstName

How can I change that so it will list ALL cases after that case number? Kind of like that 'greater than' but with a LIKE.

Any ideas?

Brenda

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-10-01 : 15:55:34

Did you miss this:

WHERE CaseNumber >= '011-984369' AND CaseNumber LIKE '011-98%'

?

Tara

brendalisalowe
Constraint Violating Yak Guru

269 Posts

Posted - 2004-10-01 : 16:04:12

Looks like it works better like this:

WHERE CaseNumber >= '011-'

Without the LIKE statement.

Thanks for ALL your help...as always!

Brenda

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-10-01 : 16:05:51

Well that'll work if you want all of the 011-s. But if you want everything after 011-984369 and not including 011-111111, then you'll want to use my solution.

Tara

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources