Solved

T-SQL: Why does "select null or 1" elicit a syntax error?

Posted on 2010-11-16
3
361 Views
Last Modified: 2012-08-13
I'm trying to manually test to see if part of my WHERE clause will work correctly:
... and (ColA=1 or ColB is null) ...
and it's likely that ColA will be null.

Question: HOW can I test this MANUALLY "at the console" with a simple select statement?

I tried something like this and doesn't work... it returns a syntax error:
select null or 1
to illustrate ColA being null and ColB NOT being null.   I want to "prove" this will evaluate TRUE (null or TRUE => TRUE).
0
Comment
Question by:ZuZuPetals
  • 2
3 Comments
 
LVL 58

Accepted Solution

by:
cyberkiwi earned 500 total points
ID: 34151111
this is a query that tests your condition (mock)

select 1
where (null = 1 or 1 is null)

here if the where condition is true it returns 1 otherwise no result
see cola replaced by null and colb by 1
0
 
LVL 2

Author Comment

by:ZuZuPetals
ID: 34151152
Ah, that helps.  I was trying to do my "console" statement in the same form as
select 2+2
Guess that's not valid SQL!
0
 
LVL 58

Expert Comment

by:cyberkiwi
ID: 34151216
select 2+2 does work
But
select (null=1)
for example is hanging.. it returns a condition, but what is it doing with it?
So we make a where clause out of it
0

Featured Post

Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Recently, when I was asked to create a new SQL 2005 cluster, Microsoft released a new service pack for MS SQL 2005 what is Service Pack 3. When I finished the installation of MS SQL 2005 I found myself troubled why the installation of SP3 failed …
There are some very powerful Data Management Views (DMV's) introduced with SQL 2005. The two in particular that we are going to discuss are sys.dm_db_index_usage_stats and sys.dm_db_index_operational_stats.   Recently, I was involved in a discu…
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…

749 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question