February 20, 2019 at 4:46 am
Hi, the brief is to:
use sql to pull a distinct list of values together and then to validate a set of data against the list.
SQLSELECT STUFF(( SELECT ',''' + Booking_Type_Code_CTM + CHAR(39)
FROM DIM_Product FOR XML PATH('') ), 1, 1, '') AS List
this gives me:
'C','E','F','GC','H','HX','I','K','P','Q','R','T','V','Z'
which I assign to the variable @valivalue
I then say:
case when @valiValue is null then 'N/A' else
CASE WHEN rtrim(@VALUE) IN (@valiValue) THEN
'PASS' else 'FAIL' END END as Validation_Result,
This works in so far as the list gets passed to the variable but using that variable as part of in(@valivalue) doesn't work and simply returns FAIL 100% of the time.
Can anyone give me any advice please?
Cheers,
Dave
February 20, 2019 at 5:00 am
case when @valiValue is null then 'N/A' else
CASE WHEN CHARINDEX(''''+RTRIM(@VALUE)+'''', @valiValue) > 0 THEN
'PASS' else 'FAIL' END END as Validation_Result,
February 20, 2019 at 5:03 am
Jonathan AC Roberts - Wednesday, February 20, 2019 5:00 AMcase when @valiValue is null then 'N/A' elseCASE WHEN CHARINDEX(''''+RTRIM(@VALUE)+'''', @valiValue) > 0 THEN
'PASS' else 'FAIL' END END as Validation_Result,
I love you!!
Perfect answer many thanks,
Dave
February 20, 2019 at 7:01 am
I don't understand why you simply don't validate the data before you concatenate it.
--Jeff Moden
Change is inevitable... Change for the better is not.
February 20, 2019 at 7:27 am
Jeff Moden - Wednesday, February 20, 2019 7:01 AMI don't understand why you simply don't validate the data before you concatenate it.
Because it's pulling a dynamic list of values from the dimension so there may be new codes added at any time therefore rather than have a list of hardcoded values to validate against, this allows for any new values to be automatically tested against.
It is part of a much bigger validation routine that happens before the data gets anywhere near the DW.
If you're interested in the full script,
Feel free to critique (nicely please)
PS it runs on 10,000 rows in about 20 seconds so processing speed is more than adequate.
Cheers,
Dave
--declare variables--
Declare @tablename varchar(1000)
Declare @colName varchar(1000)
Declare @order_int int
declare @Mandatory varchar(3)
declare @data_type varchar(50)
declare @length int
declare @SQL varchar(1000)
declare @value varchar(1000)
DECLARE @result TABLE ( Value VARCHAR (1000) )
declare @sql_tbl table (value Varchar(max))
declare @vali_tbl table (value varchar(max))
declare @row_num int
declare @row_num_var varchar(3)
declare @total_rows int
declare @total_rows_var varchar(max)
declare @max_orderint int
declare @outcome varchar(4)
declare @valiColName varchar(max)
declare @valiColSQL varchar(max)
declare @valiValue varchar(max)
--set inital varaiable values--
set @total_rows = (select COUNT(*) from [002_CGDS_VALIDATION].dbo.[002_Vali_Data])
set @total_rows_var = CAST(@total_rows as varchar(max))
set @row_num = 1
set @row_num_var = CAST(@row_num as varchar(3))
Set @tablename = '[002_CGDS_VALIDATION].dbo.[002_Vali_Data]'
set @max_orderint = (select COUNT(Column_name)
from INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME in ('Fact_Corelines_Staging')
and COLUMN_NAME not in ('UIDENT','LOAD_DATE','UPDATED_DATE','CHECK_SUM','RECORD_ID'))
Set @order_int = 1
--loop round columns of @tableName--
while @order_int <= @max_orderint
begin
set @row_num = 1
set @row_num_var = CAST(@row_num as varchar(3))
Set @colName = (select Column_Name from
(select
COLUMN_NAME,
ROW_NUMBER() over (partition by TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME order by table_Name asc) as [Order_Ident]
from
INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME in ('Fact_Corelines_Staging')
and COLUMN_NAME not in ('UIDENT','LOAD_DATE','UPDATED_DATE','CHECK_SUM','RECORD_ID')) as Admins
where admins.order_ident = @order_int)
Set @length = (SELECT Length from
(select
CHARACTER_MAXIMUM_LENGTH as 'LENGTH',
ROW_NUMBER() over (partition by TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME order by table_Name asc) as [Order_Ident]
from
INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME in ('Fact_Corelines_Staging')
and COLUMN_NAME not in ('UIDENT','LOAD_DATE','UPDATED_DATE','CHECK_SUM','RECORD_ID')) as Admins
where admins.order_ident = @order_int)
Set @data_type = (SELECT Data_Type from
(select
Data_Type,
ROW_NUMBER() over (partition by TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME order by table_Name asc) as [Order_Ident]
from
INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME in ('Fact_Corelines_Staging')
and COLUMN_NAME not in ('UIDENT','LOAD_DATE','UPDATED_DATE','CHECK_SUM','RECORD_ID')) as Admins
where admins.order_ident = @order_int)
set @Mandatory = (SELECT Mandatory from
(select
case when(is_nullable = 'NO') then 'YES' ELSE 'NO' END as 'Mandatory',
ROW_NUMBER() over (partition by TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME order by table_Name asc) as [Order_Ident]
from
INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME in ('Fact_Corelines_Staging')
and COLUMN_NAME not in ('UIDENT','LOAD_DATE','UPDATED_DATE','CHECK_SUM','RECORD_ID')) as Admins
where admins.order_ident = @order_int)
-----get validation list---------------------------------------------------------------------------------
set @valiColName = '';
set @valiColName = (SELECT
[Validates_From]
FROM [002_CGDS_VALIDATION].[dbo].[002_Vali_Validation_Lists] where Column_Name = @colName)
set @valiColSQL = (Select SQL from [002_CGDS_VALIDATION].[dbo].[002_Vali_Validation_Lists] where Column_Name = @ColName)
insert into @vali_tbl
exec sp_sqlexec @valicolSQL
set @valiValue = '';
set @valiValue = (SELECT TOP 1 Value FROM @vali_tbl)
delete from @vali_tbl
-------end get validation list ----------------------------------------------------------------------------
--loop rows of current @colname
While @row_num <= @total_rows
Begin
Set @SQL = 'Select ' + @colName +
' from (select ' + @colName +
' ,row_number() over (order by booking_type_key) AS [id]
from ' + @tablename + ') as SQL_TBL' +
' where id = ' + @row_num_var
Insert into @result
EXEC sp_sqlexec @sql
SET @Value='';
SET @Value = (SELECT TOP 1 Value FROM @result)
DELETE FROM @result
insert into temp_results(row_num,value,Length_result, Column_Name,Validation_Result,Mandatory_Result, Type_Result)
select @row_num,
@value,
case when len(( @value )) > @length
then 'FAIL' else 'PASS' END as length_result,
@colName,
case when @valiValue is null then 'N/A' else
CASE WHEN CHARINDEX(''''+RTRIM(@VALUE)+'''', @valiValue) > 0 THEN
'PASS' else 'FAIL' END END as Validation_Result,
case when (@value IS null and @Mandatory = 'NO') then 'PASS'
when (@value IS not null) then 'PASS'
when (@value IS null and @Mandatory = 'YES') then 'FAIL'
END as Mandatory_Result,
case when SQL_VARIANT_PROPERTY(@value, 'BaseType') = @data_type then 'PASS'
Else 'FAIL' END as type_result
--where
--case when len(( @value )) > @length
--then 'FAIL' else 'PASS' END = 'FAIL'
Set @row_num = @row_num +1
set @row_num_var = CAST(@row_num as varchar(3))
END
set @order_int = @order_int +1
End
February 20, 2019 at 11:21 am
If @value is a single value -- and it seems like it would have to be only a single value -- then wouldn't it be simpler and more efficient to just use something like this?:
...WHEN EXISTS(SELECT 1 FROM DIM_Product WITH (NOLOCK) /*if preferred*/ WHERE Booking_Type_Code_CTM = @Value) THEN ... ELSE ...
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply