26 Maret 2012 17:26
So I want to create a function called MissingInfo. I want to run that function against a table called tblAppointments. Here is the structure of tblAppointments:
AppntID :Primary Key, int, Allow Nulls: No
MeetPlce: , smallint, Allow Nulls: Yes
MeetTime: , smallint, Allow Nulls: Yes
MeetChng: , smallint, Allow Nulls: Yes
LblTab: ,smallint, Allow Nulls: Yes
BLblTab: ,smallint, Allow Nulls: Yes
CreateBy , nchar(40): Allow Nulls: Yes
Date: ,datetime, Allow Nulls: Yes
So every time a client has a meeting, they are entered in the system with the following info. Every one that has an ApptID, has a MeetPlce, and if the MeetPlc has a value, the MeetTime is also supposed to have a corresponding value.
Since MeetTime and MeetPlc are allowed null, some rows have a value for MeetPlc without the corresponding value for MeetTime, and MeetTime has a value of NULL. I basically want to write a function that can pull only the rows that have a value for MeetPlc without a value for MeetTime(NULL) and like wise for BLblTab(NULL). If LblTab has a value the BLblTab has to have a corresponding value as well and not NULL.
I only want to pull the rows for where MeetPlc, LblTab is Not NULL and basically has a value and their corresponding columns MeetTime and BLblTab does NOT have a value or a value of NULL.
Is there a way to do this. I have tried the where Not Exist (Select Null) but that brings up almost every row in the table since so many columns allow NULL values.
So far I am stuck on this part, here is what I have so far
Select * from tblAppointments
Where Not Exists (Select Null) from tblAppointments.
As you probably can tell my query does not pull the specific information I need. Any help would be greatly appreciated. Thanking you in advance for your time!
28 Maret 2012 2:37
declare @tblAppointments table (AppntID int,MeetPlce int,MeetTime datetime,LblTab int,BLblTab int) insert into @tblAppointments values (1,NULL,NULL,1,NULL) insert into @tblAppointments values (2,1,NULL,1,NULL) insert into @tblAppointments values (3,NULL,'19000101',NULL,1) insert into @tblAppointments values (4,NULL,NULL,NULL,1) insert into @tblAppointments values (5,1,'19000101',1,NULL) insert into @tblAppointments values (6,1,'19000101',1,1) select * from @tblAppointments where (MeetPlce is not null and MeetTime is null) or (LblTab is not null and BLblTab is null)
If it's not what you want, please show us some sample data and your expected result.
Please remember to mark the replies as answers if they help and unmark them if they provide no help.
Get or Request Code Sample from Microsoft
If you have any feedback, please tell us.
- Ditandai sebagai Jawaban oleh Iric WenModerator 02 April 2012 1:54
29 Maret 2012 15:13Please post your sample data, table structure and expected output, This will help us a lot.