SQL conditional execution in WHERE Clause

Simplified...
SELECT ContactID, SourceID
FROM Contacts
WHERE ContactID IS NOT NULL AND ISNUMERIC(SourceID) = 1 AND SourceID <> 0

Open in new window


Error
Conversion failed when converting the nvarchar value 'X' to data type int.

Open in new window


SourceID is nvarchar, contains both alph and numeric characters.  I expected the
SourceID <> 0

Open in new window

to not get executed due to the failure of the previous
ISNUMERIC(SourceID) = 1

Open in new window

 I have tried IF and CASE type solutions to no avail.

How do I handle this functionality?  MS SQL 2008R2

Thanks
Sam
SamCashAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

plusone3055Commented:
seems like your looking for

 
SELECT ContactID, SourceID
FROM Contacts
WHERE ContactID <> 0
AND  SourceID = '1' 

Open in new window


can you do a snapshot of
SELECT ContactID, SourceID
FROM Contacts
SamCashAuthor Commented:
Pluseone3055,

Please look at the WHERE clause...

1. ContactID cannot be NULL
2. SourseID must be numeric
3. IF SourseID is numeric THEN it also must not = 0

Thanks
Sam
Mike EghtebasDatabase and Application DeveloperCommented:
SELECT ContactID, SourceID
FROM Contacts
WHERE Exists(Select * From Contacts Where ContactID  is not null AND SourceID = '1' AND SourceID <> 0)

Open in new window

IT Pros Agree: AI and Machine Learning Key

We’d all like to think our company’s data is well protected, but when you ask IT professionals they admit the data probably is not as safe as it could be.

PortletPaulEE Topic AdvisorCommented:
even though you have used ISNUMERIC() you cannot treat that column as a number unless you convert it.

so

SELECT ContactID, SourceID
FROM Contacts
WHERE ContactID IS NOT NULL
AND ISNUMERIC(SourceID) = 1
AND SourceID <> '0'

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Mike EghtebasDatabase and Application DeveloperCommented:
correction:
SELECT ContactID, SourceID
FROM Contacts
WHERE Exists(Select * From Contacts Where ContactID  is not null AND SourceID = '1')

Open in new window


If you are asking for SourceID = '1' specifically, why bother with '0' comparison?  SourceID = '1' is already <>'0'
PortletPaulEE Topic AdvisorCommented:
eghtebas, SamCash has not asked for SourceID = '1'

In the original ISNUMERIC(SourceID) = 1 is asked for, that is an entirely different thing

e.g.

declare @sourceid varchar = '1234567890'
select ISNUMERIC(@sourceid)
the result is 1
isnumeric() returns
1 if the parameter can be treated as a number
  or
0 if the parameter cannot be treated as a number

however isnumeric() does not convert the paremeter into a number, it does nothing except return 1 or 0
SamCashAuthor Commented:
eghtebas,

... "ISNUMERIC(SourseID) = 1" ...  is a numeric test on SourseID, 0= false, 1= true... not

Thanks
Sam
Mike EghtebasDatabase and Application DeveloperCommented:
Thank you for the explanation. So, IsNumeric() returns Boolean.

SELECT ContactID, SourceID
FROM Contacts
WHERE Exists(Select * From Contacts Where ContactID  is not null AND (IsNumeric(SourceID) = 1 And SourceID='0'))

Open in new window

SamCashAuthor Commented:
Paul,

Worked!  Thanks for the solution.  

Two days chasing the wrong solution...

I am still curious if in SQL there is a way to write Boolean so not all is executed like c

Thanks Again
Sam
PortletPaulEE Topic AdvisorCommented:
Exactly, and now you have placed quotes around the zero too. However using EXISTS isn't necessary it just adds complexity, the only correction needed to the original where clause is to add the quotes.

SELECT ContactID, SourceID
FROM Contacts
WHERE ContactID IS NOT NULL
AND ISNUMERIC(SourceID) = 1
AND SourceID <> '0'                           --<< here

The original issue is that even though there is a test for ISNUMERIC() = 1, this test DOES NOT STOP evaluation of the ROW

e.g.

SourceID
'fred'
'12345'
'betty'


AND ISNUMERIC('fred') = 0 -- but the next test is still performed
AND SourceID = 0                --<< this cannot happen as 'fred' can't be converted to a number

in that example this would fall over on the first row

To put it another way, you could only achieve testing for <> zero by doing something like this:

SELECT ContactID, SourceID
FROM (
          SELECT ContactID, cast(SourceID as int) as SourceID
          FROM Contacts
          WHERE ContactID IS NOT NULL
          AND ISNUMERIC(SourceID) = 1
           ) as x
WHERE SourceId = 0
PortletPaulEE Topic AdvisorCommented:
>>"I am still curious if in SQL there is a way to write Boolean so not all is executed like c"
No

It is a mistake to think of SQL like a programming language. SQL does not proceed line by line of code.  The where clause is performed "per row" (of the resultset formed in the from clause), all conditions of the where are considered, and a row either passes those tests or it fails those tests. The tests are not necessarily performed in the sequence you write them either.

By the way, the FROM clause is performed first, just to reinforce that point. SQL isn't executed the way you write it.
SamCashAuthor Commented:
Paul,

Thanks Much

SAM
SamCashAuthor Commented:
Paul,

A follow up question, relating to the known issue with ISNUMERIC(nvarcharField) returning true on "." or "e" or '\' etc.

WHERE nvarcharField like '%[0-9]%' 

Open in new window

 Yields the correct result in my testing.  
I read in several places the proper test requires a double negative???  http://stackoverflow.com/questions/4603292/check-if-a-varchar-is-a-number-tsql see below.

WHERE nvarcharField not like '%[^0-9]%' 

Open in new window

Yields an incorrect result, by including all zero length strings, which are not digits.

Thanks
SAM
PortletPaulEE Topic AdvisorCommented:
Sam, you should create a new question. (i.e. in general please don't assume we watch old questions)

It is true there are faults in the ISNUMERIC() function.
If those faults are going to affect you then try the negation should be in this form

AND NOT ( [column] LIKE '%[^0-9]%' )

i.e. place the NOT in front of the column, instead of in front of "like"
      & the parentheses are optional just there as a highlight
SamCashAuthor Commented:
Paul,

I will create a new question if I do not hear from you on this one.  And always create a new question for followups in the furute.

I am still getting all rows returned with a '' (empty string or zls).  Of course I can add

AND column <> ''

But it seems like I should do it in the like statement.  I also see a a debate on weather SQL has regexp or not?

Thanks for the assistance!
SAM
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.