Problem using where 2

[worldchanger]

I have one field that has all numbers in it, but one record contains alphanumeric. For example I'm filtering out all of the records based on numbers:

Where ((object is between 30000 and 50000) ...

I have one file that is valid and it's 98E35. This causes problems when I'm trying to filter it out. Is there anyway to do a match based on the 98 part and exclude it because it's in the 98000 section even though it has an E in it? For system use it's considered to be in the 98000 section and I need to exclude it in my results. How can I do this?

 

[swampBoogie]

Where (isNumeric(object) = 1
and cast(object as int) between 30000 and 50000 )
or substring(object,1,3) = '98E'

 

[SQLBill]

Do you want to exclude only that record or all of the 98000 section records?

-SQLBill

 

[worldchanger]

I'm trying to actually exclude that entirely. I don't need 9XXXXXXXX anything. However based on how the data formated and I'm doing my comparison it's bailing out. I need objects from 30000-39999, 50000-69999, 80000-89999. However there is one object that has the value of '98E35'. How can I write my where clause to exclude anything I'm not looking for when there is one record that is alphanumeric?

INSERT INTO accounts (account_code, desc_text, dollars_maximum, dollars_expended, dollars_pre_encumbered, dollars_encumbered, account_fiscal_yr, account_type, account_class, dept_nbr_prefix, dept_nbr_suffix, active_flag, budgeted_dollars)
SELECT fund + '-' + department + '-' + object + '-' + sub_account AS account_code, department_description + ' ' + object_description AS desc_text, appropriations_after_close AS dollars_maximum, expenditures_after_close AS dollars_expended, pre_encumbrances + spa_pre_encumbrances + check_requisition_pre_encumbrances AS dollars_pre_encumbered, encumbrances_after_close AS dollars_encumbered, '03' AS account_fiscal_yr, 'A' AS account_type, filler AS account_class, filler AS dept_nbr_prefix,filler AS dept_nbr_suffix, 'A' AS active_flag, appropriations_after_close AS budgeted_dollars
FROM subled
WHERE (object BETWEEN 30000 AND 39999 OR object BETWEEN 50000 AND 69999 OR object object BETWEEN 80000 AND 89999) AND (fund BETWEEN 200 AND 249 OR fund = 434 OR fund = 454 OR fund = 455) AND (category = 1 OR category = 3)

 

[SQLBill]

AND LIKE '98%'

The percent (%) is a wildcard that means any amount of characters.

-SQLBill

 

[sunila7]

Hi,
Does this give wht u r looking ofr

select * from subled
Where (replace(object,'E',0) between 30000 and 50000 )

Sunil

 

[tlbroadbent]

worldchanger,

I think you've confused everyone. You haven't clearly stated the problem. In your second post, you said the comparison was "bailing out." I assume "bailing out" means the query aborts due to an error. It would have been helpful for everyone if you had said that up front.

If the Object column has a value of "98E35" then the column must be a character data type. You are comparing numeric ranges to a character column. SQL Server must convert the column values to a numeric data type to do the comparison. 98E35 connot be converted so the query would produce an error when that row is processed.

Sunil's solution should work but I recommend modifying the query to only select rows where the object value is numeric.
[tt]
WHERE IsNumeric(Object) = 1
And (object BETWEEN 30000 AND 39999
OR object BETWEEN 50000 AND 69999
OR object BETWEEN 80000 AND 89999)
...[/tt]

Here is an alternative method using character string comparisons. Use of the Len function is required so that values such as '31', '501', and '8234' are not selected. These values would not be selected when performing numeric comparisons but would be selected when performing string comparisons.
[tt]
WHERE Len(Object) = 5
And (object BETWEEN '30000' AND '39999'
OR object BETWEEN '50000' AND '69999'
OR object BETWEEN '80000' AND '89999')
...[/tt] If you want to get the best answer for your question read faq183-874 and faq183-3179.
Terry L. Broadbent - DBA
SQL Server Page:

http://tlbroadbent.home.attbi.com/sql/sql_articles.htm

 

[worldchanger]

That didn't seem to work. Sorry if I didn't explain it better. I'll play with it and see if I can get it to work. For now I just created a simple query to drop that one record.

I'm having a different problem however. I have further complicated the code and I am getting unexpected results. I'm sorry for all my questions. I'm new to SQL and and desperate to get this done. I have had a terrible time getting this code to work correctly. Here is the following code I'm trying to execute.


INSERT
INTO accounts
(account_code, desc_text,
dollars_maximum, dollars_expended, dollars_pre_encumbered, dollars_encumbered,account_fiscal_yr, account_type, account_class, dept_nbr_prefix, dept_nbr_suffix, active_flag, budgeted_dollars)
SELECT subled.fund + '-' + subled.department + '-' + subled.object + '-' + subled.sub_account AS account_code, subled.department_description + ' ' + subled.object_description AS desc_text, subled.appropriations_after_close AS dollars_maximum, subled.expenditures_after_close AS dollars_expended, subled.pre_encumbrances + subled.spa_pre_encumbrances + subled.check_requisition_pre_encumbrances AS dollars_pre_encumbered, subled.encumbrances_after_close AS dollars_encumbered,'03' AS account_fiscal_yr, 'A' AS account_type, '' AS account_class, '' AS dept_nbr_prefix,'' AS dept_nbr_suffix, 'A' AS active_flag, subled.appropriations_after_close AS budgeted_dollars
FROM subled LEFT JOIN
subdiv ON subled.sub_account = subdiv.sub_account and subled.object = subdiv.object AND subled.department = subdiv.department AND subled.fund = subdiv.fund
WHERE (subdiv.object IS NULL) AND (subled.object BETWEEN 30000 AND 39999 OR subled.object BETWEEN 50000 AND 69999 OR subled.object BETWEEN 80000 AND 89999) AND (subled.fund BETWEEN 200 AND 249 OR subled.fund = 434 OR subled.fund = 454 OR subled.fund = 455) AND (subled.category = 1 OR subled.category = 3)
order by account_code


Basically here is the run down. I have tables subled and subdiv. Subdiv has account codes with the object = '0000' If there is a matching account in subled I don't want it included. When I run the above command with just the select it seems to work, but the second I add the insert to the top the data seems to be doubled. Since I have see the account_code to be a primary key it won't run. If I dont' make it a pk it runs and completes, howerver all the data has two entries. But the odd thing is... when I run it without the insert it runs fine :-/

 

[tlbroadbent]

Is the Accounts table empty when you INSERT the new rows? If not that is probably the source of the double rows. If the select query doesn't return duplicates when run without the INSERT, then it won't return duplicates when run with the INSERT, unless you've uncovered a major bug in SQL Server. If you want to get the best answer for your question read faq183-874 and faq183-3179.
Terry L. Broadbent - DBA
SQL Server Page:

http://tlbroadbent.home.attbi.com/sql/sql_articles.htm

 

[sunila7]

Hi All,

I was trying wht this SQL returns. I tried it on both SQL 7 and SQL 2000. in both cases isnumeric returned 1.

select isnumeric('9E10')


This Looks to be a bug in SQL server. If u try to Cast or convert this string it returns an error

Sunil

 

[worldchanger]

Ok I fixed my repeating data problem. I needed to include one more category in my account_code, and in my comparison in order for it to become even more unique. However I still can't seem to filter out anything that has alphanumeric values.. Maybe it's a problem how I have my table setup. Currently my fund, department, object, sub_account are all varchar with respected lenghts of 3, 6, 5, 4. The object is the one that contains the one field with the 'E' in it. I was told by the city that this 'E' was valid and that there can and will be more coming. They also said there is not standard to how it is written, so it could be '98E35' or '9E835' and etc. Based on the above query are there any suggestions how I can get this accomplished?

 

[tlbroadbent]

Good pickup, Sunil. It is not a bug. SQL simply recognizes 9E10 as the scientific notation for 90000000000.0. I thought about that last night but couldn't quite pull my thoughts together. That helps explain the difficulty worldchanger had with the suggested code.

cast('9e10' as float) does work If you want to get the best answer for your question read faq183-874 and faq183-3179.
Terry L. Broadbent - DBA
SQL Server Page:

http://tlbroadbent.home.attbi.com/sql/sql_articles.htm

 

[sunila7]

Hi Terry,

I thougt the same that it is taking it to be the scientific notation, but still i thought that the string('9E10') can also be treated as a varchar.
Thanks for the info on casting as a float, I didnt try that, thought about it but forgot.

Sunil

 

[tlbroadbent]

Try one of these methods.

--eliminate the values containing 'E'
where Object Not Like '%E%' And IsNumeric(Object)=1
where patindex('%E%', Object)=0 And IsNumeric(Object)=1
where charindex('E', Object)=0 And IsNumeric(Object)=1

--Eliminate all alphabetic characters
where Object not like '%[a-z]%' And IsNumeric(Object)=1
where patindex('%[a-z]%', Object)=0 And IsNumeric(Object)=1

You still need to include the IsNumeric test in case other characters are used. If you want to get the best answer for your question read faq183-874 and faq183-3179.
Terry L. Broadbent - DBA
SQL Server Page:

http://tlbroadbent.home.attbi.com/sql/sql_articles.htm

 

[worldchanger]

What if I need to include it if the first number would be in my range I'm looking for??? Say it's 30E38. I would need to include it, not exclude it. Only reason I'm excluding it right now is because it's 98E35. The 90000 and up is out of my range.

 

[tlbroadbent]

We can't give you a query for every condition. You should be able to take the information in this thread and work through any scenario. You may need to use string comparisons as I mentioned in an earlier post. Now is the time to puut what you've learned to work. Don't be afraid to try new queries and techniques. If you want to get the best answer for your question read faq183-874 and faq183-3179.
Terry L. Broadbent - DBA
SQL Server Page:

http://tlbroadbent.home.attbi.com/sql/sql_articles.htm