In a persisted field, how do you return the number of occurrences of a column within a different table's column

Question

The following is required due to records being entered by 3rd parties in a web application.

Certain columns (such as Category) require validation including the one below. I have a table OtherTable with the allowed values.

I need to identify how many occurrences (ie: IF) there are of the current table's column's value in a different table's specified column. If there are no occurrences this results in a flagged error '1', if there are occurrences, then it results in no flagged error '0'.

If `Category` can be found in `OtherTable.ColumnA` then return 0 else 1

How can I do this please?

The question is not clear. Your title is about `the number of occurrences`, but it is not clear from the body of the post how exactly you are trying to use that number. (In fact, you aren't mentioning the number of occurrences at all.) — Andriy M, Jul 03 '12 at 11:11
@AndriyM I agree, his example of returning 0 or 1 doesn't match with what he said about count — Manatherin, Jul 03 '12 at 11:11

Tim Schmelter · Answer 1 · 2012-07-03T16:39:32.263

If Category can be found in OtherTable.ColumnA then return 0 else 1

You could use CASE with EXISTS

SELECT CASE WHEN EXISTS(
   SELECT NULL 
   FROM AllowedValues av
   WHERE av.ColumnA = Category
) THEN 0 ELSE 1 END AS ErrorCode
, Category
FROM [Table]

Edit: Here's a sql-fiddle: http://sqlfiddle.com/#!3/55a2e/1

Edit: I've only just noticed that you want to use a computed column. As i've read you can only use it with scalar values and not with sub-queries. But you can create a scalar valued function.

For example:

create table AllowedValues(ColumnA varchar(1));
insert into  AllowedValues Values('A');
insert into  AllowedValues Values('B');
insert into  AllowedValues Values('C');

create table [Table](Category varchar(1));
insert into  [Table]  Values('A');
insert into  [Table]  Values('B');
insert into  [Table]  Values('C');
insert into  [Table]  Values('D');
insert into  [Table]  Values('E');

-- create a scalar valued function to return your error-code
CREATE FUNCTION udf_Category_ErrorCode
(
    @category VARCHAR(1)
)
RETURNS INT

AS BEGIN
    DECLARE @retValue INT

    SELECT @retValue = 
      CASE WHEN EXISTS(
       SELECT NULL 
       FROM AllowedValues av
       WHERE av.ColumnA = @category
    ) THEN 0 ELSE 1 END

    RETURN @retValue
END
GO

Now you can add the column as computed column which uses the function to calculate the value:

ALTER TABLE [Table] ADD ErrorCode AS ( dbo.udf_Category_ErrorCode(Category) )
GO

Here's the running SQL: http://sqlfiddle.com/#!3/fc49e/2

Note: as @Damien_The_Unbelieve has commented at the other answer, even if you persist the result with a UDF, the value won't be updated if the rows in OtherTable change. Just keep that in mind, so you need to update the table manually if desired with the help of the UDF.

Well that code works stand alone (returning the required codes for every single row), but when I place it in a persisted field it throws a "Incorrect Syntax near SELECT and Incorrect Syntax near NULL" — aSystemOverload, Jul 03 '12 at 12:47

score 1 · Accepted Answer · answered Jul 03 '12 at 11:34

1

select mt.*,IFNULL(cat_count.ct,0) as Occurrences from MainTable mt 
left outer join (select ColumnA,count(*) as ct from OtherTable) cat_count 
on mt.Category=cat_count.ColumnA

Result:

mt.col1 | mt.col2 | Category | Occurrences 
###     | ###     | XXX      | 3
###     | ###     | YYY      | 0
###     | ###     | ZZZ      | 1

answered Jul 03 '12 at 11:34

Dojo

5,374
4
49
79

I need code that will go in a persisted field, I do not want (I think) to add additional fields via a query / extended sql statement. – aSystemOverload Jul 03 '12 at 13:50
2

@aSystemOverload - even if you persist e.g. the result of a UDF, the value won't be updated if the rows in `OtherTable` change. – Damien_The_Unbeliever Jul 03 '12 at 14:02
1

@aSystemOverload: How about "persisting" a *query* in the form of a view? That way you wouldn't need to duplicate the same logic for your flag column in various queries, you would only need to join to that view. – Andriy M Jul 03 '12 at 22:22
It was suggested on another site that I use the function method, but that if that persisted field was SELECTed, there would be a performance hit so I think the best idea is to go with a variation on your JOIN method. Thanks. – aSystemOverload Jul 04 '12 at 07:13

In a persisted field, how do you return the number of occurrences of a column within a different table's column

2 Answers2