Select statement ? 2

[mgallot]

I need to run a select statement like this:
Select * from TABLENAME where FIELD like '200101%'
or FIELD like '200102%'
or FIELD like '200103%'
or FIELD like '200104%'
or FIELD like '200105%'
or FIELD like '200106%'

Is there a better way to do this?

 

[chiph]

Try this:
[tt]
Select * from TABLENAME where FIELD IN ('200101%', '200102%','200103%','200104%','200105%','200106%')
[/tt]

Oh, wait. You've got wildcards on the end of your data. You'll have to do it like this:
[tt]
Select * from TABLENAME where FIELD LIKE '200101%'
UNION
Select * from TABLENAME where FIELD LIKE '200102%'
UNION
Select * from TABLENAME where FIELD LIKE '200103%'
UNION
Select * from TABLENAME where FIELD LIKE '200104%'
UNION
Select * from TABLENAME where FIELD LIKE '200105%'
UNION
Select * from TABLENAME where FIELD LIKE '200106%'
[/tt]

Chip H.

 

[karluk]

Another possibility is

select * from TABLENAME
where FIELD like '20010%'
and substring(FIELD,6,1) in ('1','2','3','4','5','6')

 

[tlbroadbent]

Are you working with dates? If so, try the following.

SELECT *
FROM TABLENAME
WHERE Year(FIELD)=2001
AND Month(FIELD)<=6

OR this...

SELECT *
FROM TABLENAME
WHERE FIELD BETWEEN '01/01/2001' AND '06/30/2001'

If the column isn't a datetime type then try the following.

SELECT *
FROM TABLENAME
WHERE LEFT(FIELD,6) BETWEEN '200101' AND '200106'

Or even...

SELECT *
FROM TABLENAME
WHERE FIELD BETWEEN '20010101' AND '20010630' Terry L. Broadbent
Life would be easier if I had the source code. -Anonymous

 

[grahamm]

Try:

select * from TABLENAME where FIELD like '20010[1-6]%'

 

[mgallot]

An example of the field is this:
YYYYMMDDxxxxxxxx

x being an eight digit number

I only want to select 6 months worth of data at a time, even that may be too large, but I'm going to try it.

 

[tlbroadbent]

What is the actual data type of the column? Terry L. Broadbent
Life would be easier if I had the source code. -Anonymous

 

[tlbroadbent]

Grahamm,

If you were a member, I'd give you a star. Terry L. Broadbent
Life would be easier if I had the source code. -Anonymous

 

[mgallot]

The datatype is VARCHAR.

 

[karluk]

Terry's right, Grahamm's solution is the best. Take a bow, Grahamm! I hope you decide to join Tek-tips. You'd be a valuable member.

 

[mgallot]

Thanks to everyone!

 

[mgallot]

I tried all of the solutions, but I am running into a problem. The tempdb can't seem to handle the sorting/data. Tempdb is 42mg. I tried a smaller selection of data, just 2 months instead of 6 months, but had the same problem. We are probably talking about 500,000 rows of data for just 2 months. The message says tempdb cant allocate space because 'system' segment is full. I can't extend any segments at this point, because the system lacks hard drive space. I may have to pull each month off separately...

 

[tlbroadbent]

Is it possible to select fewer columns? In many cases Select * isn't necessary and is less efficient than selecting only the columns required. Terry L. Broadbent
Life would be easier if I had the source code. -Anonymous

 

[chiph]

> In many cases Select * isn't necessary <

At my job, doing a SELECT * will get you a talking-to from the team lead about how making efficient queries will enable you to continue receiving a paycheck.
;-)

Chip H.

 

[Subzero007]

Amen to that last comment chiph. A colleague of mine ran a select * with no where clause on a non-partitioned, test fact table with 42 million records, give or take. This guy supposedly had 10 years experience and didn't even know how to kill the session so he went to the DBA for help and the DBA gave him a pink-slip that afternoon. I'll bet the hr department got a dressing down for that folly.

 

[mgallot]

Actually, I am only selecting the fields I absolutely need. I only put a * in my question to simply the question.

 

[chiph]

mgallot -

Whenever you don't know how many rows a table contains, always run a SELECT COUNT(*) FROM xxxxx first. It could come back with 0, 1, or 42 million

I'm not bashing you -- I understood about how you wanted to simplify your query for posting here. It's just that many more people read these threads than you & I, so if something I say helps them out, so much the better.

Chip H.

 

[mgallot]

Thanks! I'll keep working on this and post my solution when/if I find one.

 

[tlbroadbent]

mgallot,

Which version of SQL are you running? From your comments, it appears you are running SQL 6.5. Is this correct? Terry L. Broadbent
Life would be easier if I had the source code. -Anonymous

 

[mgallot]

You are absolutely correct. I need to stabilize this neglected database enough to upgrade to SQL 7.0