Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 isnull(datefield,'') problem

Author  Topic 

rajasekhar857
Constraint Violating Yak Guru

396 Posts
Posted - 2009-12-14 : 08:43:36
Hi,

when ever i am using isnull(datefield,'') in cursor giving causing problem in stored procedure.

like
Declare C1 Cursor Static for Select ISNULL(LAST_PERIOD_DATE,'') from x giving 01-01-1900 as values.

tried out in if condition what needs to be added for this for avoiding inserting 01-01-1900 for each null value.
IF( @LAST_PERIOD_DATE!='' OR LEN(RTRIM(LTRIM(@LAST_PERIOD_DATE)))>4 )

so whereever null value or rmpty value is there their records won't get inserted.

jimf
Master Smack Fu Yak Hacker

2875 Posts
Posted - 2009-12-14 : 08:50:07
You probably don't need a cursor to do what you're trying. You do need to supply a date value for your isnull statement
ISNULL(LAST_PERIOD_DATE,'12/31/9999') and then filter on that

Jim

Everyday I learn something that somebody else already knew
Go to Top of Page

DP978
Constraint Violating Yak Guru

269 Posts
Posted - 2009-12-14 : 08:51:05
What type of column are you sending the ISNULL() statement to? If that field is a datetime you will get an error because '' is not a valid item.
Go to Top of Page

madhivanan
Premature Yak Congratulator

22864 Posts
Posted - 2009-12-14 : 08:52:29
What do you want to get if datefield is null?


Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

rajasekhar857
Constraint Violating Yak Guru

396 Posts
Posted - 2009-12-14 : 08:57:19
mine is a datetime field only.what needs to be replaced there for solving this as i don't want to insert records having null valuesa that ate probably 01-01-1900
Go to Top of Page

rajasekhar857
Constraint Violating Yak Guru

396 Posts
Posted - 2009-12-14 : 09:01:11 


if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Migration_FemalePATIENTDETAILS]') AND type = 'P')
drop Procedure [dbo].[Migration_FemalePATIENTDETAILS]
GO

CREATE PROCEDURE [dbo].[Migration_FemalePATIENTDETAILS] AS
Declare
 @X numeric(20,0),
 @Y numeric(20,0),
 @Pre_hj numeric(20,0),
 @IS_MENARCH Varchar(50),
 @MENARCH_AGE Varchar(50),
 @Patient_id Varchar(50),
 @Encounter_id numeric(20,0),
 @IS_MENOPAUSE numeric(10,0),
 @idValue varchar(50),
 @xy varchar(50),
 @MENOPAUSE_AGE varchar(20),
 @IS_PAP numeric(10,0),
 @LAST_PAP_DATE datetime,
 @PAP_RESULT Varchar(255),
 @IS_MAM Varchar(1),
 @LAST_MAM_DATE datetime,
 @MAM_RESULT Varchar(255),
 @LAST_PERIOD_DATE Datetime,
 @IS_CONTRACEPTION numeric(20,0),
 @ISCONTRACEPTIONOPT varchar(50),
 @ISIUCD varchar(50),
 @ISSTERILISED numeric(20,0),
 @ISSTERILISEDOPT varchar(50),
 @new_var varchar(30),
 @created varchar(50)
Begin
  Declare C1 Cursor Static for Select IS_MENARCH,isnull(MENARCH_AGE,'') AS MENARCH_AGE,IS_MENOPAUSE,isnull(MENOPAUSE_AGE,'') AS MENOPAUSE_AGE,IS_PAP,ISNULL(LAST_PAP_DATE,'') AS LAST_PAP_DATE,ISNULL(PAP_RESULT,'') AS PAP_RESULT,
                IS_MAM,ISNULL(LAST_MAM_DATE,'') AS LAST_MAM_DATE,ISNULL(MAM_RESULT,'') AS MAM_RESULT,ISNULL(LAST_PERIOD_DATE,'') AS LAST_PERIOD_DATE,ISCONTRACEPTION,ISCONTRACEPTIONOPT,ISIUCD,ISSTERILISED,ISSTERILISEDOPT,
                patient_id,encounter_id from EMRFemalePatientDetails
  SELECT  @Pre_hj=Prefix_id from EMRLocationMaster where Location_id=1501
     OPEN C1
       FETCH NEXT FROM C1 INTO @IS_MENARCH,@MENARCH_AGE,@IS_MENOPAUSE,@MENOPAUSE_AGE,@IS_PAP,@LAST_PAP_DATE,@PAP_RESULT,
              @IS_MAM,@LAST_MAM_DATE,@MAM_RESULT,@LAST_PERIOD_DATE,@IS_CONTRACEPTION,@ISCONTRACEPTIONOPT,@ISIUCD,@ISSTERILISED,@ISSTERILISEDOPT,
               @Patient_id,@Encounter_id
           WHILE @@FETCH_Status = 0   
          	BEGIN
        	select @X=ezEMRxID from emrids where property_name = 'FR_EXAM_DATA_ID'
		Update EMRIDS set ezEMRxID=@X+1 WHERE PROPERTY_NAME='FR_EXAM_DATA_ID'
		SET @xy =ISNULL(Rtrim(CAST(@Pre_hj as Varchar)),'') + ISNULL(Rtrim(Cast(@X as varchar)),'')
              	INSERT INTO EMRFREXAMDATA(FR_EXAM_DATA_ID,PATIENT_ID,ENCOUNTER_ID) SELECT @xy,@Patient_id,@Encounter_id
        	IF (@IS_MENARCH='1')
        		begin
                 		select @Y= ezEMRxID FROM EMRIDS WHERE PROPERTY_NAME='FR_EXAM_ANS_ID'
                  		Update EMRIDS set ezEMRxID=@Y+1 WHERE PROPERTY_NAME='FR_EXAM_ANS_ID'
				SET @idValue = ISNULL(Rtrim(CAST(@Pre_hj as Varchar)),'') + ISNULL(Rtrim(Cast(@Y as varchar)),'')
                   		INSERT INTO EMRFRExamAns(FR_EXAM_ANS_ID,FR_EXAM_DATA_ID,QUES_ID,QUES_ANS_ID,CTRL_VALUE,DISP_LABEL) VALUES(@idValue,@xy,1001,1001,'YES',NULL)
				SET @idValue = ''
                  	IF( @MENARCH_AGE!='' OR LEN(LTRIM(RTRIM(@MENARCH_AGE)))>0)
                     -- set @created=convert(varchar(35),@MENARCH_AGE,105)
                      begin   
						SELECT @Y= ezEMRxID FROM EMRIDS WHERE PROPERTY_NAME='FR_EXAM_ANS_ID'
						Update EMRIDS set ezEMRxID=@Y+1 WHERE PROPERTY_NAME='FR_EXAM_ANS_ID'                  
						SET @idValue = ISNULL(Rtrim(CAST(@Pre_hj as Varchar)),'') + ISNULL(Rtrim(Cast(@Y as varchar)),'')
						INSERT INTO EMRFRExamAns(FR_EXAM_ANS_ID,FR_EXAM_DATA_ID,QUES_ID,QUES_ANS_ID,CTRL_VALUE,DISP_LABEL) VALUES(@idValue,@xy ,1001,1001,@MENARCH_AGE ,'Age')
						SET @idValue = ''  
                       -- SET @created=''
                   end        		
        		end
        		
        	IF(@IS_MENOPAUSE=1)
        		begin
                  		SELECT @Y= ezEMRxID FROM EMRIDS WHERE PROPERTY_NAME='FR_EXAM_ANS_ID'
                   		Update EMRIDS set ezEMRxID=@Y+1 WHERE PROPERTY_NAME='FR_EXAM_ANS_ID'
                   		SET @idValue = ISNULL(Rtrim(CAST(@Pre_hj as Varchar)),'') + ISNULL(Rtrim(Cast(@Y as varchar)),'')
                   		INSERT INTO EMRFRExamAns(FR_EXAM_ANS_ID,FR_EXAM_DATA_ID,QUES_ID,QUES_ANS_ID,CTRL_VALUE,DISP_LABEL) VALUES(@idValue,@xy ,1002,1003,'YES',NULL)
                  		SET @idValue = ''               	
        	                IF(@MENOPAUSE_AGE!=''OR LEN(LTRIM(RTRIM(@MENOPAUSE_AGE)))>0)
                            --  set @created=convert(Varchar(50),@MENOPAUSE_AGE,105)
                             begin
						    SELECT @Y= ezEMRxID FROM EMRIDS WHERE PROPERTY_NAME='FR_EXAM_ANS_ID'
		                   		Update EMRIDS set ezEMRxID=@Y+1 WHERE PROPERTY_NAME='FR_EXAM_ANS_ID'        	                		
		                   		SET @idValue = ISNULL(Rtrim(CAST(@Pre_hj as Varchar)),'') + ISNULL(Rtrim(Cast(@Y as varchar)),'')
		                   		INSERT INTO EMRFRExamAns(FR_EXAM_ANS_ID,FR_EXAM_DATA_ID,QUES_ID,QUES_ANS_ID,CTRL_VALUE,DISP_LABEL) VALUES(@idValue,@xy ,1002,1003,@MENOPAUSE_AGE,'Age')
		                   		SET @idValue = ''	
                                -- set @created=''               		
        	               end		                   		                          		        		        		        	
        		end
		IF(@IS_PAP=1)
			begin
                  		SELECT @Y= ezEMRxID FROM EMRIDS WHERE PROPERTY_NAME='FR_EXAM_ANS_ID'
                   		Update EMRIDS set ezEMRxID=@Y+1 WHERE PROPERTY_NAME='FR_EXAM_ANS_ID'
                   		SET @idValue = ISNULL(Rtrim(CAST(@Pre_hj as Varchar)),'') + ISNULL(Rtrim(Cast(@Y as varchar)),'')
			        INSERT INTO EMRFRExamAns(FR_EXAM_ANS_ID,FR_EXAM_DATA_ID,QUES_ID,QUES_ANS_ID,CTRL_VALUE,DISP_LABEL) VALUES(@idValue,@xy ,1003,1005,'YES',NULL)
			        SET @idValue = ''
			        IF( @LAST_PAP_DATE!='' OR  LEN(LTRIM(RTRIM(@LAST_PAP_DATE)))>0 )
                      set @created=convert(varchar(35),@LAST_PAP_DATE,105)
			        	begin
		                  		SELECT @Y= ezEMRxID FROM EMRIDS WHERE PROPERTY_NAME='FR_EXAM_ANS_ID'
		                   		Update EMRIDS set ezEMRxID=@Y+1 WHERE PROPERTY_NAME='FR_EXAM_ANS_ID'
		                   		SET @idValue = ISNULL(Rtrim(CAST(@Pre_hj as Varchar)),'') + ISNULL(Rtrim(Cast(@Y as varchar)),'')
				                INSERT INTO EMRFRExamAns(FR_EXAM_ANS_ID,FR_EXAM_DATA_ID,QUES_ID,QUES_ANS_ID,CTRL_VALUE,DISP_LABEL) VALUES(@idValue,@xy ,1003,1005,@created,'Date')
				                SET @idValue = ''
                             	set @created=''       	
			        	end
			        IF( @PAP_RESULT!='')
                        
			        	begin
		                  		SELECT @Y= ezEMRxID FROM EMRIDS WHERE PROPERTY_NAME='FR_EXAM_ANS_ID'
		                   		Update EMRIDS set ezEMRxID=@Y+1 WHERE PROPERTY_NAME='FR_EXAM_ANS_ID'
		                   		SET @idValue = ISNULL(Rtrim(CAST(@Pre_hj as Varchar)),'') + ISNULL(Rtrim(Cast(@Y as varchar)),'')
				                INSERT INTO EMRFRExamAns(FR_EXAM_ANS_ID,FR_EXAM_DATA_ID,QUES_ID,QUES_ANS_ID,CTRL_VALUE,DISP_LABEL) VALUES(@idValue,@xy ,1003,1005,@PAP_RESULT,'Result')
				                SET @idValue = ''
                            		        				        	
			        	end
			end        		
		IF(@IS_MAM='1')        		
        		begin
				SELECT @Y= ezEMRxID FROM EMRIDS WHERE PROPERTY_NAME='FR_EXAM_ANS_ID'
				Update EMRIDS set ezEMRxID=@Y+1 WHERE PROPERTY_NAME='FR_EXAM_ANS_ID'
				SET @idValue = ISNULL(Rtrim(CAST(@Pre_hj as Varchar)),'') + ISNULL(Rtrim(Cast(@Y as varchar)),'')
				INSERT INTO EMRFRExamAns(FR_EXAM_ANS_ID,FR_EXAM_DATA_ID,QUES_ID,QUES_ANS_ID,CTRL_VALUE,DISP_LABEL) VALUES(@idValue,@xy ,1004,1007,'YES',NULL)
				SET @idValue = ''	
			        IF( @LAST_MAM_DATE!=''OR LEN(LTRIM(RTRIM(@LAST_MAM_DATE)))>0)
                           set @created=convert(varchar(35),@LAST_MAM_DATE,105)
			        	begin
		                  		SELECT @Y= ezEMRxID FROM EMRIDS WHERE PROPERTY_NAME='FR_EXAM_ANS_ID'
		                   		Update EMRIDS set ezEMRxID=@Y+1 WHERE PROPERTY_NAME='FR_EXAM_ANS_ID'
		                   		SET @idValue = ISNULL(Rtrim(CAST(@Pre_hj as Varchar)),'') + ISNULL(Rtrim(Cast(@Y as varchar)),'')
				                INSERT INTO EMRFRExamAns(FR_EXAM_ANS_ID,FR_EXAM_DATA_ID,QUES_ID,QUES_ANS_ID,CTRL_VALUE,DISP_LABEL) VALUES(@idValue,@xy ,1004,1007,@created,'Date')
				                SET @idValue = ''
                               	set @created=''	        	
			        	end
			        IF( @MAM_RESULT!='')
                       
			        	begin
		                  		SELECT @Y= ezEMRxID FROM EMRIDS WHERE PROPERTY_NAME='FR_EXAM_ANS_ID'
		                   		Update EMRIDS set ezEMRxID=@Y+1 WHERE PROPERTY_NAME='FR_EXAM_ANS_ID'
		                   		SET @idValue = ISNULL(Rtrim(CAST(@Pre_hj as Varchar)),'') + ISNULL(Rtrim(Cast(@Y as varchar)),'')
				                INSERT INTO EMRFRExamAns(FR_EXAM_ANS_ID,FR_EXAM_DATA_ID,QUES_ID,QUES_ANS_ID,CTRL_VALUE,DISP_LABEL) VALUES(@idValue,@xy ,1004,1007,@MAM_RESULT,'Result')
				                SET @idValue = ''
                               		        				        	
			        	end				
        		end
		IF( @LAST_PERIOD_DATE!=''  OR LEN(RTRIM(LTRIM(@LAST_PERIOD_DATE)))>4 )
           set @created=convert(Varchar(35),@LAST_PERIOD_DATE,105)
			begin
				SELECT @Y= ezEMRxID FROM EMRIDS WHERE PROPERTY_NAME='FR_EXAM_ANS_ID'
				Update EMRIDS set ezEMRxID=@Y+1 WHERE PROPERTY_NAME='FR_EXAM_ANS_ID'
				SET @idValue = ISNULL(Rtrim(CAST(@Pre_hj as Varchar)),'') + ISNULL(Rtrim(Cast(@Y as varchar)),'')
				INSERT INTO EMRFRExamAns(FR_EXAM_ANS_ID,FR_EXAM_DATA_ID,QUES_ID,QUES_ANS_ID,CTRL_VALUE,DISP_LABEL) VALUES(@idValue,@xy ,1000,1000,@created,'Date')
				SET @idValue = ''	
               set @created=''	        				        	
			end	
			
               IF(@IS_CONTRACEPTION='1')
               		begin
				SELECT @Y= ezEMRxID FROM EMRIDS WHERE PROPERTY_NAME='FR_EXAM_ANS_ID'
				Update EMRIDS set ezEMRxID=@Y+1 WHERE PROPERTY_NAME='FR_EXAM_ANS_ID'
				SET @idValue = ISNULL(Rtrim(CAST(@Pre_hj as Varchar)),'') + ISNULL(Rtrim(Cast(@Y as varchar)),'')
				INSERT INTO EMRFRExamAns(FR_EXAM_ANS_ID,FR_EXAM_DATA_ID,QUES_ID,QUES_ANS_ID,CTRL_VALUE,DISP_LABEL) VALUES(@idValue,@xy ,1018,1034,'YES',NULL)
				SET @idValue = ''			        				        	               		
				IF(@ISCONTRACEPTIONOPT!=NULL)
                    begin
						if (charindex('oral', @ISCONTRACEPTIONOPT)>=1)
							begin
								SELECT @Y= ezEMRxID FROM EMRIDS WHERE PROPERTY_NAME='FR_EXAM_ANS_ID'
								Update EMRIDS set ezEMRxID=@Y+1 WHERE PROPERTY_NAME='FR_EXAM_ANS_ID'
								SET @idValue = ISNULL(Rtrim(CAST(@Pre_hj as Varchar)),'') + ISNULL(Rtrim(Cast(@Y as varchar)),'')
								INSERT INTO EMRFRExamAns(FR_EXAM_ANS_ID,FR_EXAM_DATA_ID,QUES_ID,QUES_ANS_ID,CTRL_VALUE,DISP_LABEL) VALUES(@idValue,@xy ,1018,1046,'Oral Contraceptive Pills',NULL)
								SET @idValue = ''			        				        	               									
							end
						if (charindex('hormonalinj', @ISCONTRACEPTIONOPT)>=1)
							begin
								SELECT @Y= ezEMRxID FROM EMRIDS WHERE PROPERTY_NAME='FR_EXAM_ANS_ID'
								Update EMRIDS set ezEMRxID=@Y+1 WHERE PROPERTY_NAME='FR_EXAM_ANS_ID'
								SET @idValue = ISNULL(Rtrim(CAST(@Pre_hj as Varchar)),'') + ISNULL(Rtrim(Cast(@Y as varchar)),'')
								INSERT INTO EMRFRExamAns(FR_EXAM_ANS_ID,FR_EXAM_DATA_ID,QUES_ID,QUES_ANS_ID,CTRL_VALUE,DISP_LABEL) VALUES(@idValue,@xy ,1018,1047,'Hormonal Injectables(Long Acting)',NULL)
								SET @idValue = ''			        				        	               																
							end
						if (charindex('condoms', @ISCONTRACEPTIONOPT)>=1)
							begin
								SELECT @Y= ezEMRxID FROM EMRIDS WHERE PROPERTY_NAME='FR_EXAM_ANS_ID'
								Update EMRIDS set ezEMRxID=@Y+1 WHERE PROPERTY_NAME='FR_EXAM_ANS_ID'
								SET @idValue = ISNULL(Rtrim(CAST(@Pre_hj as Varchar)),'') + ISNULL(Rtrim(Cast(@Y as varchar)),'')
								INSERT INTO EMRFRExamAns(FR_EXAM_ANS_ID,FR_EXAM_DATA_ID,QUES_ID,QUES_ANS_ID,CTRL_VALUE,DISP_LABEL) VALUES(@idValue,@xy ,1018,1046,'Condoms',NULL)
								SET @idValue = ''			        				        	               																
							end
						if (charindex('iucd', @ISCONTRACEPTIONOPT)>=1)
							begin
								SELECT @Y= ezEMRxID FROM EMRIDS WHERE PROPERTY_NAME='FR_EXAM_ANS_ID'
								Update EMRIDS set ezEMRxID=@Y+1 WHERE PROPERTY_NAME='FR_EXAM_ANS_ID'
								SET @idValue = ISNULL(Rtrim(CAST(@Pre_hj as Varchar)),'') + ISNULL(Rtrim(Cast(@Y as varchar)),'')
								INSERT INTO EMRFRExamAns(FR_EXAM_ANS_ID,FR_EXAM_DATA_ID,QUES_ID,QUES_ANS_ID,CTRL_VALUE,DISP_LABEL) VALUES(@idValue,@xy ,1018,1047,'IUCD',NULL)
								SET @idValue = ''
								if (charindex('cute', @ISIUCD)>=1)
									begin
										SELECT @Y= ezEMRxID FROM EMRIDS WHERE PROPERTY_NAME='FR_EXAM_ANS_ID'
										Update EMRIDS set ezEMRxID=@Y+1 WHERE PROPERTY_NAME='FR_EXAM_ANS_ID'
										SET @idValue = ISNULL(Rtrim(CAST(@Pre_hj as Varchar)),'') + ISNULL(Rtrim(Cast(@Y as varchar)),'')
										INSERT INTO EMRFRExamAns(FR_EXAM_ANS_ID,FR_EXAM_DATA_ID,QUES_ID,QUES_ANS_ID,CTRL_VALUE,DISP_LABEL) VALUES(@idValue,@xy ,1018,1052,'Cu-T',NULL)
										SET @idValue = ''									
									end
								if (charindex('multiload', @ISIUCD)>=1)
									begin
										SELECT @Y= ezEMRxID FROM EMRIDS WHERE PROPERTY_NAME='FR_EXAM_ANS_ID'
										Update EMRIDS set ezEMRxID=@Y+1 WHERE PROPERTY_NAME='FR_EXAM_ANS_ID'
										SET @idValue = ISNULL(Rtrim(CAST(@Pre_hj as Varchar)),'') + ISNULL(Rtrim(Cast(@Y as varchar)),'')
										INSERT INTO EMRFRExamAns(FR_EXAM_ANS_ID,FR_EXAM_DATA_ID,QUES_ID,QUES_ANS_ID,CTRL_VALUE,DISP_LABEL) VALUES(@idValue,@xy ,1018,1053,'Multi Load',NULL)
										SET @idValue = ''									
									end
								if (charindex('hormonal', @ISIUCD)>=1)
									begin
										SELECT @Y= ezEMRxID FROM EMRIDS WHERE PROPERTY_NAME='FR_EXAM_ANS_ID'
										Update EMRIDS set ezEMRxID=@Y+1 WHERE PROPERTY_NAME='FR_EXAM_ANS_ID'
										SET @idValue = ISNULL(Rtrim(CAST(@Pre_hj as Varchar)),'') + ISNULL(Rtrim(Cast(@Y as varchar)),'')
										INSERT INTO EMRFRExamAns(FR_EXAM_ANS_ID,FR_EXAM_DATA_ID,QUES_ID,QUES_ANS_ID,CTRL_VALUE,DISP_LABEL) VALUES(@idValue,@xy ,1018,1054,'Hormonal',NULL)
										SET @idValue = ''									
									end

							end
						if (charindex('contubectomy', @ISCONTRACEPTIONOPT)>=1)
							begin
								SELECT @Y= ezEMRxID FROM EMRIDS WHERE PROPERTY_NAME='FR_EXAM_ANS_ID'
								Update EMRIDS set ezEMRxID=@Y+1 WHERE PROPERTY_NAME='FR_EXAM_ANS_ID'
							    SET @idValue = ISNULL(Rtrim(CAST(@Pre_hj as Varchar)),'') + ISNULL(Rtrim(Cast(@Y as varchar)),'')
								INSERT INTO EMRFRExamAns(FR_EXAM_ANS_ID,FR_EXAM_DATA_ID,QUES_ID,QUES_ANS_ID,CTRL_VALUE,DISP_LABEL) VALUES(@idValue,@xy ,1018,1046,'Tubectomy',NULL)
								SET @idValue = ''			        				        	               																
							end
						if (charindex('natural', @ISCONTRACEPTIONOPT)>=1)
							begin
								SELECT @Y= ezEMRxID FROM EMRIDS WHERE PROPERTY_NAME='FR_EXAM_ANS_ID'
								Update EMRIDS set ezEMRxID=@Y+1 WHERE PROPERTY_NAME='FR_EXAM_ANS_ID'
								SET @idValue = ISNULL(Rtrim(CAST(@Pre_hj as Varchar)),'') + ISNULL(Rtrim(Cast(@Y as varchar)),'')
								INSERT INTO EMRFRExamAns(FR_EXAM_ANS_ID,FR_EXAM_DATA_ID,QUES_ID,QUES_ANS_ID,CTRL_VALUE,DISP_LABEL) VALUES(@idValue,@xy ,1018,1047,'Natural Methods',NULL)
								SET @idValue = ''			        				        	               																
							end							
					end
               		end	               		               		                              		
               IF(@ISSTERILISED='1')
               		begin
				SELECT @Y= ezEMRxID FROM EMRIDS WHERE PROPERTY_NAME='FR_EXAM_ANS_ID'
				Update EMRIDS set ezEMRxID=@Y+1 WHERE PROPERTY_NAME='FR_EXAM_ANS_ID'
				SET @idValue = ISNULL(Rtrim(CAST(@Pre_hj as Varchar)),'') + ISNULL(Rtrim(Cast(@Y as varchar)),'')
				INSERT INTO EMRFRExamAns(FR_EXAM_ANS_ID,FR_EXAM_DATA_ID,QUES_ID,QUES_ANS_ID,CTRL_VALUE,DISP_LABEL) VALUES(@idValue,@xy ,1019,1036,'YES',NULL)
				SET @idValue = ''			        				        	               		
				IF(@ISSTERILISEDOPT!=NULL)
					begin
						if (charindex('tubectomy', @ISSTERILISEDOPT)>=1)
							begin
								SELECT @Y= ezEMRxID FROM EMRIDS WHERE PROPERTY_NAME='FR_EXAM_ANS_ID'
								Update EMRIDS set ezEMRxID=@Y+1 WHERE PROPERTY_NAME='FR_EXAM_ANS_ID'
								SET @idValue = ISNULL(Rtrim(CAST(@Pre_hj as Varchar)),'') + ISNULL(Rtrim(Cast(@Y as varchar)),'')
								INSERT INTO EMRFRExamAns(FR_EXAM_ANS_ID,FR_EXAM_DATA_ID,QUES_ID,QUES_ANS_ID,CTRL_VALUE,DISP_LABEL) VALUES(@idValue,@xy ,1019,1046,'Tubectomy',NULL)
								SET @idValue = ''			        				        	               									
							end
						
					end
               		end               		               		
          	FETCH NEXT FROM C1 INTO @IS_MENARCH,@MENARCH_AGE,@IS_MENOPAUSE,@MENOPAUSE_AGE,@IS_PAP,@LAST_PAP_DATE,@PAP_RESULT,
                     @IS_MAM,@LAST_MAM_DATE,@MAM_RESULT,@LAST_PERIOD_DATE,@IS_CONTRACEPTION,@ISCONTRACEPTIONOPT,@ISIUCD,@ISSTERILISED,@ISSTERILISEDOPT,
                     @Patient_id,@Encounter_id
        	END
       CLOSE C1 
    DEALLOCATE C1
END
GO

--EXEC Migration_FemalePATIENTDETAILS
--GO

/*DELETE FROM EMRFREXAMDATA
GO
DELETE FROM EMRFREXAMANS
GO
SELECT * FROM EMRFREXAMDATA

SELECT * FROM EMRFREXAMANS where ques_id=1002 and ques_ans_id=1003

select * from emrfemalepatientdetails WHERE PATIENT_ID=802592

Select IS_MENARCH,MENARCH_AGE,IS_MENOPAUSE,MENOPAUSE_AGE E,IS_PAP,LAST_PAP_DATE ,PAP_RESULT,
                IS_MAM,LAST_MAM_DATE,MAM_RESULT,LAST_PERIOD_DATE ,ISCONTRACEPTION,ISCONTRACEPTIONOPT,ISIUCD,ISSTERILISED,ISSTERILISEDOPT,
                patient_id,encounter_id from EMRFemalePatientDetails

SELECT * FROM EMRFREXAMDATA WHERE PATIENT_ID=802592

SELECT * FROM EMRFREXAMANS WHERE FR_EXAM_DATA_ID=9652 WHERE DISP_LABEL='RESULT'

SP_HELP emrfemalepatientdetails


SP_HELP emrfemalepatientdetails.

this is my stored procedure.please make a help on this as in datafields like LAST_MAM_DATE,LAST_PAP_DATE,LAST_PERIOD_DATE


my recors are inseted like this.

17524 12887 1000 1000 01-01-1900 Date
17525 12888 1000 1000 01-01-1900 Date
17526 12889 1000 1000 01-01-1900 Date
17527 12890 1000 1000 01-01-1900 Date


this formated records not to insert
Go to Top of Page

madhivanan
Premature Yak Congratulator

22864 Posts
Posted - 2009-12-14 : 09:08:56
You need to add this at where clause


and date_col is not null

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

rajasekhar857
Constraint Violating Yak Guru

396 Posts
Posted - 2009-12-14 : 09:15:07
HI IS IT CORRECT PLEASE

Declare C1 Cursor Static for Select IS_MENARCH,isnull(MENARCH_AGE,'') AS MENARCH_AGE,IS_MENOPAUSE,isnull(MENOPAUSE_AGE,'') AS MENOPAUSE_AGE,IS_PAP,ISNULL(RTRIM(LAST_PAP_DATE),'') AS LAST_PAP_DATE,ISNULL(PAP_RESULT,'') AS PAP_RESULT,
IS_MAM,ISNULL(RTRIM(LAST_MAM_DATE),'') AS LAST_MAM_DATE,ISNULL(MAM_RESULT,'') AS MAM_RESULT,ISNULL(RTRIM(LAST_PERIOD_DATE),'') AS LAST_PERIOD_DATE,ISCONTRACEPTION,ISCONTRACEPTIONOPT,ISIUCD,ISSTERILISED,ISSTERILISEDOPT,
patient_id,encounter_id from EMRFemalePatientDetails WHERE LAST_PERIOD_DATE IS NOT NULL OR LAST_PAP_DATE IS NOT NULL OR LAST_MAM_DATE IS NOT NULL
Go to Top of Page

madhivanan
Premature Yak Congratulator

22864 Posts
Posted - 2009-12-14 : 09:16:37
quote:
Originally posted by rajasekhar857

HI IS IT CORRECT PLEASE

Declare C1 Cursor Static for Select IS_MENARCH,isnull(MENARCH_AGE,'') AS MENARCH_AGE,IS_MENOPAUSE,isnull(MENOPAUSE_AGE,'') AS MENOPAUSE_AGE,IS_PAP,ISNULL(RTRIM(LAST_PAP_DATE),'') AS LAST_PAP_DATE,ISNULL(PAP_RESULT,'') AS PAP_RESULT,
IS_MAM,ISNULL(RTRIM(LAST_MAM_DATE),'') AS LAST_MAM_DATE,ISNULL(MAM_RESULT,'') AS MAM_RESULT,ISNULL(RTRIM(LAST_PERIOD_DATE),'') AS LAST_PERIOD_DATE,ISCONTRACEPTION,ISCONTRACEPTIONOPT,ISIUCD,ISSTERILISED,ISSTERILISEDOPT,
patient_id,encounter_id from EMRFemalePatientDetails WHERE LAST_PERIOD_DATE IS NOT NULL OR LAST_PAP_DATE IS NOT NULL OR LAST_MAM_DATE IS NOT NULL

Apply it and see if you get the correct result

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

DP978
Constraint Violating Yak Guru

269 Posts
Posted - 2009-12-14 : 09:29:32
"WHERE LAST_PERIOD_DATE IS NOT NULL OR LAST_PAP_DATE IS NOT NULL OR LAST_MAM_DATE IS NOT NULL" Note this will NOT ensure all your dates are non null, if any value is NOT NULL then this statement will return true. If you want to ensure no Date has a null value you need -

WHERE LAST_PERIOD_DATE IS NOT NULL AND LAST_PAP_DATE IS NOT NULL AND LAST_MAM_DATE IS NOT NULL
Go to Top of Page
   

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site