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%'
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]
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...
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
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.
;-)
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.
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.
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
This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
By continuing to use this site, you are consenting to our use of cookies.