Cont'd - Intelligent SQL Statement

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 2000 Forums

Transact-SQL (2000)

Cont'd - Intelligent SQL Statement

Author

Topic

jamonakes
Starting Member

18 Posts

Posted - 2006-02-22 : 08:12:11

Thanks TG for getting my question right or my attempt at it. Now this is a continuation to TG's answer, which btw answered my question then. Now I want to add some more intelligence to the process. I want the code to check before committing a cellNo to the qryCellNo column if the cellNo is already existing, and if it exists, it checks if qryRecordCount - qryRecsToShow > 1. If this is true, then it updates the already existing record's qryM column with M if the Msg variable is M or updates qryRecNo column with an integer if Msg is a valid integer. If qryRecordCount - qryRecsToShow <= 1, it inserts the cellNo Msg etc etc as a new record. Below is the answer from TG which shed some light to me, thanks TG.

The table structure relevant fields are:

qryCellNo, qryMsg, qryM, qryRecNo, qryRecsToShow, qryRecordCount


jamonakes,
I am not sure I understand your question. But it sounds like either a simple IF block or CASE statement could do it:

if msg = 'M'
begin
INSERT tblQueryDetails (qryCellNo, qryM, isSeen, qryRecsToShow, qryRecordCount)
Values('<telno>', '<msg>', '0','0','0')
end
else
begin
INSERT tblQueryDetails (qryCellNo, qryMsg, isSeen, qryRecsToShow, qryRecordCount)
Values('<telno>', '<msg>', '0','0','0')
end

--OR maybe this:

INSERT tblQueryDetails (qryCellNo, qryMsg, qryM, isSeen, qryRecsToShow, qryRecordCount)
select '<telno>'
,case when msg = 'M' then null else '<msg>' end
,case when msg = 'M' then '<msg>' else null end
,'0'
,'0'
,'0'

Srinika,
u r 1 2 talk about difficult 2 read styles. People in gl(|) houses should not throw stones.

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2006-02-22 : 09:06:57

Why did you start a new topic?

jamonakes
Starting Member

18 Posts

Posted - 2006-02-22 : 11:57:29

I have pasted the following code to show what I intend to have it do. Someone please check the syntax and advise me. Kind regards, Mode Vigilante


If (SELECT count(*) FROM tblQueryDetails where qryCellNo = '<telno>' AND qryIsFinalized = 0) > 0  --0 or more still open records exists
	If (SELECT count(*) FROM tblQueryDetails where qryCellNo = '<telno>' AND qryIsFinalized = 0) = 1 --only 1 non-finalized record exists
		If <msg> = 'M' or <msg> = 'm' 
			Update tblQueryDetails SET qryM = '<msg>', qryMprocessed = '0' WHERE qryCellNo = '<telno>'
		Else
			If isNumeric('<msg>') 
				Update tblQueryDetails SET qryRecNo = '<msg>', qryRecNoProcessed = '0' WHERE qryCellNo = '<telno>'
			Else
				INSERT tblQueryDetails (qryCellNo, qryMsg, qryDateCommitted, isSeen, qryRecsToShow, qryRecordCount, isFinalized) 
				Values('<telno>', '<msg>', GetDate(), '0','0','0','0')
			End If
		End If
	Else  --more than non-finalized record exists finalize all in DB and insert a new one
		begin
			Update tblQueryDetails SET qryIsFinalized = 1 Where qryCellNo = '<telno>'
			INSERT tblQueryDetails (qryCellNo, qryMsg, qryDateCommitted, isSeen, qryRecsToShow, qryRecordCount)
			Values('<telno>', '<msg>', GetDate(), '0','0','0')
		end
	End If	
Else  --no record found which has that cellno or is still active, so it is safe to add a new record
	
		begin			 
			INSERT tblQueryDetails (qryCellNo, qryMsg, qryDateCommitted, isSeen, qryRecsToShow, qryRecordCount)
			Values('<telno>', '<msg>', GetDate(), '0','0','0')
		end	
		
End If

jamonakes
Starting Member

18 Posts

Posted - 2006-02-23 : 03:01:06

Is there a more effecient way of doing it better than this?


DECLARE @strMsg varchar(160), @strTelNo int
SET @strMsg = '<msg>'
SET @strTelNo = '<telno>'
If (SELECT count(*) FROM tblQueryDetails where qryCellNo = @strTelNo AND qryIsFinalized = 0) > 0  --0 or more still open records exists
	If (SELECT count(*) FROM tblQueryDetails where qryCellNo = @strTelNo AND qryIsFinalized = 0) = 1 --only 1 non-finalized record exists
		If @strMsg = 'M' or @strMsg = 'm'
			BEGIN
				Update tblQueryDetails SET qryM = @strMsg, qryMprocessed = '0' WHERE qryCellNo = @strTelNo
			END
		Else
			If (SELECT isNumeric(@strMsg)) = 0
				BEGIN
					Update tblQueryDetails SET qryRecNo = @strMsg, qryRecNoProcessed = '0' WHERE qryCellNo = @strTelNo
				END
			Else
				BEGIN
					INSERT tblQueryDetails (qryCellNo, qryMsg, qryDateCommitted, isSeen, qryRecsToShow, qryRecordCount, isFinalized)
					Values(@strTelNo, @strMsg, GetDate(), '0','0','0','0')
				END

	Else  --more than non-finalized record exists finalize all in DB and insert a new one
		BEGIN
			Update tblQueryDetails SET qryIsFinalized = 1 Where qryCellNo = @strTelNo
			INSERT tblQueryDetails (qryCellNo, qryMsg, qryDateCommitted, isSeen, qryRecsToShow, qryRecordCount)
			Values(@strTelNo, @strMsg, GetDate(), '0','0','0')
		END

Else  --no record found which has that cellno or is still active, so it is safe to add a new record

		BEGIN
			INSERT tblQueryDetails (qryCellNo, qryMsg, qryDateCommitted, isSeen, qryRecsToShow, qryRecordCount)
			Values(@strTelNo, @strMsg, GetDate(), '0','0','0')
		END

jamonakes
Starting Member

18 Posts

Posted - 2006-02-23 : 12:57:06

I finally managed to sorted it out like this and it works. Is there a way of doing it more effeciently?


DECLARE @strMsg varchar(160), @strTelNo varchar(50)
SET @strMsg = '<msg>'
SET @strTelNo = '<telno>'

If (SELECT count(*) FROM tblQueryDetails where qryCellNo = @strTelNo AND qryIsFinalized = 0) > 0  --1 or more still open records exists
	If (SELECT count(*) FROM tblQueryDetails where qryCellNo = @strTelNo AND (qryIsFinalized = 0 or qryIsFInalized IS NULL)) = 1 --only 1 non-finalized record exists
		If @strMsg = 'M' or @strMsg = 'm' -- The one record's message variable contains M
			BEGIN
				Update tblQueryDetails SET qryM = @strMsg, isSeen = '0', qryMprocessed = '0' WHERE qryCellNo = @strTelNo AND (qryIsFinalized = 0 or qryIsFInalized IS NULL)
			END
		Else
			If (SELECT isNumeric(@strMsg)) <> 0 --The one record's message variable contains a number
				BEGIN
					Update tblQueryDetails SET qryRecNo = @strMsg, isSeen = '0', qryRecNoProcessed = '0' WHERE qryCellNo = @strTelNo AND (qryIsFinalized = 0 or qryIsFInalized IS NULL)
				END
			Else --The one record's message variable contains a new word
				BEGIN
					Update tblQueryDetails SET qryIsFinalized = 1 Where qryCellNo = @strTelNo AND (qryIsFinalized = 0 or qryIsFInalized IS NULL)
					INSERT tblQueryDetails (qryCellNo, qryMsg, qryDateCommitted, isSeen, qryRecsShown, qryRecordCount, qryMProcessed, qryRecNoProcessed, qryIsFinalized)
					Values(@strTelNo, @strMsg, GetDate(), '0','0','0','0','0','0')
				END

	Else  --more than non-finalized record exists finalize all in DB and insert a new one
		BEGIN
			Update tblQueryDetails SET qryIsFinalized = 1 Where qryCellNo = @strTelNo
			INSERT tblQueryDetails (qryCellNo, qryMsg, qryDateCommitted, isSeen, qryRecsShown, qryRecordCount, qryMProcessed, qryRecNoProcessed, qryIsFinalized)
			Values(@strTelNo, @strMsg, GetDate(), '0','0','0','0','0','0')
		END

Else  --no record found which has that cellno or is still active, so it is safe to add a new record

	BEGIN
		INSERT tblQueryDetails (qryCellNo, qryMsg, qryDateCommitted, isSeen, qryRecsShown, qryRecordCount, qryMProcessed, qryRecNoProcessed, qryIsFinalized)
		Values(@strTelNo, @strMsg, GetDate(), '0','0','0','0','0','0')
	END

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources