Subquery structuring

1

I am having trouble with my search and as I am just starting sql I would like to use a subquery rather than something more advanced.

In my table I want to find row 6 only. That means test_case = 169, pass_fail = fail and fail reason contains 'init' and pressure_low but not pressure_high. So row 4 must be excluded. I figured I would need a subquery to do 2 passes; the first to find 'init' and 'pressure' and a 2nd to exclude 'pressure_high'.

I think I have the conditions right. Table name can be tab1.

Thanks,
JH

Conditions:
test_case = 169
pass_fail = 'fail'
fail_reason like '%init%'
fail_reason like '%pressure_low%'
fail_reason not like '%pressure_high%'

Table tab1table

2 
WHERE 
	test_case = 169
	AND pass_fail='fail'
	AND REPLACE(','+fail_reason+',',' ','') LIKE '%,init,%' 
	AND REPLACE(','+fail_reason+',',' ','')  LIKE '%,pressure_low,%'
	AND REPLACE(','+fail_reason+',',' ','')  NOT LIKE '%,pressure_high,%';
3

I'd split the combined string so you can do more natural comparisons. You really should get rid of the spaces between values in the "fail_reason" column: it's wasted space and they force an LTRIM() before the values can be compared.

dbo.DelimitedSplit8K is a "standard" SQL splitter, you can Google it if you don't have it, or use whatever string splitter you prefer, of course. For example, on SQL 2016+, you can use STRING_SPLIT.

create table #tab1 (
    row int not null,
    test_case int not null,
    pass_fail char(4) not null,
    fail_reason varchar(500) null
    )
insert into #tab1 values
(1,169,'pass','start, none'),
(2,111,'fail','start, protect_fail'),
(3,118,'pass','run, none'),
(4,169,'fail','init, pressure_high, pressure_low'),
(5,169,'fail','start, protect_fail'),
(6,169,'fail','init, pressure_low')

SELECT *
FROM #tab1 t1
CROSS APPLY (
    SELECT 
        MAX(CASE WHEN LTRIM(ds.Item) = 'init' THEN 1 ELSE 0 END) AS has_init,
        MAX(CASE WHEN LTRIM(ds.Item) = 'pressure_low' THEN 1 ELSE 0 END) AS has_pressure_low,
        MAX(CASE WHEN LTRIM(ds.Item) = 'pressure_high' THEN 1 ELSE 0 END) AS has_pressure_high
    FROM dbo.DelimitedSplit8K(t1.fail_reason, ',') ds
) AS ca1
WHERE has_init = 1 AND 
    has_pressure_low = 1 AND
    has_pressure_high = 0

drop table #tab1
4

Thank you both for your replies. They give me some research to do...

Is it not possible to do this with SELECT, FROM, WHERE, NOT, LIKE, '%', and '='?

That would be more understandable for me at this stage.

Also I am embarrassed to report that additional requirements have been thrust upon me after posting, I apologize. Fail reason must also not be pressure_negative, pressure_pulsing. Perhaps pressure_low exclusively is the way to say it. It would seem I could search for %init% and %pressure_% then filter again for pressure_low.

Thanks,
JH

5

That's why I would go with a method that sets each condition separately in an inner query, then test them in the outer query. The conditions to select will always be changing for various requests.

You could put the CROSS APPLY logic for all conditions into a view and then just query against the view with very-easy-to-understand code.

6

Sure, you could do something like:

Select * From #table 
Where test_case = 169 
    And pass_fail = 'fail' 
    And fail_reason like '%init%'
    And fail_reason like '%pressure_low%'
    And fail_reason not like '%pressure_high%'

but it won't perform as well and will have issues if the data changes in subtle ways, say with fail_reason containing init_fail, for example

1 Like