Inserting a 'Comment' when a query returns no recs

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

SQL Server Development (2000)

Inserting a 'Comment' when a query returns no recs

Author

Topic

IDWY
Starting Member

6 Posts

Posted - 2005-04-11 : 10:29:44

I need a function which will look through a list of descriptions in a table, and when it doesn't find a this one particular description, insert onto a new line of the table 'No Problem with.....' etc.

I have tried an insert into query, with a where statement but because the table has a number of descriptions which dont match the particular description, it starts inserting a number of comments onto new line. I only want it to insert one comment rather than a number of comments. Have i explained this enough?

Can this be done?

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-04-11 : 10:40:42

>>Have i explained this enough?
No

>>Can this be done?
anything CAN be done

Post some sample data and expected results based on several examples.

Be One with the Optimizer
TG

SamC
White Water Yakist

3467 Posts

Posted - 2005-04-11 : 10:47:06

quote:
Have i explained this enough?

I haven't a clue, but based on what little I gleaned, this seems to fit (or not)


IF NOT EXISTS (SELECT 1 FROM MyTable WHERE Descriptions <> SomeDescriptionValue) 
   AND NOT EXISTS (SELECT 1 FROM MyTable WHERE Mycolumn = 'No Problem with.....')BEGIN
  INSERT INTO MyTable (Mycolumn)
    SELECT 'No Problem with.....'
END

IDWY
Starting Member

6 Posts

Posted - 2005-04-11 : 10:56:10

ok the table is set out like...

TodaysDate Description RecordCount MonthNumber
11/04/2005 'Number records Match' 20 3
11/04/2005 'Number records Null' 25 3
11/04/2005 'Number records Missing 10 3
11/04/2005 'Customer Codes not matching' 2 3

ok this is a report produced in SQL, which compares the data between two tables. When the two tables are compared, this report counts the matching records and the nulls in the table and displayed as shown above.

ok so lets say the 'number of records missing' for the next month was '0' This would not be displayed on the report. The report only displays something if it can count how many times a missing record appears. So then nothing would be displayed for 'number of records missing' . It would just not appear on the report. What i then want to do is run a query on the report, which will scan for the Description 'Number records Missing' and when it doesn't appear in the report, insert onto a seperate line...'No records missing'

example

TodaysDate Description RecordCount MonthNumber
11/04/2005 'Number records Match' 20 3
11/04/2005 'Number records Null' 25 3
11/04/2005 'Customer Codes not matching' 2 3

looks up the description...finds that Number Records missing is not listed...

Then inset comment onto new line

TodaysDate Description RecordCount MonthNumber
11/04/2005 'Number records Match' 20 3
11/04/2005 'Number records Null' 25 3
11/04/2005 'Customer Codes not matching' 2 3
11/04/2005 'NO MISSING RECORDS' 0 3

you get what i mean. I have tried to do this with an insert into statement followed by a WHERE statement, however because there are 3 records which dont match the statement 'Number records missing', its inserting the comment for each time a description doesn't match. I only want it to insert a comment the once.

IDWY
Starting Member

6 Posts

Posted - 2005-04-11 : 11:42:59

SAMC. I have managed to get you script working, however, even when the table has the description in, it still inserts the comment onto the new line. Which part of the script, would now stop it inserting the comment when the particular description is listed!!!!

Thanx

IDWY
Starting Member

6 Posts

Posted - 2005-04-11 : 11:48:01

ALL WORKING...THANX FOR THE HELP!!!!!! :o)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources