Select Query - Restrict records if all columns are NULL 1

[BxWill]

Using the sql script below, I have over 50,000 records in the result set of which over 30,000 records only display "NULL" in seven columns; GT#_Identifier, Inspector_Name, Ancillary, Geography, Sequence, OverConsult, List.

My objective: Filter out all records that have a "NULL" in the above-mentioned seven columns.

What revisions to the sql script will allow the filtering out of all records that have a "NULL" in the seven fields?

Not yet able to resolve this and appreciate any insight.

I encounter the following errors;

Errors using Option 1:

Incorrect syntax near the keyword 'is' and the red underline underneath "m.mcase" with the message 'the multi-part identifier "m.mcase" could not be bound"

Error using Option 2:

Option 2: Incorrect syntax near the key word 'is'.

Use test_db

SELECT DISTINCT 
m.mcustomer, 
clname1, 
m.mcase, 
m.mdesc1, 
(select a.value from global a where a.globalindex = '101' and a.Gljoin = m.mcase and a.udtype = 'GT') Invoice_no,
(select a.value from global a where a.globalindex = '25' and a.Gljoin = m.mcase and a.udtype = 'GT') GT#_Identifier,
(select a.value from global a where a.globalindex = '42' and a.Gljoin = m.mcase and a.udtype = 'GT') Inspector_Name,
(select a.value from global a where a.globalindex = '125' and a.Gljoin = m.mcase and a.udtype = 'GT') Ancillary,
(select a.value from global a where a.globalindex = '130' and a.Gljoin = m.mcase and a.udtype = 'GT') Geography,
(select a.value from global a where a.globalindex = '160' and a.Gljoin = m.mcase and a.udtype = 'GT') Sequence,
(select a.value from global a where a.globalindex = '200' and a.Gljoin = m.mcase and a.udtype = 'GT') OverConsult,
(select a.value from global a where a.globalindex = '250' and a.Gljoin = m.mcase and a.udtype = 'GT') List,
(select a.value from global a where a.globalindex = '185' and a.Gljoin = m.mcase and a.udtype = 'GT') Append

  FROM case m
            inner join customer    on m.mcustomer = clnum 
            inner join employee h    on h.id = SupCon 
            inner join employee i    on i.id = OrgCon
            inner join employee b    on b.id = BillCon
            left outer join description mm on mm.mcase = m.mcase and mm.line=1
            left outer join description mn on mn.mcase = m.mcase and mn.line=2
            left outer join description mo on mo.mcase = m.mcase and mo.line=3
            left outer join description mp on mp.mcase = m.mcase and mp.line=4


--Option 1
WHERE

mcustomer = '14258'
AND m.mclosedt is null
and (select a.value from global a where a.globalindex = '25' and a.Gljoin = m.mcase and a.udtype = 'GT') is not null, --GT#_Identifier,
and (select a.value from global a where a.globalindex = '42' and a.Gljoin = m.mcase and a.udtype = 'GT') is not null, --Inspector_Name,
and (select a.value from global a where a.globalindex = '125' and a.Gljoin = m.mcase and a.udtype = 'GT') is not null, --Ancillary,
and (select a.value from global a where a.globalindex = '130' and a.Gljoin = m.mcase and a.udtype = 'GT') is not null, --Geography,
and (select a.value from global a where a.globalindex = '160' and a.Gljoin = m.mcase and a.udtype = 'GT') is not null, --Sequence,
and (select a.value from global a where a.globalindex = '200' and a.Gljoin = m.mcase and a.udtype = 'GT') is not null, --OverConsult,
and (select a.value from global a where a.globalindex = '250' and a.Gljoin = m.mcase and a.udtype = 'GT') is not null, --List,


--Option 2
WHERE 
mcustomer = '14258'
AND m.mclosedt is null 
and (select a.value from global a where (a.globalindex in ('25', '42', '125', '130', '160', '200', '250')
and a.Gljoin = m.mcase and a.udtype = 'GT') is not null

 

[fredericofonseca]

option 1

select *
from (
SELECT DISTINCT 
m.mcustomer, 
clname1, 
m.mcase, 
m.mdesc1, 
(select a.value from global a where a.globalindex = '101' and a.Gljoin = m.mcase and a.udtype = 'GT') Invoice_no,
(select a.value from global a where a.globalindex = '25' and a.Gljoin = m.mcase and a.udtype = 'GT') GT#_Identifier,
(select a.value from global a where a.globalindex = '42' and a.Gljoin = m.mcase and a.udtype = 'GT') Inspector_Name,
(select a.value from global a where a.globalindex = '125' and a.Gljoin = m.mcase and a.udtype = 'GT') Ancillary,
(select a.value from global a where a.globalindex = '130' and a.Gljoin = m.mcase and a.udtype = 'GT') Geography,
(select a.value from global a where a.globalindex = '160' and a.Gljoin = m.mcase and a.udtype = 'GT') Sequence,
(select a.value from global a where a.globalindex = '200' and a.Gljoin = m.mcase and a.udtype = 'GT') OverConsult,
(select a.value from global a where a.globalindex = '250' and a.Gljoin = m.mcase and a.udtype = 'GT') List,
(select a.value from global a where a.globalindex = '185' and a.Gljoin = m.mcase and a.udtype = 'GT') Append

FROM case m
inner join customer on m.mcustomer = clnum 
inner join employee h on h.id = SupCon 
inner join employee i on i.id = OrgCon
inner join employee b on b.id = BillCon
left outer join description mm on mm.mcase = m.mcase and mm.line=1
left outer join description mn on mn.mcase = m.mcase and mn.line=2
left outer join description mo on mo.mcase = m.mcase and mo.line=3
left outer join description mp on mp.mcase = m.mcase and mp.line=4
WHERE mcustomer = '14258'
AND m.mclosedt is null
) t
where GT#_Identifier is not null, --GT#_Identifier,
and Inspector_Name is not null, --Inspector_Name,
and Ancillary is not null, --Ancillary,
and Geography is not null, --Geography,
and Sequence is not null, --Sequence,
and OverConsult is not null, --OverConsult,
and List is not null, --List,

option 2 - just cleaning those horrible correlated queries

SELECT DISTINCT 
m.mcustomer, 
clname1, 
m.mcase, 
m.mdesc1, 
gl.Invoice_no,
gl.GT#_Identifier,
gl.Inspector_Name,
gl.Ancillary,
gl.Geography,
gl.Sequence,
gl.OverConsult,
gl.List,
gl.Append

FROM case m
inner join customer on m.mcustomer = clnum 
inner join employee h on h.id = SupCon 
inner join employee i on i.id = OrgCon
inner join employee b on b.id = BillCon
left outer join description mm on mm.mcase = m.mcase and mm.line=1
left outer join description mn on mn.mcase = m.mcase and mn.line=2
left outer join description mo on mo.mcase = m.mcase and mo.line=3
left outer join description mp on mp.mcase = m.mcase and mp.line=4
left outer join (select a.Gljoin
                      , max(case when a.globalindex = '101' then a.value else null end) as Invoice_no 
                      , max(case when a.globalindex = '25' then a.value else null end) as GT#_Identifier 
                      , max(case when a.globalindex = '42' then a.value else null end) as Inspector_Name 
                      , max(case when a.globalindex = '125' then a.value else null end) as Ancillary 
                      , max(case when a.globalindex = '130' then a.value else null end) as Geography 
                      , max(case when a.globalindex = '160' then a.value else null end) as Sequence 
                      , max(case when a.globalindex = '200' then a.value else null end) as OverConsult 
                      , max(case when a.globalindex = '250' then a.value else null end) as List 
                      , max(case when a.globalindex = '185' then a.value else null end) as Append 
                from global a
                where a.udtype = 'GT'
                  and a.globalindex in ('101', '25', '42', '125', '130', '160', '200', '250', '185')
                group by a.Gljoin
                ) gl 
on gl.Gljoin = m.mcase
WHERE mcustomer = '14258'
AND m.mclosedt is null
) t
where GT#_Identifier is not null, --GT#_Identifier,
and Inspector_Name is not null, --Inspector_Name,
and Ancillary is not null, --Ancillary,
and Geography is not null, --Geography,
and Sequence is not null, --Sequence,
and OverConsult is not null, --OverConsult,
and List is not null, --List,

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

FAQ219-2884
FAQ181-2886