March 2, 2016 at 4:37 pm
Hi, all. I have a table with information on thousands of network nodes where an engineer supplied most of the information. They seem to do a poor job and I need to run numerous simple queries to identify problems. For example, take the [SerialNumber] column. I need to check for spaces, special characters, missing value, a length of x characters or less, etc. I can identify the problems in a WHERE clause with a bunch of ORs, but for each expression, I want to also capture the issue name, like "Serial Number cannot contain spaces." I need to do the same type of analysis for IPAddresses and some other data. The output is a list of potential problems for an analyst to follow-up with.
Question is, what is the proper way to facilitate this without separate queries for each condition combined via UNIONs?
Desired output:
[font="Courier New"]
NodeID Problem Description
23 Serial number contains spaces
439 Serial number less than five charaters
488 Serial number contains an invalid character
2489 Serial number contains spaces
2984 Missing IP Address
3391 Improper/malformed IP Address
[/font]
March 2, 2016 at 4:54 pm
I would just write a function which returns the string you're interested in:
SELECT serial_number,
fn_error_code(serial_number) as err
FROM t
WHERE fn_error_code(serial_number) is not null
If you want to store the code/desc in a table, you could instead have the function return the code, and then use that in your WHERE clause to form the table relationship.
--=cf
March 2, 2016 at 5:44 pm
chuck.forbes (3/2/2016)
I would just write a function which returns the string you're interested in:
SELECT serial_number,
fn_error_code(serial_number) as err
FROM t
WHERE fn_error_code(serial_number) is not null
If you want to store the code/desc in a table, you could instead have the function return the code, and then use that in your WHERE clause to form the table relationship.
--=cf
Right idea but I'd build it as high performance iTVF (inline Table Valued Function), instead.
--Jeff Moden
Change is inevitable... Change for the better is not.
March 2, 2016 at 6:23 pm
OK. I understand the function aspect, but back to my original question, within said function, do I just have a bunch of simple, separate statements, each with their own WHERE clause, all combined by UNIONs? What I'm wondering is the best way to handle, say, twenty different expressions into one output.
March 2, 2016 at 7:05 pm
SmackMule (3/2/2016)
OK. I understand the function aspect, but back to my original question, within said function, do I just have a bunch of simple, separate statements, each with their own WHERE clause, all combined by UNIONs? What I'm wondering is the best way to handle, say, twenty different expressions into one output.
If an element fails more than one test, what do you want for output about that element? Separate rows or one row with, say, comma delimited failures? I recommend the former rather than the latter but you're the one that will need to handle it after the errors are generated.
--Jeff Moden
Change is inevitable... Change for the better is not.
March 2, 2016 at 7:20 pm
Separate rows is fine. I'm not trying to make this overly complicated. Perhaps I'm asking a silly question, and I apologize if so. Below is what I'm talking about. I'm just wondering if this is the most efficient way to accomplish this. I've been working with SQL 2000 for so long, I keep finding more efficient ways of doing things and figured/hoped there might be something for this.
SELECT
Hostname
, SerialNumber
, 'Serial number contains invalid characters' AS ProblemDesc
FROM Nodes
WHERE SerialNumber NOT LIKE '%[0-9]%'
UNION
SELECT
Hostname
, SerialNumber
, 'Missing Serial Number' AS ProblemDesc
FROM Nodes
WHERE SerialNumber IS NULL
UNION
<etc, etc...>
March 2, 2016 at 7:42 pm
SmackMule (3/2/2016)
Separate rows is fine. I'm not trying to make this overly complicated. Perhaps I'm asking a silly question, and I apologize if so. Below is what I'm talking about. I'm just wondering if this is the most efficient way to accomplish this. I've been working with SQL 2000 for so long, I keep finding more efficient ways of doing things and figured/hoped there might be something for this.
SELECT
Hostname
, SerialNumber
, 'Serial number contains invalid characters' AS ProblemDesc
FROM Nodes
WHERE SerialNumber NOT LIKE '%[0-9]%'
UNION
SELECT
Hostname
, SerialNumber
, 'Missing Serial Number' AS ProblemDesc
FROM Nodes
WHERE SerialNumber IS NULL
UNION
<etc, etc...>
You can get some better performance if you use UNION ALL, instead of UNION. UNION does a DISTINCT between sets and that's pretty much a waste of clock cycles for this kind of result set. We might be able to get by with a single pass on the table on this if you need additional performance. How many rows are in the table and how many total checks do you have?
--Jeff Moden
Change is inevitable... Change for the better is not.
March 3, 2016 at 10:14 am
I'm not familiar with the iTVF's, so I'm following my original thread, and the guru's can correct me ...
Inside your function, you can just write T-SQL code, as the SQL statement which calls this will be looping through the records in your table. Note, this only returns back a single issue at a time, though, which may not be what you're after:
create function func_show_errors
(@p_serial_num integer)
returns varchar(100)
as
begin
declare @l_return varchar(100);
set @l_return = case when charindex(' ',@p_serial_num) > 0 then 'contains spaces'
when @p_serial_num is null then 'is null'
else null
end;
return @l_return;
end
go
I would personally prefer the above (even if you're returning a code that you later use in the WHERE clause to form a table relationship) because it's a much shorter codebase if you have 50 or so tests. So while it may not run as efficiently as SQL, and you only get one error back on a single serial number when it has multiple problems, I think sometimes simplicity outweighs an alternative that might be over-built for that particular situation.
So unless I had millions of rows, or I was practicing my T-SQL skills and wanted to build something bigger (like ... building an iTVF, for example, which I need to learn), I would go with something like the above.
March 3, 2016 at 10:28 am
chuck.forbes (3/3/2016)
I'm not familiar with the iTVF's, so I'm following my original thread, and the guru's can correct me ...Inside your function, you can just write T-SQL code, as the SQL statement which calls this will be looping through the records in your table. Note, this only returns back a single issue at a time, though, which may not be what you're after:
create function func_show_errors
(@p_serial_num integer)
returns varchar(100)
as
begin
declare @l_return varchar(100);
set @l_return = case when charindex(' ',@p_serial_num) > 0 then 'contains spaces'
when @p_serial_num is null then 'is null'
else null
end;
return @l_return;
end
go
I would personally prefer the above (even if you're returning a code that you later use in the WHERE clause to form a table relationship) because it's a much shorter codebase if you have 50 or so tests. So while it may not run as efficiently as SQL, and you only get one error back on a single serial number when it has multiple problems, I think sometimes simplicity outweighs an alternative that might be over-built for that particular situation.
So unless I had millions of rows, or I was practicing my T-SQL skills and wanted to build something bigger (like ... building an iTVF, for example, which I need to learn), I would go with something like the above.
iTVFs are really good for performance and would fit this situation well (am at work right now... will try to get something out on this tonight). I also won't justify possible performance issues based on low row counts because someone "in search of" might use whatever is written on something much larger and not know the consequences. It's usually no extra effort to write high performance code that will scale with no worries than it is to write the status quo code.
That being said, you have the right idea... just the wrong vehicle.
--Jeff Moden
Change is inevitable... Change for the better is not.
March 3, 2016 at 10:35 am
Awesome. I look forward to seeing your iTVF code example. Learning ... gooood 🙂
--=cf
March 3, 2016 at 11:10 am
Here's an example on how to work with iTVFs, actually 2.
For more information about iTVFs and how they compare with normal scalar functions, check this article written by Jeff: http://www.sqlservercentral.com/articles/T-SQL/91724/
Here are the examples:
--First Option: One row per error
CREATE FUNCTION iValidateSerialNumber(@p_serial_num varchar(10))
RETURNS TABLE WITH SCHEMABINDING
AS
RETURN
SELECT 'Serial number contains spaces' AS Error WHERE @p_serial_num LIKE '% %'
UNION ALL
SELECT 'Serial number less than five charaters' AS Error WHERE LEN(@p_serial_num) < 5
UNION ALL
SELECT 'Serial number contains an invalid character' AS Error WHERE @p_serial_num LIKE '%[^0-9]%';
GO
--Second Option: One row for all errors
CREATE FUNCTION iValidateSerialNumber2(@p_serial_num varchar(10))
RETURNS TABLE WITH SCHEMABINDING
AS
RETURN
WITH CTE(Error) AS(
SELECT 'Serial number contains spaces' WHERE @p_serial_num LIKE '% %'
UNION ALL
SELECT 'Serial number less than five charaters' WHERE LEN(@p_serial_num) < 5
UNION ALL
SELECT 'Serial number contains an invalid character' WHERE @p_serial_num LIKE '%[^0-9]%'
) -- Concatenation Method explained in: http://www.sqlservercentral.com/articles/comma+separated+list/71700/
SELECT STUFF(( SELECT '; ' + Error
FROM CTE
FOR XML PATH(''), TYPE).value('./text()[1]', 'varchar(max)'), 1, 2, '') AS Error;
GO
--Create Sample Data
CREATE TABLE SerialNumbers(
SerialNumber varchar(10)
)
INSERT INTO SerialNumbers
VALUES( '12345'),
('123'),
('SA123'),
('AE'),
('123 5'),
('841351816')
--Usage examples
SELECT s.SerialNumber,
v.Error
FROM SerialNumbers s
OUTER APPLY iValidateSerialNumber(s.SerialNumber) v;
SELECT s.SerialNumber,
v.Error
FROM SerialNumbers s
OUTER APPLY iValidateSerialNumber2(s.SerialNumber) v;
GO
--Clean my DB
DROP TABLE SerialNumbers;
DROP FUNCTION iValidateSerialNumber;
DROP FUNCTION iValidateSerialNumber2;
By the way, you had the invalid characters validation wrong.
I was going to include a full validation for the ip address, but please confirm that you're working on 2012 or higher.
March 3, 2016 at 7:29 pm
This is beautiful! Exactly what I wanted and so much to follow up on and learn. Thank you *very* much for the info and example. I implemented per your example and it's working great. Now to go read and practice so I understand all this, especially the CTE/STUFF portion.
Again, thanks to all for the help.
March 4, 2016 at 7:26 am
SmackMule (3/3/2016)
This is beautiful! Exactly what I wanted and so much to follow up on and learn. Thank you *very* much for the info and example. I implemented per your example and it's working great. Now to go read and practice so I understand all this, especially the CTE/STUFF portion.Again, thanks to all for the help.
Just be sure to understand what's going on. If you have any questions after reading the articles, be sure to ask.
By the way, if this is something unique, you don't need a function.
SELECT s.SerialNumber,
v.Error
FROM SerialNumbers s
OUTER APPLY (SELECT 'Serial number contains spaces' AS Error WHERE s.SerialNumber LIKE '% %'
UNION ALL
SELECT 'Serial number less than five charaters' AS Error WHERE LEN(s.SerialNumber) < 5
UNION ALL
SELECT 'Serial number contains an invalid character' AS Error WHERE s.SerialNumber LIKE '%[^0-9]%') v;
March 4, 2016 at 7:44 am
Luis Cazares (3/3/2016)
Here's an example on how to work with iTVFs, actually 2.For more information about iTVFs and how they compare with normal scalar functions, check this article written by Jeff: http://www.sqlservercentral.com/articles/T-SQL/91724/
Here are the examples:
--First Option: One row per error
CREATE FUNCTION iValidateSerialNumber(@p_serial_num varchar(10))
RETURNS TABLE WITH SCHEMABINDING
AS
RETURN
SELECT 'Serial number contains spaces' AS Error WHERE @p_serial_num LIKE '% %'
UNION ALL
SELECT 'Serial number less than five charaters' AS Error WHERE LEN(@p_serial_num) < 5
UNION ALL
SELECT 'Serial number contains an invalid character' AS Error WHERE @p_serial_num LIKE '%[^0-9]%';
GO
--Second Option: One row for all errors
CREATE FUNCTION iValidateSerialNumber2(@p_serial_num varchar(10))
RETURNS TABLE WITH SCHEMABINDING
AS
RETURN
WITH CTE(Error) AS(
SELECT 'Serial number contains spaces' WHERE @p_serial_num LIKE '% %'
UNION ALL
SELECT 'Serial number less than five charaters' WHERE LEN(@p_serial_num) < 5
UNION ALL
SELECT 'Serial number contains an invalid character' WHERE @p_serial_num LIKE '%[^0-9]%'
) -- Concatenation Method explained in: http://www.sqlservercentral.com/articles/comma+separated+list/71700/
SELECT STUFF(( SELECT '; ' + Error
FROM CTE
FOR XML PATH(''), TYPE).value('./text()[1]', 'varchar(max)'), 1, 2, '') AS Error;
GO
--Create Sample Data
CREATE TABLE SerialNumbers(
SerialNumber varchar(10)
)
INSERT INTO SerialNumbers
VALUES( '12345'),
('123'),
('SA123'),
('AE'),
('123 5'),
('841351816')
--Usage examples
SELECT s.SerialNumber,
v.Error
FROM SerialNumbers s
OUTER APPLY iValidateSerialNumber(s.SerialNumber) v;
SELECT s.SerialNumber,
v.Error
FROM SerialNumbers s
OUTER APPLY iValidateSerialNumber2(s.SerialNumber) v;
GO
--Clean my DB
DROP TABLE SerialNumbers;
DROP FUNCTION iValidateSerialNumber;
DROP FUNCTION iValidateSerialNumber2;
By the way, you had the invalid characters validation wrong.
I was going to include a full validation for the ip address, but please confirm that you're working on 2012 or higher.
Luis, you are "da MAN"! Thank you for the outstanding cover on this.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply