469,946 Members | 1,826 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,946 developers. It's quick & easy.

Need help with SQL Statement checking for nulls

88
Hi,

I have a table with over 20 columns. Seven of these of these columns correspond to a filter on a search form. Based on what is selected on the search form, and what is in these seven fields in the table, it indicates whether or not I pull the record. Thing is....is that not all the records use the same combination of these seven fields. One might only use the first two and the other five will be null, or one might use the middle three and the other four will be null. However, if any of these fields match up to its corresponding filter on the form, even if the others are null (because if a field is NULL it applies to all), I need to pull it.

I started out trying to do this in VB code and executing a regular select statment for each combination, but I quickly realized that this is not good as it will mean a ton of code to check for each field combination that is possible.

So, I'm trying to put together a single SQL Statment that I will execute on the SQL Server that will check if each of these fields is null before trying to do a comparrison on it. I don't know if this can be done and I am very new to the syntax for SQL Server queries. Here is what I have so far just trying to do the check on one of the columns;

Expand|Select|Wrap|Line Numbers
  1. SELECT [RecordNumber]
  2.       ,[Lender]
  3.       ,[LoanName]
  4.       ,[Adjustment]
  5. FROM [Website].[dbo].[Adjustments]
  6. WHERE [Website].[dbo].[Adjustments].[Lender] = 'Test'
  7. AND [Website].[dbo].[Adjustments].[LoanName] = 'Testing'
  8. IF [Website].[dbo].[Adjustments].[Units] IS NOT NULL THEN
  9.     AND [Website].[dbo].[Adjustments].[Units] = 2
  10.  
If I try to run this I get a message saying;

incorrect synax near 'THEN'

So I remove that and get

incorrect syntax near '2'

Is this possible or am I way out in left field?
Jan 22 '08 #1
2 1213
ck9663
2,878 Expert 2GB
Hi,

I have a table with over 20 columns. Seven of these of these columns correspond to a filter on a search form. Based on what is selected on the search form, and what is in these seven fields in the table, it indicates whether or not I pull the record. Thing is....is that not all the records use the same combination of these seven fields. One might only use the first two and the other five will be null, or one might use the middle three and the other four will be null. However, if any of these fields match up to its corresponding filter on the form, even if the others are null (because if a field is NULL it applies to all), I need to pull it.

I started out trying to do this in VB code and executing a regular select statment for each combination, but I quickly realized that this is not good as it will mean a ton of code to check for each field combination that is possible.

So, I'm trying to put together a single SQL Statment that I will execute on the SQL Server that will check if each of these fields is null before trying to do a comparrison on it. I don't know if this can be done and I am very new to the syntax for SQL Server queries. Here is what I have so far just trying to do the check on one of the columns;

Expand|Select|Wrap|Line Numbers
  1. SELECT [RecordNumber]
  2.       ,[Lender]
  3.       ,[LoanName]
  4.       ,[Adjustment]
  5. FROM [Website].[dbo].[Adjustments]
  6. WHERE [Website].[dbo].[Adjustments].[Lender] = 'Test'
  7. AND [Website].[dbo].[Adjustments].[LoanName] = 'Testing'
  8. IF [Website].[dbo].[Adjustments].[Units] IS NOT NULL THEN
  9.     AND [Website].[dbo].[Adjustments].[Units] = 2
  10.  
If I try to run this I get a message saying;

incorrect synax near 'THEN'

So I remove that and get

incorrect syntax near '2'

Is this possible or am I way out in left field?

first your error. sql server IF condition does not have the THEN (read here.

about your search condition, can the operator OR handle it?

-CK
Jan 22 '08 #2
fperri
88
first your error. sql server IF condition does not have the THEN (read here.

about your search condition, can the operator OR handle it?

-CK
Never thought about OR, I'll give it a try.
Jan 22 '08 #3

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

2 posts views Thread by rdshultz | last post: by
8 posts views Thread by Brian Basquille | last post: by
4 posts views Thread by Tony WONG | last post: by
2 posts views Thread by bbhushan44 | last post: by
1 post views Thread by Dennis Hartmann | last post: by
7 posts views Thread by tshad | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.